触发器中使用ALL_TABLES/ALL_TAB_COLUMNS跨Schema统计结果异常

我之前碰到过好几个类似的场景，核心原因其实是触发器的权限执行上下文和你直接在SQL Developer里查询时不一样，具体来说有两个关键点：

问题根源 #

1. 触发器默认使用定义者权限模式
   触发器（包括存储过程、函数）默认是AUTHID DEFINER模式，也就是用创建触发器的用户的权限来执行。但这里有个容易忽略的细节：在这种模式下，用户通过角色获得的权限是不生效的，只有直接授予给用户的权限才会被识别。
2. 直接查询与触发器的权限差异
   你在SQL Developer里能正确统计EDW_STG的表，大概率是因为你通过某个角色（比如DBA角色、自定义的权限角色）获得了访问EDW_STG对象的权限。但触发器执行时，角色权限会被禁用，导致它看不到EDW_STG的表，自然统计结果就错了。

两种解决方案 #

方案一：给用户直接授予所需权限 #

如果不想修改触发器的权限模式，可以直接给创建触发器的用户（比如EDW_SRC）授予访问相关视图或表的权限：

• 如果需要查询ALL_TAB_COLUMNS，直接授予系统权限：

  GRANT SELECT ON SYS.ALL_TAB_COLUMNS TO EDW_SRC;
  

• 或者更精准地，授予访问EDW_STG模式下对象的权限：

  -- 全局权限，能访问所有表
  GRANT SELECT ANY TABLE TO EDW_SRC;
  -- 或者针对特定表的权限
  GRANT SELECT ON EDW_STG.你的目标表 TO EDW_SRC;
  

方案二：修改触发器为调用者权限模式 #

让触发器执行时使用触发它的用户的权限（和你直接查询时的权限上下文完全一致），只需要在触发器定义里加上AUTHID CURRENT_USER：

CREATE OR REPLACE TRIGGER 你的触发器名称
AUTHID CURRENT_USER
-- 这里是你的触发器触发条件，比如 BEFORE INSERT ON 某表
FOR EACH ROW
DECLARE
  -- 你的变量声明
BEGIN
  -- 你的统计逻辑，比如查询ALL_TAB_COLUMNS的代码
END;
/

验证步骤 #

你可以先确认自己的权限来源，排查是否是角色权限导致的问题：

-- 查看直接授予的系统权限
SELECT * FROM USER_SYS_PRIVS WHERE PRIVILEGE LIKE '%SELECT%';
-- 查看拥有的角色
SELECT * FROM USER_ROLE_PRIVS;

如果发现访问EDW_STG的权限来自某个角色，那上述两种方案都能解决你的问题。

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