Remote DBA Plans
Remote DBA Service

Internal Roles in Oracle8, Oracle8i
and Oracle9i

Oracle Tips by Burleson Consulting

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

   CONNECT
   RESOURCE
   Remote 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 Remote 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.

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
Remote 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
HS_ADMIN_ROLE	(Only table grants)	N/A
SNMPAGENT	ANALYZE ANY	NO

You can grant additional privileges to, or revoke privileges from, the internal roles, including the CONNECT, RESOURCE, and Remote 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.