Azure SQL DB中T-SQL存储过程所有权链断裂问题排查

核心权限逻辑差异 #

1. 动态SQL的所有权链断裂特性：EXEC @sql执行的动态SQL会完全脱离存储过程的所有权链，其执行权限依赖于执行时的安全上下文，而非存储过程所有者的权限。静态SQL则可以正常利用所有权链（只要存储过程与目标对象所有者相同）。
2. 跨调用的上下文传递问题：
   • 当用户直接执行[b].[doB]时，由于所有架构所有者都是dbo，SQL Server通过模块所有权链允许用户执行该存储过程，且此时动态SQL的执行上下文继承了存储过程所有者dbo的权限，因此能访问[c]架构的对象。
   • 当[a].[doA]调用[b].[doB]时，[a].[doA]的执行上下文是原始用户（默认EXECUTE AS CALLER），调用后[b].[doB]中的动态SQL会继承这个原始用户的权限。而用户仅拥有[a]架构的权限，无[c]架构权限，因此触发权限拒绝错误。
3. 已尝试方案无效的原因：
   • 授予用户[b]架构的EXECUTE权限：仅允许用户直接执行[b].[doB]，但跨调用时动态SQL的上下文依然是原始用户权限，无法访问[c]。
   • 将[doB]移至[a]架构：存储过程调用的所有权链依然有效，但动态SQL的执行上下文还是原始用户权限，用户无[c]权限，因此报错。

方案1：修改存储过程执行上下文（推荐，简单直接） #

给[b].[doB]（或移至[a]架构后的存储过程）添加EXECUTE AS OWNER属性，确保动态SQL始终以dbo的权限执行：

ALTER PROCEDURE [a].[doB] -- 或[b].[doB]，根据实际位置调整
WITH EXECUTE AS OWNER
AS
-- 原存储过程的逻辑代码

无论该存储过程被哪个模块调用，动态SQL都会以所有者dbo的权限运行，自然能访问[c]架构的对象。

方案2：证书签名存储过程（推荐，最小权限原则） #

通过证书签名给存储过程附加必要权限，无需修改执行上下文，也无需给用户额外权限：

-- 1. 创建证书
CREATE CERTIFICATE Cert_DoB_Permissions
ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!'
WITH SUBJECT = 'Permissions for doB dynamic SQL operations';

-- 2. 创建证书对应的用户
CREATE USER CertUser_DoB FOR CERTIFICATE Cert_DoB_Permissions;

-- 3. 授予证书用户[c]架构的必要权限
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::[c] TO CertUser_DoB;

-- 4. 用证书给存储过程签名
ADD SIGNATURE TO [b].[doB] -- 或[a].[doB]，根据实际位置调整
BY CERTIFICATE Cert_DoB_Permissions
WITH PASSWORD = 'YourStrongPasswordHere!';

签名后，存储过程执行时会自动获得证书用户的权限，动态SQL即可正常访问[c]架构的对象，同时遵循最小权限原则。

方案3：直接授予用户[c]架构权限（不推荐） #

若业务允许，可直接给用户授予[c]架构的必要权限，但这会破坏最小权限原则，扩大用户权限范围：

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::[c] TO [YourUserName];

内容的提问来源于stack exchange，提问作者Tamás Bárász