Oracle多用户场景下使用层级查询获取全部直接/间接授予角色咨询

你可以直接使用如下查询语句实现需求：

SELECT DISTINCT 
       CONNECT_BY_ROOT(GRANTEE) AS Grantee,
       GRANTED_ROLE AS Role
FROM DBA_ROLE_PRIVS
-- 若只需查询指定用户，可将下方子查询替换为 IN ('USER_1','USER_2') 格式的枚举列表
START WITH GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
CONNECT BY PRIOR GRANTED_ROLE = GRANTEE
ORDER BY Grantee, Role;

逻辑说明 #

• CONNECT_BY_ROOT是Oracle层级查询的原生函数，可返回当前层级链路最顶层的起始节点值，这里用来获取每个角色对应的原始授权用户，也就是你需要的Grantee列
• START WITH条件改为筛选所有系统用户作为层级遍历的起点，即可同时完成所有用户的角色递归查询，不需要逐个输入用户名参数
• 保留DISTINCT可避免同一用户通过多条路径获得同一个角色时产生重复记录

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