Oracle-12c

如何在另一個角色 oracle 中獲取角色

  • April 13, 2022

我正在嘗試製作一個腳本,允許我獲取所有使用者的另一個角色中的角色在此處輸入圖像描述

授予的角色都在 中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>'

引用自:https://dba.stackexchange.com/questions/310929