• 行级安全（Row

    通过授予和拒绝（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，创建数据表，并插入数据

     View Code

    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
  • 相关阅读:
    给花花看的链接
    Arguments Optional闭包
    对闭包最好的解释
    Everything Be True判断对象是否存在指定的属性且有值
    Binary Agents将二进制句子翻译成英文
    伤情鉴定和伤残鉴定
    Steamroller 数组扁平化处理
    Drop it
    2021年flag-300+道算法～～～
    微服务之玉麟宝典，呕心沥血的日常
  • 原文地址：https://www.cnblogs.com/wangsicongde/p/7551057.html
Copyright © 2020-2023  润新知