Security Policy:行级安全(Row-Level Security)

2016-12-15 18:36:21来源:cnblogs.com作者:悦光阴人点击

第七城市

行级安全RLS(Row-Level Security)是在数据行级别上控制用户的访问,控制用户只能访问数据库表的特定数据行。断言是逻辑表达式,在SQL Server 2016中,RLS是基于安全断言(Security Predicate)的访问控制,Security Predicate是由内联表值函数实现的,默认是启用的。当安全断言返回结果时,逻辑表达式的结果是True;当安全断言不返回任何结果时,逻辑表达式的结果是False。如果安全断言被禁用,那么将总是返回True。

 

实现RLS需要定义三个组件:

  • 数据表,用于存储数据行,在该表上创建Security Policy,那么RLS将控制用户返回的数据行;
  • 断言函数,是内联表值函数,用于执行安全断言,Security Policy调用该函数过滤数据行或阻塞写操作;
  • Security Policy,将数据表和断言函数绑定,并设置安全断言的类型;

一,内联表值函数定义安全断言(Security Predicate)

访问数据行的权限受到安全断言(Security Predicate)的限制,Security Predicate 是在内联表值函数中定义的逻辑表达式,Security Policy调用内联表值函数,返回Security Predicate 的结果。在执行Security Policy时,仅当Security Predicate返回逻辑结果时,才允许访问数据;如果Security Predicate 不返回任何结果,不允许访问数据。如果在一个Base Table上创建了Security Policy,但是Security Predicate 被禁用,那么,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'; 

Access to row-level data in a table is restricted by a security predicate defined as an inline table-valued function. The function is then invoked and enforced by a security policy. For filter predicates, there is no indication to the application that rows have been filtered from the result set; if all rows are filtered, then a null set will be returned. For block predicates, any operations that violate the predicate will fail with an error.

二,过滤断言和阻塞断言(Filter 和 Block)

在Security Policy中,RLS支持两种类型的安全断言(Security Predicates):

  • Filter Predicate:在从Base Table读取数据行时,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,创建数据表,并插入数据

CREATE TABLE dbo.Sales  (  OrderID int not null,  SalesRep sysname,  Product varchar(10) not null,  Qty int not null );  INSERT dbo.Sales VALUES   (1, 'Sales1', 'Valve', 5),   (2, 'Sales1', 'Wheel', 2),   (3, 'Sales1', 'Valve', 4),  (4, 'Sales2', 'Bracket', 2),   (5, 'Sales2', 'Wheel', 5),   (6, 'Sales2', 'Seat', 5);

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

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

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

--create schemacreate schema rls;  authorization dbo; --create functionCREATE 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,将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 禁用或启用

--diableALTER SECURITY POLICY rls.SalesFilter  WITH (STATE = OFF);  --enableALTER SECURITY POLICY rls.SalesFilter  WITH (STATE = ON);  

 

参考文档:

CREATE SECURITY POLICY (Transact-SQL)

Row-Level Security

第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台