澳门金沙vip 5

行级安全(Row-Level Security)

背景

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

假如我们有关键数据存储在一个表里面,比如人员表中包含员工、部门和薪水信息。只允许用户访问各自部门的信息,但是不能访问其他部门。一般我们都是在程序端实现这个功能,而在sqlserver2016以后也可以直接在数据库端实现这个功能。

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

解决

 澳门金沙vip 1

安全已经是一个数据方面的核心问题,每一代的MS数据库都有关于安全方面的新功能,那么在Sql
Server 2016,也有很多这方面的升级,比如‘Row Level Security’, ‘Always
Encrypted’, ‘Dynamic Data Masking’, 和‘Enhancement of Transparent Data
Encryption’ 等等都会起到安全方面的作用。本篇我将介绍关于Row Level
Security (RLS–行级别安全), 能够控制表中行的访问权限。RLS
能使我们根据执行查询人的属性来控制基础数据,从而帮助我们容易地为不同用户提透明的访问数据。行级安全性使客户能够根据执行查询的用户的特性控制数据库中的行。

实现RLS,必须显式定义三个组件:

为了实现RLS我们需要准备下面三个方面:

  • 数据表(Base Table):用于存储数据行,在该表上创建Security
    Policy,使用RLS控制用户能够访问的数据行;
  • 断言函数(Predicate
    Function):
    是内联表值函数,用于执行安全断言,Security
    Policy调用该函数过滤数据行或阻塞写操作;
  • 安全策略(Security
    Policy):
    将数据表和断言函数绑定,并设置安全断言的类型;
  1. 谓词函数
  2. 安全谓词
  3. 安全策略

一,内联表值函数定义安全断言(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),该表达式根据用户名作为断言控制用户访问的数据行:

谓词函数是一个内置的表值函数,用于检查用户执行的查询访问数据是否基于其逻辑定义。这个函数返回一个1来表示用户可以访问。

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)

安全谓词就是将谓词函数绑定到表里面,RLS提供了两种安全谓词:过滤谓词和阻止谓词。过滤谓词就是在使用SELECT,
UPDATE, 和
DELETE语句查询数据时只是过滤数据但是不会报错。而阻止谓词就是在使用违反谓词逻辑的数据时,显示地报错并且阻止用户使用
AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE 等操作。

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

安全策略

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

安全策略对象专门为行级别安全创建,分组所有涉及谓词函数的安全谓词。

1,过滤断言(Filter Predicate)

实例

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

实例中我们创建一个Person表和测试数据,最后我们让不懂得用户访问各自部门的信息,代码如下:

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

Create table dbo.Person

(

PersonId INT IDENTITY(1,1),

PersonName varchar(100),

Department varchar(100),

Salary INT,

User_Access varchar(50)

)

GO

INSERT INTO Person (PersonName, Department, Salary, User_Access)

SELECT 'Ankit', 'CS', 40000, 'User_CS'

UNION ALL

SELECT 'Sachin', 'EC', 20000, 'User_EC'

UNION ALL

SELECT 'Kapil', 'CS', 30000, 'User_CS'

UNION ALL

SELECT 'Ishant', 'IT', 50000, 'User_IT'

UNION ALL

SELECT 'Aditya', 'EC', 45000, 'User_EC'

UNION ALL

SELECT 'Sunny', 'IT', 60000, 'User_IT'

UNION ALL

SELECT 'Rohit', 'CS', 55000, 'User_CS'

GO

2,阻塞断言(Block Predicate)

 

阻塞断言(Block predicates)将Update操作拆分成两个独立的操作:Before
Update 和 After Update。

 

Block Predicate影响所有的写操作,有四种阻塞操作:

此时表已经被创建,并且插入了测试数据,执行下面语句检索有是有的记录:

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

SELECT * FROM Person

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

澳门金沙vip 2

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

正如所示,目前有三个部门department(CS,EC,IT),并且User_Access列表示各自的用户组。让我们创建三个测试用户数据的账户语句如下:

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

--For CS department

CREATE USER User_CS WITHOUT LOGIN

--For EC department

CREATE USER User_EC WITHOUT LOGIN

-- For IT Department

CREATE USER User_IT WITHOUT LOGIN

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

 

澳门金沙vip 3澳门金沙vip 4

在创建了用户组以后,授权读取权限给上面是哪个新建的用户,执行语句如下:

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);
---授予select权限给所有的用户

GRANT SELECT ON Person TO User_CS

GRANT SELECT ON Person TO User_EC

GRANT SELECT ON Person TO User_IT

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;  
----Create function

CREATE FUNCTION dbo.PersonPredicate

( @User_Access AS varchar(50) )

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN SELECT 1 AS AccessRight

WHERE @User_Access = USER_NAME()

GO

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

这个函数是只返回行,如果正在执行查询的用户的名字与User_Access
列匹配,那么用户允许访问指定的行。在创建该函数后,还需要创建一个安全策略,使用上面的谓词函数PersonPredicate来对表进行过滤逻辑的绑定,脚本如下:

4,创建和启动安全策略(Security Policy)

--安全策略

CREATE SECURITY POLICY PersonSecurityPolicy

ADD FILTER PREDICATE dbo.PersonPredicate(User_Access) ON dbo.Person

WITH (STATE = ON)

在新建的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)**

State(状态)为ON才能是策略生效,如果打算关闭策略,你可以改变状态为OFF。

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)**

澳门金沙vip 5

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