SQL Server 安全篇——安全元数据(1)——安全主体(Principal)元数据

186 篇文章 6 订阅
65 篇文章 0 订阅

本文属于SQL Server安全专题系列


 虽然大量的安全元数据(security metadata)可以从SSMS中查询(指鼠标操作),但是有些元数据仅能通过T-SQL来查看。完整的安全元数据足以单独成书,这里仅介绍一些有用的或者可能会用到的内容。

安全主体元数据:

 当在实例上实施安全策略时,很有可能就要收集很多安全实体或者安全对象的信息。比如一个策略是所有数据库必须属于sa。那么为了验证这个情况,必须找到每个库的当前拥有者,然后进行对应修改。如果一个实例有200个库,那么GUI操作恐怕会很惨。
 对于使用GUI,更应该借用元数据来实现,每个数据库的拥有者可以通过sp_MShasdbaccess存储过程或者从sys.databases目录视图来获取。
 sp_MShasdbaccess存储过程不接受参数,直接返回库名及其拥有者,如下图,但是注意这个存储过程只返回有权限访问的库的信息:


 如果需要更详细的信息,可以从sys.databases中获得。如果只是上面的需求,可以从该视图中获取sid(安全ID),然后使用SUSER_SNAME()系统函数翻译成名字:




 对于SUSER_SNAME()和SUSER_NAME(),两者都可以返回登录名,但是前者参数为SID而后者是一个登录ID(安全主体ID)。

查找用户实际权限:


 当服务器角色和数据库角色的曾经比较复杂的时候,如果权限也直接授权到用户,那么权限识别工作将非常痛苦,而此时可以使用sys.fn_my_permissions()系统函数来协助,其参数如下表:

参数描述
securable用户权限所属的安全对象名字
securable_class需要查找的安全对象类型,如SERVER、DATABASE或对象
 函数返回的结果描述如下表:

描述
entity_name安全对象的名字
subentity_name如果安全对象有列,那么这列就包含列名,否则为NULL
permission_name由安全主体分配的权限名。
 这个函数用于返回关于调用函数的用户信息,但是也可以通过EXECUTE AS 来指定检查的用户信息,如下面脚本:

USE master 
GO 
--创建一个演示登录
CREATE LOGIN DemoLogin WITH PASSWORD=N'Pa$$w0rd', CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF 
--添加到sysadmin角色
ALTER SERVER ROLE sysadmin ADD MEMBER DemoLogin 
GO
USE AdventureWorks2016
GO 
--返回登录名
SELECT SUSER_SNAME() ; 
EXECUTE AS LOGIN = 'DemoLogin' ; 
SELECT SUSER_SNAME() ; 
REVERT ; 
SELECT SUSER_SNAME() ; 
 本机结果如下:


 然后在演示使用sys.fn_my_permissions函数组合EXECUTE AS子句来查找用户权限。

EXECUTE AS LOGIN = 'DemoLogin' 
   SELECT o.name 
        , a.entity_name 
        , a.subentity_name 
        , a.permission_name 
   FROM sys.objects o 
   CROSS APPLY sys.fn_my_permissions(CONCAT( 
                                        QUOTENAME( 
                                                SCHEMA_NAME(schema_id)) 
                                     , '.' 
                                     , QUOTENAME(o.name)) 
                                     , 'OBJECT') a   
   UNION ALL 
   SELECT d.name 
        , a.entity_name 
        , a.subentity_name 
        , a.permission_name 
   FROM sys.databases d 
   CROSS APPLY fn_my_permissions(QUOTENAME(d.name), 'DATABASE') a 
   UNION ALL 
   SELECT @@SERVERNAME COLLATE Latin1_General_CI_AS 
          , a.entity_name 
          , a.subentity_name 
          , a.permission_name 
   FROM fn_my_permissions(NULL, 'SERVER') a 
   ORDER BY 1 
REVERT 
 结果太多,这里只能截取一小部分,读者可以自行测试:


 结果包含了实例、库、对象(当前库,包含索引等)层级的实际权限。语句包含三个独立部分并用UNION 连接起来。第一部分从sys.objects中返回对象名及架构名,传入sys.fn_my_permissions()函数中。第二部分把sys.objects的行为变成sys.databases,及把对象上升到库层面。最后一部分是实例层面的权限信息。

 如有相关脚本会尽量同步更新到本文。








评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值