史丹利好熱

 

以前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

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