数据防泄与最小可见：ABP 统一封装行级安全（RLS）+ 列级脱敏

数据防泄与最小可见：ABP 统一封装行级安全（RLS）+ 列级脱敏

TL;DR：把“谁能看到哪些行、字段可见到哪一位”下沉到数据库强制层（PostgreSQL：RLS + 安全视图；SQL Server：RLS + DDM），应用层（ABP + EF Core）只做会话/事务上下文注入、兜底过滤与审计。本版在此前基础上继续打磨：
PG ➜ SELECT/INSERT/UPDATE/DELETE 全覆盖策略、customers 同样启/强制 RLS、security_barrier + security_invoker 安全视图、Schema USAGE 最小授权；
MSSQL ➜ Orders + Customers 过滤+写阻断（BEFORE/AFTER）、SESSION_CONTEXT 统一只读；
EF/ABP ➜ AsyncLocal 访问器 + 双拦截器（会话兜底 / 事务限定）+ TagWith/TagWithCallSite 审计打标；

---

---

0）版本与约定 🧾

• 框架：.NET 8、ABP v8+、EF Core 8
• 数据库：PostgreSQL 15+（支持 security_invoker 视图）；SQL Server 2019+（datetime() 脱敏仅 SQL Server 2022+）
• 驱动：Npgsql 8.x、Microsoft.Data.SqlClient 5.x
• 术语：RLS = Row-Level Security；DDM = Dynamic Data Masking

---

1）问题与目标 🎯

• 反模式：仅在应用层 WHERE TenantId=... —— 一旦脚本直连/批处理/服务异常，即可能绕过。

• 目标：

  • DB 原生强制：PG 用 RLS + 自定义 GUC + 安全视图；MSSQL 用 RLS（过滤/阻断）+ DDM。
  • 应用统一注入：ABP/EF 拦截器在连接/事务阶段统一注入租户/用户/角色上下文。
  • 全链路可观测、可评测、可灰度与快速回滚。

总体思路一图看懂 🗺️

---

2）职责与架构 🏗️

• 数据库层（主防线）

  • PG：ENABLE RLS + FORCE RLS；SELECT/INSERT/UPDATE/DELETE 全覆盖策略（读 USING，写 WITH CHECK）；STABLE 函数 + 安全视图（security_barrier,security_invoker）；最小授权（仅授视图 SELECT + Schema USAGE）。
  • MSSQL：RLS = TVF + Security Policy（过滤 + 写阻断 BEFORE/AFTER）；敏感列 DDM；sp_set_session_context 只读。

• 应用层（ABP + EF）

  • DbConnectionInterceptor 会话级注入（兜底）。
  • DbTransactionInterceptor 事务级注入 set_config(..., true)（避免连接池“串味”）。
  • EF 全局过滤器仅作兜底（软删/租户），不替代 RLS。
  • 审计：TagWith/TagWithCallSite 打标 + 采样事件（User/Tenant/Entity/Key/TraceId）。

---

3）策略 DSL（可选）🧬

# policies/tenants.yml
tenants:- id: "t1"roles:- name: "auditor"rls:Orders: "tenant_id = current_tenant()"masking:Customers.phone: "last4"Customers.email: "email"- name: "csr"rls:Orders: "tenant_id = current_tenant() AND region = current_region()"masking:Customers.phone: "partial(0,'****-',4)"

生成器输出：PG 的 CREATE POLICY/函数/视图与 MSSQL 的 TVF/CREATE SECURITY POLICY/ALTER ... MASKED，并生成 ABP 常量 & 迁移。

---

4）PostgreSQL 实现（RLS + 安全视图）🐘

4.1 建表与 RLS（读/写/删全覆盖）

CREATE EXTENSION IF NOT EXISTS pgcrypto;CREATE TABLE public.customers(id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),tenant_id uuid NOT NULL,name      text NOT NULL,phone     text NOT NULL,email     text NOT NULL
);CREATE TABLE public.orders(id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),tenant_id   uuid NOT NULL,customer_id uuid NOT NULL REFERENCES public.customers(id),region      text,amount      numeric(12,2) NOT NULL
);-- 两表启用并强制 RLS（避免直查/误授）
ALTER TABLE public.customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.customers FORCE ROW LEVEL SECURITY;
ALTER TABLE public.orders    ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.orders    FORCE ROW LEVEL SECURITY;-- customers：读策略（若允许写/删，照 orders 同步补齐 WITH CHECK/DELETE）
CREATE POLICY customers_select_tenant
ON public.customers
AS PERMISSIVE
FOR SELECT
USING (tenant_id = current_setting('app.tenant_id', true)::uuid);-- orders：读策略
CREATE POLICY orders_select_tenant
ON public.orders
AS PERMISSIVE
FOR SELECT
USING (tenant_id = current_setting('app.tenant_id', true)::uuid);-- orders：写策略（INSERT）
CREATE POLICY orders_insert_tenant
ON public.orders
AS PERMISSIVE
FOR INSERT
WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);-- orders：写策略（UPDATE）
CREATE POLICY orders_update_tenant
ON public.orders
AS PERMISSIVE
FOR UPDATE
USING      (tenant_id = current_setting('app.tenant_id', true)::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);-- orders：删策略（DELETE）
CREATE POLICY orders_delete_tenant
ON public.orders
AS PERMISSIVE
FOR DELETE
USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

4.2 PG 请求执行流程 🧭

4.3 角色匹配（健壮化）与脱敏函数（STABLE）

-- 角色精确匹配：忽略大小写与空白
CREATE OR REPLACE FUNCTION public.has_role(role_name text)
RETURNS boolean
LANGUAGE sql STABLE
AS $$SELECT lower(role_name) = ANY (regexp_split_to_array(coalesce(lower(current_setting('app.roles', true)), ''),'\s*,\s*'))
$$;CREATE OR REPLACE FUNCTION public.mask_phone(p text)
RETURNS text
LANGUAGE sql STABLE
AS $$SELECT CASE WHEN public.has_role('auditor') THEN pELSE '****-' || right(p, 4) END
$$;

4.4 安全视图（最小暴露面 + Schema 权限）

-- PG15+：security_barrier 防谓词下推探查；security_invoker 以调用者权限/RLS 评估
CREATE OR REPLACE VIEW public.v_customers
WITH (security_barrier = true, security_invoker = true) AS
SELECT id, name, mask_phone(phone) AS phone_masked, email
FROM public.customers;-- 权限最小化：仅授予视图读取，回收底表 & 授予 schema USAGE
REVOKE ALL ON TABLE public.customers FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO app_reader;
GRANT SELECT ON public.v_customers TO app_reader;

4.5 索引与 SARGable 示例

-- 典型多租户复合索引（忽略已存在校验/CONCURRENTLY 视运维）
CREATE INDEX idx_orders_tenant_region ON public.orders(tenant_id, region);

---

5）SQL Server 实现（RLS + DDM）🧱

5.1 建表与 DDM

CREATE SCHEMA sec;CREATE TABLE dbo.Customers(Id         uniqueidentifier NOT NULL DEFAULT NEWID() PRIMARY KEY,Tenant_Id  uniqueidentifier NOT NULL,Name       nvarchar(200) NOT NULL,Phone      nvarchar(32)  NOT NULL MASKED WITH (FUNCTION='partial(0,"****-",4)'),Email      nvarchar(256) NOT NULL MASKED WITH (FUNCTION='email()')-- SQL Server 2022+：可对 datetime 列用 FUNCTION='datetime()'
);CREATE TABLE dbo.Orders(Id          uniqueidentifier NOT NULL DEFAULT NEWID() PRIMARY KEY,Tenant_Id   uniqueidentifier NOT NULL,Customer_Id uniqueidentifier NOT NULL REFERENCES dbo.Customers(Id),Region      nvarchar(64) NULL,Amount      decimal(12,2) NOT NULL
);

5.2 RLS：Orders + Customers 过滤 + 写阻断（BEFORE/AFTER）

-- 通用过滤谓词（读可见性）
CREATE OR ALTER FUNCTION sec.fn_tenant_filter(@tenant_id uniqueidentifier)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT 1 AS fn_result
WHERE @tenant_id = TRY_CONVERT(uniqueidentifier, SESSION_CONTEXT(N'tenant_id'));-- 通用阻断谓词（防跨租户写入）
CREATE OR ALTER FUNCTION sec.fn_tenant_block(@tenant_id uniqueidentifier)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT 1 AS fn_result
WHERE @tenant_id = TRY_CONVERT(uniqueidentifier, SESSION_CONTEXT(N'tenant_id'));-- 单个策略统一管控两张表（Add 多条谓词）
CREATE SECURITY POLICY sec.tenant_policy
ADD FILTER PREDICATE sec.fn_tenant_filter(Tenant_Id) ON dbo.Orders,
ADD FILTER PREDICATE sec.fn_tenant_filter(Tenant_Id) ON dbo.Customers,
ADD BLOCK  PREDICATE sec.fn_tenant_block(Tenant_Id)  ON dbo.Orders    AFTER  INSERT,
ADD BLOCK  PREDICATE sec.fn_tenant_block(Tenant_Id)  ON dbo.Orders    AFTER  UPDATE,
ADD BLOCK  PREDICATE sec.fn_tenant_block(Tenant_Id)  ON dbo.Orders    BEFORE UPDATE,
ADD BLOCK  PREDICATE sec.fn_tenant_block(Tenant_Id)  ON dbo.Orders    BEFORE DELETE,
ADD BLOCK  PREDICATE sec.fn_tenant_block(Tenant_Id)  ON dbo.Customers AFTER  INSERT,
ADD BLOCK  PREDICATE sec.fn_tenant_block(Tenant_Id)  ON dbo.Customers AFTER  UPDATE,
ADD BLOCK  PREDICATE sec.fn_tenant_block(Tenant_Id)  ON dbo.Customers BEFORE UPDATE,
ADD BLOCK  PREDICATE sec.fn_tenant_block(Tenant_Id)  ON dbo.Customers BEFORE DELETE
WITH (STATE = ON);

5.3 RLS/DDM 工作示意 🧩

5.4 会话上下文注入（统一只读）

EXEC sys.sp_set_session_context @key=N'tenant_id', @value=@TenantId,  @read_only=1;
EXEC sys.sp_set_session_context @key=N'roles',     @value=@CsvRoles,  @read_only=1;

限制：带 Security Policy 的表不能创建索引视图。汇总/分析用列存/覆盖索引或只读物化层（ETL/CDC）。

---

6）ABP / EF Core 集成（生产级）⚙️

6.1 异步上下文访问器（避免单例捕获作用域对象）

public interface ITenantContextAccessor
{string? TenantId { get; }string? UserId { get; }IReadOnlyList<string> Roles { get; }IDisposable Use(string? tenantId, string? userId, IEnumerable<string> roles);
}public sealed class TenantContextAccessor : ITenantContextAccessor
{private sealed class Holder{public string? TenantId { get; init; }public string? UserId { get; init; }public IReadOnlyList<string> Roles { get; init; } = Array.Empty<string>();}private static readonly AsyncLocal<Holder?> _current = new();public string? TenantId => _current.Value?.TenantId;public string? UserId   => _current.Value?.UserId;public IReadOnlyList<string> Roles => _current.Value?.Roles ?? Array.Empty<string>();public IDisposable Use(string? tenantId, string? userId, IEnumerable<string> roles){var prev = _current.Value;_current.Value = new Holder { TenantId = tenantId, UserId = userId, Roles = roles.ToArray() };return new DisposableAction(() => _current.Value = prev);}private sealed class DisposableAction : IDisposable{private readonly Action _a; public DisposableAction(Action a) => _a = a;public void Dispose() => _a();}
}

中间件在请求首/尾 using accessor.Use(...) 设置/清理上下文，使单例拦截器安全读取。

6.2 连接拦截器（会话级兜底）

using System.Data.Common;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Npgsql;public sealed class TenantSessionContextInterceptor : DbConnectionInterceptor
{private readonly ITenantContextAccessor _ctx;public TenantSessionContextInterceptor(ITenantContextAccessor ctx) => _ctx = ctx;public override async Task ConnectionOpenedAsync(DbConnection connection, ConnectionEndEventData eventData, CancellationToken token = default){if (_ctx.TenantId is null) return;if (connection is NpgsqlConnection){await using var cmd = connection.CreateCommand();cmd.CommandText = @"SET app.tenant_id = @tenant;SET app.user_id   = @user;SET app.roles     = @roles;";var p1 = cmd.CreateParameter(); p1.ParameterName = "tenant"; p1.Value = _ctx.TenantId!;var p2 = cmd.CreateParameter(); p2.ParameterName = "user";   p2.Value = _ctx.UserId ?? "";var p3 = cmd.CreateParameter(); p3.ParameterName = "roles";  p3.Value = string.Join(',', _ctx.Roles);cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); cmd.Parameters.Add(p3);await cmd.ExecuteNonQueryAsync(token);}else // SQL Server{await using var cmd = connection.CreateCommand();cmd.CommandText ="EXEC sys.sp_set_session_context @key=N'tenant_id', @value=@TenantId, @read_only=1; " +"EXEC sys.sp_set_session_context @key=N'roles',     @value=@Roles,     @read_only=1;";var p1 = cmd.CreateParameter(); p1.ParameterName = "TenantId"; p1.Value = _ctx.TenantId!;var p2 = cmd.CreateParameter(); p2.ParameterName = "Roles";    p2.Value = string.Join(',', _ctx.Roles);cmd.Parameters.Add(p1); cmd.Parameters.Add(p2);await cmd.ExecuteNonQueryAsync(token);}}public override async Task ConnectionClosingAsync(DbConnection connection, ConnectionEventData eventData, CancellationToken token = default){if (connection is NpgsqlConnection){await using var cmd = connection.CreateCommand();cmd.CommandText = "RESET app.tenant_id; RESET app.user_id; RESET app.roles;";await cmd.ExecuteNonQueryAsync(token);}}
}

6.3 事务拦截器（事务级强约束）

using System.Data.Common;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Npgsql;public sealed class TenantTransactionContextInterceptor : DbTransactionInterceptor
{private readonly ITenantContextAccessor _ctx;public TenantTransactionContextInterceptor(ITenantContextAccessor ctx) => _ctx = ctx;public override async Task TransactionStartedAsync(DbTransaction transaction, TransactionEndEventData eventData, CancellationToken cancellationToken = default){if (transaction.Connection is NpgsqlConnection && _ctx.TenantId is not null){await using var cmd = transaction.Connection.CreateCommand();cmd.Transaction = transaction;cmd.CommandText = @"SELECTset_config('app.tenant_id', @tenant, true),set_config('app.user_id',   @user,   true),set_config('app.roles',     @roles,  true);";var p1 = cmd.CreateParameter(); p1.ParameterName = "tenant"; p1.Value = _ctx.TenantId!;var p2 = cmd.CreateParameter(); p2.ParameterName = "user";   p2.Value = _ctx.UserId ?? "";var p3 = cmd.CreateParameter(); p3.ParameterName = "roles";  p3.Value = string.Join(',', _ctx.Roles);cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); cmd.Parameters.Add(p3);await cmd.ExecuteNonQueryAsync(cancellationToken);}}
}

6.4 注册与中间件

// Program.cs / Module.ConfigureServices
services.AddSingleton<ITenantContextAccessor, TenantContextAccessor>();
services.AddSingleton<TenantSessionContextInterceptor>();
services.AddSingleton<TenantTransactionContextInterceptor>();services.AddAbpDbContext<AppDbContext>((sp, options) =>
{options.AddInterceptors(sp.GetRequiredService<TenantSessionContextInterceptor>(),sp.GetRequiredService<TenantTransactionContextInterceptor>());
});// 中间件（示意）：从 ABP/Claims 取租户/用户/角色并设置异步上下文
app.Use(async (http, next) =>
{var accessor = http.RequestServices.GetRequiredService<ITenantContextAccessor>();var tenantId = /* 从 ABP IMultiTenancy/Claims 取 */;var userId   = /* 从 ClaimsPrincipal 取 */;var roles    = /* 从 Claims/ABP Role 取 */;using (accessor.Use(tenantId, userId, roles)){await next();}
});

6.5 查询打标与兜底过滤

var items = await _db.Orders.TagWith("PII:Customers").TagWithCallSite() // 可选：自动带上调用文件与行号，利于溯源.Where(x => x.TenantId == CurrentTenantId) // 兜底；生产以 RLS 为准.ToListAsync();

---

7）“谁看过什么”审计 🔎

• PG：启用 pgaudit（shared_preload_libraries='pgaudit'），pgaudit.log='read,write'。
• MSSQL：配置 SERVER AUDIT + DATABASE AUDIT SPECIFICATION，记录 SELECT/对象访问到文件/安全日志。
• 应用采样：记录 (User,Tenant,Entity,Key,Purpose,TraceId,Time)；后台提供检索/导出/告警。

---

8）性能与容量评测 ⚡

• 谓词可搜索（SARGable）：RLS 表达式命中索引，避免在列上包函数/计算。

• 索引策略：

  • PG/MSSQL：为 tenant_id、常用维度（如 region）建复合索引；
  • MSSQL：不要在带 RLS 的表上建索引视图（不兼容）；改用列存/覆盖索引或只读物化层。

• 视图代价：security_barrier 限制谓词下推，热点查询可为可信后端提供直查接口（严格授权）。

• 基准建议：

  • PG：pgbench 对比 RLS ON/OFF；
  • MSSQL：Extended Events + Query Store，关注计划重用、回表率、p50/p95/p99、CPU。

---

9）灰度上线与回滚 🧰

• CI 门禁：DSL/SQL golden tests（允许/拒绝矩阵）+ 静态检查禁止对含 SECURITY POLICY 的表创建索引视图。

---

10）目录结构（落盘）📁

abp-data-min-visibility/modules/Abp.DataVisibility/            # ABP 模块：拦截器、审计扩展、策略加载db/pg/rls/*.sql                   # PG：RLS/视图/函数mssql/rls/*.sql                # MSSQL：TVF/Policy/DDMtests/integration/                   # xUnit：允许/拒绝用例（golden）dashboards/audit-app/                     # ABP 后台审计面板tools/policyc/                       # DSL → SQL/ABP 常量 生成器（可选）

---

11）摘录 🧪

PostgreSQL

BEGIN;
SELECT set_config('app.tenant_id','00000000-0000-0000-0000-000000000001', true);
SELECT set_config('app.roles','csr', true);-- 仅返回本租户
SELECT * FROM public.orders;-- 跨租户写：应被 WITH CHECK 拦截
INSERT INTO public.orders(tenant_id, customer_id, amount)
VALUES ('00000000-0000-0000-0000-000000000002', gen_random_uuid(), 10.00); -- 期望失败-- 跨租户删：应被 DELETE USING 策略拦截
DELETE FROM public.orders WHERE tenant_id='00000000-0000-0000-0000-000000000002'; -- 期望失败-- 视图脱敏
SELECT id, phone_masked FROM public.v_customers;
COMMIT;

SQL Server

DECLARE @TenantId uniqueidentifier = '00000000-0000-0000-0000-000000000001';
EXEC sys.sp_set_session_context @key=N'tenant_id', @value=@TenantId,  @read_only=1;
EXEC sys.sp_set_session_context @key=N'roles',     @value=N'csr',      @read_only=1;-- 仅本租户订单/客户可见
SELECT * FROM dbo.Orders;
SELECT * FROM dbo.Customers;-- 跨租户写：应被 BLOCK PREDICATE 拦截（AFTER/BEFORE）
INSERT INTO dbo.Orders (Tenant_Id, Customer_Id, Amount)
VALUES ('00000000-0000-0000-0000-000000000002', NEWID(), 10.00); -- 期望失败-- DDM 脱敏效果
SELECT Phone, Email FROM dbo.Customers;

---