SQL Server 2016 行级别权限决定

背景

设若我们来主要数据存储在一个表里面,比如人口表中包含员工、部门和薪金信息。只同意用户访问各自单位的音,但是未可知访问其他机关。一般我们都是以先后端实现这作用,而于sqlserver2016以后呢堪直接当多少库端实现者功效。

解决

安全已是一个数方面的主导问题,每一代之MS数据库都有关于安全地方的新力量,那么以Sql
Server 2016,也发出那么些及时方面的晋升,比如‘Row Level Security’, ‘Always
Encrypted’, ‘Dynamic Data Masking’, 和‘Enhancement of Transparent Data
Encryption’ 等等都见面起至平安者的企图。本篇我拿介绍有关Row Level
Security (RLS–行级别安全), 能够控制表中行的访问权限。RLS
能要我们根据实施查询人的性来控制基础数据,从而帮助我们爱地啊歧用户提透明的看数。行级安全性使客户能基于实施查询的用户之特性控制数据库中的实行。

为了贯彻RLS我们需要未雨绸缪下三只地方:

  1. 名为词函数
  2. 安全谓词
  3. 安全策略

次第描述方面三单地方

名词函数

称为词函数是一个搭的表值函数,用于检查用户执行之询问访问数是否基于其论理定义。这个函数返回一个1来表示用户可看。

安康谓词

安康谓词就是用称为词函数绑定到表里面,RLS提供了区区种安全谓词:过滤谓词和阻止谓词。过滤谓词即是以使SELECT,
UPDATE, 和
DELETE语句询问数据常常单是过滤数据而未见面报错。而阻止谓词即是在利用违反谓词逻辑的多少经常,显示地报错并且阻止用户采取
AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE 等操作。

安全策略

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

实例

实例中我们创建一个Person表和测试数据,最后咱们被匪知道用户访问各自部门的消息,代码如下:

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

 

 

此时表已经给创造,并且插入了测试数据,执行下告诉句检索有是有些记录:

SELECT * FROM Person

恰恰使所示,目前出三单机关department(CS,EC,IT),并且User_Access排表示各自的用户组。让咱们创建三单测试用户数据的账户语句如下:

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

 

在开创了用户组以后,授权读取权限给方是何人新建的用户,执行报告句如下:

---授予select权限给所有的用户

GRANT SELECT ON Person TO User_CS

GRANT SELECT ON Person TO User_EC

GRANT SELECT ON Person TO User_IT

 

今昔咱们创建一个叫做词函数,该函数是于查询用户是不可见的。

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

 

 

这个函数是仅仅回行,如果正在尽查询的用户之名字和User_Access
列匹配,那么用户同意看指定的履行。在创建该函数后,还待创造一个安全策略,使用方面的号称词函数PersonPredicate来对表进行过滤逻辑的绑定,脚本如下:

--安全策略

CREATE SECURITY POLICY PersonSecurityPolicy

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

WITH (STATE = ON)

 

 

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

更来拘禁一下查询结果:

这次询问没有回外履行,这意味着谓词函数的定义跟策略的开创后,用户查询需要有相应权限才会回来行,接下去使用不同用户来询问者数量,首先,我们之所以用户User_CS来询问一下结果:

EXECUTE AS USER = 'User_CS'

SELECT * FROM dbo.Person

REVERT

 

 

正好使所示,我们看只有三行数据数据该用户,User_CS,已经查找出来。因此,过滤函数将另不属该用户组的多少过滤了。

事实上这个查询执行之长河尽管是数据库里调用谓词函数,如下所示:

SELECT * FROM dbo.Person

WHERE User_Name() = ‘User_CS’

别两组用户的询问结果是一般的这里就是不一一演示了。

故,我们能够看实行查询根据用之两样得只属指定用户组的指定数量。这就是是咱若达的目的。

顶目前为止,我们都演示了过滤谓词,接下去我们演示一下什么样堵住谓词。执行如下语句来授权DML操作权限给用户。

--授权DML 权限

GRANT INSERT, UPDATE, DELETE ON Dbo.Person TO User_CS

GRANT INSERT, UPDATE, DELETE ON Dbo.Person TO User_EC

GRANT INSERT, UPDATE, DELETE ON Dbo.Person TO User_IT

 

俺们就此用户User_IT执行插入语句,并且插入用户组为UserCS的,语句如下:

EXECUTE AS USER = 'User_IT'

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

SELECT 'Soniya', 'CS', 35000, 'User_CS'

REVERT

 

but,竟然没有报错,插入成功了。

让我们在检查一下用户数据插入的情况:

EXECUTE AS USER = 'User_IT'

SELECT * FROM dbo.Person

REVERT

 

竟然,新插入行并不曾加塞儿到该用户组’User_IT’中。而是出现在了‘User_CS’
的用户组数据遭到。

--插入数据出现在了不同的用户组

EXECUTE AS USER = 'User_CS'

SELECT * FROM dbo.Person

REVERT

 

经上面的事例我们发现,过滤谓词不不见面阻拦用户插入数据,因此尚未错,这是以无于安全策略中定义阻止谓词。让咱在阻止谓词来显示报错,有四只阻止谓词AFTER
INSERT, AFTER UPDATE, BEFORE UPDATE, 和 BEFORE
DELETE可以采取。我们这边测试用AFTER INSERT
谓词。这个谓词阻止用户插入记录到无权力查看的数码用户组。

补给加谓词阻止的安全策略,代码如下:

--添加阻止谓词

ALTER SECURITY POLICY PersonSecurityPolicy

ADD BLOCK PREDICATE dbo.PersonPredicate(User_Access)

ON dbo.Person AFTER INSERT

 

今我们因此前好像代码再试一下,是否可以插数据:

EXECUTE AS USER = 'User_CS'

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

SELECT 'Sumit', 'IT', 35000, 'User_IT'

REVERT

 

* *

摩,果然这次错误有提示出现了,阻止了不同权用户的插。因此我们能够说经过添加阻止谓词,未授权用户之DML操作为限了。

小心:在例子中每个单位仅仅生一个用户做。如果在一个机关包含多只用户之气象下,我们用创造分支登录为每个用户还分配需的权,因为谓词函数应用为用户基础而安全策略取决于谓词函数。

 

实施级别安全的界定

此间有几乎独执行级别安全之限定:

  1. 号称词函数一定要含有WITH
    SCHEMABINDING关键词,如果函数没有拖欠重大字则绑定安全策略时会抛来很。
  2. 在推行了履行级别安全的表上不克创索引视图。
  3. 内存数据表不支持
  4. 全文索引不支持

总结

带有行级别安全力量的SQLServer2016,我们可免经应用程序级别之代码修改来兑现多少记录之权杖控制。行级别安全通过以谓词函数和安康政策实现,不需要修改各种DML代码,伴随着水土保持代码即可实现。

相关文章