The new Oracle RAC installation will include a consolidation most of the Oracle database instances in a single instance using different tablespaces. For this reason all privileges will be restricted. There are currently foreseen 3 types of roles (reader, writer and developer) and 1 type of profile to limit resource usage.

Default privileges for a database developer ("admin") on Oracle rac installation (rac01-rac04):

CREATE CLUSTER
CREATE DIMENSION
QUERY REWRITE
CREATE INDEXTYPE
CREATE LIBRARY
CREATE MATERIALIZED VIEW
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
FORCE TRANSACTION
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
CREATE DATABASE LINK

Default privilege categories for a database deveoper on lbora01:

• Privileges for a database developer:

 DROP ANY RULE SET 
 DROP ANY SEQUENCE 
 DROP ANY TABLE 
 DROP ANY TRIGGER 
 DROP ANY TYPE 
 DROP ANY VIEW 
 DROP PROFILE 
 DROP ROLLBACK SEGMENT 
 DROP TABLESPACE 
 EXECUTE ANY INDEXTYPE 
 EXECUTE ANY OPERATOR 
 EXECUTE ANY PROCEDURE 
 EXECUTE ANY RULE 
 EXECUTE ANY RULE SET 
 EXECUTE ANY TYPE 
 EXPORT FULL DATABASE 
 FLASHBACK ANY TABLE 
 FORCE ANY TRANSACTION 
 FORCE TRANSACTION 
 IMPORT FULL DATABASE 
 INSERT ANY TABLE 
 LOCK ANY TABLE 
 MANAGE TABLESPACE 
 MERGE ANY VIEW 
 ON COMMIT REFRESH 
 QUERY REWRITE 
 READ ANY FILE GROUP 
 RESTRICTED SESSION 
 RESUMABLE 
 "CONNECT"
 "EXP_FULL_DATABASE"
 "IMP_FULL_DATABASE" 
 SELECT ANY TABLE 
 SELECT ANY TRANSACTION 
 UNDER ANY TABLE 
 UNDER ANY TYPE 
 UNDER ANY VIEW 
 UPDATE ANY TABLE 

Default profile:

• Developer profile:

CREATE PROFILE "DEV_PROFILE" LIMIT CPU_PER_SESSION DEFAULT
CPU_PER_CALL 36000
CONNECT_TIME 2880  -- minutes
IDLE_TIME 2880  -- minutes
SESSIONS_PER_USER 10
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 10000
PRIVATE_SGA DEFAULT
COMPOSITE_LIMIT DEFAULT
PASSWORD_LIFE_TIME 365 -- days
PASSWORD_GRACE_TIME DEFAULT
PASSWORD_REUSE_MAX DEFAULT
PASSWORD_REUSE_TIME DEFAULT
PASSWORD_LOCK_TIME DEFAULT
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_VERIFY_FUNCTION DEFAULT

Change a user profile:

alter <username> profile <profile_name>

-- RaduStoica - 04 Apr 2007