Oracle脚本与存储过程查询DBA_SCHEDULER_JOB_RUN_DETAILS结果不一致

在单台Oracle服务器上观测到SQL执行结果不一致的异常：

• 提前创建测试表TEST_OWNERS，表结构为owner varchar2(100)
• 同一客户端窗口、同一会话下直接执行以下SQL，可正常查询DBA_SCHEDULER_JOB_RUN_DETAILS视图的所有owner值并写入测试表：

INSERT INTO TEST_OWNERS
select distinct owner from DBA_SCHEDULER_JOB_RUN_DETAILS;
commit;

• 同一会话下调用逻辑完全一致的存储过程TEST_OWNERS_P后，测试表仅能查询到存储过程执行用户对应的单条owner数据，存储过程代码如下：

CREATE OR REPLACE PROCEDURE TEST_OWNERS_P IS
BEGIN
EXECUTE IMMEDIATE('TRUNCATE TABLE TEST_OWNERS');
INSERT INTO TEST_OWNERS
select distinct owner from DBA_SCHEDULER_JOB_RUN_DETAILS;
commit;
END;

• 相同代码逻辑在其余Oracle服务器运行均正常，直接执行SQL与调用存储过程的查询结果完全一致。

问题由Oracle PL/SQL的权限生效范围差异导致：
当前执行存储过程的用户，访问DBA_SCHEDULER_JOB_RUN_DETAILS视图的权限是通过角色授予的，并非直接授予用户的对象级/系统级权限。
Oracle对两类运行场景的权限判定规则完全不同：

• 直接在会话执行SQL、运行匿名PL/SQL块时，会同时启用用户直接持有的权限、用户名下所有角色关联的权限
• 默认创建的存储过程是定义者权限模式（未显式加AUTHID CURRENT_USER参数），这类编译型PL/SQL对象执行时，仅会启用存储过程属主直接持有的权限，所有通过角色授予的权限都会在存储过程运行时失效。

这台异常服务器上，用户大概率是通过DBA、SELECT_CATALOG_ROLE这类角色拿到的数据字典访问权限，没有做直接授权，因此存储过程运行时丢失了全量数据访问权限。加上DBA_SCHEDULER_JOB_RUN_DETAILS视图内置了行级访问过滤逻辑：当访问者没有全量数据字典查询权限时，不会抛出ORA-01031权限不足错误，只会自动过滤返回当前用户名下的调度任务记录，最终就出现了仅返回当前执行用户对应单条owner数据的现象。
其余正常运行的服务器，对应账号的SELECT ANY DICTIONARY系统权限、或是针对DBA_SCHEDULER_JOB_RUN_DETAILS的SELECT对象权限是直接授予用户的，不受角色权限失效的影响，因此两种执行方式结果一致。

使用存储过程的属主用户登录数据库，执行以下SQL确认权限配置：

-- 1. 检查当前用户直接持有的系统权限，确认是否拥有全量数据字典访问权限
select privilege from user_sys_privs where privilege = 'SELECT ANY DICTIONARY';
-- 2. 检查当前用户是否被直接授予目标视图的查询权限
select * from user_tab_privs where table_name = 'DBA_SCHEDULER_JOB_RUN_DETAILS';
-- 3. 检查是否通过角色持有对应访问权限
select privilege from role_sys_privs where role in (select granted_role from user_role_privs) and privilege = 'SELECT ANY DICTIONARY';

如果前两条查询无返回结果、第三条查询可返回对应权限记录，即可确认根因判断正确。

两种方案选择其一即可：

1. 给存储过程属主直接授予必要权限，使用DBA权限账号执行：

   GRANT SELECT ANY DICTIONARY TO 【存储过程属主用户名】;
   

   授权后重新编译存储过程，即可正常返回全量数据。
2. 修改存储过程为调用者权限模式，存储过程运行时会沿用当前调用会话的权限集，角色授予的权限可正常生效：

   CREATE OR REPLACE PROCEDURE TEST_OWNERS_P AUTHID CURRENT_USER IS
   BEGIN
   EXECUTE IMMEDIATE('TRUNCATE TABLE TEST_OWNERS');
   INSERT INTO TEST_OWNERS
   select distinct owner from DBA_SCHEDULER_JOB_RUN_DETAILS;
   commit;
   END;
   

内容的提问来源于stack exchange，提问作者Łukasz Kudelski