iT邦幫忙

2017 iT 邦幫忙鐵人賽
DAY 10
1
Security

資料安全與簡單加密演算法見面會 系列 第 10

[Day10] 資料使用安全(限閱戶RLS)

負責的系統主要在處理金融支付後端的帳務作業,資料庫內總有些客戶資料(CIF),其中有些資料屬於VVIP(Very Very important man),每次看到香港或是美國電影總有演到臥底探員的檔案被加了密,只有具備一定權限以上的督察才能閱讀,常常這想除了分開兩個資料表的作法,有沒有什麼DB功能是row level的權限管理?

曾聽Oracle ERP顧問講到這個功能,後來因為研發的解決方案投票選擇微軟SQL Server,這個功能就設計成AP的手段來完成客戶對於限閱戶資料的管控需求。

但畢竟是AP,有時還是沒辦法過濾到多筆資料回傳的情形,今年的SQL Server 2016也到位了RLS,透過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分。

http://ithelp.ithome.com.tw/upload/images/20161225/20103434yqSGoa3j0J.png

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

http://ithelp.ithome.com.tw/upload/images/20161225/20103434TDvBJO98WA.png

  1. 再給人員權限值70,70>={70,60} : 只能查到2張老K(凱撒(70)、亞歷山大(60))。
DECLARE @BinVar varbinary(128) = CONVERT(BINARY(128),70);
SET CONTEXT_INFO  @BinVar;
GO
SELECT * FROM Customers

http://ithelp.ithome.com.tw/upload/images/20161225/20103434EYHlSQnB1W.png

3.來試給50分,50>={?} : 查詢不到資料了。

http://ithelp.ithome.com.tw/upload/images/20161225/20103434Z2iy6Fz9Y8.png

最後透過.NET 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} : 查詢結果:

http://ithelp.ithome.com.tw/upload/images/20161225/20103434DXirRSpfdo.png

小結:


  • 如果要鎖Update或Delete可以用Blocking predicate
  • 鎖不住Insert/Trucate
  • 哈!如果底層資料存取本來就有抽離主程式,改起來會順手多了。

參考:


SQL Server 2016 Row Level Security

SET CONTEXT_INFO


關門橋下的長州砲
http://ithelp.ithome.com.tw/upload/images/20161225/20103434uzCaftaCSM.jpg
2016.04攝於下關,日本


上一篇
[Day09] 資料儲存安全(磁碟加密BitLocker )
下一篇
[Day11] 資料使用安全(SQL資料遮罩)
系列文
資料安全與簡單加密演算法見面會 30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言