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

Query dba_sys_privs table

  
SQL>
SQL> set verify off
SQL>
SQL> column priv   format a30 heading "Privilege"
SQL> column wadmin format a6 heading "Admin?"
SQL>
SQL> select privilege priv, initcap(admin_option) wadmin
  2    from dba_sys_privs
  3   where rownum < 50
  4  /
Privilege                      Admin?
------------------------------ ------
CREATE VIEW                    No
ALTER SESSION                  No
CREATE SESSION                 No
CREATE SYNONYM                 No
CREATE SEQUENCE                No
CREATE DATABASE LINK           No
UNLIMITED TABLESPACE           No
ADVISOR                        Yes
AUDIT ANY                      Yes
DROP USER                      Yes
RESUMABLE                      Yes
ALTER USER                     Yes
CREATE JOB                     Yes
ANALYZE ANY                    Yes
BECOME USER                    Yes
CREATE ROLE                    Yes
CREATE RULE                    Yes
CREATE TYPE                    Yes
CREATE USER                    Yes
CREATE VIEW                    Yes
ALTER SYSTEM                   Yes
AUDIT SYSTEM                   Yes
CREATE TABLE                   Yes
DROP PROFILE                   Yes
ALTER PROFILE                  Yes
ALTER SESSION                  Yes
DROP ANY ROLE                  Yes
DROP ANY RULE                  Yes
DROP ANY TYPE                  Yes
DROP ANY VIEW                  Yes
QUERY REWRITE                  Yes
ALTER ANY ROLE                 Yes
ALTER ANY RULE                 Yes
ALTER ANY TYPE                 Yes
ALTER DATABASE                 Yes
CREATE ANY JOB                 Yes
CREATE CLUSTER                 Yes
CREATE LIBRARY                 Yes
CREATE PROFILE                 Yes
CREATE SESSION                 Yes
CREATE SYNONYM                 Yes
CREATE TRIGGER                 Yes
DROP ANY INDEX                 Yes
DROP ANY TABLE                 Yes
GRANT ANY ROLE                 Yes
LOCK ANY TABLE                 Yes
MERGE ANY VIEW                 Yes
UNDER ANY TYPE                 Yes
UNDER ANY VIEW                 Yes
49 rows selected.
SQL>
SQL> --

select distinct privilege from dba_sys_privs

  
                                                                                                                                                                                                                                                            VARCHAR2(3)
SQL> select distinct privilege
  2  from dba_sys_privs
  3  where rownum < 50
  4  order by privilege;
PRIVILEGE
----------------------------------------
ADVISOR
ALTER SYSTEM
ALTER USER
ANALYZE ANY
AUDIT ANY
AUDIT SYSTEM
BECOME USER
CREATE JOB
CREATE ROLE
CREATE RULE
CREATE TABLE
PRIVILEGE
----------------------------------------
CREATE TYPE
CREATE USER
CREATE VIEW
DROP USER
RESUMABLE
16 rows selected.

User System Privileges

    
SQL>
SQL> SET FEEDBACK OFF
SQL> SET PAGESIZE 20
SQL> SET LINESIZE 77
SQL> SET HEADING ON
SQL>
SQL> COLUMN username FORMAT A12 HEADING "User"
SQL> COLUMN privilege FORMAT A20 HEADING "Privilege"
SQL> TTITLE LEFT "User System Privileges" RIGHT "Page " FORMAT 999 SQL.PNO SKIP 2
SQL>
SQL> SELECT username, privilege
  2  FROM dba_users, dba_sys_privs
  3  WHERE username = grantee
  4  ORDER BY username, privilege;
User System Privileges                                              Page    1
User         Privilege
------------ --------------------
ANONYMOUS    CREATE SESSION
CTXSYS       ALTER SESSION
CTXSYS       CREATE PUBLIC SYNONY
             M
CTXSYS       CREATE SESSION
CTXSYS       CREATE SYNONYM
CTXSYS       CREATE VIEW
CTXSYS       DROP PUBLIC SYNONYM
CTXSYS       UNLIMITED TABLESPACE
DBSNMP       CREATE PROCEDURE
DBSNMP       CREATE TABLE
DBSNMP       SELECT ANY DICTIONAR
             Y

User System Privileges                                              Page    2
User         Privilege
------------ --------------------
DBSNMP       UNLIMITED TABLESPACE
DEFINER      UNLIMITED TABLESPACE
DIP          CREATE SESSION
FLOWS_020100 ALTER DATABASE
FLOWS_020100 ALTER SESSION
FLOWS_020100 ALTER SYSTEM
FLOWS_020100 ALTER USER
FLOWS_020100 CREATE ANY CONTEXT
FLOWS_020100 CREATE ANY DIRECTORY
FLOWS_020100 CREATE CLUSTER
FLOWS_020100 CREATE DATABASE LINK
FLOWS_020100 CREATE DIMENSION
FLOWS_020100 CREATE INDEXTYPE
FLOWS_020100 CREATE JOB
FLOWS_020100 CREATE LIBRARY
User System Privileges                                              Page    3
User         Privilege
------------ --------------------
FLOWS_020100 CREATE MATERIALIZED
             VIEW
FLOWS_020100 CREATE OPERATOR
FLOWS_020100 CREATE PROCEDURE
FLOWS_020100 CREATE PUBLIC SYNONY
             M
FLOWS_020100 CREATE ROLE
FLOWS_020100 CREATE SEQUENCE
FLOWS_020100 CREATE SYNONYM
FLOWS_020100 CREATE TABLE
FLOWS_020100 CREATE TABLESPACE
FLOWS_020100 CREATE TRIGGER
FLOWS_020100 CREATE TYPE
SQL>
SQL> SET FEEDBACK ON