Oracle PL/SQL/System Tables Views/dba role privs
Содержание
Query DBA_ROLE_PRIVS, ROLE_TAB_PRIVS for Name of the object, Privilege granted,Was admin option granted
<source lang="sql">
SQL> select
4 ROLE_TAB_PRIVS.Table_Name, /*Name of the object*/ 5 ROLE_TAB_PRIVS.Privilege, /*Privilege granted*/ 6 ROLE_TAB_PRIVS.Grantable /*Was admin option granted?*/ 7 from DBA_ROLE_PRIVS, ROLE_TAB_PRIVS 8 where DBA_ROLE_PRIVS.Granted_Role = ROLE_TAB_PRIVS.Role 9 and DBA_ROLE_PRIVS.Grantee = "some username";
SQL> SQL> SQL>
</source>
Query DBA_ROLE_PRIVS, ROLE_TAB_PRIVS for Recipient of the grant,Owner of the object
<source lang="sql">
SQL> select
2 DBA_ROLE_PRIVS.Grantee, /*Recipient of the grant*/ 3 ROLE_TAB_PRIVS.Owner /*Owner of the object*/ 7 from DBA_ROLE_PRIVS, ROLE_TAB_PRIVS 8 where DBA_ROLE_PRIVS.Granted_Role = ROLE_TAB_PRIVS.Role 9 and DBA_ROLE_PRIVS.Grantee = "some username";
SQL> SQL> SQL>
</source>
Query dba_role_privs table
<source lang="sql">
SQL> SQL> set verify off SQL> SQL> column grole format a20 heading "Role Granted" SQL> column wadmin format a6 heading "Admin?" SQL> SQL> select granted_role grole, initcap(admin_option) wadmin
2 from dba_role_privs 3 where rownum < 50 4 /
Role Granted Admin?
------
CONNECT No RESOURCE No XDBADMIN No PLUSTRACE Yes XDBWEBSERVICES No SCHEDULER_ADMIN Yes EXP_FULL_DATABASE No IMP_FULL_DATABASE No DELETE_CATALOG_ROLE Yes SELECT_CATALOG_ROLE Yes EXECUTE_CATALOG_ROLE Yes GATHER_SYSTEM_STATIS No TICS DBA Yes CTXAPP Yes CONNECT Yes RESOURCE Yes XDBADMIN Yes PLUSTRACE Yes OEM_ADVISOR Yes OEM_MONITOR Yes AQ_USER_ROLE Yes HS_ADMIN_ROLE Yes XDBWEBSERVICES Yes SCHEDULER_ADMIN Yes AUTHENTICATEDUSER Yes EXP_FULL_DATABASE Yes IMP_FULL_DATABASE Yes DELETE_CATALOG_ROLE Yes SELECT_CATALOG_ROLE Yes EXECUTE_CATALOG_ROLE Yes AQ_ADMINISTRATOR_ROL Yes E LOGSTDBY_ADMINISTRAT Yes OR RECOVERY_CATALOG_OWN Yes ER GATHER_SYSTEM_STATIS Yes TICS CTXAPP No RESOURCE No CONNECT No RESOURCE No RESOURCE No CTXAPP Yes RESOURCE No OEM_MONITOR No DBA No DBA Yes AQ_ADMINISTRATOR_ROL Yes E RESOURCE No XDBWEBSERVICES No CONNECT No RESOURCE No 49 rows selected. SQL> SQL> --
</source>
Query user granted roles
<source lang="sql">
SQL> SQL> SQL> COL grantee FORMAT A8 SQL> COL granted_role FORMAT A30 SQL> COL grantor FORMAT A8 SQL> COL privilege FORMAT A12 SQL> COL owner FORMAT A4 SQL> COL table_name FORMAT A30 SQL> SQL> SQL> SELECT grantee
2 , granted_role 3 FROM dba_role_privs 4 WHERE grantee = "PLSQL";
GRANTEE GRANTED_ROLE
------------------------------
PLSQL CTXAPP PLSQL CONNECT PLSQL RESOURCE 3 rows selected. SQL> SQL> -- Query resources. SQL> SELECT grantor
2 , owner 3 , table_name 4 , grantee 5 , privilege 6 FROM dba_tab_privs 7 WHERE grantee = "PLSQL";
no rows selected
</source>