Using Predefined Roles

The roles in this section are defined automatically when the database is created. Table 18.1 contains the predefined roles and their descriptions.

Table 18.1. Oracle 9i Provided Roles and Descriptions

Role	Description
CONNECT	Includes CREATE SESSION, ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE TABLE, CREATE SYNONYM, and CREATE VIEW.
RESOURCE	Includes CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, and UNLIMITED TABLESPACE.
DBA	Includes all the system privileges WITH ADMIN OPTION as well as UNLIMITED TABLESPACE.
EXP_FULL_DATABASE	Provides all the privileges required to perform full database exports and incremental database exports. Includes SELECT ANY TABLE, BACKUP ANY TABLE, EXECUTE ANY PROCEDURE, EXECUTE ANY TYPE, and ADMINISTER RESOURCE MANAGER system privileges. Includes INSERT, DELETE, and UPDATE on the tables SYS.INCVID, SYS.INCFIL, and SYS.INCEXP object privileges. Includes the following roles: EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE.
IMP_FULL_DATABASE	Provides the privileges necessary to perform full database imports. There is an extensive list of system privileges and the EXECUTE_CATALOG_ROLE and the SELECT_CATALOG_ROLE.
DELETE_CATALOG_ROLE	Allows user to DELETE from the SYS.AUD$ table.
EXECUTE_CATALOG_ROLE	Allows user to execute objects in the data dictionary.
SELECT_CATALOG_ROLE	Allows user to select objects in the data dictionary.
RECOVER_CATALOG_OWNER	Provides privileges for the owner of the recovery catalog (used by RMAN). Includes CREATE SESSION, ALTER SESSION, CREATE SYNONYM, CREATE VIEW, CREATE DATABASE LINK, CREATE TABLE, CREATE CLUSTER, CREATE SEQUENCE, CREATE TRIGGER, and CREATE PROCEDURE.
HS_ADMIN_ROLE	Used to protect access to the Heterogeneous Services (Heterogeneous Services is necessary for accessing a non-Oracle database system, typically in a distributed database) data dictionary tables (grants SELECT) and packages (grants EXECUTE). This role is granted to the SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE so that users who have generic data dictionary access also can access the Heterogeneous Services data dictionary.
AQ_USER_ROLE	This role has been deprecated but kept mainly for Oracle 8i compatibility. It provides execute privilege on the DBMS_AQ and DBMS_AQIN packages.
AQ_ADMINISTRATOR_ROLE	This role provides privileges necessary to administer Advance Queuing. Includes ENQUEUE ANY QUEUE, DEQUEUE ANY QUEUE, and MANAGE ANY QUEUE system privileges; SELECT privileges on AQ tables; and EXECUTE privileges on AQ packages object privileges.
SNMPAGENT	Primarily, Oracle Enterprise Manager and the Intelligent Agent use this role. Includes ANALYZE ANY and SELECT on several different views.

CONNECT, RESOURCE, and DBA roles are created by the database for backward compatibility.

Enabling and Disabling Roles

You can disable a role to revoke that role temporarily from a user. You can enable a role to grant it temporarily to a user.

You use the SET ROLE command to enable or disable roles and provide a password for those that require passwords to be enabled. Default roles are enabled at user login, but often there is a point where it becomes necessary for a user to take on another role after logging in.

For a role to be enabled or disabled, the user must first have been granted the role.

ALTER USER...DEFALUT ROLE can be used to indicate which roles will be enabled for a given user at login, and all others will be disabled.

Whenever a role is enabled, the user to whom the role is enabled can use the privileges that have been granted to the role. If a role is disabled, the user cannot use the privileges associated with the role until those privileges have been granted directly to the user outside the disabled role. Enabling, or disabling, a role lasts only for the duration of the current session. At the next session's login, the user's active role reverts to his default role.

The SET ROLE command can be used to enable or disable roles, as can the DBMS_SESSION.SET_ROLE procedure, and enabling can be done from any program or tool that allows PL/SQL commands (except from a stored procedure). Using these commands, you would enable all roles included in the statement and disable all not included. If a password is required to enable a role, that password must be included in the SET ROLE command to enable the role; a default role enabled at login does not require a password.

A role cannot be enabled from a stored procedure. This is true because the action could change the security domain and its set of privileges that allowed the procedure to be called in the first place. For this reason, roles need to be enabled and disabled in an anonymous block or in application procedures rather than within PL/SQL procedures.

If a stored procedure contains the SET ROLE command, an error is generated.

An example of enabling and disabling roles follows. The first command enables the developer role; the second command has the effect of disabling the developer role:

 SET ROLE developer; SET ROLE ALL EXCEPT developer;