Oracle查询已授予c##前缀练习用户的表权限时执行SQL返回无结果的排查

问题场景 #

在Oracle多租户环境下创建c##U100、c##U101、c##U102、c##U103、c##U104共5个公共用户，按既定授权关系完成全量表权限配置后，执行导师给出的权限校验语句，系统返回“未选定行”结果，需要明确问题根源是授权流程疏漏还是查询语句本身缺陷，拿到可正常运行的校验逻辑。

原校验语句 #

col owner format a20
col table_name format a15
col privilege format a10
col grantee format a10

SELECT owner,privilege,table_name,grantee
  FROM sys.dba_tab_privs
 WHERE grantee like 'c##U1%'
 ORDER BY grantee, table_name, privilege;

常见问题根源 #

从实操层面看，返回空结果一般来自三类常见问题，逐类排查即可快速定位：

• 连接容器不匹配
  公共用户的全局表权限记录存储在CDB根容器的系统视图中，如果当前会话连接的是业务PDB而非CDB$ROOT，将无法捕获根容器层面配置的公共用户权限记录，直接查询自然返回空。
• 授权操作的作用域不符合公共用户规则
  Oracle要求公共用户的全局权限授予操作必须在CDB根容器下执行，如果之前的GRANT操作是在某个业务PDB内完成，哪怕写的grantee是c##开头的公共用户名，生成的也是PDB内的本地权限记录，不会被根容器的dba_tab_privs视图全量汇总；此外如果授权时存在用户名拼写错误，把权限授给了不带c##前缀的本地同名用户，也会导致按c##前缀过滤的查询抓不到任何记录。
• 权限链路是角色中转而非直接授权
  如果之前的配置是先把表权限授予自定义角色，再将角色分配给5个公共用户，dba_tab_privs视图中记录的grantee是角色名而非用户本身，直接按用户名过滤的查询也会返回空。

可落地的排错与校验步骤 #

1. 先确认当前会话连接的数据库容器，执行：

   show con_name
   

   如果返回值不是CDB$ROOT，先执行切容器操作回到根容器：

   ALTER SESSION SET CONTAINER = CDB$ROOT;
   

2. 放弃原语句的通配符过滤逻辑，直接精确匹配5个目标用户名查询，排除通配符匹配规则的问题：

   col owner format a20
   col table_name format a15
   col privilege format a10
   col grantee format a10
   
   SELECT owner, privilege, table_name, grantee
     FROM sys.dba_tab_privs
    WHERE grantee IN ('c##U100','c##U101','c##U102','c##U103','c##U104')
    ORDER BY grantee, table_name, privilege;
   

3. 如果精确查询仍无结果，进一步校验两类场景：
   • 核对用户是否真实存在，执行全量公共用户列表查询，确认5个目标用户是否和预期名称完全一致：

     SELECT username, common FROM dba_users WHERE username LIKE 'C##U1%';
     

   • 校验是否存在角色中转的权限链路，关联dba_role_privs视图，定位角色赋予用户的权限链路：

     SELECT tp.owner, tp.table_name, tp.privilege, tp.grantee as role_name, rp.grantee as user_name
       FROM dba_tab_privs tp
       JOIN dba_role_privs rp ON tp.grantee = rp.granted_role
      WHERE rp.grantee IN ('c##U100','c##U101','c##U102','c##U103','c##U104');
     

修正后的通用校验语句 #

兼容上述场景的可直接使用的校验脚本，可覆盖绝大多数多租户权限校验场景：

-- 切到根容器执行公共用户权限校验
ALTER SESSION SET CONTAINER = CDB$ROOT;

col owner format a20
col table_name format a15
col privilege format a10
col grantee format a10
col auth_type format a15

-- 同时查询直接授权和角色中转授权的全量记录
SELECT DISTINCT owner, table_name, privilege, grantee, '直接授权' as auth_type
  FROM sys.dba_tab_privs
 WHERE grantee IN ('c##U100','c##U101','c##U102','c##U103','c##U104')
UNION ALL
SELECT DISTINCT tp.owner, tp.table_name, tp.privilege, rp.grantee, '经角色授权' as auth_type
  FROM sys.dba_tab_privs tp
  JOIN dba_role_privs rp ON tp.grantee = rp.granted_role
 WHERE rp.grantee IN ('c##U100','c##U101','c##U102','c##U103','c##U104')
ORDER BY grantee, table_name, privilege;

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