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代码,伴随在水土保持代码即可兑现。

相关文章