Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Internal Roles in Oracle8, Oracle8i
and Oracle

Oracle Tips by Burleson
 

Oracle8, Oracle8i, and Oracle have the following internal roles that are created based on the options you select to load during installation:

    CONNECT

   RESOURCE

   DBA

   DELETE_CATALOG_ROLE

   EXECUTE_CATALOG_ROLE

   SELECT_CATALOG_ROLE

   AQ_USER_ROLE

   AQ_ADMINISTRATION_ROLE

   SNMPAGENT

   RECOVERY_CATALOG_OWNER

   HS_ADMIN_ROLE

The CONNECT, RESOURCE, and DBA roles are predefined roles that are available for backward compatibility. These are generated by Oracle when the database is created. When you create a user with Oracle Enterprise Manager, the CONNECT role is automatically granted to that user. In some releases, the RESOURCE privilege has the hidden grant UNLIMITED TABLESPACE, which allows the user receiving the grant to create objects in any tablespace, including SYSTEM.

Table 9.2 lists all the internal roles and the privileges granted to them.

Table 9.2 Internal Roles and Their Grants

Role

Grants

Admin Option?

AQ_ADMINISTRATOR_ROLE

DEQUEUE ANY QUEUE

YES

 

ENQUEUE ANY QUEUE

YES

 

MANAGE ANY QUEUE

YES

CONNECT

ALTER SESSION

NO

 

CREATE CLUSTER

NO

 

CREATE DATABASE LINK

NO

 

CREATE SEQUENCE

NO

 

CREATE SESSION

NO

 

CREATE SYNONYM

NO

 

CREATE TABLE

NO

 

CREATE VIEW

NO

DBA

ADMINISTER DATABASE TRIGGER

YES

 

ADMINISTER RESOURCE MANAGER

YES

 

ALTER ANY CLUSTER

YES

 

ALTER ANY DIMENSION

YES

 

ALTER ANY INDEX

YES

 

ALTER ANY INDEXTYPE

YES

 

ALTER ANY LIBRARY

YES

 

ALTER ANY OUTLINE

YES

 

ALTER ANY PROCEDURE

YES

 

ALTER ANY ROLE

YES

 

ALTER ANY SEQUENCE

YES

 

ALTER ANY SNAPSHOT

YES

 

ALTER ANY TABLE

YES

 

ALTER ANY TRIGGER

YES

 

ALTER ANY TYPE

YES

 

ALTER DATABASE

YES

 

ALTER PROFILE

YES

 

ALTER RESOURCE COST

YES

 

ALTER ROLLBACK SEGMENT

YES

 

ALTER SESSION 

YES

 

ALTER SYSTEM

YES

 

ALTER TABLESPACE

YES

 

ALTER USER

YES

 

ANALYZE ANY

YES

 

AUDIT ANY

YES

 

AUDIT SYSTEM

YES

 

BACKUP ANY TABLE

YES

 

BECOME USER

YES

 

COMMENT ANY TABLE

YES

 

CREATE ANY CLUSTER

YES

 

CREATE ANY CONTEXT

YES

 

CREATE ANY DIMENSION

YES

 

CREATE ANY DIRECTORY

YES

 

CREATE ANY INDEX

YES

 

CREATE ANY INDEXTYPE

YES

 

CREATE ANY LIBRARY

YES

 

CREATE ANY OPERATOR

YES

 

CREATE ANY OUTLINE

YES

 

CREATE ANY PROCEDURE

YES

 

CREATE ANY SEQUENCE

YES

 

CREATE ANY SNAPSHOT

YES

 

CREATE ANY SYNONYM

YES

 

CREATE ANY TABLE

YES

 

CREATE ANY TRIGGER

YES

 

CREATE ANY TYPE

YES

 

CREATE ANY VIEW

YES

 

CREATE CLUSTER

YES

 

CREATE DATABASE LINK

YES

 

CREATE DIMENSION

YES

 

CREATE INDEXTYPE

YES

 

CREATE LIBRARY

YES

 

CREATE OPERATOR

YES

 

CREATE PROCEDURE

YES

 

CREATE PROFILE

YES

 

CREATE PUBLIC DATABASE LINK

YES

 

CREATE PUBLIC SYNONYM

YES

 

CREATE ROLE

YES

 

CREATE ROLLBACK SEGMENT

YES

 

CREATE SEQUENCE

YES

 

CREATE SESSION

YES

 

CREATE SNAPSHOT

YES

 

CREATE SYNONYM

YES

 

CREATE TABLE

YES

 

CREATE TABLESPACE

YES

 

CREATE TRIGGER

YES

 

CREATE TYPE

YES

 

CREATE USER

YES

 

CREATE VIEW

YES

 

DELETE ANY TABLE

YES

 

DEQUEUE ANY QUEUE

YES

 

DROP ANY CLUSTER

YES

 

DROP ANY CONTEXT

YES

 

DROP ANY DIMENSION

YES

 

DROP ANY DIRECTORY

YES

 

DROP ANY INDEX

YES

 

DROP ANY INDEXTYPE

YES

 

DROP ANY LIBRARY

YES

 

DROP ANY OPERATOR

YES

 

DROP ANY OUTLINE

YES

 

DROP ANY PROCEDURE

YES

 

DROP ANY ROLE

YES

 

DROP ANY SEQUENCE

YES

 

DROP ANY SNAPSHOT

YES

 

DROP ANY SYNONYM

YES

 

DROP ANY TABLE

YES

 

DROP ANY TRIGGER

YES

 

DROP ANY TYPE

YES

 

DROP ANY VIEW

YES

 

DROP PROFILE

YES

 

DROP PUBLIC DATABASE LINK

YES

 

DROP PUBLIC SYNONYM

YES

 

DROP ROLLBACK SEGMENT

YES

 

DROP TABLESPACE

YES

 

DROP USER

YES

 

ENQUEUE ANY QUEUE

YES

 

EXECUTE ANY INDEXTYPE

YES

 

EXECUTE ANY LIBRARY

YES

 

EXECUTE ANY OPERATOR

YES

 

EXECUTE ANY PROCEDURE

YES

 

EXECUTE ANY TYPE

YES

 

FORCE ANY TRANSACTION

YES

 

FORCE TRANSACTION

YES

 

GLOBAL QUERY REWRITE

YES

 

GRANT ANY PRIVILEGE

YES

 

GRANT ANY ROLE

YES

 

INSERT ANY TABLE

YES

 

LOCK ANY TABLE

YES

 

MANAGE ANY QUEUE

YES

 

MANAGE TABLESPACE

YES

 

QUERY REWRITE

YES

 

RESTRICTED SESSION

YES

 

SELECT ANY SEQUENCE

YES

 

SELECT ANY TABLE

YES

 

UPDATE ANY TABLE

YES

 

EXP_FULL_DATABASE

 

 

ADMINISTER RESOURCE MANAGER

NO

 

BACKUP ANY TABLE

NO

 

EXECUTE ANY PROCEDURE

NO

 

EXECUTE ANY TYPE

NO

 

SELECT ANY TABLE

NO

 

 IMP_FULL_DATABASE

 

 

ADMINISTER DATABASE TRIGGER

NO

 

ADMINISTER RESOURCE MANAGER

NO

 

ALTER ANY PROCEDURE

NO

 

ALTER ANY TABLE

NO

 

ALTER ANY TRIGGER

NO

 

ALTER ANY TYPE

NO

 

AUDIT ANY

NO

 

BECOME USER

NO

 

COMMENT ANY TABLE

NO

 

CREATE ANY CLUSTER

NO

 

CREATE ANY CONTEXT

NO

 

CREATE ANY DIMENSION

NO

 

CREATE ANY DIRECTORY

NO

 

CREATE ANY INDEX

NO

 

CREATE ANY INDEXTYPE

NO

 

CREATE ANY LIBRARY

NO

 

CREATE ANY OPERATOR

NO

 

CREATE ANY PROCEDURE

NO

 

CREATE ANY SEQUENCE

NO

 

CREATE ANY SNAPSHOT

NO

 

CREATE ANY SYNONYM

NO

 

CREATE ANY TABLE

NO

 

CREATE ANY TRIGGER

NO

 

CREATE ANY TYPE

NO

 

CREATE ANY VIEW

NO

 

CREATE DATABASE LINK

NO

 

CREATE PROFILE

NO

 

CREATE PUBLIC DATABASE LINK

NO

 

CREATE PUBLIC SYNONYM

NO

 

CREATE ROLE

NO

 

CREATE ROLLBACK SEGMENT

NO

 

CREATE TABLESPACE

NO

 

CREATE USER

NO

 

DROP ANY CLUSTER

NO

 

DROP ANY CONTEXT

NO

 

DROP ANY DIMENSION

NO

 

DROP ANY DIRECTORY

NO

 

DROP ANY INDEX

NO

 

DROP ANY INDEXTYPE

NO

 

DROP ANY LIBRARY

NO

 

DROP ANY OPERATOR

NO

 

DROP ANY OUTLINE

NO

 

DROP ANY PROCEDURE

NO

 

DROP ANY ROLE

NO

 

DROP ANY SEQUENCE

NO

 

DROP ANY SNAPSHOT

NO

 

DROP ANY SYNONYM

NO

 

DROP ANY TABLE

NO

 

DROP ANY TRIGGER

NO

 

DROP ANY TYPE

NO

 

DROP ANY VIEW

NO

 

DROP PROFILE

NO

 

DROP PUBLIC DATABASE LINK

NO

 

DROP PUBLIC SYNONYM

NO

 

DROP ROLLBACK SEGMENT

NO

 

DROP TABLESPACE

NO

 

DROP USER

NO

 

EXECUTE ANY PROCEDURE

NO

 

EXECUTE ANY TYPE

NO

 

GLOBAL QUERY REWRITE

NO

 

INSERT ANY TABLE

NO

 

MANAGE ANY QUEUE

NO

 

SELECT ANY TABLE

NO

OUTLN

EXECUTE ANY PROCEDURE

NO

 

UNLIMITED TABLESPACE

NO

RECOVERY_CATALOG_OWNER

ALTER SESSION

NO

 

CREATE CLUSTER

NO

 

CREATE DATABASE LINK

NO

 

CREATE PROCEDURE

NO

 

CREATE SEQUENCE

NO

 

CREATE SESSION

NO

 

CREATE SYNONYM

NO

 

CREATE TABLE

NO

 

CREATE TRIGGER

NO

 

CREATE VIEW

NO

RESOURCE

CREATE CLUSTER

NO

 

CREATE INDEXTYPE

NO

 

CREATE OPERATOR

NO

 

CREATE PROCEDURE

NO

 

CREATE SEQUENCE

NO

 

CREATE TABLE

NO

 

CREATE TRIGGER

NO

 

CREATE TYPE

NO

You can grant additional privileges to, or revoke privileges from, the internal roles, including the CONNECT, RESOURCE, and DBA roles, just as you can any other role that you create.


See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.
  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter