沈云，资深工程师，微软解决方案专家
这个数据爆炸的年代，数据安全性不可忽视，很多客户都曾经无数次的问到这个问题如何解决数据读取时候的安全性，如何实现用户分角色、分职位、分group来区分数据。简单来讲不同用户在读取数据时候，得到的数据不同。如下：这是一张病人统计表。执行的查询是：
SELECT * FROM patients 一共7条数据
而往往我们需要的是
根据不同医生或者护士查到的病人不同，如：
这是 护士： “小昭”负责的病人
一般方法是关联表，然后进行筛选查询
select * from patients a, staffDuties s,employees e
where
a.wing=s.wing and s.empid=e.empid
order by s.empid
是这样的结果
这样再去按照需要进行where过滤。如
但是这样做，代码复杂，并且难于控制。安全性不高。因此在SQL2016里面出现了行级安全性来解决。
如何来解决呢。我们来看看实现的效果。
-- Impersonate various users in the system (for demo purposes)
EXECUTE ('SELECT * FROM patients;') AS USER = 'nurse_BartonC'; --3
EXECUTE ('SELECT * FROM patients;') AS USER = 'nurse_AllenM'; --4
EXECUTE ('SELECT * FROM patients;') AS USER = 'nurse_NightingaleF'; --2
EXECUTE ('SELECT * FROM patients;') AS USER = 'doctor_ApgarV'; --7
EXECUTE ('SELECT * FROM patients;') AS USER = 'doctor_CharcotJ'; --7
执行的相同的查询语句：SELECT * FROM patients;只是按照不同的身份去执行
而数据库给出的结果完全是不同的，依赖于身份的权限。
在应用上通过用户登录的信息获得数据库的权限。记得反馈不同结果。那么如何实现的呢。这个测试库的代码如下：
CREATE DATABASE RLS_Hospital_Demo
USE RLS_Hospital_Demo -- note, if you're on Azure SQL Database, you must change the connection manually
go
CREATE TABLE [patients] (
patientId INT PRIMARY KEY,
name nvarchar(256),
room int,
wing int,
startTime datetime,
endTime datetime
)
CREATE TABLE [employees] (
empId int PRIMARY KEY,
name nvarchar(256),
databasePrincipalId int
)
CREATE TABLE [staffDuties] (
empId int,
wing int,
startTime datetime,
endTime datetime
)
CREATE TABLE [wings] (
wingId int PRIMARY KEY,
name nvarchar(128)
)
go
CREATE ROLE [nurse]
CREATE ROLE [doctor]
go
GRANT SELECT, UPDATE ON [patients] to [nurse]
GRANT SELECT, UPDATE ON [patients] to [doctor]
go
-- Create a user for each nurse & doctor (without logins to simplify demo)
-- Add to corresponding role (in practice, these could also be Windows Groups)
-- Add to employees table
CREATE USER [nurse_BartonC] WITHOUT LOGIN
ALTER ROLE [nurse] ADD MEMBER [nurse_BartonC]
INSERT INTO [employees] VALUES ( 1001, N'张三丰', DATABASE_PRINCIPAL_ID('nurse_BartonC'));
go
CREATE USER [nurse_AllenM] WITHOUT LOGIN
ALTER ROLE [nurse] ADD MEMBER [nurse_AllenM]
INSERT INTO [employees] VALUES ( 1002, N'小静', DATABASE_PRINCIPAL_ID('nurse_AllenM') );
go
CREATE USER [nurse_NightingaleF] WITHOUT LOGIN
ALTER ROLE [nurse] ADD MEMBER [nurse_NightingaleF]
INSERT INTO [employees] VALUES ( 1003, N'小昭', DATABASE_PRINCIPAL_ID('nurse_NightingaleF'));
go
CREATE USER [doctor_ApgarV] WITHOUT LOGIN
ALTER ROLE [doctor] ADD MEMBER [doctor_ApgarV]
INSERT INTO [employees] VALUES ( 2001, N'张无忌', DATABASE_PRINCIPAL_ID('doctor_ApgarV'));
go
CREATE USER [doctor_CharcotJ] WITHOUT LOGIN
ALTER ROLE [doctor] ADD MEMBER [doctor_CharcotJ]
INSERT INTO [employees] VALUES ( 2002, N'令狐冲', DATABASE_PRINCIPAL_ID('doctor_CharcotJ'));
go
INSERT INTO wings VALUES( 1, N'North');
INSERT INTO wings VALUES( 2, N'South');
INSERT INTO wings VALUES( 3, N'Emergency');
go
INSERT INTO [patients] VALUES ( 01, N'田伯光', 101, 1, '12-17-2017', '03-26-2017')
INSERT INTO [patients] VALUES ( 02, N'岳不群', 102, 1, '10-27-2016', '05-27-2017')
INSERT INTO [patients] VALUES ( 05, N'邓八公', 107, 1, '5-7-2016', '11-6-2016')
INSERT INTO [patients] VALUES ( 03, N'丹青生', 203, 2, '3-8-2016', '12-14-2016')
INSERT INTO [patients] VALUES ( 04, N'仇松年', 205, 2, '1-27-2016', '12-5-2016')
INSERT INTO [patients] VALUES ( 06, N'于人豪', 301, 3, '1-31-2016', null)
INSERT INTO [patients] VALUES ( 07, N'不戒', 308, 3, '6-15-2016', '9-4-2016')
INSERT INTO [staffDuties] VALUES ( 1001, 1, '01-01-2016', '12-31-2016' )
INSERT INTO [staffDuties] VALUES ( 1001, 2, '01-01-2017', '12-31-2017' )
INSERT INTO [staffDuties] VALUES ( 1002, 1, '01-01-2016', '06-30-2016' )
INSERT INTO [staffDuties] VALUES ( 1002, 2, '07-01-2016', '12-31-2016' )
INSERT INTO [staffDuties] VALUES ( 1002, 3, '01-01-2017', '12-31-2017' )
INSERT INTO [staffDuties] VALUES ( 1003, 3, '01-01-2016', '12-31-2017' )
INSERT INTO [staffDuties] VALUES ( 2001, 1, '01-01-2016', '12-31-2016' )
INSERT INTO [staffDuties] VALUES ( 2001, 3, '01-01-2017', '12-31-2017' )
INSERT INTO [staffDuties] VALUES ( 2002, 1, '01-01-2016', '12-31-2017' )
go
-- END SETUP
创建好数据库后。 创建行级安全性代码
CREATE SCHEMA rls ---创建行级安全性构架
go
---创建一个内联表值函数
---根据用户信息。房间号，开始时间，结束时间进行过滤.
---如果是医生就能查看所有的信息 返回1
CREATE FUNCTION rls.accessPredicate(@wing int, @startTime datetime, @endTime datetime)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS accessResult FROM
dbo.StaffDuties d INNER JOIN dbo.Employees e ON (d.EmpId = e.EmpId)
WHERE
(
-- nurses can only access patients who overlap with their wing assignments
IS_MEMBER('nurse') = 1
AND e.databasePrincipalId = DATABASE_PRINCIPAL_ID()
AND @wing = d.Wing
AND
(
d.endTime >= @startTime AND d.startTime <= ISNULL(@endTime, GETDATE())
)
)
OR
(
-- doctors can see all patients
IS_MEMBER('doctor') = 1
)
go
----创建过滤策略
---并且设置了更新策略，也就是说只有读权限没有update权限
CREATE SECURITY POLICY rls.PatientsSecurityPolicy
ADD FILTER PREDICATE rls.accessPredicate(wing, startTime, endTime) ON dbo.patients,
ADD BLOCK PREDICATE rls.accessPredicate(wing, startTime, endTime) ON dbo.patients AFTER UPDATE
Go
结果就可以如上图了：
另外执行update结果
可以看到无法修改数据。直接从根本解决了数据访问问题
那么问题又来了！
普通的用户不使用windows验证，也不使用SQL用户验证，也就是说所有用户都是使用一个SQL连接用户到数据库，这样就玩不了，因为权限是一样的。当然微软肯定会想到这点。给出了更好玩的方案：
我们来修改下策略：
alter FUNCTION rls.accessPredicate(@wing int, @startTime datetime, @endTime datetime)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS accessResult FROM
dbo.StaffDuties d INNER JOIN dbo.Employees e ON (d.EmpId = e.EmpId)
WHERE
(
d.EmpId=CAST(SESSION_CONTEXT(N'empid') AS int)
AND @wing = d.Wing
)
Go
通过CAST(SESSION_CONTEXT(N'empid')来取得权限
---删除老策略
drop SECURITY POLICY rls.PatientsSecurityPolicy
-----创建新策略
create SECURITY POLICY rls.PatientsSecurityPolicy
ADD FILTER PREDICATE rls.accessPredicate(wing, startTime, endTime) ON dbo.patients,
ADD BLOCK PREDICATE rls.accessPredicate(wing, startTime, endTime) ON dbo.patients AFTER UPDATE
go
执行结果：
这样的话只需要在用户登录系统时候 在 SESSION_CONTEXT中设置不同的用户ID后，可以通过从Sales表进行选择，来模拟连接筛选。 在实践中，应用程序负责在打开连接后在SESSION_CONTEXT中设置当前用户ID
语法：
EXEC sp_set_session_context @key=N'empid', @value=1003;
另外：
下面视图可以看到策略权限
SELECT * FROM sys.security_policies
SELECT * FROM sys.security_predicates
go
这样就可以用这个功能实现用户很爽的安全性的管理。
立即访问http://market.azure.cn
