Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / System Tables Data Dictionary

 

Union user name from sys dba_users table

SQL> SQL> SELECT a.username,   2         b.granted_role || DECODE(admin_option,'YES',   3         ' (With Admin Option)',NULL) what_granted   4  FROM   sys.dba_users a, sys.dba_role_privs b   5  WHERE  a.username = b.grantee and rownum < 50   6  UNION   7  SELECT a.username,   8         b.privilege || DECODE(admin_option,'YES',   9         ' (With Admin Option)', NULL) what_granted  10  FROM   sys.dba_users a, sys.dba_sys_privs b  11  WHERE  a.username = b.grantee and rownum < 50  12  UNION  13  SELECT a.username,  14         b.table_name || ' - ' || b.privilege  15         || DECODE(grantable,'YES',  16         ' (With Grant Option)',NULL) what_granted  17  FROM   sys.dba_users a, sys.dba_tab_privs b  18  WHERE  a.username = b.grantee  and rownum < 50  19  ORDER BY 1; USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- CTXSYS ARGUMENT$ - SELECT (With Grant Option) CTXSYS CCOL$ - SELECT (With Grant Option) CTXSYS CDEF$ - SELECT (With Grant Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- CTXSYS COL$ - SELECT (With Grant Option) CTXSYS CON$ - SELECT (With Grant Option) CTXSYS CREATE SESSION USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- CTXSYS CTXAPP (With Admin Option) CTXSYS ICOL$ - SELECT (With Grant Option) CTXSYS IND$ - SELECT (With Grant Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- CTXSYS OBJ$ - SELECT (With Grant Option) CTXSYS RESOURCE CTXSYS SYN$ - SELECT (With Grant Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- CTXSYS SYSAUTH$ - SELECT (With Grant Option) CTXSYS TAB$ - SELECT (With Grant Option) CTXSYS TS$ - SELECT (With Grant Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- CTXSYS USER$ - SELECT (With Grant Option) CTXSYS VIEW$ - SELECT (With Grant Option) DBSNMP CREATE PROCEDURE USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- DBSNMP OEM_MONITOR FLOWS_020100 ALTER USER FLOWS_020100 CONNECT (With Admin Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- FLOWS_020100 CREATE ANY CONTEXT (With Admin Option) FLOWS_020100 CREATE ANY DIRECTORY FLOWS_020100 CREATE DATABASE LINK (With Admin Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- FLOWS_020100 CREATE DIMENSION (With Admin Option) FLOWS_020100 CREATE LIBRARY (With Admin Option) FLOWS_020100 CREATE PUBLIC SYNONYM USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- FLOWS_020100 CREATE SYNONYM (With Admin Option) FLOWS_020100 CREATE USER FLOWS_020100 DBA (With Admin Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- FLOWS_020100 DROP ANY DIRECTORY FLOWS_020100 OBJ$ - SELECT FLOWS_020100 RESOURCE (With Admin Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- FLOWS_020100 SELECT_CATALOG_ROLE FLOWS_020100 USER$ - SELECT (With Grant Option) FLOWS_FILES CONNECT USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- FLOWS_FILES CREATE ANY SYNONYM FLOWS_FILES CREATE PUBLIC SYNONYM FLOWS_FILES RESOURCE USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- HR CONNECT HR CREATE DATABASE LINK HR CREATE VIEW USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- HR RESOURCE HR UNLIMITED TABLESPACE RNTSOFT DBA USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- MDSYS CONNECT MDSYS CREATE OPERATOR MDSYS CREATE SEQUENCE USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- MDSYS CREATE TABLE MDSYS CREATE TYPE MDSYS CREATE VIEW USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- MDSYS DELETE ANY TABLE MDSYS RESOURCE MDSYS UNLIMITED TABLESPACE USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- OUTLN RESOURCE OUTLN UNLIMITED TABLESPACE PLSQL CONNECT USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- PLSQL CTXAPP PLSQL RESOURCE PLSQL UNLIMITED TABLESPACE USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYS ALTER ANY RULE (With Admin Option) SYS AQ_ADMINISTRATOR_ROLE (With Admin Option) SYS AQ_USER_ROLE (With Admin Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYS AUTHENTICATEDUSER (With Admin Option) SYS CONNECT (With Admin Option) SYS CREATE ANY EVALUATION CONTEXT (With Admin Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYS CREATE ANY RULE (With Admin Option) SYS CREATE RULE SET (With Admin Option) SYS CTXAPP (With Admin Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYS DBA (With Admin Option) SYS DELETE_CATALOG_ROLE (With Admin Option) SYS ENQUEUE ANY QUEUE (With Admin Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYS EXECUTE ANY EVALUATION CONTEXT (With Admin Option) SYS EXECUTE ANY PROCEDURE SYS EXECUTE ANY TYPE USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYS EXECUTE_CATALOG_ROLE (With Admin Option) SYS EXP_FULL_DATABASE (With Admin Option) SYS GATHER_SYSTEM_STATISTICS (With Admin Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYS HS_ADMIN_ROLE (With Admin Option) SYS IMP_FULL_DATABASE (With Admin Option) SYS INSERT ANY TABLE USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYS LOCK ANY TABLE SYS LOGSTDBY_ADMINISTRATOR (With Admin Option) SYS MANAGE ANY QUEUE (With Admin Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYS OEM_ADVISOR (With Admin Option) SYS OEM_MONITOR (With Admin Option) SYS PLUSTRACE (With Admin Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYS RECOVERY_CATALOG_OWNER (With Admin Option) SYS RESOURCE (With Admin Option) SYS SCHEDULER_ADMIN (With Admin Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYS SELECT ANY TABLE (With Admin Option) SYS SELECT_CATALOG_ROLE (With Admin Option) SYS UPDATE ANY TABLE USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYS XDBADMIN (With Admin Option) SYS XDBWEBSERVICES (With Admin Option) SYSTEM AQ_ADMINISTRATOR_ROLE (With Admin Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYSTEM CREATE MATERIALIZED VIEW SYSTEM CREATE TABLE SYSTEM DBA (With Admin Option) USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYSTEM GLOBAL QUERY REWRITE SYSTEM INCEXP - ALTER SYSTEM INCEXP - DEBUG USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYSTEM INCEXP - DELETE SYSTEM INCEXP - FLASHBACK SYSTEM INCEXP - INDEX USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYSTEM INCEXP - INSERT SYSTEM INCEXP - ON COMMIT REFRESH SYSTEM INCEXP - QUERY REWRITE USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYSTEM INCEXP - REFERENCES SYSTEM INCEXP - SELECT SYSTEM INCEXP - UPDATE USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYSTEM INCFIL - DEBUG SYSTEM INCFIL - DELETE SYSTEM INCFIL - FLASHBACK USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYSTEM INCFIL - INDEX SYSTEM INCFIL - INSERT SYSTEM INCFIL - ON COMMIT REFRESH USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYSTEM INCFIL - QUERY REWRITE SYSTEM INCFIL - REFERENCES SYSTEM INCFIL - SELECT USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYSTEM INCFIL - UPDATE SYSTEM INCVID - ALTER SYSTEM INCVID - DEBUG USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYSTEM INCVID - DELETE SYSTEM INCVID - FLASHBACK SYSTEM INCVID - INDEX USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYSTEM INCVID - INSERT SYSTEM INCVID - ON COMMIT REFRESH SYSTEM INCVID - QUERY REWRITE USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- SYSTEM INCVID - REFERENCES SYSTEM INCVID - SELECT SYSTEM INCVID - UPDATE USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- TSMSYS RESOURCE XDB CREATE INDEXTYPE XDB CREATE LIBRARY USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- XDB CREATE OPERATOR XDB CREATE PUBLIC SYNONYM XDB CTXAPP USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- XDB DROP PUBLIC SYNONYM XDB QUERY REWRITE XDB RESOURCE USERNAME ------------------------------ WHAT_GRANTED -------------------------------------------------------------------------------- XDB UNLIMITED TABLESPACE XDB USER$ - SELECT 143 rows selected. SQL>