专注,勤学,慎思。戒骄戒躁,谦虚谨慎

just do it

导航

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

 

posted on 2017-02-07 14:10  MSSQL123  阅读(7267)  评论(3编辑  收藏  举报