解决数据分权访问----SQL 2016 行级安全解决方案

沈云，资深工程师，微软解决方案专家

这个数据爆炸的年代，数据安全性不可忽视，很多客户都曾经无数次的问到这个问题如何解决数据读取时候的安全性，如何实现用户分角色、分职位、分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