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