SQL Server 用角色(Role)管理資料庫許可權

當資料庫越來越多，連接到資料庫的應用程式，伺服器，賬號越來越多的時候，為了既能達到滿足賬號操作數據許可權需求，又不擴大其操作許可權，保證資料庫的安全性，有時候需要用角色來參與到許可權管理中，通過角色做一個許可權與訪問用不之前的映射，可以更加方便地管理許可權。

USE master
GO
--創建一個用戶
CREATE LOGIN ReadUser WITH PASSWORD ='123qwe!@#',DEFAULT_DATABASE=DBTest


USE DBTest
GO
 --創建用戶，指定到上面
CREATE USER ReadUser FOR LOGIN ReadUser WITH DEFAULT_SCHEMA = dbo

用SETUSER 切換到上面建的ReadUser賬號下麵，通過print Session_user發現已經切換到了ReadUser，以ReadUser的身份執行一個查詢，

此時提示ReadUser沒有DetailTable的SELECT 許可權

以管理員身份授權給ReadUser查詢dbo.DetailTable表的許可權

再次以ReadUser的身份執行上述查詢，這次發現可以正常執行了

如果允許ReadUser這個賬號對當前庫多張表都要有查詢的全新,就要將GRANT SELECT ON TableName重覆N次,
那麼問題就來了，如果此時需要再建一個同樣許可權的用戶,ReadUser2，授予同樣的許可權，又要重覆N此GRANT操作？

此時就需要藉助角色這一資料庫對象來管理許可權，將User加入到某一個角色中，來避免每次新增一個User都要執行一遍授權操作。

首先用管理員許可權創建一個角色ReadRole

然後依次執行如下操作，將之前授權給ReadUser的許可權給Revoke掉，

1，新建一個名稱為ReadRole的角色

2，將多張表的查詢許可權授予ReadRole這一角色

3，將User加入到這個角色中

執行完成之後，我們在來嘗試UserRead這個角色的許可權，可以發現：角色有的許可權，ReadUser也都有了

如果此時再新建一個ReadUser2

此時切換到ReadUser2的身份下，發現ReadUser2也具備了ReadRole這個自定義角色的許可權

上述的ReadRole是自定義的角色，上面給他授權的是當前資料庫中的部分表的SELECT許可權
如果需要全庫的所有表的SELECT許可權，就可以藉助DataBase Role來實現了，將用戶加入到DataBase級別的db_dataReader這個角色中
如下截圖

DataBase級別的角色作用範圍是整個DB的，比如db_datareader，db_datawriter都是作用在資料庫所有的對象
對於這種範圍比較大而不適合使用的場景，就可以採用類似上述自定義角色，通過給角色授予指定範圍內的許可權的方式來實現用戶許可權管理

角色不僅可以在表上做許可權控制和管理，也可以管理視圖（查詢），存儲過程（執行），函數（查詢），Sequence（Sequence是Update）等對象上的操作許可權，通過授權給角色許可權，把某一列用戶加入到某一個角色中，用角色來管理用戶和資料庫對象之間的許可權管理，可以做到更加統一地管理許可權。

最後，附上兩個腳本

1.查詢某個角色用哪些許可權

--查詢某個角色擁有的許可權
select USER_NAME(p.grantee_principal_id) AS principal_name,
            dp.principal_id,
            dp.type_desc AS principal_type_desc,
            p.class_desc,
            OBJECT_NAME(p.major_id) AS object_name,
            p.permission_name,
            p.state_desc AS permission_state_desc
from sys.database_permissions p 
            INNER JOIN sys.database_principals dp on  p.grantee_principal_id = dp.principal_id 
where USER_NAME(p.grantee_principal_id) =  'ReadRole' --角色名稱

2.查詢某個User有哪些角色的許可權（User數據哪一個（多個）角色）

--某個User有哪些角色的許可權（User數據哪一個（多個）角色）
SELECT u.name, r.name
FROM sys.database_role_members AS m
        INNER JOIN sys.database_principals AS r ON m.role_principal_id = r.principal_id
        INNER JOIN sys.database_principals AS u ON u.principal_id = m.member_principal_id
WHERE u.name = 'ReadUser'; --UserName

3.查詢某個賬號有哪些許可權，直接授權給賬號的,而不是通過角色繼承來的

--查詢某個賬號有哪些許可權，直接授權給賬號的,而不是通過角色繼承來的
select USER_NAME(p.grantee_principal_id) AS principal_name,
        p.grantee_principal_id,
        dp.principal_id,
        dp.type_desc AS principal_type_desc,
        p.class_desc,
        OBJECT_NAME(p.major_id) AS object_name,
        p.permission_name,
        p.state_desc AS permission_state_desc
from sys.database_permissions p 
        INNER JOIN sys.database_principals dp on  p.grantee_principal_id = dp.principal_id 
where USER_NAME(p.grantee_principal_id) = 'ReadUser'

4.查詢一個UserName擁有的所有許可權（通過角色集成的許可權和自身具備的許可權）

--查詢一個UserName擁有的角色以及角色擁有的操作對象
;WITH LoginName
AS
(
  SELECT u.name                    　　 AS LoginName, 
         r.name                　　　　  AS RoleName,
         role_principal_id        　　  AS PrincipalId
  FROM sys.database_role_members AS m
        INNER JOIN sys.database_principals AS r ON r.principal_id = m.role_principal_id 
        INNER JOIN sys.database_principals AS u ON u.principal_id = m.member_principal_id
),
UserPermission
AS
(
    select USER_NAME(p.grantee_principal_id)    AS principal_name,
            dp.principal_id            AS principal_id,
            dp.type_desc            AS principal_type_desc,
            p.class_desc            AS class_desc,
            OBJECT_NAME(p.major_id)        AS object_name,
            p.permission_name            AS permission_name,
            p.state_desc            AS permission_state_desc
    from sys.database_permissions p 
            INNER JOIN sys.database_principals dp on  p.grantee_principal_id = dp.principal_id 
)
SELECT * FROM
(
    --通過角色獲取的許可權對象
    SELECT  u.LoginName,
            u.RoleName,
            p.principal_type_desc,
            p.class_desc,
            p.permission_name,
            p.object_name,
            p.permission_state_desc
    FROM LoginName u left join UserPermission p on p.principal_name = u.RoleName
    WHERE u.LoginName = '***'

    UNION ALL

    --直接授權給賬號的許可權對象
    select     '***'            AS LoginName,
            NULL                            AS RoleName,
            dp.type_desc                    AS principal_type_desc,
            p.class_desc                    AS class_desc,
            p.permission_name                    AS permission_name,
            OBJECT_NAME(p.major_id)                AS object_name,
            p.state_desc                    AS permission_state_desc
    from sys.database_permissions p 
            INNER JOIN sys.database_principals dp on  p.grantee_principal_id = dp.principal_id 
    where USER_NAME(p.grantee_principal_id) = '***'
)t WHERE 1=1 
order by class_desc,RoleName ,principal_type_desc