SQL Server 2016 行级别权限控制

时间：2022-09-07 15:27:13

背景

假如我们有关键数据存储在一个表里面，比如人员表中包含员工、部门和薪水信息。只允许用户访问各自部门的信息，但是不能访问其他部门。一般我们都是在程序端实现这个功能，而在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

SQL Server 2016 行级别权限控制

正如所示，目前有三个部门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。

再来看一下查询结果：

SQL Server 2016 行级别权限控制

这次查询没有返回任何行，这意味着谓词函数的定义和策略的创建后，用户查询需要具有相应权限才能返回行，接下来使用不同用户来查询这个数据，首先，我们用用户User_CS来查询一下结果:

EXECUTE AS USER = 'User_CS'

SELECT * FROM dbo.Person

REVERT

 

SQL Server 2016 行级别权限控制

 

正如所示，我们看到只有三行数据数据该用户，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

 

SQL Server 2016 行级别权限控制

通过上面的例子我们发现，过滤谓词不不会阻止用户插入数据，因此没有错误，这是因为没有在安全策略中定义阻止谓词。让我们加入阻止谓词来显示报错，有四个阻止谓词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

 

SQL Server 2016 行级别权限控制

 

擦，果然这次错误出提示出现了，阻止了不同权限用户的插入。因此我们能说通过添加阻止谓词，未授权用户的DML操作被限制了。

注意：在例子中每个部门只有一个用户组成。如果在一个部门包含多个用户的情况下，我们需要创建分支登录为每个用户都分配需要的权限，因为谓词函数应用于用户基础并且安全策略取决于谓词函数。

 

行级别安全的限制

这里有几个行级别安全的限制：

  1. 谓词函数一定要带有WITH SCHEMABINDING关键词，如果函数没有该关键字则绑定安全策略时会抛出异常。
  2. 在实施了行级别安全的表上不能创建索引视图。
  3. 内存数据表不支持
  4. 全文索引不支持

总结

带有行级别安全功能的SQLServer2016,我们可以不通过应用程序级别的代码修改来实现数据记录的权限控制。行级别安全通过使用谓词函数和安全策略实现，不需要修改各种DML代码，伴随着现有代码即可实现。

标签：

相关文章