具有多个父级的Oracle分层查询

Question

我需要帮助构建一个以以下方式返回数据的查询。我认为层次查询可能会对它有所帮助。但我不确定如何在我的情况下使用它。

例如，我有以下数据

Grantee  Role
-------  ------
User_1   Role_4
Role_4   Role_1
Role_4   Role_2
Role_4   Role_3 
User_2   Role_5
User_2   Role_6

我需要的是返回用户直接或间接授予的所有角色。我知道如何查询单个用户的所有角色

SELECT DISTINCT GRANTED_ROLE FROM DBA_ROLE_PRIVS
START WITH GRANTEE=UPPER('&&USERNAME')
CONNECT BY PRIOR GRANTED_ROLE=GRANTEE

用户user_1的查询结果为

ROLE_1
ROLE_2
ROLE_3
ROLE_4

但我不知道如何为多个用户返回所有角色。例如，我希望以以下方式获得结果集

Grantee  Role
-------  ------
User_1   Role_4
User_1   Role_1     
User_1   Role_2     
User_1   Role_3     
User_2   Role_5
User_2   Role_6

Answer 1

在我看来，你必须从一些东西开始——因此我的子查询(见第11行(；那么，这可能是一种选择：

SQL> with test (grantee, role) as
2    (select 'user_1', 'role_4' from dual union all
3     select 'role_4', 'role_1' from dual union all
4     select 'role_4', 'role_2' from dual union all
5     select 'role_4', 'role_3' from dual union all
6     select 'user_2', 'role_5' from dual union all
7     select 'user_2', 'role_6' from dual
8    )
9  select distinct regexp_substr(sys_connect_by_path(grantee, '-'), 'w+', 1, 1) grantee, role
10  from test
11  start with grantee in (select grantee from test where grantee like 'user%')
12  connect by prior role = grantee
13  order by grantee, role;
GRANTEE    ROLE
---------- ------
user_1     role_1
user_1     role_2
user_1     role_3
user_1     role_4
user_2     role_5
user_2     role_6
6 rows selected.
SQL>