Security4：Role 和 Permission

Grants permissions on a securable to a principal.  The general concept is to GRANT <some permission> ON <some object> TO <some user, login, or group>. 简单记作 Grant Permission on securable to principal

授予权限分为三部分：Permission，Securable 和 principal，用一句话来解释这三个concept：授予 Principal 操作 Securable 的 Permission。Principal是被授予权限的实体，Securable是table，view等对象，是Principal操作的对象；有时Principal也会作为Securable，被Principal操纵。

一，Role 作为 Principal，被授予权限

查看Permission列表，点击：Permissions (Database Engine)，这里列出的是Individual permission，role 是权限的集合。

1, Create Role 和 Create Server role

Create Role 子句 Creates a new database role in the current database.

Syntax

CREATE ROLE role_name 
[ AUTHORIZATION owner_name ]

AUTHORIZATION owner_name

Is the database user or role that is to own the new role. If no user is specified, the role will be owned by the user that executes CREATE ROLE.

Remarks                                       

2,GRANT Permissions to Role

Role作为一个Principal，可以使用Grant子句为role授予权限，这里为role授予Object的Permissions，参考 GRANT Object Permissions (Transact-SQL)

GRANT <permission> [ ,...n ] ON 
    [ OBJECT :: ][ schema_name ]. object_name [ ( column [ ,...n ] ) ]
    TO <database_principal> [ ,...n ] 
    [ WITH GRANT OPTION ]
    [ AS <database_principal> ]

<permission> ::=
    ALL [ PRIVILEGES ] | permission [ ( column [ ,...n ] ) ]

<database_principal> ::= 
        Database_user 
    | Database_role 
    | Application_role 
    | Database_user_mapped_to_Windows_User 
    | Database_user_mapped_to_Windows_Group 
    | Database_user_mapped_to_certificate 
    | Database_user_mapped_to_asymmetric_key 
    | Database_user_with_no_login

Examples:

grant ALTER,DELETE,INSERT,SELECT,UPDATE        --Permission List
on dbo.dt_test        --Table Name
to dabase_level_role    --Role Name
with GRANT OPTION 

3，Add member

Adds members to a database role

Syntax

ALTER ROLE role_name
{
      [ ADD MEMBER database_principal ]
    | [ DROP MEMBER database_principal ]
    | WITH NAME = new_name
}

ADD MEMBER database_principal              

Adds the specified database principal to the database role. database_principal can be a user or a user-defined database role. database_principal cannot be a fixed database role, or a server principal.

二，Role 作为Securable，Principal授予操纵Role的权限

Role 可以是Dabase Principal，被授予权限;Role可以是Database Securable，Grant子句可以授予Principal操纵Role的权限。

参考 GRANT Database Principal Permissions (Transact-SQL)

GRANT permission [ ,...n ]  
    ON 
    {  [ USER :: database_user ]
              | [ ROLE :: database_role ]
       | [ APPLICATION ROLE :: application_role ]
    }
    TO <database_principal> [ ,...n ]
    [ WITH GRANT OPTION ]
        [ AS <database_principal> ]

<database_principal> ::=
        Database_user 
    | Database_role 
    | Application_role 
    | Database_user_mapped_to_Windows_User 
    | Database_user_mapped_to_Windows_Group 
    | Database_user_mapped_to_certificate 
    | Database_user_mapped_to_asymmetric_key 
    | Database_user_with_no_login

Examples

1,Granting CONTROL permission on a user to another user

GRANT CONTROL 
ON USER::Wanida 
   TO RolandX;

2,Granting VIEW DEFINITION permission on a role to a user with GRANT OPTION

GRANT VIEW DEFINITION 
ON ROLE::SammamishParking 
    TO JinghaoLiu  
WITH GRANT OPTION;

三，其他

1，GRANT Database Permissions

参考文档：https://msdn.microsoft.com/en-us/library/ms178569(v=sql.110).aspx

GRANT <permission> [ ,...n ]  
    TO <database_principal> [ ,...n ] [ WITH GRANT OPTION ]
    [ AS <database_principal> ]

<permission>::=   
permission | ALL [ PRIVILEGES ]

<database_principal> ::=  
        Database_user 
    | Database_role 
    | Application_role 
    | Database_user_mapped_to_Windows_User 
    | Database_user_mapped_to_Windows_Group 

Examples

1，Granting permission to create tables

USE AdventureWorks2012;
GRANT CREATE TABLE TO MelanieK;
GO

USE AdventureWorks2012;
GRANT CREATE VIEW TO CarmineEs WITH GRANT OPTION;
GO

2，GRANT Object Permissions

参考文档：https://msdn.microsoft.com/en-us/library/ms188371(v=sql.110).aspx

GRANT <permission> [ ,...n ] ON 
    [ OBJECT :: ][ schema_name ]. object_name [ ( column [ ,...n ] ) ]
    TO <database_principal> [ ,...n ] 
    [ WITH GRANT OPTION ]
    [ AS <database_principal> ]

<permission> ::=
    ALL [ PRIVILEGES ] | permission [ ( column [ ,...n ] ) ]

<database_principal> ::= 
        Database_user 
    | Database_role 
    | Application_role 
    | Database_user_mapped_to_Windows_User 
    | Database_user_mapped_to_Windows_Group 

ON [ OBJECT :: ] [ schema_name ] . object_name              

Specifies the object on which the permission is being granted. The OBJECT phrase is optional if schema_name is specified. If the OBJECT phrase is used, the scope qualifier (::) is required. If schema_name is not specified, the default schema is used. If schema_name is specified, the schema scope qualifier (.) is required.

ALL              

Granting ALL does not grant all possible permissions. Granting ALL is equivalent to granting all ANSI-92 permissions applicable to the specified object. The meaning of ALL varies as follows:

Scalar function permissions: EXECUTE, REFERENCES.

Table-valued function permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.

Stored procedure permissions: EXECUTE.

Table permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.

View permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.

Examples

A. Granting SELECT permission on a table

USE AdventureWorks2012;
GRANT SELECT ON OBJECT::Person.Address TO RosaQdM;
GO

USE AdventureWorks2012; 
GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo
    TO Recruiting11;
GO 

参考文档：

https://msdn.microsoft.com/en-us/library/ms173848(v=sql.110).aspx

https://msdn.microsoft.com/en-us/library/ms188371(v=sql.110).aspx

https://msdn.microsoft.com/en-us/library/ms178569(v=sql.110).aspx

Security4：Role 和 Permission

原文：http://www.cnblogs.com/ljhdo/p/5125235.html