於中介層應用程式使用資料列層級安全性

  • Article

本文大綱：

說明

使用 SET CONTEXT_INFO建立工作階段的關聯

防止未經授權的新增資料

套用資料列層級安全性到所有資料表

參考資料

說明

在上一篇我們介紹了如何使用 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

有了這個約束條件之後，若要再新增與目前工作階段不符的資料，則會出現下列的錯誤訊息，以阻止資料寫入到其他租戶的情況發生。

套用資料列層級安全性到所有資料表

要將 RLS 套用到已開發的多租戶應用程式（multi-tenant applications），逐一建立篩選述詞函式及安全性原則，可想而知會是一個大工程，所幸在 CodePlex 日前提供 Script Helper 可用來幫助更有效率的將 RLS 套用到所有資料表。

從 CodePlex 下載並解壓縮 RLS Sample 範例程式會看到 4 個 T-SQL 指令碼檔案（如下圖），簡單說明如下：

  • RLS-Auto-Enable.sql 提供套用 RLS 到所有資料表所需的預存程序，待會會說明如何使用。

  • RLS-Blocking-Inserts.sql 示範如何在啟用 RLS 時透過 CHECK 條件約束防止未經授權的新增資料。

  • RLS-Middle-Tier-Apps-Demo.sql 示範多租戶環境下中介應用程式如何使用 RLS。

  • RLS-Middle-Tier-Apps-Demo-using_disjunctions.sql 示範進階使用者（Super Users）在查詢啟用 RLS 的資料表時有關效能方面應注意的部分。

     

本節的重點在於 RLS-Auto-Enable.sql，當您執行該指令碼後，會建立一個名稱為 sp_enable_rls_auto 的預存程序，用來幫助您產生或直接執行 RLS 的相關設定，其參數說明如下：

  • rlsColType，多租戶應用程式所使用的租戶 ID，目前支援的資料型態有 short、int 及 bigint。

  • rlsColName，多租戶應用程式租戶 ID 的資料行名稱。

  • applyToSchema，預設為 null。設定套用安全性原則的結構描述名稱，若未指定則表示所有結構描述。

  • deactivateExistingPolicies，預設為 0。若設為 1 則會停用現有的安全性原則到目的資料表。

  • rlsSchemaName，RLS 物件的結構描述名稱。

  • rlsPredicateFunctionName，RLS 述詞函述名稱。

  • isNullAdmin，預設為 0。用來設定是否允許 CONTEXT_INFO 等於 null。

  • isNullAdminOptimized，預設為 1。用來設定是否針對進階使用者的查詢作最佳化處理。

  • restrictedToAppUserName，預設為 null。用來設定是否限制使用者存取資料列。

  • printCommands，預設為 1。用來設定是否於執行結果視窗印出 RLS 的相關指令碼。

  • runCommands，預設為 0。用來設定是否直接執行 Script Helper 所產生的篩選述詞函式及安全性原則。

您可以依照應用程式的架構及情境來執行這個 Script Helper，它會幫您產生建立 RLS 所需的篩選述詞函式及安全性原則，甚至還可以針對進階使用者使用 RLS 進行查詢最佳化（如下圖）。

參考資料

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)

5. CONTEXT_INFO (Transact-SQL)

6. SET CONTEXT_INFO (Transact-SQL)

7. Row-Level Security for Middle-Tier Apps – Using Disjunctions in the Predicate

8. Apply Row-Level Security to all tables -- helper script