Oracle Hierarchical query with multiple parents

Question

I need help to build a query returning the data in the following way. I think a hierarchical query could be of help to it. But I'm not sure how to use it in my case.

For instance, I have following data

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

What I need is to return all roles users have been granted directly or indirectly. I know how to query all roles of a single user

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

The query result for the user User_1 is

ROLE_1
ROLE_2
ROLE_3
ROLE_4

But I can't figure it out how to return all roles for multiple users. For instance I wish to get a result set in the following way

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

Littlefoot · Accepted Answer · 2021-12-02 13:56:51Z

2

The way I see it, you have to have something to start with - hence my subquery (see line #11); then, this might be one option:

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>

answered Dec 2, 2021 at 13:56

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matthew McPeak Over a year ago

Nice answer, but why the subquery and not just start with grantee like 'user%'?

Collectives™ on Stack Overflow

Oracle Hierarchical query with multiple parents

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related