-
When setting permissions, regardless of user or role, they are the objects for setting permissions. Except that application roles are special and have mutually exclusive relationships with other roles and users, database roles and members have a very close relationship in terms of permissions. A user may belong to multiple database roles at the same time, and each role may have different permissions. At this time, the principle for users to obtain actual permissions is "union" unless there is a deny (forbidden) setting. For example, role A is prohibited from reading data table B, but role C can read data table
B, at this time, the user who belongs to roles A and C is still unable to read data table B because he has been banned.
The objects of permissions can be divided into servers and databases, that is, permissions can be set separately for these two, please see the following explanation.
Specify server permissions (1) Right-click on the server in the Object Explorer and select "Properties".
(2) Switch to "Permissions" in the dialog box.
(3) Select the object for which permissions are to be set in "Login Name or Role", and check authorization respectively in "Explicit Permissions".
After completion, click the "OK" button, as shown in Figure 15-21.
As shown in Figure 15-21, it is the permission setting for the server. The "authorizer" is the one currently logged in to SQL Server
The login name of the server; "Permissions" are the permissions that can be set by all current login names; "Grant" means granting permissions; if "Have Grant" is checked, it means that sa grants permissions to the selected object and can then grant them to other logins; "Reject" It's just forbidden to use.
The selection of these three options is related. If "Deny" is checked, "Grant" and "Have Grant" will be automatically cleared; if "Have Grant" is checked, "Reject" will be cleared and "Grant" will be selected.
"Login name or role" is the object for which permissions are set. Readers can click the "Add" button to specify other login names or roles. However, all built-in server roles cannot change permissions, but permissions can be specified for the public role.
To view the permissions of the currently selected login or role, click the Effective Permissions button, as the effective permissions may not only be set in Figure 15-21, but also depend on the role to which the login belongs. Therefore, Figure 15-21 will only display the permissions defined in this setting, and the default existing permissions will not be displayed.
Figure 15-21 Set server permissions and specify database permissions (1) Select the eBook database in the Object Explorer, right-click and select "Properties".
(2) Switch to "Permissions" in the dialog box.
(3) Select the object for which permissions are to be set in "User or Role", check the permissions in "Explicit Permissions", and click the "OK" button when completed, as shown in Figure 15-22.
Object permissions (1) Select the eBook database in the object explorer, open the "Data Table", select the "Orders" data table, right-click and select "Properties".
(2) Switch to "Permissions" in the dialog box.
(3) Click the "Add" button, and then specify the user, database role, or application role whose permissions you want to change in the dialog box.
(4) Select the object for which permissions are to be set in "User or Role", check the permissions in "Explicit Permissions", and click the "OK" button when completed, as shown in Figure 15-23.
The operation methods of Figure 15-21 and Figure 15-23 are exactly the same. The difference is that the permissions of the server, database and objects are set separately, and the objects that can be set are also different. In Figure 15-21, permissions can be specified for the login name and server role. In Figure 15-22
In Figure 15-23, permissions can be specified for users, database roles and application roles; another difference is that the permissions that can be specified are different.
Figure 15-22 Setting database permissions
Figure 15-23 Setting object permissions and permissions
The permissions that can be set in SQL Server are very complex. There are 94 permissions from the server to the object. What is more difficult to understand are the properties of the server and database. The important items are shown in Table 15-4.
Table 15-4 Important permission descriptions
Permission content description
CONTROL confers ownership-like capabilities to the grantee. The grantee actually has a secure right to
All permissions defined on the icon
ALTER Grants the ability to change the properties (except ownership) of a specific securable object. in a specific range
When ALTER permission is granted to a scope, it is also granted to change, create, or delete the scope.
The capabilities of any security object contained
ALTER ANY <server securable object> Grants the ability to create, alter, or delete individual instances of a server securable object
ALTER ANY <database safe object> Grants CREATE, ALTER, or DROP to an individual instance of a database safe object.
ability
TAKE OWNERSHIP allows the grantee to take ownership of the granted security object
CREATE <server security object> Grants the ability to create server security objects to the grantee
CREATE <database security object> Grants the ability to create database security objects to the grantee
VIEW DEFINITION allows the grantee to access metadata
The "security object" in Table 15-4 refers to the object of the permission setting operation, such as "Role" in "Create Role"
This is the safe object here.
Use T-SQL to set permissions. To set permissions in T-SQL, you can use the three statements GRANT (grant), DENY (deny) and REVOKE (revoke).
These three statements have many uses, and their basic forms are as follows:
GRANT ALL or permission name ON object TO user or role name WITH option
REVOKE ALL or permission name ON object TO user or role name CASCADE
DENY ALL or permission name ON object TO user or role name CASCADE
Each statement has three elements, namely permission name, object and user (role), as follows (Ch151524Grant.sql):
USE eBook
GRANT SELECT ON OBJECT::dbo.members TO David
GRANT REFERENCES (MemberID) ON OBJECT::dbo.Orders
TO DAVID WITH GRANT OPTION
The above statements use the Grant statement to grant permissions to David respectively, but the object content is different. Please use the object name as
Start with "OBJECT::", followed by the full name, preferably followed by the owner. The second Grant statement acts on a special object, "(MemberID) ON OBJECT:: dbo.Orders", which represents the MemberID acting on the Orders object.
field, grant view permissions on this column to David. At the same time, adding "WITH GRANT OPTION" means that David can grant this permission to others, which is equivalent to "with grant" in Figure 15-23.
In the Revoke and Deny statements, Cascade can be added at the end. The function of this keyword is to revoke the permissions that the user can grant to others after To, that is, to cancel "have granted".
View available permissions. Since there are many available permissions, there are no options to choose from in T-SQL, so use GRANT, DENY and
When REVOKE, you must first know the permission name before you can set it. Readers can view sys.fn_builtin_permissions,
The three system data tables sys.database_permissions and sys.server_permissions are shown in Figure 15-24.
Figure 15-24 View available permissions. “Permission_name” in Figure 15-24 is the available permission name, which can be applied to Grant, DENY or
Revoke statement.
View permission content To view the current login name and user permission content, you can execute the sp_helprotect stored procedure, as shown in Figure 15-25
shown.
Grantee in Figure 15-25 is the name of the user who grants permissions, and Grantor is the name of the user who grants permissions to others.
Up to four parameters can be used after the sp_helprotect stored procedure, representing the object name, user name, and grantor name. The fourth parameter can be o or s, representing query object permissions or statement permissions respectively.