Tag Archives: Security Audit

Find User with unlimited Tablespace Quota

When performing a database security audit various informations about users, roles and privileges have to be collected, including “who has unlimited tablespace on SYSTEM”. It is quite easy to find user with UNLIMITED TABLESPACE or a UNLIMITED quota on SYSTEM. But what when the system privilege is assigned to a role or over several roles? It is still easy if you use hierarchical queries to drill down the cascaded roles, but there is plenty to write.

Ok, why do we want to know who has unlimited quota on the SYSTEM tablespace?

User who have unlimited quota on SYSTEM as well the privilege to create tables or clusters could do some kind of denial of service attack to the database. The just have to fill up the free space in the SYSTEM tablespace. If there default tablespace is as well SYSTEM the could even do this without intention.

Then let’s find them

First find the user with direct quota on tablespace SYSTEM.

SELECT username,tablespace_name, bytes, max_bytes 
FROM dba_ts_quotas 
WHERE max_bytes = -1 AND tablespace_name ='SYSTEM';

USERNAME                  TABLESPACE_NAME                BYTES  MAX_BYTES
------------------------- ------------------------- ---------- ----------
SCOTT                     SYSTEM                             0         -1
TEST                      SYSTEM                             0         -1

As mentioned above it is quite easy to find user with system privilege UNLIMITED TABLESPACE.

select * from dba_sys_privs where privilege = 'UNLIMITED TABLESPACE'

GRANTEE                        PRIVILEGE                      ADM
------------------------------ ------------------------------ ---
WMSYS                          UNLIMITED TABLESPACE           NO
RRDOMREG                       UNLIMITED TABLESPACE           NO
HR                             UNLIMITED TABLESPACE           NO
OE                             UNLIMITED TABLESPACE           NO
SYS                            UNLIMITED TABLESPACE           NO
LOGSTDBY_ADMINISTRATOR         UNLIMITED TABLESPACE           NO
SCOTT                          UNLIMITED TABLESPACE           NO
BI                             UNLIMITED TABLESPACE           NO
OUTLN                          UNLIMITED TABLESPACE           NO
DBSNMP                         UNLIMITED TABLESPACE           NO
IX                             UNLIMITED TABLESPACE           NO
SH                             UNLIMITED TABLESPACE           NO
DBA                            UNLIMITED TABLESPACE           YES
SYSTEM                         UNLIMITED TABLESPACE           YES

14 rows selected.

What about cascaded roles?

Mmh, but since Oracle 11g it is possible to grant UNLIMITED TABLESPACE to a role and this can be granted to an other role which is granted again to an other role. It could be a role concept or somebody who want to hide a little bit some privileges. To test it I’ve created three roles DBA3, DBA2 and DBA1, granted UNLIMITED TABLESPACE to DBA3.

SELECT 
  grantee, 
  privilege, 
  DECODE(p,'=>'||grantee,'direct',p) path
FROM (
  SELECT 
    grantee, 
    privilege,
    SYS_CONNECT_BY_PATH(grantee, '=>') p
  FROM (
    SELECT 
      grantee, 
      privilege
    FROM dba_sys_privs
    UNION ALL
    SELECT 
      grantee, 
      granted_role privilege
    FROM 
      dba_role_privs)
  START WITH privilege = 'UNLIMITED TABLESPACE'
  CONNECT BY PRIOR grantee = privilege )
WHERE 
  (grantee in (SELECT username FROM dba_users)
  OR grantee = 'PUBLIC');

GRANTEE   PRIVILEGE               PATH
--------- ----------------------- -------------------------------
BI        UNLIMITED TABLESPACE    direct
SYS       DBA                     =>DBA=>SYS
SYSTEM    DBA                     =>DBA=>SYSTEM
SCOTT     DBA1                    =>DBA3=>DBA2=>DBA1=>SCOTT
SYS       DBA1                    =>DBA3=>DBA2=>DBA1=>SYS
SYS       DBA2                    =>DBA3=>DBA2=>SYS
SYS       DBA3                    =>DBA3=>SYS
DBSNMP    UNLIMITED TABLESPACE    direct
HR        UNLIMITED TABLESPACE    direct
IX        UNLIMITED TABLESPACE    direct
SYS       LOGSTDBY_ADMINISTRATOR  =>LOGSTDBY_ADMINISTRATOR=>SYS
OE        UNLIMITED TABLESPACE    direct
OUTLN     UNLIMITED TABLESPACE    direct
RRDOMREG  UNLIMITED TABLESPACE    direct
SH        UNLIMITED TABLESPACE    direct
SYS       UNLIMITED TABLESPACE    direct
SYSTEM    UNLIMITED TABLESPACE    direct
WMSYS     UNLIMITED TABLESPACE    direct

18 rows selected.

We now see that SCOTT has UNLIMITED TABLESPACE through DBA3, DBA2 and DBA1

All wrapped up

Ok, create one to find user’s with direct quotas as well through a system privilege will give something like this.

SELECT 
  username,
  tablespace_name,
  privilege
FROM (
  SELECT 
    grantee username, 'Any Tablespace' tablespace_name, privilege
  FROM (
    -- first get the users with direct grants
    SELECT 
      p1.grantee grantee, privilege
    FROM 
      dba_sys_privs p1
    WHERE 
      p1.privilege='UNLIMITED TABLESPACE'
    UNION ALL
    -- and then the ones with UNLIMITED TABLESPACE through a role...
    SELECT 
      r3.grantee, granted_role privilege
    FROM 
      dba_role_privs r3
      START WITH r3.granted_role IN (
          SELECT 
            DISTINCT p4.grantee 
          FROM 
            dba_role_privs r4, dba_sys_privs p4 
          WHERE 
            r4.granted_role=p4.grantee 
            AND p4.privilege = 'UNLIMITED TABLESPACE')
    CONNECT BY PRIOR grantee = granted_role)
    -- we just whant to see the users not the roles
  WHERE grantee IN (SELECT username FROM dba_users) OR grantee = 'PUBLIC'
  UNION ALL 
  -- list the user with unimited quota on a dedicated tablespace
  SELECT 
    username,tablespace_name,'DBA_TS_QUOTA' privilege 
  FROM 
    dba_ts_quotas 
  WHERE 
    max_bytes = -1 )
WHERE tablespace_name LIKE UPPER('SYSTEM') 
    OR tablespace_name = 'Any Tablespace';


USERNAME                  TABLESPACE_NAME           PRIVILEGE
------------------------- ------------------------- ------------------------------
...
SYSTEM                    Any Tablespace            UNLIMITED TABLESPACE
SYS                       Any Tablespace            DBA
SYSTEM                    Any Tablespace            DBA
SCOTT                     Any Tablespace            DBA1
SYS                       Any Tablespace            DBA1
SYS                       Any Tablespace            DBA2
SYS                       Any Tablespace            DBA3
SYS                       Any Tablespace            LOGSTDBY_ADMINISTRATOR
TEST                      SYSTEM                    DBA_TS_QUOTA

19 rows selected.

Due to the fact that the query is far to long to write more than one time, I’ve put everything in one script.

@tsq SYSTEM

User Name     Tablespace Name        Privilege
------------- ---------------------- -------------------------
BI            Any Tablespace         UNLIMITED TABLESPACE
DBSNMP        Any Tablespace         UNLIMITED TABLESPACE
HR            Any Tablespace         UNLIMITED TABLESPACE
IX            Any Tablespace         UNLIMITED TABLESPACE
OE            Any Tablespace         UNLIMITED TABLESPACE
OUTLN         Any Tablespace         UNLIMITED TABLESPACE
....

The tsq.sql script can be downloaded at the script section of OraDBA or direct ( tsq.sql). All OraDBA scripts can also be downloaded as a complete archive ( oradba.tgz).