I am trying to make a script that allows me to get the roles that are inside another role of all users
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Granted roles are all in
dba_granted_roles, so you can do a simple recursive query to get them.
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>'
sys_connect_by_path so each returned row tells me the route, e.g. for
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>'
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