sql语句大全之行级安全（Row-Level Security）

通过授予和拒绝（Grant/Deny）命令控制用户的权限，只能控制用户对数据库对象的访问权限，这意味着，用户访问的粒度是对象整体，可以是一个数据表，或视图等，用户要么能够访问数据库对象，要么没有权限访问，就是说，一个数据库对象，通过授予和拒绝用户的权限/角色（Permission或Role），无法使特定的数据行只允许特定身份的人访问，但是，该需求可以使用安全策略（Security Policy）实现。

当启用行级安全（Row-Level Security，简称RLS）时，Security Policy在数据行级别上控制用户的访问，粒度是数据行，控制用户只能访问数据表的特定数据行。断言（Predicate ）是逻辑表达式，返回的结果是布尔（boolean）值：true 或false。在SQL Server 2016中，RLS是基于安全断言（Security Predicate）的访问控制，Security Predicate是由内联表值函数实现的，当逻辑表达式返回结果时，安全断言的结果是True；当逻辑表达式不返回任何结果时，安全断言的结果是False。如果安全策略（Security Policy）被禁用，那么用户总是访问所有数据行，跟数据表上不关联任何安全策略一样。

 

实现RLS，必须显式定义三个组件：

• 数据表（Base Table）：用于存储数据行，在该表上创建Security Policy，使用RLS控制用户能够访问的数据行；
• 断言函数（Predicate Function）：是内联表值函数，用于执行安全断言，Security Policy调用该函数过滤数据行或阻塞写操作；
• 安全策略（Security Policy）：将数据表和断言函数绑定，并设置安全断言的类型；

一，内联表值函数定义安全断言（Security Predicate）

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

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

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'; 

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

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

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

1，过滤断言（Filter Predicate）

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

过滤断言（Filter Predicate）定义一个Security Policy，在Base Table上执行select，update和delete命令时，Security Policy透明地过滤数据行，应用程序不会意识到Filter操作的存在；应用程序能够插入任何数据，不管数据是否被过滤掉。

2，阻塞断言（Block Predicate）

阻塞断言（Block predicates）将Update操作拆分成两个独立的操作：Before Update 和 After Update。

Block Predicate影响所有的写操作，有四种阻塞操作：

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

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

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

三，使用Security Policy控制用户只能访问指定的数据

1，创建数据表，并插入数据

 

2，创建User，并授予查询权限

--create user
create user Sales1 without login;
create user Manager without login;
--grant permission
GRANT SELECT ON Sales TO Manager;  
GRANT SELECT ON Sales TO Sales1;  

3，创建内存表值函数，用于过滤数据行，返回Security Predicate 的结果

强烈推荐创建一个单独的Schema，用于RLS对象（Predicate Function和 Security Policy），本例中创建RLS Schema。

--create schema
create schema rls;  
authorization dbo;
 
--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';  

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

在新建的Security Policy中，将Base Table和Security Predicate 绑定，添加Filter Predicate，使用dbo.Sales作为过滤条件，启用新建的Security Policy

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

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

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; 

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

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

四，维护安全策略（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任何数据行，看到的数据表的全部数据行。

 

参考文档：

CREATE SECURITY POLICY (Transact-SQL)

Row-Level Security