How get role inside another role oracle

All we need is an easy explanation of the problem, so here it is.

I am trying to make a script that allows me to get the roles that are inside another role of all usersHow get role inside another role oracle

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Granted roles are all in dba_granted_roles, so you can do a simple recursive query to get them.

I prefer connect by when I can get away with the simplicity:

select granted_role
      ,ltrim(sys_connect_by_path(granted_role,'>'),'>') how
from   dba_role_privs rp
connect by prior rp.granted_role = rp.grantee
start with rp.grantee = '<USER>'

I’m using sys_connect_by_path so each returned row tells me the route, e.g. for SYS

GRANTED_ROLE                   HOW
______________________________ __________________________________________________________________________________________________________
ACCHK_READ                     ACCHK_READ
ADM_PARALLEL_EXECUTE_TASK      ADM_PARALLEL_EXECUTE_TASK
APPLICATION_TRACE_VIEWER       APPLICATION_TRACE_VIEWER
AQ_ADMINISTRATOR_ROLE          AQ_ADMINISTRATOR_ROLE
AQ_USER_ROLE                   AQ_USER_ROLE
AUDIT_ADMIN                    AUDIT_ADMIN
AUDIT_VIEWER                   AUDIT_VIEWER
AUTHENTICATEDUSER              AUTHENTICATEDUSER
BDSQL_ADMIN                    BDSQL_ADMIN
BDSQL_USER                     BDSQL_USER
CAPTURE_ADMIN                  CAPTURE_ADMIN
CDB_DBA                        CDB_DBA
CONNECT                        CONNECT
DATAPATCH_ROLE                 DATAPATCH_ROLE
DATAPUMP_EXP_FULL_DATABASE     DATAPUMP_EXP_FULL_DATABASE
EXP_FULL_DATABASE              DATAPUMP_EXP_FULL_DATABASE>EXP_FULL_DATABASE
EXECUTE_CATALOG_ROLE           DATAPUMP_EXP_FULL_DATABASE>EXP_FULL_DATABASE>EXECUTE_CATALOG_ROLE
HS_ADMIN_EXECUTE_ROLE          DATAPUMP_EXP_FULL_DATABASE>EXP_FULL_DATABASE>EXECUTE_CATALOG_ROLE>HS_ADMIN_EXECUTE_ROLE
HS_ADMIN_ROLE                  DATAPUMP_EXP_FULL_DATABASE>EXP_FULL_DATABASE>EXECUTE_CATALOG_ROLE>HS_ADMIN_ROLE
HS_ADMIN_EXECUTE_ROLE          DATAPUMP_EXP_FULL_DATABASE>EXP_FULL_DATABASE>EXECUTE_CATALOG_ROLE>HS_ADMIN_ROLE>HS_ADMIN_EXECUTE_ROLE
...

You’ll notice that some roles are granted via multiple other roles, you could do a simple distinct to remove duplicates here (of course you have to give up the route as this is what’s making them distinct)

select distinct granted_role
from   dba_role_privs rp
connect by prior rp.granted_role = rp.grantee
start with rp.grantee = '<USER>'

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags:

Leave a Reply