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列表示各自的用户组。让我们创建三个测试用户数据的账户语句如下：