在 Data API builder 中使用工作階段內容實作資料列層級安全性

使用 SQL 的 工作階段內容 功能，在資料 API 產生器中實作資料列層級安全性。

這很重要

使用 SQL Server 列級安全性的會話上下文與 Data API 建置器的資料庫政策不同。 資料庫政策（例如） --policy-database "@item.owner eq @claims.user_id"會由 Data API 建構器轉譯成 WHERE 子句，而 session context 則將聲明轉發至 SQL Server，讓 SQL 原生的列層級安全負責過濾。

先決條件

• 現有的 SQL 伺服器和資料庫。
• 數據 API 產生器 CLI。 安裝 CLI

備註

會話上下文支援於：

• SQL Server 2016 及更新版本
• Azure SQL Database
• Azure Synapse Analytics （Dedicated SQL pool）
• Azure Synapse Analytics（Serverless SQL pool）不被支援

建立 SQL 資料表和數據

建立含有虛構數據的數據表，以在此範例案例中使用。

1. 使用您慣用的用戶端或工具連線到 SQL 資料庫。

2. 使用名稱為 Revenues 的資料表，建立具有 id、category、revenue 和 accessible_role 欄位。

   DROP TABLE IF EXISTS dbo.Revenues;
   
   CREATE TABLE dbo.Revenues(
       id int PRIMARY KEY,  
       category varchar(max) NOT NULL,  
       revenue int,  
       accessible_role varchar(max) NOT NULL  
   );
   GO
   

3. 在 Revenues 表格中插入四行樣本。

   INSERT INTO dbo.Revenues VALUES
       (1, 'Book', 5000, 'Oscar'),  
       (2, 'Comics', 10000, 'Oscar'),  
       (3, 'Journals', 20000, 'Hannah'),  
       (4, 'Series', 40000, 'Hannah')
   GO
   

   在這個例子中，欄位 accessible_role 儲存可存取該列的角色名稱。

小提示

常見的會話上下文使用案例：

• 基於角色的篩選（如圖所示）使用 roles
• 使用多租戶隔離 tenant_id
• 使用者專用過濾 user_id

1. 使用簡單的 SELECT * 查詢來測試您的數據。

   SELECT * FROM dbo.Revenues
   

2. 建立名為 RevenuesPredicate的函式。 此函式會根據目前的會話內容來篩選結果。

   CREATE FUNCTION dbo.RevenuesPredicate(@accessible_role varchar(max))
   RETURNS TABLE
   WITH SCHEMABINDING
   AS RETURN SELECT 1 AS fn_securitypredicate_result
   WHERE @accessible_role = CAST(SESSION_CONTEXT(N'roles') AS varchar(max));
   

3. 使用函式建立名為 RevenuesSecurityPolicy 的安全策略。

   CREATE SECURITY POLICY dbo.RevenuesSecurityPolicy
   ADD FILTER PREDICATE dbo.RevenuesPredicate(accessible_role)
   ON dbo.Revenues;
   

備註

此 WITH SCHEMABINDING 子句是安全政策中使用函式所必需的，因此底層架構的變更不會使謂詞失效。

（可選）建立儲存程序

本節展示了一個簡單的「hello world」模式，用於直接在 T-SQL 中使用會話上下文值。

1. 建立一個儲存程序來讀取 roles 會話上下文值並用它來過濾結果。

   CREATE OR ALTER PROCEDURE dbo.GetRevenuesForCurrentRole
   AS
   BEGIN
       SET NOCOUNT ON;
   
       DECLARE @role varchar(max) = CAST(SESSION_CONTEXT(N'roles') AS varchar(max));
   
       SELECT id, category, revenue, accessible_role
       FROM dbo.Revenues
       WHERE accessible_role = @role;
   END
   GO
   

執行工具

執行數據 API 產生器 （DAB） 工具來產生組態檔和單一實體。

1. 將 --set-session-context 設定為 true 時，建立新的組態。

   dab init \
       --database-type mssql \
       --connection-string "<sql-connection-string>" \
       --set-session-context true \
       --auth.provider Simulator
   

   當 SQL Server 啟用會話上下文時，Data API 建構器會透過呼叫 sp_set_session_context （例如 ）將已認證的使用者權利要求傳送到 SQL（例如 roles）。 啟用會話情境後，將會停用該資料來源的回應快取。

警告

啟用 set-session-context 時，該資料來源的回應快取會被關閉。 對於流量高的情境，可以考慮測試效能、索引謂詞欄位，或在符合需求時使用 Data API 建置器資料庫政策。

1. 為數據表新增名為 revenuedbo.Revenues 的新實體。

   dab add revenue \
       --source "dbo.Revenues" \
       --permissions "Authenticated:read"
   

2. 啟動資料 API 產生器工具。

   dab start
   

3. 查詢端點時，不指定有效角色。 請注意，由於以下原因，沒有回傳任何資料：

   • 有效角色預設為 Authenticated。
   • 沒有任何列包含accessible_role = 'Authenticated'。
   • 當角色不匹配時，安全政策會過濾結果。

   curl http://localhost:5000/api/revenue
   

4. 查詢端點，並將有效角色設定為 Oscar。 請注意，篩選後的結果只包含列數 Oscar 。

   curl -H "X-MS-API-ROLE: Oscar" http://localhost:5000/api/revenue
   

5. 請重複使用Hannah角色。

   curl -H "X-MS-API-ROLE: Hannah" http://localhost:5000/api/revenue
   

使用 GraphQL 進行測試

Session context 也適用於 GraphQL 查詢。

query {
    revenues {
        items {
            id
            category
            revenue
            accessible_role
        }
    }
}

傳遞角色標題：

curl -X POST http://localhost:5000/graphql \
    -H "Content-Type: application/json" \
    -H "X-MS-API-ROLE: Oscar" \
    -d '{"query": "{ revenues { items { id category revenue accessible_role } } }"}'

Data API 建構器會傳送給 SQL Server 的內容

啟用 session context 後，Data API 建構器會在執行查詢前為每個請求設定 session context 值。

EXEC sp_set_session_context 'roles', 'Oscar', @read_only = 0;
-- Then executes your query
SELECT * FROM dbo.Revenues;

所有經過認證的使用者聲明皆以鍵值對形式傳送。 常見的宣告包括 roles、sub 或 oid，以及來自你的身份提供者的任何自訂宣告。

在 SQL 中測試

直接在 SQL 中測試篩選和述詞，以確保其運作正常。

1. 使用您慣用的用戶端或工具再次連線到 SQL Server。

2. 執行sp_set_session_context以手動地將會話內容的roles宣稱設置為Oscar靜態值。

   EXEC sp_set_session_context 'roles', 'Oscar';
   

3. 執行一般 SELECT * 查詢。 觀察結果是使用述詞自動篩選。

   SELECT * FROM dbo.Revenues;  
   

4. （可選）使用儲存程序查詢資料表。

   EXEC dbo.GetRevenuesForCurrentRole;
   

清理資源

如果你想移除樣本物件，請執行：

DROP SECURITY POLICY IF EXISTS dbo.RevenuesSecurityPolicy;
DROP FUNCTION IF EXISTS dbo.RevenuesPredicate;
DROP PROCEDURE IF EXISTS dbo.GetRevenuesForCurrentRole;
DROP TABLE IF EXISTS dbo.Revenues;

故障排除

• 未回傳結果：確認安全政策是否有效SELECT * FROM sys.security_policies（），檢查會話上下文值SELECT SESSION_CONTEXT(N'roles')（），並確認 --set-session-context true Data API 建構器設定中是否已設定。
• 所有列都回傳：確認安全政策沒有被禁用（WITH STATE = OFF），且謂詞只回傳 1 授權列。
• 效能問題：索引謂詞欄位accessible_role，並考慮暫時停用該策略，以隔離效能影響。

相關內容

• 資料庫特定功能
• 使用環境