SQL Server未推出RLS(Row-Level Security)前,我們都是用AP的手段來完成客戶對於限閱戶資料的管控需求,
但畢竟是AP,有時還是沒辦法過濾到多筆資料回傳的情形,透過RLS中的Filter predicated,限閱戶資料可以處理的更簡單。
以前AP端限閱戶作法:
每一個客戶賦予幾種權限分數,比方說VIP客戶權限分數給80或90分,一般客戶就給60或70。
接著依據使用者權責也給分,比方服務VIP的CSR給80或90分,服務一般客戶CSR就給60或70。
最後是AP程式的判讀: 使用者擁有足夠權限就可以讀取資料(當使用者權限分數 >= 客戶權限分數)。
改用RLS作法:
使用者及客戶權限分數的給分作法不變,但因為判斷式要移到DB,DB連線時需要取得使用者身份及分數,
但通常DB連線帳號是應用系統帳號,如果用使用者帳號,我們則要在DB開數百個使用者帳號,這部分也有管理的難度,
這邊百敬老師建議可以搭配CONTEXT_INFO,透過AP塞人員權限分數到Context information,這樣DB端函數就有依據可以判讀。
設定步驟大致如下:
1.建立判斷函數(table value function)。
2.建立安全政策及使用剛剛的判斷函數(這邊先筆記查詢的部分,用filter predicated)。
3.啟用安全政策(Security polocy)。
首先建立測試初始資料庫、資料表及獨立schema,這邊我們在客戶資料表上加了一個權限分數的欄位(DataRights)。
開始寫入測試客戶資料,撲克牌K人物: 大衛王給90分、查理曼80分、凱撒70分、亞歷山大60分。
create table Customers(
ID int identity primary key,
Name nvarchar(40),
DataRights int
)
GO
INSERT INTO Customers VALUES
('David',90), ('Charlemagne',80), ('Caesar',70), ('Alexander',60)
GO
create schema rls
go
1.建立判斷函數(table value function)
這邊利用CONTEXT_INFO讓前端AP可以塞使用者的權限值進來。
當使用者權限值 >= 客戶資料表中的權限值時,回傳這一筆 "1":表示可讀取。
create function rls.AccessCustomerPredicate(@DataRights int)
returns table
with schemabinding
as
return SELECT
1 AS AccessResult
FROM dbo.Customers c
WHERE DataRights <= (SELECT CONVERT(INT, CONTEXT_INFO()))
and c.DataRights = @DataRights
GO
2.建立安全政策及使用剛剛的判斷函數(這邊先處理查詢的部分,用filter predicated)
create security policy rls.CustomerRLSPolicy
add filter predicate rls.AccessCustomerPredicate(DataRights) on dbo.Customers
GO
3.啟用安全政策security policy
alter security policy rls.CustomerRLSPolicy WITH(state=on)
1. 先給人員權限值90分,90>={90,80,70,60} : 4張老K國王都可以查詢的到。
DECLARE @BinVar varbinary(128) = CONVERT(BINARY(128),90);
SET CONTEXT_INFO @BinVar;
GO
SELECT * FROM Customers
2. 再給人員權限值70,70>={70,60} : 只能查到2張老K(凱撒(70)、亞歷山大(60))。
DECLARE @BinVar varbinary(128) = CONVERT(BINARY(128),70);
SET CONTEXT_INFO @BinVar;
GO
SELECT * FROM Customers
3.來試給50分,50>={?} : 查詢不到資料了。
最後透過AP端測試,以下是C#程式碼,給人員權限值80分。除了直接Sqlcommand text 給Context information外,還有兩個選擇:
- 如果是用EF6以上版本,可以參考這篇給Context information
- 或者寫好一段stored procedure,然後呼叫
using (SqlCommand cmd = new SqlCommand("", conn))
{
cmd.CommandText = @"
DECLARE @BinVar varbinary(128) = CONVERT(BINARY(128), 80);
SET CONTEXT_INFO @BinVar;";
cmd.ExecuteNonQuery();
cmd.CommandText =" SELECT * FROM Customers;";
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
DataTable table = new DataTable();
adapter.Fill(table);
}
80>={80,70,60} : 查詢結果:
小結:
- 如果要鎖Update或Delete可以用Blocking predicate。
- 鎖不住Insert/Trucate。
- 如果底層資料存取的部分有抽離主程式,改起來會順手多了。
參考: