正文

Security6：查看授予的权限

IP云
IP云V管理员/1 阅读
09/08
小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

在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和state_desc：权限的状态，分别是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和type_desc：主体的类型，常见的主体类型是：SQL_USER、WINDOWS_USER、WINDOWS_GROUP、DATABASE_ROLE；
  • authentication_type：验证类型，常见的是DATABASE 和 WINDOWS；
  • owning_principal_id：安全主体（Principal）的所有者，除了数据角色之外的所有主体的所有者必须是dbo；

系统视图：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；
  • major_id和minor_id：

安全对象（Securable）：通过major_id 和 minor_id 指定安全对象

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

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

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

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

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

2，查看数据库级别的安全主体的权限

安全主体包括User和数据库级别的role，通过以下脚本查看它们的权限，这是直接授予安全主体的权限：

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;

2，从数据库角色获得的权限

查询数据库Role的成员，可以查看数据库用户从数据库角色获得的权限：

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

四，查看用户在表、view或schema上的权限

用户可以被授予数据库对象上或schema上的权限

1，查看用户在对象上的权限

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;

2，查看用户在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;

参考文档：

Getting Started with Database Engine Permissions

Security Catalog Views (Transact-SQL)