The simplest definition of what is a user right may be, "What users can do and what they can't do." "Here, the simple definition is pretty good.
user permissions are grouped into 3 categories：
L login Permission;
• access to specific databases;
L The right to perform specific operations on specific objects in the database.
Now that we've seen the creation of a login account, we'll focus on the specific permissions that the login account can have.
22.3.1 grant access to a specific database
If you want a user to have access to a database, the first thing you need to do is give the user access to that database. You can do this in Management Studio by adding users to the user members of the server's database node. If you want to add a user with T-SQL, you need to use create user or legacy stored procedure sp_grantdbaccess.
Note that when you create a user in a database, the permissions are actually stored in the database and mapped to that user's server identifier. When you restore a database, you may have to remap the user rights to the server identifier where you restored the database.
1. CREATE USER
The CREATE User command adds a new user to the database. Users can originate from an existing login name, certificate, or asymmetric key, or they can be local users only in the current database. The syntax is as follows:
CREATE User < user name >
[{for | from}
{
Login < Login >
| Certificate < certificate name >
| Asymmetric key < Key name >
}
| Without LOGIN]
[With Default_schema = < mode name >]
For these elements, let's look at what the meaning of some of these elements is:
Options
Description
LOGIN
The login name that you want to grant access to the current database
Certificate
The logical name of the certificate associated with the user. Note that the certificate must have been created using the Create Certificate command
Asymmetric KEY
The logical name of the asymmetric key associated with the user. Note that the key must have been created using the Create asymmetric key command
Without LOGIN
Create users who can only be active in the current database. It can be used to establish a specific security context, but the user cannot map to a login other than the current database or access any other database
With Default_schema
Set up a schema that is not the default "dbo" to be the default mode for the current user
2. sp_grantdbaccess
This is a legacy method for granting access to a login to a specific database. The syntax is as follows:
sp_grantdbaccess [@loginame =] < ' sign-in name ' >[, [@name_in_db =] < ' Alias in database ' >
Note that you are granted access to the current database-that is, you must make sure that the database you want the user to be able to access is the current database when the command was issued. The login name is the actual login ID that is used to log in to SQL Server. Parameter name_in_db allows the user to have another distinguished name. This alias applies only to the database here-all other databases will still use the default name for that login ID, or the alias defined when granting access to the user's database. Defining an alias affects the identity function, such as USER_NAME (). System-level functions, such as SYSTEM_USER, return the underlying login ID.
22.3.2 permissions granted to objects in the database
Well, if the user has a login and is able to access the database you want him or her to access, is it all right now? If it's that simple! Now of course it's not all ready.
SQL Server gives us a fairly granular level of control over what the user can access. Most of the time, some information is expected to be accessible to users, but there are other information in the database that you do not want users to access. For example, you may want a customer service person to be able to view and maintain order information, but you may not want them to look at payroll information randomly. Perhaps, and vice versa-you need human resources staff to be able to edit employee records, but you may not want them to give someone a big discount on the deal.
SQL Server allows you to assign a different set of permissions to a number of different objects in SQL Server. The objects that can be assigned permissions include tables, views, and stored procedures. Triggers implicitly have the permissions of the person who created them.
The user rights on the object are divided into 6 different types.
User Rights
Description
SELECT
Allows the user to "see" the data. If the user has this permission, the user can run the SELECT statement on the table or view to which they are granted permission
INSERT
Allows the user to create new data. A user with this permission can run an INSERT statement. Note that, unlike many systems, having the insert capability does not necessarily mean that you have SELECT permissions
UPDATE
Allows the user to modify existing data. A user with this permission can run the UPDATE statement. Like an INSERT statement, having an update capability does not necessarily mean having a SELECT permission.
DELETE
Allows the user to delete data. A user with this permission can run the DELETE statement. Similarly, having a delete capability does not necessarily imply having
SELECT permission
REFERENCES
There is a FOREIGN KEY constraint referencing another table in the table to insert the row, and the user does not have a SELECT permission on that table, references permission allows the user to insert rows
EXECUTE
Allow the user to execute the specified stored procedure
In a particular table, view, or stored procedure that you are assigning permissions to, you can mix and match these permissions as needed.
You can assign these permissions in Management Studio, and you only need to navigate to the login option on the server's security node. Right-click on the user and select Properties. Depending on whether you are in a database or a security node, the Open dialog box will be different, but in either case, you can get the option to set permissions. Assigning permissions using T-SQL uses three commands, and it is useful to understand that three commands, even if you are only prepared to assign permissions through Management Studio (the terminology is the same).
1. GRANT
Grant assigns the access rights specified on the object to the specified user or role, and the object is the body of the grant statement.
The syntax for the GRANT statement is as follows:
GRANT
All [Privileges] | < permissions >[,... N]
On
< table name or view name >[(< column name >[,... N])]
|< stored procedure or extended stored procedure name >
To < login ID or role name >[,... N]
[With GRANT OPTION]
[As < role name]
The All keyword indicates that you want to grant all permissions that apply to that object type (execute never applies to the table). If you do not use the ALL keyword, you need to provide one or more specific permissions that are specific to the object that you want to grant.
Privileges is a new keyword that provides no actual functionality beyond ANSI-92 compatibility.
The ON keyword is used as a placeholder to illustrate the next object that you want to grant permissions to. Note that if you grant permissions on a table, you can specify permissions at the bottom to the column level by explicitly stating the list of affected columns--if you do not provide a specific column, you think it will affect all of the columns.
Microsoft seems to be doing something superficial about the view of column-level permissions. The ability to say that a user can select on a particular table, but only select on a specific column in the table, which seems cool, however, in the use of column-level permissions and Microsoft's work to implement column-level permissions, it does make security handling too complex. In view of this, the recent literature on the subject and the information I have received from insiders seem to indicate that Microsoft wants to discard column-level security. In use they suggest--if you want to limit the user to seeing only specific columns, consider using the view instead.
The to statement does what you expect-it specifies who you want to grant that access. The permissions can be granted a login ID or a role name.
With GRANT option allows you to grant access to other users as well.
Since this option is used, it will quickly become painful to know who has access to what, so I recommend that you avoid using this option. Of course, you can always go into management Studio to see the permissions on objects, but that's the passive response rather than the proactive approach-you're looking for what's going wrong at the current access level, rather than stopping the unexpected access in advance.
The last, but not least, is the AS keyword. This keyword handles a problem where a login name belongs to multiple roles.
Next, let's look at one or two examples. We'll see later that we've created a Testaccount account that has some access rights based on the members of the public role (all the database users belong to and cannot be removed from). However, there are a large number of items that Testaccount do not have access to (because public is the only role testaccount, so public does not have those permissions).
Start with the Testaccount user login first. Then try a SELECT statement on the region table:
Soon, you will receive a message from SQL Server informing you that you are trying to get to where you should not be visiting.
Login separately with SA-if you want, you can do this by selecting the menu "file" → "connection" in the same Query editor instance. Then, select SQL Server Authentication for the new connection and log in as SA with the correct password. Now execute the GRANT statement:
Next, switch back to the Testaccount connection (keep in mind that the information that connects to the user is displayed in the title bar of the connection window), and then try to execute the SELECT statement: This time, you get a much better result:
Let's continue to try another statement. This time, we run the same tests and commands on the Employeeterritories table:
The statement fails-this is also because you do not have the appropriate permissions, so give the user permissions on the table:
Then, run the SELECT statement again, and everything goes well:
However, to add a little change, try to insert in this table:
SQL Server will immediately let us go-we do not have the necessary permissions, so give the user the appropriate permissions (using the SA Connection):
Now run the INSERT statement again:
Everything is progressing well.
2. DENY
Deny explicitly prevents a user from obtaining the specified access rights on the target object. The key to the deny is that it will overwrite any grant statement. Because a user can belong to more than one role (this is discussed immediately), a user may belong to a role that is granted access, but is also affected by the deny. If the user's personal permissions are mixed with the permissions that are based on the role membership, deny and grant exist at the same time, then the Deny always takes precedence. In short, if a user or any role a user belongs to has a deny on a permission issue, the user will not be able to use the access rights on that object.
The syntax is so complex and varied that it looks like the GRANT statement:
DENY
All [privileges]|< permissions >[,... N]
On
< table name or view name >[(column name [,... N])]
|< stored procedure or extended stored procedure name >
To < login ID or role name >[,... N]
[CASCADE]
Similarly, the all keyword indicates that you want to deny all available permissions on the object type (execute never applies to the table). If you do not use the ALL keyword, you need to provide one or more specific permissions that are specific to the object that you want to deny permissions to.
Privileges is still the new keyword, and there is no real functionality beyond providing ANSI-92 compatibility.
The ON keyword is used as a placeholder to indicate the next object that you want to deny permissions to.
So far, everything is almost the same as the GRANT statement. The CASCADE keyword corresponds to the WITH GRANT option in the GRANT statement. Cascade told SQL Server that if a user granted access to another person under the WITH GRANT option rule, then for all of these people, they would also be denied access.
To run an example on the deny, we try to execute a simple SELECT statement using the testaccount login name:
After the statement is run, approximately 9 records are returned. How does it get access when we never grant Testaccount this permission? The reason is that testaccount belongs to public and public is granted access on employees.
If we don't want Testaccount to be able to access employees. For whatever reason, Testaccount is an exception, and we don't want the user to see that data-we just emit the deny statement (remember to run deny with the sa login):
When you run the SELECT statement again with the Testaccount login, you get an error-you can no longer access it. Also note that because we use the ALL keyword, we also deny Testaccount the INSERT, delete, and update access rights that public has.
Note that the DENY is a new statement in SQL Server 7.0. There is a concept of denying permissions in version 6.5, but the implementation is different. In version 6.5, instead of using deny, the REVOKE statement is issued two times. The new deny keyword makes things clearer.
3. REVOKE
Revoke will eliminate the effects of previously issued grant or deny statements. You can think of this statement as a targeted "undo" statement.
The REVOKE syntax mixes the GRANT and DENY statements:
REVOKE [GRANT OPTION for]
All [Privileges] | < permissions >[,... N]
On
< table name or view name >[(column name [,... N])]
|< stored procedure or extended stored procedure name >
to | From < login ID or role name >[,... N]
[CASCADE]
[As < role name]
In fact, the instructions here are the same as the description of the grant and deny statements--however, I'll tell you again here, lest you go through the book to quickly find instructions about revoke.
Similarly, the all keyword indicates that you want to undo all the available permissions on the object type. If you do not use the ALL keyword, you need to provide one or more specific permissions that are specific to the permissions that the object wants to revoke.
In addition to providing ANSI-92 compatibility, privileges still has no practical effect.
The ON keyword is used as a placeholder to indicate the next object that you want to revoke permissions for.
The CASCADE keyword corresponds to the WITH GRANT option in the GRANT statement. Cascade told SQL Server that if a user granted access to another person under the WITH GRANT option rule, all of those granted permission would also revoke their access rights.
Similarly, the AS keyword is used only to indicate which role you want to send this command on.
We use the SA connection to revoke the grant access to the region table in Northwindsecure.
After the statement is executed, Testaccount will no longer be able to run the SELECT statement on the region table.
To undo the deny, we also issue a revoke statement. This time, you will regain access to the Employees table:
Now that we understand how all of these commands control access to individual users, let's look at a method that greatly simplifies the management of these permissions through group management.
22.3.3 permission for user rights and statement levels
User License permissions are not limited to objects in the database-they can also be extended to some other statements that are not tied directly to any particular object. SQL Server allows you to control permission permissions to run several different statements, including:
L CREATE DATABASE;
L CREATE DEFAULT;
L CREATE PROCEDURE;
l CREATE rule;
L CREATE TABLE;
L CREATE VIEW;
L BACKUP DATABASE;
L BACKUP LOG.
So far, we've seen all of these commands in operation except for two backup commands. (What the backup commands do is self-explanatory, so we're not going to spend time on it right now, and we'll discuss them in chapter 24th-Just remember, they're things you can control at the statement level.) ）
So how do we assign these license permissions? In fact, now that you've seen the operation of Grant, REVOKE, and deny for objects, you already have a pretty good idea of permission at the statement level. They are basically the same as the permission permissions at the object level, except that they are simpler (you don't have to fill in so many things). The syntax is as follows:
GRANT <all | statement [,... n]> to < login id>[,... N]
It's simple, isn't it? Next, verify that our test users do not have the authority to execute the create for a quick trial. Be sure to log in as Testaccount, and then run the following command (in the following statement, do not forget to convert Aristotle to your domain name):
It is completely impracticable for us to run the above command:
Now log in to SQL Server using the SA account (or other account with northwindsecure dbo power). Then, run the command to grant permission:
You will receive a confirmation message that the command was executed successfully. Then, try running the Create statement again (remember to log in with Testaccount):
This time everything goes well.
Deny and revoke work the same way on permission permissions at the object level.
