Oracle Security

The RESOURCE Role

The RESOURCE role grants a user the privileges necessary to create procedures, triggers and, in Oracle8, types within the user’s own schema area. Granting a user RESOURCE without CONNECT, while possible, does not allow the user to log in to the database. Therefore, if you really must grant a user RESOURCE, you have to grant CONNECT also — or, at least, CREATE SESSION — so the user can log in.

System Privileges for the RESOURCE Role

The system privileges for the RESOURCE role are shown in Table 5.2.

Table 5-2. RESOURCE Role System Privileges

Privilege
CREATE CLUSTER
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE (new in Oracle8)

Problems with the RESOURCE Role

There are several potential problems with the use of the RESOURCE role.

The Oracle-supplied roles can be moving targets

As we mentioned earlier in the section “About the Defaults,” the system privileges of an Oracle-supplied role may change with a new version or upgrade release. For example, the privileges listed in Table 5.2 are from an Oracle8 RESOURCE role. Note that in an Oracle7 database, the CREATE TYPE privilege does not exist. There is another problem that has as much or more impact on your database security, which we examine next.

UNLIMITED TABLESPACE access

Another issue with the RESOURCE role is that the UNLIMITED TABLESPACE system privilege is explicitly granted. This privilege gives the user unlimited quotas on any tablespace in the database. Even if an explicit quota ...