沈云狈癞，資深工程師履羞，微軟解決方案專家
這個(gè)數(shù)據(jù)爆炸的年代峦萎，數(shù)據(jù)安全性不可忽視，很多客戶都曾經(jīng)無數(shù)次的問到這個(gè)問題如何解決數(shù)據(jù)讀取時(shí)候的安全性忆首，如何實(shí)現(xiàn)用戶分角色爱榔、分職位、分group來區(qū)分?jǐn)?shù)據(jù)糙及。簡單來講不同用戶在讀取數(shù)據(jù)時(shí)候详幽，得到的數(shù)據(jù)不同。如下：這是一張病人統(tǒng)計(jì)表浸锨。執(zhí)行的查詢是：
SELECT * FROM patients? 一共7條數(shù)據(jù)
而往往我們需要的是
根據(jù)不同醫(yī)生或者護(hù)士查到的病人不同唇聘，如：
這是 護(hù)士： “小昭”負(fù)責(zé)的病人
一般方法是關(guān)聯(lián)表，然后進(jìn)行篩選查詢
select * from patients a, staffDuties s,employees e
where
a.wing=s.wing and s.empid=e.empid
order by s.empid
是這樣的結(jié)果
這樣再去按照需要進(jìn)行where過濾揣钦。如
但是這樣做雳灾，代碼復(fù)雜，并且難于控制冯凹。安全性不高谎亩。因此在SQL2016里面出現(xiàn)了行級(jí)安全性來解決。
如何來解決呢宇姚。我們來看看實(shí)現(xiàn)的效果匈庭。
-- 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
執(zhí)行的相同的查詢語句：SELECT * FROM patients;只是按照不同的身份去執(zhí)行
而數(shù)據(jù)庫給出的結(jié)果完全是不同的，依賴于身份的權(quán)限浑劳。
在應(yīng)用上通過用戶登錄的信息獲得數(shù)據(jù)庫的權(quán)限阱持。記得反饋不同結(jié)果。那么如何實(shí)現(xiàn)的呢魔熏。這個(gè)測試庫的代碼如下：
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
創(chuàng)建好數(shù)據(jù)庫后衷咽。 創(chuàng)建行級(jí)安全性代碼
CREATE SCHEMA rls? ---創(chuàng)建行級(jí)安全性構(gòu)架
go
---創(chuàng)建一個(gè)內(nèi)聯(lián)表值函數(shù)
---根據(jù)用戶信息鸽扁。房間號(hào)，開始時(shí)間镶骗，結(jié)束時(shí)間進(jìn)行過濾.
---如果是醫(yī)生就能查看所有的信息 返回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
----創(chuàng)建過濾策略
---并且設(shè)置了更新策略桶现，也就是說只有讀權(quán)限沒有update權(quán)限
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
結(jié)果就可以如上圖了：
另外執(zhí)行update結(jié)果
可以看到無法修改數(shù)據(jù)。直接從根本解決了數(shù)據(jù)訪問問題
那么問題又來了鼎姊！
普通的用戶不使用windows驗(yàn)證骡和，也不使用SQL用戶驗(yàn)證，也就是說所有用戶都是使用一個(gè)SQL連接用戶到數(shù)據(jù)庫相寇，這樣就玩不了慰于，因?yàn)闄?quán)限是一樣的。當(dāng)然微軟肯定會(huì)想到這點(diǎn)唤衫。給出了更好玩的方案：
我們來修改下策略：
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')來取得權(quán)限
---刪除老策略
drop SECURITY POLICY rls.PatientsSecurityPolicy
-----創(chuàng)建新策略
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
執(zhí)行結(jié)果：
這樣的話只需要在用戶登錄系統(tǒng)時(shí)候 在 SESSION_CONTEXT中設(shè)置不同的用戶ID后婆赠，可以通過從Sales表進(jìn)行選擇，來模擬連接篩選战授。 在實(shí)踐中页藻，應(yīng)用程序負(fù)責(zé)在打開連接后在SESSION_CONTEXT中設(shè)置當(dāng)前用戶ID
語法：
EXEC sp_set_session_context @key=N'empid', @value=1003;
另外：
下面視圖可以看到策略權(quán)限
SELECT * FROM sys.security_policies
SELECT * FROM sys.security_predicates
go
這樣就可以用這個(gè)功能實(shí)現(xiàn)用戶很爽的安全性的管理。
立即訪問http://market.azure.cn
