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