[SQL Server]Row-Level Security實作限閱戶筆記(SQL Server 2016新功能)

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)

 

接著先透過SSMS下T-SQL測試

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

http://stackoverflow.com/questions/24168904/how-can-i-set-the-context-info-sql-statement-from-devforce-ideablade-app

  • 或者寫好一段stored procedure,然後呼叫

http://stackoverflow.com/questions/28584319/using-sql-server-context-info-feature-in-asp-net-enterprise-library-v5-abov

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。
  • 如果底層資料存取的部分有抽離主程式,改起來會順手多了。

 

參考:

SQL Server 2016 Row Level Security

SET CONTEXT_INFO