Oracle-12c
如何在另一個角色 oracle 中獲取角色
授予的角色都在 中
dba_granted_roles
,因此您可以執行簡單的遞歸查詢來獲取它們。
connect by
當我可以擺脫簡單時,我更喜歡: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
,所以每個返回的行都告訴我路線,例如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 ...
您會注意到某些角色是通過多個其他角色授予的,您可以在
distinct
這裡簡單地刪除重複項(當然您必須放棄路線,因為這是使它們與眾不同的原因)select distinct granted_role from dba_role_privs rp connect by prior rp.granted_role = rp.grantee start with rp.grantee = '<USER>'