VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > sql数据库 >
  • 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,创建数据表,并插入数据

 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

作者悦光阴
本文版权归作者和博客园所有,欢迎转载,但未经作者同意,必须保留此段声明,且在文章页面醒目位置显示原文连接,否则保留追究法律责任的权利。
 
分类: SQL Server


相关教程