0

I am trying to make a script that allows me to get the roles that are inside another role of all usersenter image description here

1 Answer 1

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>'
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.