Oracle PL/SQL Tutorial/System Tables Data Dictionary/dba sys privs

Oracle System Privilege Grants to Roles

SQL>
SQL> SELECT privilege what_granted,
  2         admin_option, grantee
  3  FROM   sys.dba_sys_privs
  4  WHERE  NOT EXISTS
  5  (SELECT "x"
  6  FROM    sys.dba_users
  7  WHERE   username = grantee)
  8  and     rownum < 50
  9  ORDER BY 1,2;
WHAT_GRANTED                             ADM GRANTEE
---------------------------------------- --- ------------------------------
ADMINISTER ANY SQL TUNING SET            YES DBA
ALTER ANY INDEXTYPE                      YES DBA
ALTER ANY LIBRARY                        YES DBA
ALTER ANY PROCEDURE                      YES DBA
ALTER ANY RULE SET                       YES DBA
ALTER ANY SQL PROFILE                    YES DBA
ALTER DATABASE                           YES DBA
ALTER PROFILE                            YES DBA
ALTER ROLLBACK SEGMENT                   YES DBA
ALTER SESSION                            YES DBA
BECOME USER                              NO  IMP_FULL_DATABASE
WHAT_GRANTED                             ADM GRANTEE
---------------------------------------- --- ------------------------------
BECOME USER                              YES DBA
CHANGE NOTIFICATION                      YES DBA
CREATE ANY DIRECTORY                     YES DBA
CREATE ANY JOB                           YES SCHEDULER_ADMIN
CREATE ANY LIBRARY                       YES DBA
CREATE ANY MATERIALIZED VIEW             NO  IMP_FULL_DATABASE
CREATE ANY SYNONYM                       NO  IMP_FULL_DATABASE
CREATE DIMENSION                         YES DBA
CREATE JOB                               NO  OEM_ADVISOR
CREATE ROLE                              YES DBA
CREATE RULE                              YES DBA
WHAT_GRANTED                             ADM GRANTEE
---------------------------------------- --- ------------------------------
CREATE RULE SET                          YES DBA
CREATE SESSION                           YES DBA
CREATE SYNONYM                           NO  RECOVERY_CATALOG_OWNER
DEBUG ANY PROCEDURE                      YES DBA
DELETE ANY TABLE                         YES DBA
DEQUEUE ANY QUEUE                        YES DBA
DROP ANY INDEX                           YES DBA
DROP ANY INDEXTYPE                       YES DBA
DROP ANY LIBRARY                         NO  IMP_FULL_DATABASE
DROP ANY MATERIALIZED VIEW               YES DBA
DROP ANY TRIGGER                         NO  IMP_FULL_DATABASE
WHAT_GRANTED                             ADM GRANTEE
---------------------------------------- --- ------------------------------
DROP ANY TRIGGER                         YES DBA
DROP ROLLBACK SEGMENT                    YES DBA
DROP TABLESPACE                          YES DBA
EXECUTE ANY EVALUATION CONTEXT           YES DBA
EXECUTE ANY LIBRARY                      YES DBA
EXECUTE ANY PROCEDURE                    NO  IMP_FULL_DATABASE
EXECUTE ANY PROCEDURE                    YES DBA
EXPORT FULL DATABASE                     YES DBA
FORCE ANY TRANSACTION                    YES DBA
INSERT ANY TABLE                         YES DBA
MANAGE ANY QUEUE                         NO  OEM_MONITOR
WHAT_GRANTED                             ADM GRANTEE
---------------------------------------- --- ------------------------------
READ ANY FILE GROUP                      NO  EXP_FULL_DATABASE
SELECT ANY SEQUENCE                      YES DBA
SELECT ANY TABLE                         YES DBA
UNDER ANY TYPE                           YES DBA
UPDATE ANY TABLE                         YES DBA
49 rows selected.
SQL>

Oracle System Privilege Grants to Users

SQL>
SQL> SELECT b.privilege what_granted,
  2         b.admin_option, a.username
  3  FROM   sys.dba_users a, sys.dba_sys_privs b
  4  WHERE  a.username = b.grantee
  5  and    rownum< 50
  6  ORDER BY 1,2;
WHAT_GRANTED                             ADM USERNAME
---------------------------------------- --- ------------------------------
ALTER ANY EVALUATION CONTEXT             YES SYS
ALTER ANY RULE SET                       YES SYS
ALTER DATABASE                           NO  FLOWS_020100
ALTER SESSION                            NO  XDB
ALTER SESSION                            NO  FLOWS_020100
ALTER SESSION                            NO  HR
ALTER SYSTEM                             NO  FLOWS_020100
ANALYZE ANY                              NO  SYS
CREATE ANY CONTEXT                       YES FLOWS_020100
CREATE ANY DIRECTORY                     NO  FLOWS_020100
CREATE ANY EVALUATION CONTEXT            YES SYS
WHAT_GRANTED                             ADM USERNAME
---------------------------------------- --- ------------------------------
CREATE ANY RULE SET                      YES SYS
CREATE ANY SYNONYM                       NO  FLOWS_FILES
CREATE CLUSTER                           YES FLOWS_020100
CREATE DATABASE LINK                     NO  HR
CREATE DIMENSION                         YES FLOWS_020100
CREATE INDEXTYPE                         YES FLOWS_020100
CREATE LIBRARY                           NO  MDSYS
CREATE MATERIALIZED VIEW                 NO  SYSTEM
CREATE MATERIALIZED VIEW                 YES FLOWS_020100
CREATE PUBLIC SYNONYM                    NO  MDSYS
CREATE ROLE                              NO  FLOWS_020100
WHAT_GRANTED                             ADM USERNAME
---------------------------------------- --- ------------------------------
CREATE RULE                              YES SYS
CREATE RULE SET                          YES SYS
CREATE SEQUENCE                          NO  HR
CREATE SESSION                           NO  MDSYS
CREATE SESSION                           NO  XDB
CREATE SESSION                           NO  DIP
CREATE SESSION                           NO  sqle
CREATE SESSION                           NO  OUTLN
CREATE SYNONYM                           NO  HR
CREATE TABLE                             NO  MDSYS
CREATE TABLE                             YES FLOWS_020100
WHAT_GRANTED                             ADM USERNAME
---------------------------------------- --- ------------------------------
DEQUEUE ANY QUEUE                        YES SYS
DROP ANY RULE SET                        YES SYS
ENQUEUE ANY QUEUE                        YES SYS
EXECUTE ANY PROCEDURE                    NO  FLOWS_020100
EXECUTE ANY RULE SET                     YES SYS
EXECUTE ANY TYPE                         NO  SYS
LOCK ANY TABLE                           NO  SYS
MANAGE ANY QUEUE                         YES SYS
SELECT ANY TABLE                         YES SYS
UNLIMITED TABLESPACE                     NO  DBSNMP
UNLIMITED TABLESPACE                     NO  XDB
WHAT_GRANTED                             ADM USERNAME
---------------------------------------- --- ------------------------------
UNLIMITED TABLESPACE                     NO  FLOWS_FILES
UNLIMITED TABLESPACE                     NO  PLSQL
UNLIMITED TABLESPACE                     NO  OUTLN
UNLIMITED TABLESPACE                     NO  MDSYS
UPDATE ANY TABLE                         NO  SYS
49 rows selected.
SQL>
SQL>

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