Azure Synapse专用池存储过程所有权链权限问题求助

环境与相关对象查询 #

环境：Azure Synapse专用池（原Azure Data Warehouse）

执行以下SQL查询获取相关对象信息：

SELECT 
              s.name + '.' + o.name AS ObjectName
            , COALESCE(p.name, p2.name) AS OwnerName
            , o.type

  FROM sys.all_objects o
  LEFT OUTER JOIN sys.database_principals p 
             ON o.principal_id = p.principal_id

  LEFT OUTER JOIN sys.schemas s 
                ON o.schema_id = s.schema_id

  LEFT OUTER JOIN sys.database_principals p2 
                ON s.principal_id = p2.principal_id

  WHERE      s.name NOT IN ('sys', 'INFORMATION_SCHEMA')
           and o.name like '%waterm%'

查询结果：

已执行的授权操作 #

已为用户[ot-linkworkx]授予存储过程执行权限：

GRANT EXECUTE ON OBJECT::[dbo].[Reset_Watermarks] TO [ot-linkworkx]
GO

执行错误信息 #

以[ot-linkworkx]身份执行存储过程时，触发以下权限错误：

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'ADF_Watermark', database 'syndpeu2dtaedw9', schema 'dbo'.
The DELETE permission was denied on the object 'ADF_Watermark', database 'syndpeu2dtaedw9', schema 'dbo'.

Completion time: 2023-05-25T15:06:50.8775877+02:00

问题原因分析 #

虽然存储过程dbo.Reset_Watermarks和目标表dbo.ADF_Watermark的所有者均为dbo，符合所有权链生效的基础条件，但在Azure Synapse专用池中，所有权链未生效通常由以下几种情况导致：

1. 存储过程包含动态SQL：如果存储过程内部使用动态SQL访问ADF_Watermark表，所有权链规则不会生效——动态SQL会以调用者（ot-linkworkx）的身份执行，而非存储过程所有者（dbo）的身份，因此需要调用者具备直接访问表的权限。
2. 存储过程定义使用了非默认的EXECUTE AS子句：若存储过程创建时指定了EXECUTE AS SELF/EXECUTE AS '其他用户'等非CALLER的执行上下文，所有权链的权限豁免规则会失效，调用者的权限会被直接校验。
3. 所有者一致性存在隐性差异：查询结果中显示的OwnerName均为dbo，但实际可能存在principal_id不一致的情况（例如不同主体被重命名为dbo），导致所有权链无法识别为同一所有者。

解决办法 #

针对不同原因，对应解决方案如下：

1. 处理动态SQL场景 #

• 将动态SQL改写为静态SQL（优先推荐），让所有权链规则正常生效；
• 若必须使用动态SQL，在动态SQL块中添加EXECUTE AS OWNER：

  EXECUTE AS OWNER;
  EXEC('DELETE FROM dbo.ADF_Watermark ...');
  REVERT;
  

• 临时方案：直接授予调用者表权限（不推荐长期使用）：

  GRANT SELECT, DELETE ON OBJECT::dbo.ADF_Watermark TO [ot-linkworkx];
  GO
  

2. 修正EXECUTE AS子句 #

• 修改存储过程，恢复默认的EXECUTE AS CALLER上下文：

  ALTER PROCEDURE dbo.Reset_Watermarks
  WITH EXECUTE AS CALLER
  AS
  -- 存储过程原有逻辑
  GO
  

3. 确认并统一所有者 #

• 执行以下SQL确认存储过程和表的principal_id是否一致：

  SELECT 
      name, principal_id 
  FROM sys.objects 
  WHERE name IN ('Reset_Watermarks', 'ADF_Watermark') 
    AND schema_id = SCHEMA_ID('dbo');
  

• 若principal_id不一致，执行以下语句统一所有者为dbo：

  ALTER AUTHORIZATION ON OBJECT::dbo.ADF_Watermark TO dbo;
  ALTER AUTHORIZATION ON OBJECT::dbo.Reset_Watermarks TO dbo;
  

内容的提问来源于stack exchange，提问作者Harry Leboeuf