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

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

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

Max