Chapter 3 Protecting the Data (1): Understanding Permissions

原文出处：http://blog.csdn.net/dba_huangzj/article/details/39548665，专题目录：http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, publish in the form of "original" or be used for commercial purposes without any liability.

Last article: http://blog.csdn.net/dba_huangzj/article/details/39496517

Preface:

relational database management Systems (RDBMS), such as SQL Server, Oracle, MySQL, and so on, need to be responsible not only for accessing data, but also for ensuring data consistency and security. Similar to other server systems, control the access behavior of the data by authorizing a user and maintaining the user's session. SQL Server checks these permissions whenever you try to read and write data. First, understand some concepts:

Permissions: Permissions, authorized at the security principal level, these security principals include: Login name, database user, role, etc. All of these objects can be Grant/deny/revok permissions. Abbreviation GDR (Grant,deny,revoke). The body that accepts the permission is called the recipient (grantee), and the account that sets the permission becomes the grantor (grantor).

Permissions can be granted using the grant option, which belongs to the data Control Language (DCL) command, independent of DML/DDL. The basic syntax is as follows:

<grant | REVOKE | Deny> on <class of securable>::<securable> to <principal>;

The 3 states of the permission are: GRANT: Permission is allowed deny: permission is explicitly denied (priority is higher than GRANT) REVOKE: Eliminates the grant/deny effect, equal to the removal of permissions.

implementation:

You can use the following statement to view the permissions that can be granted:

--All returns a complete list of built-in permissions 
SELECT * from sys.fn_builtin_permissions (DEFAULT); 
--Returns the permissions of a specific category, such as SCHEMA: 
SELECT * from sys.fn_builtin_permissions (' schema ');

The results of this machine are as follows:

The first column is the category of the security principal, that is, the permission is applied to this category. Permission_name and type are used to describe permissions, and type is usually shorthand for permissions. The Covering_permission_name column, or, if not NULL, the permission name of the class (implicitly the other permissions for that class). For example, the first line in the screenshot, CREATE table permission means that ALTER DATABASE permissions are required. Parent_class_desc (

If not NULL, the name of the parent class that contains the current class. and parent_covering_permission_name (if not null, the permission name of the parent class (implicitly all other permissions for that class). ), which can be interpreted to view Books Online.

Here are some examples of grant permissions:

--Authorize Bill's database user to have a select on the Accounting.account table, 
grant Select on Object::accounting.account to Bill; 
--Authorize bill this database user to execute all stored procedures and scalar functions in the Accounting schema 
GRANT execute on schema::accounting to bill; 
--Authorize user-defined role Availabilitymanager to have permission to modify the availability group on the server 
GRANT ALTER any availability group to Availabilitymanager;

If you need to reclaim permissions, you can use the REVOKE command:

REVOKE SELECT on Object::accounting.account to Fred;

If explicit prohibitions are required, you can use the Deny command:

DENY SELECT on Object::accounting.account to Fred;

After the deny, if a user needs to run an operation that has been compromised, a 229 error is reported, which is prohibited by the permission on the object, but it gives the attacker some information of interest because it exposes the information (such as if the XX object is prohibited, meaning that the XX object exists). Error 208 indicates an attempt to access an object that does not exist.

principle:

The following are the most commonly used permissions:

Permission Name	Describe
Alter	Modify permissions for an object definition
CONNECT	Permissions to access the database or connection endpoint
DELETE	Permission to delete an object
EXECUTE	Permission to execute a stored procedure or function
Impersonate	Equivalent to the EXECUTE AS command
INSERT	Permissions to insert data into a table or view
REFERENCES	The foreign key definition or the permission to apply the object in the with schemabinding in the view
SELECT	The ability to execute a SELECT command on an object or column
TAKE ownership	Permissions to be the owner of an object
UPDATE	Permissions to update data
VIEW DEFINITION	To view the permissions defined by an object

Example:

DENY SELECT on OBJECT::d bo. Contact to fred;--forbid Fred query dbo.contact table data 
DENY UPDATE on OBJECT::d bo. The contact to fred;--fred updates the Dbo.contact table's data 
GRANT SELECT on OBJECT::d bo. Contact to fred;--authorize Fred to query dbo.contact table data

Note that GRANT all exists, but will be discarded in the future. The revoke command removes the effect of grant and does not use the Deny command to remove the command unless you are certain that the user does not need to access the object. The deny command overrides all Grant commands.

Login names can be grant/deny at the schema or object level, such as

DENY SELECT on Accounting.account to dbo;

The CONTROL permission contains other permissions on the security entity, and if you deny the SELECT permission and then grant the control permission, the Deny permission is reclaimed. Because of SQL Server's permission checking algorithm and complexity, it is best to keep the permissions policy simple. The hierarchy and transport diagram of SQL Server object permissions:

MORE:

If you need to test the permissions of the current user, maliciously use the Has_perms_by_name function, and if you return 1, the certificate Authority is granted:

-whether there is a SELECT permission on the DBO schema. 
Select Has_perms_by_name (' dbo ', ' SCHEMA ', ' select '); 
--Check all permissions on the server level 
SELECT has_perms_by_name (null, NULL, ' VIEW server State ');

Use the following statement to get a list of granted permissions:

--What permissions the current user has on the dbo schema. 
SELECT * from sys.fn_my_permissions (' dbo ', ' SCHEMA '); 
--Server All Permissions 
SELECT * from sys.fn_my_permissions (null, NULL);

Server permissions are recorded in the Sys.server_permissions system view, and database permissions are stored in the Sys.database_permissions system view of each database. You can view a collection of permissions on the Dbo.prospect table:

SELECT  Grantee.name as grantee, 
        Grantor.name, 
        dp.permission_name as permission, 
        Dp.state_desc as State 
from    sys.database_permissions dp 
        JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id 
        JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id 
WHERE   dp.major_id = object_id (' Dbo.prospect ');

With GRANT option works:

means that the authorized subject can grant permissions of the same or less than the current permission to other principals. Like what

GRANT SELECT on OBJECT::d bo.contact to Fred with GRANT OPTION;

This gives Fred two permissions, one for select and one for Grant Select, which can reclaim permissions other than SELECT:

REVOKE SELECT on OBJECT::d bo.contact to Fred CASCADE; 
GRANT SELECT on OBJECT::d bo.contact to Fred;

The two statements are to reclaim all permissions first, and then assign only select, plus the meaning of cascade is to take back the SELECT permission that Fred once authorized to other principals.

Reference permissions:

This permission is not only scoped to the table, but also can be used for databases, schemas and other bodies. Reference allows a FOREIGN key constraint to be created in a table that can be used in a view for a reference to the table affected by the with schemabinding.

Next: http://blog.csdn.net/dba_huangzj/article/details/39577861