Azure解密系列文章是由微软资深工程师执笔，对Azure进行深度技术解析的文章，从技术层面介绍微软云安全、可靠、弹性、灵活等特性，让您了解您的每一份托付都有据可依！
这个数据爆炸的年代，数据安全性不可忽视，很多客户都曾经无数次的问到这个问题如何解决数据读取时候的安全性，如何实现用户分角色、分职位、分group来区分数据。简单来讲不同用户在读取数据时候，得到的数据不同。如下：这是一张病人统计表。执行的查询是：
SELECT * FROMpatients一共7条数据
而往往我们需要的是
根据不同医生或者护士查到的病人不同，
如：这是 护士： “小昭”负责的病人
一般方法是关联表，然后进行筛选查询
select*from patients a, staffDuties s,employees e
where
a.wing=s.wingand s.empid=e.empid orderby 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 mustchange 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 simplifydemo)
-- Add to corresponding role (in practice, these could also be WindowsGroups)
-- 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 ASaccessResult FROM
dbo.StaffDuties d INNER JOINdbo.Employees e ON (d.EmpId = e.EmpId)
WHERE
(
-- nurses can onlyaccess 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 seeall patients
IS_MEMBER('doctor') =1
)
go
----创建过滤策略
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(@wingint, @startTimedatetime, @endTimedatetime)
RETURNSTABLE
WITHSCHEMABINDING
AS
RETURNSELECT 1AS accessResultFROM
dbo.StaffDuties dINNERJOIN 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')来取得权限
---删除老策略
dropSECURITYPOLICY rls.PatientsSecurityPolicy
-----创建新策略
createSECURITYPOLICY rls.PatientsSecurityPolicy
ADDFILTERPREDICATE rls.accessPredicate(wing, startTime, endTime)ON dbo.patients,
ADDBLOCKPREDICATE 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*FROMsys.security_policies
SELECT*FROMsys.security_predicates
go
这样就可以用这个功能实现用户很爽的安全性的管理。那么Power BI又怎么来进行连接使用呢？敬请期待下一篇文章。
关注微软科技，关注Azure解密，把自己的数据安全保障记于心中！回复“Azure”或您对文章的见解与期望，将获得您在微软科技的专属标签，就有机会获得对应产品的线下体验活动或粉丝回馈活动，快快戳留言吧！
1. Azure解密 | Azure SQL的DTU和eDTU到底是个什么鬼
2. 微软携手世纪互联 可信云成功植根中国三周年
3. 微软发布Azure Stack第三版技术预览 提供混合应用创新以及按用量付费定价模式
