SQL Server permissions system (bottom)

Introduction

In the previous article, I made a comprehensive exposition of the concept of the subject. This article then describes the securable objects that the principal is acting on and the corresponding permissions.

Understanding securable Objects (Securable)

A securable object that is the resource that the SQL Server database engine authorizes the system to control access to. The popular point is that the object is controlled under the SQL Server permissions system because all objects (from the server, to the table, to the view trigger, and so on) are under the control of the SQL Server permissions system, so any object in SQL Server can be called a securable object.

As with principals, there is a hierarchy between securable objects, and the permissions applied to securable objects at the parent level are inherited by securable objects at their child levels. SQL Server divides securable objects into three tiers, namely:

• Server-level
• Database hierarchy
• Architecture level

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

Figure 1: Containment Relationships between securable object tiers

For a detailed breakdown of SQL Server hierarchies, you can refer to MSDN (http://msdn.microsoft.com/zh-cn/library/ms190401.aspx). All of the security objects in SQL Server 2 and Figure 3 are shown.

Figure 2: Server-Level security objects

Figure 3: Security objects at the database and architecture level

Understanding Permissions (Permission)

Permissions are the link between the body and the securable. In SQL Server 2008, permissions are divided into rights and restrictions , corresponding to the grant statement and the Deny statement, respectively. Grant means that the principal is allowed to do certain things for securable objects, and deny means that the principal does not allow certain operations on certain securable objects. There is also a revoke statement that reclaims the previous permission to the principal grant or deny.

When setting permissions, pay particular attention to the inheritance of permissions on securable objects. Permissions that are set on the parent securable object are automatically inherited to the child securable object. The hierarchy of principals and securable objects is shown in 4.

Figure 4: Hierarchical relationship between principals and securable objects

For example, I give the principal Careyson (login name) a SELECT (permission) for the securable object careyson-pc (server), So Careyson this subject automatically has the SELECT permission for all the child securable objects, such as tables and views, in all databases under the CAREYSON-PC server. As shown in 5.

Figure 5: Principals ' permissions on securable objects are inherited at the level

At this point, the principal Careyson can see all the database extremely child securable objects, as shown in 6

Figure 6: Principals ' permissions on securable objects are inherited at the level

using T-SQL statements for permission control

After understanding the concepts of principals, securable objects and permissions, 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 retract the grant or restriction of permissions before.

The prototype for Grant on MSDN is:

GRANT {All [privileges]}      | permission [(column [,... n])] [,... n]      [on [Class::] securable] to Prin Cipal [,... N]       [with GRANT OPTION] [as principal]

An understanding of the grant statement is like a sentence grant a permission on a securable object type: Security object to body. If with Grant OPTION is specified, the principal granted permission can grant the same permissions to other principals.

For the DENY statement in MSDN, the prototype and grant are similar:

DENY {All [privileges]}      | permission [(column [,... n])] [,... n]      [on [Class::] securable] to Princ Ipal [,... n]       [CASCADE] [as principal]

It is important to note that the CASCADE option indicates that the deny principal has access to securable objects while denying that the principal grants other principals permissions on securable objects.

The REVOKE statement is used to retract the original grant or deny permission for a principal to a securable object. The prototypes of revoke in MSDN are 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--Permissions on Schema::saleslt--type:: securable object to  Careyson--principal deny Select--Permission on SCHEMA::SALESLT --Type:: Security object to  Careyson--Body Revoke Select--Permission on SCHEMA::SALESLT--type:: securable object to Careyson--Body 

There are several points to keep in mind when controlling permissions:

• Grant removes the DENY and revoke that the principal acts on the securable object
• Deny and revoke remove the grant that the principal acts on the securable object
• Revoke removes the deny and grant that the principal acts on the securable object
• Deny at the high level overrides any child-level grant. For example, if you deny the schema and grant the table it contains, the grant of the table will be overwritten by the schema's Deny lock, as shown in 7.

Figure 7. Deny overrides child-level grant at parent level

• For Grant that the subject is acting on the upper level is overridden by its child deny, or the above example, I grant to the schema, deny the table, and the final result is deny,8.

Figure 8. A child-level deny overrides the parent-level grant

• SQL Server does not perform any permission validation operations on members of the sysadmin group. In other words, members of the sysadmin group can do whatever

For the grant,revoke,deny of what kind of security objects can do what permissions, see MSDN (http://msdn.microsoft.com/zh-cn/library/ms191291.aspx)

Summary

This article goes on to describe the security objects and the corresponding permissions. For permission control, understanding the inheritance of permissions and overwriting of permissions can reduce many problems when setting permissions.

SQL Server permissions system (bottom)