於中介層應用程式使用 Azure SQL Database V12 資料列層級安全性
本文大綱：
說明
上一篇我們介紹了如何使用 Azure SQL Database v12 的資料列層級安全性（Row-Level Security，以下簡稱 RLS）所提供的功能，來為 SELECT、UPDATE 與 DELETE 等 DML 動作進行特定條件的資料篩選，以防止未經授權的使用者試圖窺探不該查詢的資料，或降低因應用程式的疏忽導致資料外洩的影響，甚至還連帶降低誤刪或誤改資料所造成的衝擊。若您的應用程式是透過中介應用程式（Middle-Tier Application）來存取Azure SQL Database ，通常會在連線字串中使用單一的登入（Login）來進行連線，並於應用程式實作身分驗證機制來判斷使用者身份，進而作為寫入資料時的識別。如下圖所示，我們常會在資料表中使用某些資料行（或主索引鍵（Primary Key））來識別資料是由哪個使用者所寫入，並寫在查詢資料時透過 WHERE 敘述來篩選資料，例如 WHERE name = ‘Ou’。
如何在這樣的架構下限制登入應用程式的使用者只能存取自己寫入的資料， RLS 可以很輕易地滿足您這個需求，請見下一節的介紹。
使用 SET CONTEXT_INFO 建立工作階段的關聯
為了判斷目前執行的身份，以作為後續進行查詢時，RLS 可以自動進行資料的篩選，可以透過預存程序執行 SET CONTEXT_INFO 陳述式將應用程式的使用者名稱與工作階段進行關聯。
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P') DROP PROC usp_setContextInfoAsName GO
CREATE PROCEDURE usp_setContextInfoAsName(@name VARCHAR(10)) AS DECLARE @bin VARBINARY(128) = CONVERT(VARBINARY(128), @name) SET CONTEXT_INFO @bin
GO |
建立篩選述詞函式（Filter Predicate Function）
如同之前所提到，建議RLS所需的篩選述詞函式及安全性原則等物件都集中在特定的結構描述之中，因此下列程式碼首先會先建立結構描述。
接著建立篩選述詞函式並於函式中判斷應用程式所使用的登入是否為 dbo（您可以依照實際需求做調整），並將判斷傳入的應用程式的使用者轉型成 context_info 值進行比對。
IF EXISTS(SELECT * FROM sys.schemas WHERE name = 'rls') DROP SCHEMA rls
GO
CREATE SCHEMA rls
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'fn_FilterByName') DROP FUNCTION rls.fn_FilterByName
GO
CREATE FUNCTION rls.fn_FilterByName(@name VARCHAR(10)) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_result WHERE DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('dbo') AND CONTEXT_INFO() = CONVERT(VARBINARY(128), @name)
GO |
建立安全性原則（Security Policy）
最後則是建立安全性原則並使用前一節所建立的篩選述詞函式來套用到 MVP 資料表。
CREATE SECURITY POLICY rls.spo_AccessByName ADD FILTER PREDICATE rls.fn_FilterByName(name) ON dbo.MVP
GO |
測試資料列層級安全性
回顧前面所做的步驟，首先我們先建立預存程序用來設定與工作階段的關聯，接著建立篩選述詞函式，並在函式中判斷登入的身份以及比對應用程式所傳入的使用者名稱，最後建立安全性原則並套用到資料表。如此一來在查詢資料表前，若未使用預存程序 usp_setContextInfoAsName 來設定工作階段，直接查詢MVP資料表會查詢不到任何資料；反之，假設登入應用程式的身份為 Ou，只要在查詢資料前先執行下列 T-SQL 指令碼，就可以取得使用者 Ou 所允許查看的資料列。
EXEC usp_setContextInfoAsName 'Ou'
GO
SELECT * FROM dbo.MVP
GO |
執行結果如下：
防止未經授權的新增資料
預設情況下 RLS 並無法限制資料寫入是否合乎安全性原則，例如目前登入應用程式的身份為 terry ，但嘗試新增 name 並非 terry 的資料也一樣可以新增成功，例如下列的 T-SQL 指令碼。
INSERT INTO MVP(name, interest) VALUES ('sky','ASP.NET/IIS'),('sky','Visual Studio ALM'),('sky','Microsoft Azure') |
這看起來相當不合邏輯，若在多租戶（multi-tenancy）架構下，很可能會因此把資料寫到其他租戶可以看到的地方，要如何防止這種狀況發生？
首先可以建立一個用來檢查是否符合篩選述詞函式的純量函數（Scalar Function），在函數中會傳入應用程式的使用者名稱來判斷要新增的資料是否符合目前工作階段。
CREATE FUNCTION rls.fnCheckFilterdName(@name VARCHAR(10)) RETURNS bit AS BEGIN IF EXISTS(SELECT * FROM rls.fn_FilterByName(@name)) RETURN 1 RETURN 0 END GO |
接著將這個純量函數作為資料表的 CHECK 條件約束（Check Constraint）。
ALTER TABLE dbo.MVP WITH NOCHECK ADD CONSTRAINT chk_prvent_unauthorized_insert CHECK(rls.fnCheckFilterdName(name) = 1)
GO |
有了這個約束條件之後，若要再新增與目前工作階段不符的資料，則會出現下列的錯誤訊息。
參考資料
1. Azure SQL Database 資安講堂 - 認識資料列層級安全性
2. Building More Secure Middle-Tier Applications with Azure SQL Database using Row-Level Security
3. Row-Level Security: Blocking unauthorized INSERTs
4. Row-Level Security (Azure SQL Database)