Security6:查看授予的权限

存储架构 2017-11-15

在SQL Server的安全体系中,权限分为服务器级别(Server-Level)和数据库级别(Database-Level),用户的权限分为两种形式,分别是直接授予的权限,以及由于加入角色而获得的权限。在安全体系中,授予权限涉及到有三种对象,分别是权限(Permission)、安全主体(Principal)和安全对象(Securable),授予权限的过程,可以用一句话概括:Grants permissions on a securable to a principal。

一,查看服务器级别的权限

在服务器级别,安全主体(Principal)是指Login和Server Role,权限对应的是服务器级别的权限。

1,查看服务器级别的安全主体

系统视图:sys.server_principals 用于查看服务器级别的安全主体:

  • name
    :主体的名称
  • principal_id
    :主体ID
  • sid
    :主体的SID (Security-IDentifier),如果主体是Windows的登陆或组,那么该字段是Winows SID
  • type
    :主体的类型,常见的主体类型是:SERVER_ROLE、SQL_LOGIN、WINDOWS_GROUP、WINDOWS_LOGIN

2,由于属于角色而获得的权限

系统视图:sys.server_role_members 用于查看属于服务器角色(Server Role)的Login:

select r.principal_id as role_id
    ,r.name as role_name
    ,r.is_fixed_role
    ,r.type_desc as role_type
    ,m.principal_id as member_id
    ,m.name as member_name
    ,m.is_disabled
    ,m.type_desc as member_type
from sys.server_role_members srm 
inner join sys.server_principals r
    on srm.role_principal_id=r.principal_id
inner join sys.server_principals m
    on srm.member_principal_id=m.principal_id

3,直接授予的权限

系统视图:sys.server_permissions,用于查看服务器级别的权限

  • class:
    权限存在的分类,常见的分类是:SERVER、SERVER_PRINCIPAL、ENDPOINT
  • grantee_principal_id指定:被授予权限的主体ID, grantor_principal_id
    指定:授予者的主体ID。

  • type
    :服务器级别的权限类型(server permission type);
  • permission_name
    :服务器级别的权限的名称;
  • state:
    权限的状态,分别是DENY、REVOKE、GRANT、GRANT_WITH_GRANT_OPTION;

如果安全主体是Login,那么查看Login被直接授予的权限;如果安全主体是Role,那么查看Role被授予的权限。

select pr.principal_id
    ,pr.name as principal_name
    ,pr.type_desc as principal_type
    ,pe.class_desc as class
    ,pe.permission_name
    ,pe.state_desc as state
from sys.server_principals pr
inner join sys.server_permissions pe
    on pr.principal_id=pe.grantee_principal_id

二,查看Login和User的映射

Login和User 通过sid关联,用户是存在于特定数据库的安全主体,如果User没有映射到Login,那么该用户称作孤立用户(Orphaned User),也就是说,User的sid不能映射到Login的sid。

select sp.principal_id as login_id
    ,sp.name as login_name
    ,sp.type_desc as login_type
    ,dp.principal_id as user_id
    ,dp.name as user_name
    ,dp.type_desc as user_type
    ,dp.authentication_type_desc as authentication_type
from sys.server_principals sp
inner join sys.database_principals dp 
    on dp.sid=sp.sid

三,查看数据库级别的权限

在数据库级别,安全主体是User和Role,权限对应的是数据库级别的权限,包括操作数据库对象,执行的权限等。

1,查看数据库级别的安全主体

系统视图:sys.database_principals 用于查看数据库级别的安全主体:

  • name
    :主体的名称;
  • principal_id
    :主体ID;
  • sid
    :主体的SID (Security-IDentifier),如果主体是Windows的登陆或组,那么该字段是Winows SID;
  • type
    :主体的类型,常见的主体类型是:SQL_USER、WINDOWS_USER、WINDOWS_GROUP、DATABASE_ROLE;
  • authentication_type:验证类型,常见的是DATABASE 和 WINDOWS;

系统视图:sys.database_permissions 用于查看数据库级别的权限:

  • class:
    权限存在的分类,常见的分类是:DATABASE、OBJECT_OR_COLUMN、SCHEMA、DATABASE_PRINCIPAL
  • grantee_principal_id指定:被授予权限的主体ID, grantor_principal_id
    指定:授予者的主体ID。

  • type
    :数据库级别的权限类型(server permission type);
  • permission_name
    :数据库级别的权限的名称;
  • state:
    权限的状态,分别是DENY、REVOKE、GRANT、GRANT_WITH_GRANT_OPTION;
  • 安全对象(Securable):
    通过major_id 和 minor_id 指定安全对象

major_id:该字段共有3种类型的数值:

  • 正整数,标识数据库对象,是object_id;
  • 0,标识数据库级别的授权,class是DATABASE;
  • 负整数,标识系统对象;

minor_id:该字段共有2种类型的数值:

  • 正整数,标识的是数据库对象的column_id,该字段连接到sys.columns中的column_id;
  • 0,标识的是整个数据库对象object;

2,查询数据库Role的成员

系统视图:sys.database_role_members 用于查看数据库级别的角色和数据库主体的映射关系。

select r.principal_id as role_id 
    ,r.name as role_name
    ,r.type_desc as role_type
    ,r.is_fixed_role
    ,u.name as member_name
    ,u.type_desc as member_type
    ,u.authentication_type_desc as member_authentication
from sys.database_role_members rm
inner join sys.database_principals r
    on rm.role_principal_id=r.principal_id 
inner join sys.database_principals u
    on rm.member_principal_id=u.principal_id 
where r.type='R'    --database role
order by role_name

3,查看数据库级别的安全主体的权限

select pr.principal_id
    ,pr.name as principal_name
    ,pr.type_desc as principal_type
    ,pr.is_fixed_role
    ,pr.authentication_type_desc as authentication_type
    ,pe.permission_name
    ,pe.class_desc as permission_class
    ,pe.state_desc as permission_state
    ,pe.major_id
    ,pe.minor_id
from sys.database_principals as pr
inner join sys.database_permissions as pe
    on pe.grantee_principal_id = pr.principal_id
order by pr.name;

4,查看数据库对象上的权限

select pr.principal_id
    ,pr.name
    ,pr.type_desc
    ,pr.authentication_type_desc
    ,pe.permission_name
    ,pe.class_desc
    ,pe.state_desc
    ,o.name as object_name
    ,isnull(c.name,'entire_table') as column_name
from sys.database_principals as pr
inner join sys.database_permissions as pe
    on pe.grantee_principal_id = pr.principal_id
inner join sys.objects as o
    on pe.major_id=o.object_id
left join sys.columns c 
    on o.object_id=c.object_id
        and pe.minor_id=c.column_id
where pe.class=1    -- Object or Column
order by pr.name
    ,o.name
    ,c.column_id;

5,查看数据库schema上的权限

select pr.principal_id
    ,pr.name
    ,pr.type_desc
    ,pr.authentication_type_desc
    ,pe.permission_name
    ,pe.class_desc
    ,pe.state_desc
    ,s.name as schema_name
from sys.database_principals as pr
inner join sys.database_permissions as pe
    on pe.grantee_principal_id = pr.principal_id
inner join sys.schemas as s 
    on pe.major_id=s.schema_id
where pe.class=3    -- Object or Column
order by pr.name;

参考文档:

Security Catalog Views (Transact-SQL)

您可能感兴趣的

区块链VS数据库——解决方案选哪个? 区块链可以被认为是一种新型的数据库。这种数据库可以直接被分享,通过一群非信任节点,无中心化管理的方式运作。它不同于传统的SQL和NoSQL数据库被一个单一实体控制,即使是在防火墙后的分布式架构的数据库。 在某些方面讲区块链是一种更安全的数据库,在某些方面则不是。考虑到当前中心化的数据库依然...
事件溯源和CQRS实施一年总结 事件溯源和CQRS实施一年总结 分布式事务 EventSourcing CQRS架构 DDD领域驱动设计 banq 2018-06-12 17:33 赞助商链接 ...
Primary Replica Jobs This post talks about having primary replica jobs. That is, jobs that will only run on the primary replica of an availability group. Overview An ...
Amazon和Salesforce正在试图远离Oracle数据库?绝非易事... 至顶网软件频道消息: Oracle可能会失去最大的两个客户。 Salesforce和Amazon这两家公司都依赖Oracle旗舰的关系型数据库来支持他们的运营。据今天The Information的一篇报道,这两家公司都在“积极寻找”替代方案。 这篇报道并不令人感到意外,特别是Ora...
Beware the privacy and security risks of smart spe... Would you be willing to equip your bedroom or living room with an internet-connected microphone that could record and send all your conversati...