VB.net 2010 视频教程 VB.net 2010 视频教程 VB.net 2010 视频教程
当前位置:
首页 > 数据库 > sql数据库 >
  • sql语句大全之SQL Server 用角色(Role)管理数据库权限

  • 2018-12-15 21:40 来源:未知

当数据库越来越多,连接到数据库的应用程序,服务器,账号越来越多的时候,为了既能达到满足账号操作数据权限需求,又不扩大其操作权限,保证数据库的安全性,有时候需要用角色来参与到权限管理中,通过角色做一个权限与访问用不之前的映射,可以更加方便地管理权限。

复制代码
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

相关教程
关于我们--广告服务--免责声明--本站帮助-友情链接--版权声明--联系我们       黑ICP备07002182号