Oracle PL/SQL/System Tables Views/dba role privs

Материал из SQL эксперт
Перейти к: навигация, поиск

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>