Security6:查看授予的权限

在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)

博客园-原创精华区稿源:博客园-原创精华区 (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Security6:查看授予的权限

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录