在数据墙DBW中使用 session context 实现行级安全

使用 SQL 的 session context 功能在数据墙DBW中实现行级安全。

IMPORTANT

SQL Server 行级安全中的 session context 与数据墙DBW数据库策略不同。数据库策略（例如 --policy-database "@item.owner eq @claims.user_id"）会被数据墙DBW转换成 WHERE 子句；而 session context 则是把 claims 转发给 SQL Server，再由 SQL 原生的行级安全机制负责过滤。

TIP

当前仍处于预览阶段的 数据墙DBW 2.0 还支持用于 SQL Server 的 On-Behalf-Of（OBO）用户委托身份验证。它会将传入的用户令牌交换为下游 SQL 令牌，使数据库以真实调用用户身份完成身份验证。OBO 适合那些依赖真实用户身份的行级安全策略。有关更多信息，请参阅用户委托身份验证。2.0 新增内容页面可在对应专题中查看。

先决条件

• 一个现有的 SQL Server 和数据库。
• 数据墙DBW CLI。请参阅安装 CLI。

NOTE

Session context 适用于：

• SQL Server 2016 及更高版本
• SQL Server 2016 及更高版本

创建 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 列存储了可以访问该行的角色名称。

TIP

常见的 session context 使用场景：

• 使用 roles 做基于角色的过滤（本例展示的方式）
• 使用 tenant_id 做多租户隔离
• 使用 user_id 做用户级过滤

4. 使用一个简单的 SELECT * 查询测试数据。

   SELECT * FROM dbo.Revenues

5. 创建一个名为 RevenuesPredicate 的函数。该函数会根据当前 session context 过滤结果。

   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));

6. 使用该函数创建一个名为 RevenuesSecurityPolicy 的安全策略。

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

NOTE

对于被安全策略使用的函数，WITH SCHEMABINDING 子句是必需的，这样底层架构变更才不会使谓词失效。

（可选）创建存储过程

本节展示一个简单的“hello world”模式，说明如何在 T-SQL 中直接使用 session context 值。

1. 创建一个存储过程，读取 roles session context 值并用它过滤结果。

   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

运行工具

运行数据墙DBW工具以生成配置文件和单个实体。

1. 创建一个新配置，并将 --set-session-context 设为 true。

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

   当为 SQL Server 启用 session context 时，数据墙DBW会通过调用 sp_set_session_context 向 SQL 发送已认证用户的 claims（例如 roles）。为该数据源启用 session context 也会同时禁用响应缓存。

WARNING

启用 set-session-context 后，数据源的响应缓存会被禁用。对于高流量场景，请考虑做性能测试、为谓词列建立索引，或者在满足需求时改用数据墙DBW数据库策略。

2. 为 dbo.Revenues 表添加一个名为 revenue 的实体。

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

3. 启动数据墙DBW工具。

   dab start

4. 在不指定最终角色的情况下查询终结点。注意这里不会返回任何数据，因为：

   • 最终角色默认为 Authenticated
   • 没有任何行满足 accessible_role = 'Authenticated'
   • 安全策略会在角色不匹配时过滤结果

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

5. 查询终结点，并将最终角色设置为 Oscar。注意过滤后的结果只包含 Oscar 对应的行。

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

6. 使用 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 } } }"}'

数据墙DBW 会向 SQL Server 发送什么

当启用 session context 时，数据墙DBW会在每次执行查询前都设置 session context 值。

EXEC sp_set_session_context 'roles', 'Oscar', @read_only = 0;
-- 然后执行你的查询
SELECT * FROM dbo.Revenues;

所有已认证用户的 claims 都会作为键值对发送。常见 claims 包括 roles、sub 或 oid，以及身份提供程序中的任意自定义 claims。

在 SQL 中测试

直接在 SQL 中测试过滤器和谓词，以确认其工作正常。

1. 再次使用你偏好的客户端或工具连接到 SQL Server。

2. 运行 sp_set_session_context，手动将当前 session context 的 roles claim 设为静态值 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），检查 session context 值（SELECT SESSION_CONTEXT(N'roles')），并确认你的数据墙DBW配置中已设置 --set-session-context true。
• 返回了所有行：请确认安全策略没有被禁用（例如 WITH STATE = OFF），并确认谓词函数只会对已授权行返回 1。
• 性能问题：请为谓词列（accessible_role）建立索引，并考虑临时禁用安全策略，以隔离性能影响来源。

相关内容

• 数据库特定功能页可在对应专题中查看
• 环境配置页可在对应专题中查看