Understanding the permission system (lower) in SQL Server-security objects and permissions

Before reading this article, make sure you have read the previous article.Article, Article address:

Understanding the permission system in SQL Server (I)-Subject

 

Introduction

In the previous article, I fully explained the concept of the subject. This article describes the security objects and permissions of the subject.

 

Securable)

A security object is the resource that the SQL Server database engine authorizes the system to control its access. In layman's terms, it is the object controlled under the SQL Server permission system, because all objects (from servers, to tables, to view triggers, etc) all are under the control of the SQL Server permission system. Therefore, any object in SQL Server can be called a security object.

Like a subject, security objects are also hierarchical. permissions on the application of security objects at the parent level are inherited by the security objects at its sublevel. SQL Server divides security objects into three layers:

• Server level
• Database level
• Architecture level

These three levels are included from top to bottom, as shown in 1:

Figure 1. inclusion relationships between security object levels

 

For detailed hierarchy of SQL Server, see msdn (http://msdn.microsoft.com/zh-cn/library/ms190401.aspx ). All security objects in SQL Server 2 and Figure 3 are shown.

Figure 2. Security objects at the server level

Figure 3. Security objects at the database and architecture levels

 

Permission)

Permission is the link between the subject and the security object. In SQL Server 2008, permissions are divided into rights and restrictions, which correspond to grant statements and deny statements respectively. Grant indicates that the principal is allowed to perform certain operations on security objects, and deny indicates that the principal is not allowed to perform certain operations on certain security objects. There is also a revoke statement used to revoke the previous permissions on the subject grant or deny.

When setting permissions, pay special attention to the inheritance relationship of permissions on security objects. Permissions set on the parent security object are automatically inherited to the child security object. The hierarchical relationship between the subject and the security object is shown in Figure 4.

Figure 4. hierarchical relationship between subject and security object

For example, I give the principal careyson (login name) Select (permission) for the security object careyson-PC (server ), then, the careyson subject automatically has the select permission for sub-security objects such as tables and views in all databases under the careyson-PC server. 5.

Figure 5. The subject's permissions on security objects are inherited at the level

 

At this time, the main careyson can see all the database extremely sub-security objects, as shown in Figure 6

Figure 6. The subject's permissions on security objects are inherited at the level.

 

Permission Control Using T-SQL statements

After understanding the concept of subject, security object and permission, it is very easy to use T-SQL statements for permission control. Use the grant statement to grant permissions, use the deny statement to restrict permissions, and use the revoke statement to revoke the previous permissions granted or restrictions.

Grant's prototype in msdn is:

 
Grant {ALL [privileges]} | permission [(column [,... n])] [,... n] [ON [Class:] securable] to principal [,... n] [With grant option] [as principal]
 

The understanding of a grant statement is like making a sentence to grant a certain permission on the security object type: Security object to the subject. If with grant option is specified, the authorized subject can grant the same permissions to other subjects.

The prototype of the deny statement in msdn is similar to that of Grant:

Deny {ALL [privileges]} | permission [(column [,... n])] [,... n] [ON [Class:] securable] to principal [,... n] [cascade] [as principal]
 

It is worth noting that the cascade option denies the access permission of the subject to the security object, and disallows the subject to grant permissions of other subjects to the security object.

The revoke statement is used to revoke the permissions granted or denied by a subject for security objects. The revoke prototype in msdn is as follows:

 
Revoke [grant option for] {[ALL [privileges] | permission [(column [,... n])] [,... n]} [ON [Class:] securable] {to | from} principal [,... n] [cascade] [as principal]
 

An example of permission control is as follows:

Grant select-- PermissionOn Schema: saleslt-- Type: Security objectTo careyson-- SubjectDeny select-- PermissionOn Schema: saleslt-- Type: Security objectTo careyson-- SubjectRevoke select-- PermissionOn Schema: saleslt-- Type: Security objectTo careyson-- Subject
 

 

Pay attention to the following points when controlling permissions:

• Grant removes the deny and revoke actions on the security object.
• Deny and revoke remove the principal from the grant on the security object
• Revoke removes the deny and grant actions on the security object.
• Deny at a higher level overwrites any sub-level grant. For example, if you perform deny on the schema and grant on the tables it contains, the table's grant will be overwritten by the schema's deny lock, as shown in 7.

Figure 7. Parent-level deny overwrite sub-level grant

 

• For a higher-level grant of a subject, it will be overwritten by its Sub-Deny. In the preceding example, I grant the schema and deny the table. The final result is deny, as shown in 8.

Figure 8. Sub-Level deny overwrite parent-level grant

 

• SQL Server does not perform any permission verification on SysAdmin group members. In other words, SysAdmin group members can do whatever they want.

 

And for what kind of security object can be the corresponding permissions of grant, revoke, deny, see msdn (http://msdn.microsoft.com/zh-cn/library/ms191291.aspx)

 

Summary

Next, I will introduce the security object and related permissions in the previous article. For permission control, understanding the inheritance of permissions and overwriting of permissions reduces many problems when setting permissions.