专栏名称: 微软科技
微软中国的认证公众号，分享微软产品资讯，技术方案，成功案例，行业视野，精彩活动。第一时间获取微软在云计算，大数据，移动，企业社交商务等话题的解决方案。
目录
相关文章推荐
51好读  ›  专栏  ›  微软科技

Azure解密 | 解决数据分权访问--SQL2016行级安全解决方案

微软科技  · 公众号  · 科技媒体  · 2017-03-05 09:59

正文

请到「今天看啥」查看全文


Azure解密系列文章是由微软资深工程师执笔，对Azure进行深度技术解析的文章，从技术层面介绍微软云安全、可靠、弹性、灵活等特性，让您了解您的每一份托付都有据可依！

这个数据爆炸的年代，数据安全性不可忽视，很多客户都曾经无数次的问到这个问题如何解决数据读取时候的安全性，如何实现用户分角色、分职位、分group来区分数据。简单来讲不同用户在读取数据时候，得到的数据不同。如下：这是一张病人统计表。执行的查询是：

SELECT * FROMpatients 一共 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 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 ( @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

这样就可以用这个功能实现用户很爽的安全性的管理。那么 Power BI 又怎么来进行连接使用呢？敬请期待下一篇文章。

关注微软科技，关注Azure解密，把自己的数据安全保障记于心中！回复“ Azure ”或您对文章的见解与期望，将获得您在微软科技的专属标签，就有机会获得对应产品的线下体验活动或粉丝回馈活动，快快戳留言吧！


推荐阅读

1. Azure解密 | Azure SQL的DTU和eDTU到底是个什么鬼

2. 微软携手世纪互联 可信云成功植根中国三周年

3. 微软发布Azure Stack第三版技术预览 提供混合应用创新以及按用量付费定价模式







请到「今天看啥」查看全文


推荐文章
乒乓time  ·  『曝光』国家队女乒训练大解密，丁宁挨踹！
7 年前
IPO案例库  ·  刚刚，这位注册会计师火了！
7 年前
TripAdvisor猫途鹰  ·  这种孤高的行为
7 年前
哲学人  ·  “思”与思维着的我——笛卡尔和康德自我观的异同
7 年前
正略管理评论  ·  毒舌高晓松怒批清华博士学霸：名校跟技校没什么区别！
7 年前
Sov5搜索   ·   小百科   ·   今天看啥   ·   移动版
51好读 - 好文章就要读起来!