行级安全（Row-Level Security）

常规的权限控制，是通过授予和拒绝（Grant/Deny）命令，控制用户对数据库对象（数据表或视图）的访问权限，用户访问的粒度是对象的全部数据行，这意味着，用户要么有权限访问该对象，要么没有权限访问该对象，无法实现使特定的数据行只允许特定的用户访问，但是，行级安全（Row-Level Security，简称 RLS）可以实现该该需求。

RLS根据用户的安全上下文控制用户可以访问的数据行。RLS主要由两部分构成：安全策略（Security Policy）和安全断言（Security Predicate），一个安全策略可以添加多个安全断言。

如果安全策略（Security Policy）被禁用，那么用户总是访问所有数据行，跟数据表上不关联任何安全策略一样。
如果安全策略（Security Policy）被启用，那么用户在数据行级别上控制用户的访问，粒度是数据行，控制用户只能访问数据表的特定数据行。

RLS是基于安全断言（Security Predicate）来实现行级的访问控制，断言（Predicate ）是逻辑表达式，返回的结果是布尔（boolean）值：true 或false。Security Predicate是由内联表值函数实现的，该函数称作断言函数（Predicate Function)。当断言函数返回的数据行不是空时，安全断言的结果是True；当断言函数不返回数据行时，安全断言的结果是False。

在目标表上实现RLS，控制用户访问数据行的权限，通常需要显式定义3个组件：

断言函数（Predicate Function）：是内联表值函数，用于执行安全断言，Security Policy调用该函数过滤数据行或阻塞写操作；
安全策略（Security Policy）：将目标数据表和断言函数绑定，并设置安全断言的类型；
权限配置表：如果需要对用户访问目标数据表的权限进行动态控制，还需要定义权限配置表。权限配置表通常由两列：User Name 和 Rowset，用于表示用户有权限访问的行集。

一，定义安全断言（Security Predicate）

如果在数据表上启用RLS，那么一个用户访问数据行的权限受到安全断言（Security Predicate）的限制，Security Predicate 是在内联表值函数中定义的逻辑表达式，Security Policy调用内联表值函数，返回Security Predicate 的结果。在用户访问行级别数据时，SQL Server自动执行预定义的安全策略（Security Policy），仅当Security Predicate返回逻辑True时，才允许用户访问指定的数据行；如果Security Predicate 返回逻辑False，那么不允许用户访问数据。如果在一个数据表上创建了Security Policy，但是，安全策略（Security Policy）被禁用，那么，Security Predicate将不会过滤或阻塞任何数据行，不执行任何的Filter 或 Block操作，用户能够访问所有的数据行。

下面的示例代码定义了断言函数（Predicate Function），该表达式根据用户名作为断言控制用户对数据行的访问：

CREATE FUNCTION rls.fn_securitypredicate
(@SalesRep AS sysname)  
RETURNS TABLE  
WITH SCHEMABINDING  
AS  
RETURN SELECT 1 AS fn_securitypredicate_result   
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';

通常情况下，实现RLS的目的是为了根据权限配置表对用户的访问目标表的权限进行动态控制。假定权限配置表只有两列：UserName和Rowset，在定义断言函数时，需要同时传递用户名称和RowSet参数。

create function rls.fn_SecurityPredicate_ByUserName
(@UserName as sysname,@Rowset as int)  
returns table  
with schemabinding  
as
return 
(
    select UserName
    from rls.UserSecurityConfig
    where UserName=substring(@UserName, charindex('\',@UserName)+1,len(@UserName))  
        and RowSet=@Rowset
)
go

二，过滤断言和阻塞断言（Filter 和 Block）

在Security Policy中，RLS支持两种类型的安全断言（Security Predicates）：

Filter Predicate：当用户从目标表读取数据行时，Filter Predicate透明地过滤数据行，用户只能读取有权限访问的数据行；如果所有的数据行都被过滤掉，那么返回空集给用户；
Block Predicate：当违反断言时，阻塞写操作事务的提交，回滚写操作事务；

1，过滤断言（Filter Predicate）

当从目标标中读取数据时，读操作受到Filter Predicate的影响，读取数据的操作包括：select，delete和update，用户不能查询，删除和更新被过滤的数据行。

过滤断言（Filter Predicate）定义的安全策略，使得用户在Base Table上执行select，update和delete命令时，不会意识到Filter操作的存在，Security Policy透明地过滤数据行，但是用户能够插入任何数据，不管数据是否被过滤掉，用户也可以把数据更新为被过滤的数据。也就是说，用户更新的数据可能会违反安全断言。

2，阻塞断言（Block Predicate）

阻塞断言（Block Predicate）阻塞所有违反安全断言的写操作，有四种阻塞操作：

After Insert 断言：阻止用户插入违反断言的字段值，就是说，用户插入的数据必须满足断言；
After Update 断言：阻止用户将数据更新为违反断言的字段值，就是说，数据被更新后，其值必须满足断言；
Before Update 断言：只允许用户更新符合断言的数据行，就是说，在Update之前，对于符合断言的数据行，用户能够更新为任意值；
Before Delete 断言：只允许用户删除符合断言的数据行，就是说，在Delete之前，对于符合断言的数据行，用户能够删除；

阻塞操作有分为After 和Before选项：

After 指定：在执行Insert 或 Update操作之后，计算断言的逻辑结果；如果逻辑结果为false，那么回滚Insert 或 Update操作；
Before 指定：在执行Update 或Delete 操作之前，计算断言的逻辑结果，用户只能Update或Delete符合断言的数据；
如果没有指定，那么默认会指定所有四种阻塞操作。

3，过滤断言和阻塞断言的SCHEMABINDING选项

在定义断言函数时，可能需要与表、视图进行连接，或者调用函数。当创建Security Policy指定SCHEMABINDING = ON选项，不会对断言函数内部调用的表或函数做额外的权限检查。如果指定的SCHEMABINDING = OFF选项，那么用户需要被授予断言函数内部调用的表或函数上的SELECT 或EXECUTE权限。也就是说，如果使用SCHEMABINDING = OFF选项创建安全策略，那么在查询目标表之前之前，必须对断言函数、以及断言函数调用的表、视图或函数具有SELECT 或EXECUTE权限。如果使用SCHEMABINDING = ON选项创建安全策略，当用户查询目标表时，将绕过这些权限检查。

4，安全策略是安全断言的容器

一个安全策略可以定义多个安全断言，对多个目标表进行RLS权限控制。

三，创建RLS的示例

使用RLS控制用户只能访问指定的数据，第一步是创建断言函数，第二步是创建安全策略，这两个对象都有架构。强烈推荐创建一个单独的Schema，用于RLS对象（Predicate Function和 Security Policy），本例中创建rls架构。

create schema rls  
authorization dbo;

1，创建断言函数

创建内联表值函数，用于过滤数据行，返回安全断言的结果：

--create function
CREATE FUNCTION rls.fn_securitypredicate
(@SalesRep AS sysname)  
RETURNS TABLE  
WITH SCHEMABINDING  
AS  
RETURN SELECT 1 AS fn_securitypredicate_result   
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';

2，创建和启动安全策略（Security Policy）

创建Security Policy，把目标表和Security Predicate 绑定，添加Filter Predicate，并向断言函数传递目标表 dbo.Sales的字段SalesRep字段作为参数值：

CREATE SECURITY POLICY rls.SalesFilter  
ADD FILTER PREDICATE rls.fn_securitypredicate(SalesRep)   
ON dbo.Sales  
WITH (STATE = ON, SCHEMABINDING=ON);

3，测试安全策略（Security Policy）

由于断言函数中使用User_Name来获取用户的名称，因此，可以使用EXECUTE AS命令来模拟用户的权限：

EXECUTE AS USER = 'Sales1'; 
SELECT USER_NAME() as UserName,* 
FROM dbo.Sales;   
REVERT;  

EXECUTE AS USER = 'Manager';  
SELECT USER_NAME() as UserName,* 
FROM dbo.Sales;    
REVERT;

如果要避免权限模拟对数据安全带来的隐患，建议在断言函数中使用原始Login：

ORIGINAL_LOGIN( )

四，维护安全策略（Security Policy）

安全策略（Security Policy）适用于所有的用户，包括最高权限角色 sysadmin 和 db_owner 的成员，以及dbo用户，虽然这些成员拥有很高的权限，能够更改Security Policy的定义，甚至删除Security Policy，但是，在访问数据行时，仍然会受到Security Policy的影响，访问的数据是Filter 或Block的结果。一个User要想访问所有的数据行，必须在Predicate Function中显式定义。一般情况下，会设置一个管理RLS的Manager用户，用于维护Security Predicate控制的数据，必要时对数据处理进行故障排除。如果安全策略（Security Policy）被禁用，那么，用户在访问数据表时，不会Filter或Block任何数据行，看到的数据表的全部数据行。

1，启用或禁用安全策略（Security Policy）

--diable
ALTER SECURITY POLICY rls.SalesFilter  
WITH (STATE = OFF);  
--enable
ALTER SECURITY POLICY rls.SalesFilter  
WITH (STATE = ON);

2，更新安全策略

用户可以把安全策略应用到多个表上：

ALTER SECURITY POLICY rls.SalesFilter  
ADD FILTER PREDICATE rls.fn_securitypredicate(SalesRep)   
ON dbo.FactSales;

3，查看系统中的安全策略和断言

select p.object_id as security_policy_id
    ,s.name as security_policy_name
    ,s.is_enabled as security_policy_state
    ,s.is_schema_bound
    ,p.security_predicate_id
    ,p.target_object_id
    ,schema_name(o.schema_id)+'.'+object_name(p.target_object_id) as target_object_name
    ,p.predicate_definition
    ,p.predicate_type_desc
    ,p.operation_desc
from sys.security_predicates p
inner join sys.security_policies s
    on p.object_id=s.object_id
inner join sys.objects o
    on p.target_object_id=o.object_id

五，RLS对查询性能的影响

RLS会影响查询的性能，RLS 过滤断言在功能上等价于在查询的（Where）上追加一个条件。在创建安全策略时，应该使断言函数内的查询足够简单。

参考文档：

CREATE SECURITY POLICY (Transact-SQL)

Row-Level Security

posted @ 2017-01-17 20:18 悦光阴阅读(4449) 评论(5) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

悦光阴

请不要假装很努力，因为结果不会陪你演戏。