Oracle Quotas Security Oracle Security Tips by Burleson Consulting

This is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.
 

---

Quotas

When a user has the CREATE TABLE privilege, the user can’t just create the table in any tablespace. He or she must have explicit privileges to create any type of stored object in a tablespace. This privilege is not given in a regular privilege granting statement, rather through a separate ALTER USER command as follows.

ALTER USER JUDY QUOTA 10K ON USER_DATA;

This will allow Judy to create only 10 Kilobytes of stored data, such as tables and materialized views, inside tablespace user_data. Although this command is designed for storage management in Oracle, it offers powerful solutions for security. By limiting how much storage the user can create in a tablespace, the user is not allowed to take over available space in a tablespace and cause failure in other applications. If a user should have unrestricted space, the word 10K should be replaced by UNLIMITED as in:

ALTER USER JUDY QUOTA UNLIMITED ON USER_DATA;

However, if the user has the UNLIMITED TABLESPACE system privilege, he or she can create tables in any tablespace, including the SYSTEM tablespace! Surely, that is not desirable. You should not grant this system privilege to any user. The following script shows the quota of each user in each tablespace.

* find_ts_quota.sql

--**********************************************
--
--   Copyright © 2003 by Rampant TechPress Inc.
--
--   Free for non-commercial use.
--   For commercial licensing, e-mail info@rampant.cc
--
-- *********************************************

select
   username,
   tablespace_name,
   bytes,

   max_bytes
from
   dba_ts_quotas
where
   username not in ('SYS','SYSTEM')
order
   by username,
   tablespace_name
/

A sample output is as follows:

USERNAME   TABLESPACE      BYTES  MAX_BYTES
---------- ---------- ---------- ----------
ANANDA     XDB                 0      12288
HR         EXAMPLE       1638400         -1
JUDY       XDB             65536     102400

The output is only partial and needs some explanation. The first line shows that the user ANANDA has been allowed only 12288 bytes on the tablespace XDB, as indicated by the MAX_BYTES column. Out of that, the user has not used any space, as indicated by the BYTES column. The user HR has UNLIMITED quota on tablespace EXAMPLE as shown by the value of “-1” in the MAX_BYTES column.

* As a rule of thumb, you should never grant any regular user sweeping privileges like SELECT ANY TABLE, CREATE ANY TABLE, etc. If a user needs access to another user's tables, then he or she should be given specific grants on those objects only.

Here is a complete list of system privileges that can be granted.

ADMINISTER DATABASE TRIGGER

ADMINISTER RESOURCE MANAGER

ADMINISTER SECURITY

ALTER ANY CLUSTER

ALTER ANY DIMENSION

ALTER ANY EVALUATION CONTEXT

ALTER ANY INDEX

ALTER ANY INDEXTYPE

ALTER ANY LIBRARY

ALTER ANY OPERATOR

ALTER ANY OUTLINE

ALTER ANY PROCEDURE

ALTER ANY ROLE

ALTER ANY RULE

ALTER ANY RULE SET

ALTER ANY SECURITY PROFILE

ALTER ANY SEQUENCE

ALTER ANY SNAPSHOT

ALTER ANY TABLE

ALTER ANY TRIGGER

ALTER ANY TYPE

ALTER DATABASE

ALTER PROFILE

ALTER RESOURCE COST

ALTER ROLLBACK SEGMENT

ALTER SESSION

ALTER SYSTEM

ALTER TABLESPACE

ALTER USER

ANALYZE ANY

AUDIT ANY

AUDIT SYSTEM

BACKUP ANY TABLE

BECOME USER

COMMENT ANY TABLE

CREATE ANY CLUSTER

CREATE ANY CONTEXT

CREATE ANY DIMENSION

CREATE ANY DIRECTORY

CREATE ANY EVALUATION CONTEXT

CREATE ANY INDEX

CREATE ANY INDEXTYPE

CREATE ANY LIBRARY

CREATE ANY OPERATOR

CREATE ANY OUTLINE

CREATE ANY PROCEDURE

CREATE ANY RULE

CREATE ANY RULE SET

CREATE ANY SECURITY PROFILE

CREATE ANY SEQUENCE

CREATE ANY SNAPSHOT

CREATE ANY SYNONYM

CREATE ANY TABLE

CREATE ANY TRIGGER

CREATE ANY TYPE

CREATE ANY VIEW

CREATE CLUSTER

CREATE DATABASE LINK

CREATE DIMENSION

CREATE EVALUATION CONTEXT

CREATE INDEXTYPE

CREATE LIBRARY

CREATE OPERATOR

CREATE PROCEDURE

CREATE PROFILE

CREATE PUBLIC DATABASE LINK

CREATE PUBLIC SYNONYM

CREATE ROLE

CREATE ROLLBACK SEGMENT

CREATE RULE

CREATE RULE SET

CREATE SECURITY PROFILE

CREATE SEQUENCE

CREATE SESSION

CREATE SNAPSHOT

CREATE SYNONYM

CREATE TABLE

CREATE TABLESPACE

CREATE TRIGGER

CREATE TYPE

CREATE USER

CREATE VIEW

DEBUG ANY PROCEDURE

DEBUG CONNECT ANY

DEBUG CONNECT SESSION

DEBUG CONNECT USER

DELETE ANY TABLE

DEQUEUE ANY QUEUE

DROP ANY CLUSTER

DROP ANY CONTEXT

DROP ANY DIMENSION

DROP ANY DIRECTORY

DROP ANY EVALUATION CONTEXT

DROP ANY INDEX

DROP ANY INDEXTYPE

DROP ANY LIBRARY

DROP ANY OPERATOR

DROP ANY OUTLINE

DROP ANY PROCEDURE

DROP ANY ROLE

DROP ANY RULE

DROP ANY RULE SET

DROP ANY SECURITY PROFILE

DROP ANY SEQUENCE

DROP ANY SNAPSHOT

DROP ANY SYNONYM

DROP ANY TABLE

DROP ANY TRIGGER

DROP ANY TYPE

DROP ANY VIEW

DROP PROFILE

DROP PUBLIC DATABASE LINK

DROP PUBLIC SYNONYM

DROP ROLLBACK SEGMENT

DROP TABLESPACE

DROP USER

ENQUEUE ANY QUEUE

EXECUTE ANY EVALUATION CONTEXT

EXECUTE ANY INDEXTYPE

EXECUTE ANY LIBRARY

EXECUTE ANY OPERATOR

EXECUTE ANY PROCEDURE

EXECUTE ANY RULE

EXECUTE ANY RULE SET

EXECUTE ANY TYPE

EXEMPT ACCESS POLICY

FLASHBACK ANY TABLE

FORCE ANY TRANSACTION

FORCE TRANSACTION

GLOBAL QUERY REWRITE

GRANT ANY OBJECT PRIVILEGE

GRANT ANY PRIVILEGE

GRANT ANY ROLE

INSERT ANY TABLE

LOCK ANY TABLE

MANAGE ANY QUEUE

MANAGE TABLESPACE

ON COMMIT REFRESH

QUERY REWRITE

READUP

READUP DBHIGH

RESTRICTED SESSION

RESUMABLE

SELECT ANY DICTIONARY

SELECT ANY SEQUENCE

SELECT ANY TABLE

SYSDBA

SYSOPER

UNDER ANY TABLE

UNDER ANY TYPE

UNDER ANY VIEW

UNLIMITED TABLESPACE

UPDATE ANY TABLE

WRITEDOWN

WRITEDOWN DBLOW

WRITEUP

WRITEUP DBHIGH

Table 4.7 List of System Privileges

With so many privileges, shouldn't you keep track of who has what privileges? The following query selects that information from the dictionary for the user JUDY.

select
    privilege,
    admin_option
from
    dba_sys_privs
where
    grantee = 'JUDY';

The output will be similar to:

PRIVILEGE                                ADM
---------------------------------------- ---
CREATE VIEW                              NO
CREATE TABLE                             NO
CREATE SESSION                           NO
CREATE SEQUENCE                          NO
SELECT ANY TABLE                         NO

The column ADMIN_OPTION will be discussed later. For now, see how the grants are given. JUDY is a regular claim analyst who uses the application. So why does she need CREATE privileges such as VIEW, TABLE, etc.? Worse, why does she have SELECT ANY TABLE privilege? These questions should be asked and answered. The unnecessary privileges should be immediately revoked.

* Tip: If the application user needs the CREATE TABLE privilege to create temporary tables, as in the case of reporting, etc., suggest using alternative methods, such as PL/SQL tables, instead.

A Note on ALTER SESSION privilege

This particular privilege needs special mention since it defies logic in some cases. Granting this privilege will merely allow the user to do operations that file I/O on the server, for example, setting sql_trace, or setting events. Otherwise, this privilege has no effect. A user without this privilege can still do all of the following:

ALTER SESSION SET NLS_DATE_FORMAT …
ALTER SESSION SET SORT_AREA_SIZE …
ALTER SESSION SET OPTIMIZER_MODE …

As you can see, this is one of the many idiosyncrasies of the Oracle server. Contrary to what you may expect, the ALTER SESSION system privilege does not affect these potentially dangerous commands. It only affects the ability to set sql_trace or events.

This can be partially prevented by the use of Product User Profiles in SQL*Plus, described later in this chapter. However, that will simply prevent only commands entered from SQL*Plus. Any other tool will bypass the security scheme. Moreover, only the ALTER command can be blocked in the product profile table. This means any ALTER, including ALTER TABLE, ALTER VIEW, etc. will be blocked too. This may not be desirable.

There is no workaround for this problem. You are advised to watch out for this specific vulnerability just in case it may resolve some potential or materialized problems in your environment.

* The ALTER SESSION privilege only controls the setting of sql_trace and events; all other session altering commands are always available to the user, regardless of this system privilege.

Identify Users with Sweeping Privileges

Some privileges are so powerful that they can do many things a normal user is not supposed to do, but they are not generally considered harmful and are ignored in a security audit. One example is CREATE ANY TABLE. These privileges should be immediately revoked. Here is a script to find out who may have these sweeping privileges.

* find_sweeping_privs.sql

--**********************************************
--
--   Copyright © 2003 by Rampant TechPress Inc.
--
--   Free for non-commercial use.
--   For commercial licensing, e-mail info@rampant.cc
--
-- *********************************************
select
    grantee,
    privilege,
    admin_option
from
    dba_sys_privs
where
    privilege not in
(
   'ALTER SESSION',
   'QUERY REWRITE',
   'CREATE DIMENSION',
   'CREATE INDEXTYPE',
   'CREATE LIBRARY',
    'CREATE OPERATOR',
   'CREATE PROCEDURE',
   'CREATE SEQUENCE',
   'CREATE SESSION',
   'CREATE SNAPSHOT',
   'CREATE SYNONYM',
   'CREATE TABLE',
   'CREATE TRIGGER',
   'CREATE TYPE',
   'CREATE USER',
   'CREATE VIEW'
)
and
    grantee not in
       ('SYS','SYSTEM','WKSYS','XDB',
        'MDSYS','ORDPLUGINS','ODM')
    /* Place all the user names you want to exclude */
order by
    grantee,
    privilege
/

The output will tell you which of the users have these sweeping privileges, and they should be reviewed.

 

This is an excerpt from the book "Oracle Privacy Security Auditing". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle security and auditing scripts.