Oracle PL/SQL/System Tables Views/ROLE SYS PRIVS

Privileges assigned to roles and available to the currently logged in user

   <source lang="sql">

SQL> --Data Dictionary Views Describing Roles SQL> SQL> --Privileges assigned to roles and available to the currently logged in user SQL> SQL> desc ROLE_SYS_PRIVS;

Name                                                                                                  Null?    Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
ROLE                                                                                                  NOT NULL VARCHAR2(30)
PRIVILEGE                                                                                             NOT NULL VARCHAR2(40)
ADMIN_OPTION                                                                                           VARCHAR2(3)

SQL> select * from ROLE_SYS_PRIVS; ROLE PRIVILEGE ADM

---

-------------------- ---

DBA CREATE SESSION YES DBA ALTER SESSION YES DBA DROP TABLESPACE YES DBA BECOME USER YES DBA DROP ROLLBACK SEGMEN YES

                              T

DBA SELECT ANY TABLE YES DBA INSERT ANY TABLE YES DBA UPDATE ANY TABLE YES DBA DROP ANY INDEX YES DBA SELECT ANY SEQUENCE YES DBA CREATE ROLE YES DBA EXECUTE ANY PROCEDUR YES

                              E

DBA ALTER PROFILE YES DBA CREATE ANY DIRECTORY YES DBA CREATE ANY LIBRARY YES DBA EXECUTE ANY LIBRARY YES DBA ALTER ANY INDEXTYPE YES DBA DROP ANY INDEXTYPE YES DBA DEQUEUE ANY QUEUE YES DBA EXECUTE ANY EVALUATI YES

                              ON CONTEXT

DBA EXPORT FULL DATABASE YES DBA CREATE RULE YES DBA ALTER ANY SQL PROFIL YES

                              E

DBA ADMINISTER ANY SQL T YES

                              UNING SET

DBA CHANGE NOTIFICATION YES IMP_FULL_DATABASE BECOME USER NO IMP_FULL_DATABASE CREATE ANY MATERIALI NO

                              ZED VIEW

IMP_FULL_DATABASE CREATE ANY SYNONYM NO IMP_FULL_DATABASE EXECUTE ANY PROCEDUR NO

                              E

IMP_FULL_DATABASE DROP ANY TRIGGER NO IMP_FULL_DATABASE DROP ANY LIBRARY NO SCHEDULER_ADMIN CREATE ANY JOB YES EXP_FULL_DATABASE READ ANY FILE GROUP NO DBA ALTER ROLLBACK SEGME YES

                              NT

DBA DELETE ANY TABLE YES DBA ALTER DATABASE YES DBA FORCE ANY TRANSACTIO YES

                              N

DBA ALTER ANY PROCEDURE YES DBA DROP ANY TRIGGER YES DBA DROP ANY MATERIALIZE YES

                              D VIEW

DBA UNDER ANY TYPE YES DBA ALTER ANY LIBRARY YES DBA CREATE DIMENSION YES DBA DEBUG ANY PROCEDURE YES DBA CREATE RULE SET YES DBA ALTER ANY RULE SET YES DBA ANALYZE ANY DICTIONA YES

                              RY

IMP_FULL_DATABASE CREATE ANY CLUSTER NO IMP_FULL_DATABASE CREATE ANY INDEX NO IMP_FULL_DATABASE INSERT ANY TABLE NO IMP_FULL_DATABASE CREATE ANY LIBRARY NO IMP_FULL_DATABASE DROP ANY SEQUENCE NO IMP_FULL_DATABASE DROP ROLLBACK SEGMEN NO

                              T

IMP_FULL_DATABASE DROP ANY CONTEXT NO SCHEDULER_ADMIN EXECUTE ANY PROGRAM YES SCHEDULER_ADMIN EXECUTE ANY CLASS YES DBA RESTRICTED SESSION YES DBA CREATE TABLESPACE YES DBA ALTER TABLESPACE YES DBA CREATE USER YES DBA ALTER USER YES DBA LOCK ANY TABLE YES DBA CREATE VIEW YES DBA DROP ANY VIEW YES DBA GRANT ANY ROLE YES DBA CREATE TRIGGER YES DBA CREATE TYPE YES DBA EXECUTE ANY OPERATOR YES DBA CREATE ANY DIMENSION YES DBA ALTER ANY DIMENSION YES DBA CREATE ANY OUTLINE YES DBA ADMINISTER DATABASE YES

                              TRIGGER

ROLE PRIVILEGE ADM

---

-------------------- ---

DBA RESUMABLE YES DBA FLASHBACK ANY TABLE YES DBA CREATE ANY RULE SET YES DBA EXECUTE ANY RULE SET YES DBA IMPORT FULL DATABASE YES DBA EXECUTE ANY RULE YES DBA EXECUTE ANY PROGRAM YES EXP_FULL_DATABASE EXECUTE ANY PROCEDUR NO

                              E

EXP_FULL_DATABASE RESUMABLE NO IMP_FULL_DATABASE CREATE ANY PROCEDURE NO IMP_FULL_DATABASE CREATE ANY VIEW NO IMP_FULL_DATABASE CREATE TABLESPACE NO IMP_FULL_DATABASE SELECT ANY TABLE NO IMP_FULL_DATABASE CREATE ANY CONTEXT NO IMP_FULL_DATABASE CREATE ANY OPERATOR NO IMP_FULL_DATABASE GLOBAL QUERY REWRITE NO IMP_FULL_DATABASE DROP ANY OUTLINE NO IMP_FULL_DATABASE DROP ANY VIEW NO IMP_FULL_DATABASE DROP PROFILE NO IMP_FULL_DATABASE DROP USER NO IMP_FULL_DATABASE DROP ANY TYPE NO DBA CREATE ANY TABLE YES DBA CREATE ANY INDEX YES DBA CREATE ANY SEQUENCE YES DBA ALTER ANY ROLE YES DBA ANALYZE ANY YES DBA DROP ANY LIBRARY YES DBA CREATE ANY OPERATOR YES DBA CREATE INDEXTYPE YES DBA UNDER ANY TABLE YES DBA DROP ANY DIMENSION YES DBA SELECT ANY DICTIONAR YES

                              Y

DBA GRANT ANY OBJECT PRI YES

                              VILEGE

DBA CREATE EVALUATION CO YES

                              NTEXT

DBA CREATE ANY EVALUATIO YES

                              N CONTEXT

DBA DROP ANY EVALUATION YES

                              CONTEXT

DBA CREATE ANY RULE YES DBA CREATE JOB YES DBA CREATE ANY JOB YES EXP_FULL_DATABASE SELECT ANY TABLE NO EXP_FULL_DATABASE EXECUTE ANY TYPE NO IMP_FULL_DATABASE CREATE PUBLIC DATABA NO

                              SE LINK

IMP_FULL_DATABASE CREATE PUBLIC SYNONY NO

                              M

IMP_FULL_DATABASE CREATE ROLLBACK SEGM NO

                              ENT

IMP_FULL_DATABASE CREATE ROLE NO IMP_FULL_DATABASE DROP ANY INDEX NO IMP_FULL_DATABASE DROP ANY TABLE NO IMP_FULL_DATABASE DROP ANY MATERIALIZE NO

                              D VIEW

IMP_FULL_DATABASE DROP ANY ROLE NO IMP_FULL_DATABASE ALTER ANY TYPE NO IMP_FULL_DATABASE MANAGE ANY QUEUE NO DBA ALTER SYSTEM YES DBA AUDIT SYSTEM YES DBA CREATE ROLLBACK SEGM YES

                              ENT

DBA DROP ANY TABLE YES DBA COMMENT ANY TABLE YES DBA CREATE CLUSTER YES DBA ALTER ANY INDEX YES DBA DROP PUBLIC DATABASE YES

                               LINK

DBA CREATE PROFILE YES DBA ALTER ANY MATERIALIZ YES

                              ED VIEW

DBA ALTER ANY TYPE YES DBA DROP ANY TYPE YES DBA UNDER ANY VIEW YES DBA EXECUTE ANY INDEXTYP YES

                              E

DBA DROP ANY CONTEXT YES DBA ALTER ANY OUTLINE YES DBA ADMINISTER RESOURCE YES ROLE PRIVILEGE ADM

---

-------------------- ---

                              MANAGER

DBA MANAGE SCHEDULER YES DBA MANAGE FILE GROUP YES EXP_FULL_DATABASE BACKUP ANY TABLE NO IMP_FULL_DATABASE CREATE PROFILE NO IMP_FULL_DATABASE COMMENT ANY TABLE NO IMP_FULL_DATABASE CREATE ANY INDEXTYPE NO IMP_FULL_DATABASE CREATE ANY DIMENSION NO IMP_FULL_DATABASE DROP ANY CLUSTER NO IMP_FULL_DATABASE DROP ANY PROCEDURE NO IMP_FULL_DATABASE DROP PUBLIC SYNONYM NO IMP_FULL_DATABASE DROP ANY DIRECTORY NO EXP_FULL_DATABASE ADMINISTER RESOURCE NO

                              MANAGER

DBA CREATE TABLE YES DBA BACKUP ANY TABLE YES DBA CREATE ANY CLUSTER YES DBA DROP ANY SYNONYM YES DBA DROP PUBLIC SYNONYM YES DBA CREATE ANY VIEW YES DBA CREATE SEQUENCE YES DBA ALTER ANY SEQUENCE YES DBA FORCE TRANSACTION YES DBA CREATE PROCEDURE YES DBA CREATE ANY PROCEDURE YES DBA ALTER RESOURCE COST YES DBA DROP ANY DIRECTORY YES DBA CREATE ANY TYPE YES DBA CREATE ANY INDEXTYPE YES DBA ENQUEUE ANY QUEUE YES DBA ON COMMIT REFRESH YES DBA DEBUG CONNECT SESSIO YES

                              N

DBA DROP ANY RULE SET YES DBA EXECUTE ANY CLASS YES DBA MANAGE ANY FILE GROU YES

                              P

EXP_FULL_DATABASE SELECT ANY SEQUENCE NO IMP_FULL_DATABASE EXECUTE ANY TYPE NO IMP_FULL_DATABASE UPDATE ANY TABLE NO IMP_FULL_DATABASE CREATE ANY DIRECTORY NO IMP_FULL_DATABASE CREATE ANY TYPE NO IMP_FULL_DATABASE DROP ANY SYNONYM NO IMP_FULL_DATABASE DROP PUBLIC DATABASE NO

                               LINK

IMP_FULL_DATABASE DROP TABLESPACE NO IMP_FULL_DATABASE ALTER ANY TRIGGER NO IMP_FULL_DATABASE DROP ANY INDEXTYPE NO IMP_FULL_DATABASE DROP ANY DIMENSION NO IMP_FULL_DATABASE ANALYZE ANY NO IMP_FULL_DATABASE ADMINISTER RESOURCE NO

                              MANAGER

SCHEDULER_ADMIN MANAGE SCHEDULER YES SCHEDULER_ADMIN CREATE EXTERNAL JOB YES DBA ALTER ANY TABLE YES DBA DROP ANY CLUSTER YES DBA CREATE SYNONYM YES DBA CREATE PUBLIC SYNONY YES

                              M

DBA DROP ANY SEQUENCE YES DBA DROP ANY ROLE YES DBA AUDIT ANY YES DBA DROP ANY PROCEDURE YES DBA CREATE ANY TRIGGER YES DBA ALTER ANY TRIGGER YES DBA DROP PROFILE YES DBA GRANT ANY PRIVILEGE YES DBA CREATE LIBRARY YES DBA CREATE OPERATOR YES DBA DROP ANY OUTLINE YES DBA MERGE ANY VIEW YES DBA ADMINISTER SQL TUNIN YES

                              G SET

IMP_FULL_DATABASE CREATE ANY TABLE NO IMP_FULL_DATABASE CREATE DATABASE LINK NO IMP_FULL_DATABASE CREATE USER NO IMP_FULL_DATABASE AUDIT ANY NO IMP_FULL_DATABASE CREATE ANY SQL PROFI NO

                              LE

IMP_FULL_DATABASE ALTER ANY PROCEDURE NO IMP_FULL_DATABASE DROP ANY OPERATOR NO IMP_FULL_DATABASE RESUMABLE NO IMP_FULL_DATABASE DROP ANY SQL PROFILE NO DBA MANAGE TABLESPACE YES DBA DROP USER YES DBA ALTER ANY CLUSTER YES DBA CREATE ANY SYNONYM YES DBA CREATE DATABASE LINK YES ROLE PRIVILEGE ADM

---

-------------------- ---

DBA CREATE PUBLIC DATABA YES

                              SE LINK

DBA CREATE MATERIALIZED YES

                              VIEW

DBA CREATE ANY MATERIALI YES

                              ZED VIEW

DBA EXECUTE ANY TYPE YES DBA DROP ANY OPERATOR YES DBA QUERY REWRITE YES DBA GLOBAL QUERY REWRITE YES DBA MANAGE ANY QUEUE YES DBA CREATE ANY CONTEXT YES DBA ALTER ANY EVALUATION YES

                               CONTEXT

DBA ALTER ANY RULE YES DBA DROP ANY RULE YES DBA ADVISOR YES DBA SELECT ANY TRANSACTI YES

                              ON

DBA DROP ANY SQL PROFILE YES DBA CREATE ANY SQL PROFI YES

                              LE

DBA READ ANY FILE GROUP YES DBA CREATE EXTERNAL JOB YES IMP_FULL_DATABASE CREATE ANY SEQUENCE NO IMP_FULL_DATABASE CREATE ANY TRIGGER NO IMP_FULL_DATABASE ALTER ANY TABLE NO IMP_FULL_DATABASE ADMINISTER DATABASE NO

                              TRIGGER

SCHEDULER_ADMIN CREATE JOB YES 242 rows selected. SQL> SQL>

      </source>