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拥有的角色以及角色拥有的操作对象
DECLARE @login_name varchar(100) = 'XXX'

;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 = @login_name
      
    UNION ALL
    --直接授权给账号的权限对象
    select      @login_name             AS LoginName,
                ''                      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) = @login_name

    UNION ALL
    --固定服务器角色的权限
    SELECT      r.name            ,
                cast(r.principal_id as varchar(10))    ,
                r.type_desc,
                null as class_desc,
                null as object_name,
                p2.name as permission_name,
                null as permission_state_desc
    FROM sys.server_principals r
    INNER JOIN sys.server_role_members m ON r.principal_id = m.member_principal_id
    INNER JOIN sys.server_principals p1 ON p1.principal_id = m.member_principal_id
    INNER JOIN sys.server_principals p2 ON p2.principal_id = m.role_principal_id  
    WHERE r.name = @login_name
)t