Privileges assigned to roles and available to the currently logged in user : ROLE_SYS_PRIVS « System Tables Views « Oracle PL / SQL






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



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>
           
       








Related examples in the same category