怎么在SQL Server中利用触发器实现多租户数据的隔离与鉴权-SQL-PHP中文网

sql server触发器中安全获取当前租户id须依赖应用层通过sp_set_session_context显式设置tenant_id，触发器内用session_context(n'tenant_id')读取并校验，禁用context_info及系统用户函数；校验需用is not distinct from处理null，delete/after触发器须基于deleted表核验tenant_id一致性，且推荐优先采用行级安全（rls）替代触发器实现自动过滤。

怎么在sql server中利用触发器实现多租户数据的隔离与鉴权

SQL Server触发器里怎么安全拿到当前租户ID

SQL Server触发器本身不暴露业务租户上下文，CURRENT_USER、SESSION_USER或SUSER_NAME()返回的是登录身份，不是租户ID。必须依赖应用层显式传入——最可靠的方式是用SESSION_CONTEXT()，它支持键值对、线程安全、且在连接生命周期内有效。

应用需在每次获取连接后调用：
EXEC sp_set_session_context @key = N'tenant_id', @value = N'tenant_abc'

触发器中读取时必须加@fallback_null = 0参数，否则未设置时返回NULL，容易被绕过：
DECLARE @tenant_id NVARCHAR(128) = SESSION_CONTEXT(N'tenant_id')

别用CONTEXT_INFO：只能存128字节二进制，类型转换麻烦，且多个键会互相覆盖
别查sys.dm_exec_sessions反推：需要VIEW SERVER STATE权限，且并发下可能读到其他会话数据
连接池（如ADO.NET的SqlConnection Pool）默认复用连接，务必确保每次请求前都重设SESSION_CONTEXT，不能只在首次连接时设

INSERT/UPDATE触发器中如何强制校验并填充tenant_id

必须用BEFORE语义的触发器（SQL Server叫INSTEAD OF或AFTER但需配合IF UPDATE()判断），核心是两件事：校验一致性 + 填充缺失值。推荐用AFTER INSERT, UPDATE配合RAISEERROR拦截非法写入，同时用INSTEAD OF INSERT接管插入逻辑来补值。

关键点：

NEW在SQL Server里不可直接访问，得用inserted虚拟表；OLD对应deleted表
校验必须用IS NOT DISTINCT FROM，避免tenant_id = @tenant_id在任一为NULL时失效
字段类型要严格匹配：SESSION_CONTEXT()返回NVARCHAR，若列是UNIQUEIDENTIFIER，必须显式转换：CAST(SESSION_CONTEXT(N'tenant_id') AS UNIQUEIDENTIFIER)
如果表定义了DEFAULT值（比如DEFAULT 'public'），会覆盖触发器逻辑——必须删掉

示例片段（AFTER INSERT校验）：

CREATE TRIGGER tr_orders_tenant_check ON orders AFTER INSERT AS BEGIN
  DECLARE @tenant_id NVARCHAR(128) = SESSION_CONTEXT(N'tenant_id');
  IF EXISTS (SELECT 1 FROM inserted i WHERE i.tenant_id IS NOT DISTINCT FROM @tenant_id)
    RETURN;
  RAISERROR('tenant_id mismatch or missing', 16, 1);
  ROLLBACK;
END

DELETE触发器为什么必须校验OLD.tenant_id

DELETE不涉及新值，但最容易被越权利用：DELETE FROM orders WHERE id = 123如果没有租户约束，就可能删掉其他租户的同ID记录。触发器无法改写WHERE条件，只能靠deleted表里的tenant_id做事后核验。

必须用AFTER DELETE，因为deleted表只在AFTER阶段完整可用
不用判空：deleted.tenant_id一定非NULL（否则行根本不存在）
校验失败立即ROLLBACK，不能只RAISERROR，否则事务已提交部分数据
别试图在触发器里INSERT INTO audit_log：可能引发嵌套触发器或死锁，审计应由应用层或CDC捕获

注意：SQL Server不允许在AFTER触发器里修改deleted表，所以校验是唯一可行动作。

比起触发器，SQL Server的RLS才是更稳的选择

触发器在SQL Server里有硬伤：无法拦截SELECT * FROM orders JOIN customers这类跨表查询中的租户泄露，且每个表都要单独建、易遗漏；而ROW LEVEL SECURITY是数据库原生能力，自动注入过滤条件，连视图、存储过程、临时表都覆盖。

启用RLS只需三步：

创建内联表值函数（必须SCHEMABINDING）：CREATE FUNCTION dbo.fn_tenant_filter() RETURNS TABLE ... AS RETURN SELECT 1 AS fn_result WHERE SESSION_CONTEXT(N'tenant_id') = tenant_id
在表上绑定策略：CREATE SECURITY POLICY dbo.tenant_policy ADD FILTER PREDICATE dbo.fn_tenant_filter() ON dbo.orders
开启RLS：ALTER TABLE orders ENABLE ROW LEVEL SECURITY

复杂点在于：RLS策略函数不能包含子查询或聚合，也不能调用非确定性函数（如GETDATE()），且SESSION_CONTEXT必须提前设置——这点和触发器要求一致，但一旦配好，后续所有访问都自动受控，不用再操心每个INSERT/UPDATE/DELETE是否漏了逻辑。