Category Archives: Security Audit

DOAG SIG Security

1 Reply

Just a couple of hours ago I’ve lecture a presentation about the latest Generation of Database Technology at the DOAG SIG Security in München. It is a sneak preview on a few upcoming security improvements. Unfortunately I do not yet have the permission to provide the presentation for download. But I will make the download link available once the dust settles on the latest Generation of Database Technology…

so stay tuned.

New Oracle Audit Vault and Database Firewall

Leave a reply

In the hustle and bustle of the Christmas season, it went under that Oracle had released a new version of Oracle Audit Vault respectively Oracle Audit Vault and Database Firewall. This weekend I found some time to take a first look into the new release.

What’s New

About a year ago Oracle released the Audit Vault Server 10.3. (see New release of Oracle Audit Vault). During this update Oracle mainly moved internally to a 11.2.0.3 database. The architecture has remained more or less the same. But this has changed now. Oracle is trying to complete its security portfolio. Therefore Oracle has merged the two Oracle Audit Vault and Oracle Database Firewall into the new Oracle Audit Vault and Database Firewall. From the security officer point of view it is definitely more interesting to only have one platform. On the other hand a software appliance is one of the favorites of the DBA and Unix admins. What about, updates, HA, backup & recovery etc? I’ll try to consider these thoughts in a later post on installing and configuring the new Oracle Audit Vault and Database Firewall.

Some short notes on the new features:

  • Oracle Audit Vault and Database Firewall is released as a software appliance-based platform
  • Internally Oracle does use Oracle 11.2.0.3 including Advance Security and Database Vault to enforce Database security and segregation of duties
  • One simple setup does install and configure the operating system, software, database, web frontend etc
  • Audit Vault Agents for:
  • Oracle Database 10g
  • Oracle Database 11g
  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008
  • Sybase Adaptive Server Enterprise (ASE) versions 12.5.4 to 15.0.x
  • IBM DB2 version 9.x (Linux, UNIX, Microsoft Windows)
  • Solaris operating system
  • Oracle ACFS
  • Microsoft Windows Server 2008
  • Microsoft Windows Server 2008 R2
  • Microsoft Active Directory 2008
  • Microsoft Active Directory 2008 R2 on 64 bit

New Architecture

As initially mentioned Audit Vault and Database Firewall are moving closer. Oracle Audit Vault is now also the data storage and analysis platform for the Oracle Database Firewall. Former Database Firewall Management Server is eliminated and thus is replaced with Oracle Audit Vault.

OverviewAVDF

An important note here is that Oracle Audit Vault can not be installed on different platforms as before. It is rather a software appliance like the Oracle Database Firewall. The license for each Oracle Audit Vault and Oracle Database Firewall includes always a license for Oracle Enterprise Linux as well. To install only the appropriate hardware is required. This can be a virtual or a physical host. To setup my test environment, I’ve use as usual virtual servers.

Oracle AVDF Requirements

To install Oracle AVDF the following minimal Hardware Requirements must be met. See as the online installation guide for more details on the installation requirements in particular for the supported secured target products (agents).

  • x86 64-bit Server
  • 2 GB Ram
  • single hard drive 125 GB
  • 1 NIC for Audit Vault Server
  • 1 NIC for Database Firewall Proxy Mode
  • 2 NICs for Database Firewall DAM Mode (monitoring)
  • 3 NICs for Database Firewall DPE Mode (blocking)

In addition to the hardware the following software is required to begin the installation:

  • Oracle Linux Release 5 Update 8 for x86_64 (64 Bit) V31120-01 (3.7GB)
  • Oracle Audit Vault and Database Firewall (12.1.0.0.0) – Server V35715-01 (3.4GB)
  • Oracle Audit Vault and Database Firewall (12.1.0.0.0) – Database Firewall V35716-01 (3.1GB)

The server can not be used for other activities, setup of either Oracle Audit Vault or Oracle Database Firewall will completely reimage the server. But I’ll post more details on the installation later this month.

Resources

Links all around the new Oracle Audit Vault and Database Firewall…

Update: DOAG / SOUG Security-Lounge at Basel

As I announced in my last post DOAG / SOUG Security-Lounge at Basel I’ve been at the Security-Lounge at Basel. The slides can know be downloaded below or from the download section on this website.

 Oracle_Audit_in_a_Nutshell.pdf  Oracle_Database_Security.pdf

I’m happy for any comment on the presentation or the slides. Feel free to add a comment or drop me a line by mail.

DOAG / SOUG Security-Lounge at Basel

I haven’t found time to provide any blog post in the past weeks. Never the less I would like to inform about the upcoming security lounge in Basel at which I’ll give two lectures about Oracle Security. It’s a small even with just one speaker ;-) Ok it was planned to have a second one but it did not work. The event is organized by the DOAG regional group Freiburg and SOUG. It will start at 17:30 on the 24th of April.

Have a look at the DOAG Webpage for a detailed Agenda of the Event and the location. Looking forward to see you there.

I’ll post the slides for both presentations shortly after the event on this page.

New release of Oracle Audit Vault

Somewhen beginning of 2012 Oracle has secretly released an update of Oracle Audit Vault. So far just for Linux x86-64bit but I guess other OS will follow. The new release is available trough OTN or Oracle eDelivery. You’ll have to download around 2.3GB for the Audit Vault Server and an other 620MB for the Audit Vault Collection Agent. According the Oracle Audit Vault documentation this release has the following new features.

  • Starting with this release Oracle use a 11.2.0.3 Database as Audit Vault repository
  • change of console URL respectively port from old http://host:5700/av to new https://host:1158/av
  • Updated MS SQL Server JDBC Driver. MS SQL Server JDBC Driver version 3.0 has to be used to configure Microsoft SQL Server source databases
  • Support for Sybase Adaptive Server Enterprise 15.5 and IBM DB2 9.7 for Linux, UNIX and MS Windows
  • SSL and HTTPS is automatically configured. Due to this a two avca command have been removed (secure_agent,secure_av)

OK the update to 11gR2 was somehow foreseeable. I wonder more why it took that long. Any way, I’ll setup a VM to do a short test installation and check how to new Audit Vault does look like. I’ll post my experience on the installation a bit later.

More details on these new features as well on all changes for 10.2.3.2 and 10.2.3.1 can be found in Oracle® Audit Vault Administrator’s Guide and Oracle Audit Vault Auditor’s Guide on OTN.

Database Audit and Audit trail purging

There are several reason to enable database audit and at least as much reason to not enable database audit. For today I will focus on what can be done after the audit has enabled and how to get rid of the audit data quite easily. It is nothing new nor is it rocket engineering. :-) Ok, removing data is mostly a quite easy task, sometimes to easy…
Rather than deleting or truncating the data, I would like to user the procedures provided by DBMS_AUDIT_MGMT. This post is kind of a sequel to my post earlier this february about Manage Audit Trails with DBMS_AUDIT_MGMT

Enable Audit

Ok, first of all we have to set the corresponding init.ora parameter and enable the database audit. In my case I’ll also enable audit of all sys operation. This is not necessary when you plan to use standard audit. I just did it because I restarted the database anyway. ok lame excuse, I will come back again later on.

Set the init.ora parameter:

ALTER system SET audit_sys_operations=TRUE scope=spfile;

ALTER system SET audit_trail=db_extended scope=spfile;

startup force;

SHOW parameter audit

Initialize the audit infrastructure. In this example I’ve initialized the standard audit and set a default interval of 10 days.

BEGIN
  DBMS_AUDIT_MGMT.INIT_CLEANUP(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    DEFAULT_CLEANUP_INTERVAL => 240 /*hours*/
  );
END;
/

commit;

Create a dedicated tablespace for the audit data and change the location of the audit trail. If this is not done AUD$ will remain in the SYSAUX tablespace.

CREATE tablespace audit_data datafile '/u01/oradata/TDB03/audit01TDB03.dbf'
SIZE 100M autoextend ON NEXT 50M maxsize 5G;

BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_DATA'
  );
END;
/

commit;

Review the audit settings

col PARAMETER_NAME FOR a30
col PARAMETER_VALUE FOR a15
col AUDIT_TRAIL FOR a20
SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL
FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
WHERE audit_trail = 'STANDARD AUDIT TRAIL';

PARAMETER_NAME                 PARAMETER_VALUE AUDIT_TRAIL
------------------------------ --------------- --------------------
DEFAULT CLEAN UP INTERVAL      12              STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            AUDIT_DATA      STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000           STANDARD AUDIT TRAIL

col segment_name FOR a10
SELECT owner,segment_name,tablespace_name FROM dba_segments WHERE segment_name ='AUD$';

OWNER                          SEGMENT_NA TABLESPACE_NAME
------------------------------ ---------- ------------------------------
SYS                            AUD$       AUDIT_DATA

Ok, the audit infrastructure has been setup and configured. Now lets enable audit for certain system privilege and objects. The first part of the following audit statements have been taken from $ORACLE_HOME/rdbms/admin/secconf.sql. This script is used in 11g to enable audit during database creation.

audit ALTER any TABLE BY access;
audit CREATE any TABLE BY access;
audit DROP any TABLE BY access;
audit CREATE any PROCEDURE BY access;
audit DROP any PROCEDURE BY access;
audit ALTER any PROCEDURE BY access;
audit GRANT any privilege BY access;
audit GRANT any object privilege BY access;
audit GRANT any ROLE BY access;
audit audit system BY access;
audit CREATE external job BY access;
audit CREATE any job BY access;
audit CREATE any library BY access;
audit CREATE public DATABASE link BY access;
audit exempt access policy BY access;
audit ALTER USER BY access;
audit CREATE USER BY access;
audit ROLE BY access;
audit CREATE SESSION BY access;
audit DROP USER BY access;
audit ALTER DATABASE BY access;
audit ALTER system BY access;
audit ALTER profile BY access;
audit DROP profile BY access;
audit DATABASE link BY access;
audit system audit BY access;
audit profile BY access;
audit public synonym BY access;
audit system GRANT BY access;

audit DELETE ON sys.aud$;
audit ALTER ON DEFAULT;
audit GRANT ON DEFAULT;

There are a few data dictionary views to review which object and system privilege has audit enabled.

SELECT  * FROM DBA_STMT_AUDIT_OPTS;

USER_NAME  PROXY_NAME AUDIT_OPTION                 SUCCESS    FAILURE
---------- ---------- ---------------------------- ---------- ----------
                      ALTER SYSTEM                 BY ACCESS  BY ACCESS
                      SYSTEM AUDIT                 BY ACCESS  BY ACCESS
                      CREATE SESSION               BY ACCESS  BY ACCESS
                      CREATE USER                  BY ACCESS  BY ACCESS
                      ALTER USER                   BY ACCESS  BY ACCESS
                      DROP USER                    BY ACCESS  BY ACCESS
                      PUBLIC SYNONYM               BY ACCESS  BY ACCESS
                      DATABASE LINK                BY ACCESS  BY ACCESS
                      ROLE                         BY ACCESS  BY ACCESS
                      PROFILE                      BY ACCESS  BY ACCESS
                      CREATE ANY TABLE             BY ACCESS  BY ACCESS
                      ALTER ANY TABLE              BY ACCESS  BY ACCESS
                      DROP ANY TABLE               BY ACCESS  BY ACCESS
                      GRANT TABLE                  BY ACCESS  BY ACCESS
                      CREATE PUBLIC DATABASE LINK  BY ACCESS  BY ACCESS
                      GRANT ANY ROLE               BY ACCESS  BY ACCESS
                      SYSTEM GRANT                 BY ACCESS  BY ACCESS
                      ALTER DATABASE               BY ACCESS  BY ACCESS
                      CREATE ANY PROCEDURE         BY ACCESS  BY ACCESS
                      ALTER ANY PROCEDURE          BY ACCESS  BY ACCESS
                      DROP ANY PROCEDURE           BY ACCESS  BY ACCESS
                      ALTER PROFILE                BY ACCESS  BY ACCESS
                      DROP PROFILE                 BY ACCESS  BY ACCESS
                      GRANT ANY PRIVILEGE          BY ACCESS  BY ACCESS
                      CREATE ANY LIBRARY           BY ACCESS  BY ACCESS
                      EXEMPT ACCESS POLICY         BY ACCESS  BY ACCESS
                      GRANT ANY OBJECT PRIVILEGE   BY ACCESS  BY ACCESS
                      CREATE ANY JOB               BY ACCESS  BY ACCESS
                      CREATE EXTERNAL JOB          BY ACCESS  BY ACCESS

SELECT * FROM DBA_PRIV_AUDIT_OPTS;

USER_NAME  PROXY_NAME PRIVILEGE                    SUCCESS    FAILURE
---------- ---------- ---------------------------- ---------- ----------
                      CREATE EXTERNAL JOB          BY ACCESS  BY ACCESS
                      CREATE ANY JOB               BY ACCESS  BY ACCESS
                      GRANT ANY OBJECT PRIVILEGE   BY ACCESS  BY ACCESS
                      EXEMPT ACCESS POLICY         BY ACCESS  BY ACCESS
                      CREATE ANY LIBRARY           BY ACCESS  BY ACCESS
                      GRANT ANY PRIVILEGE          BY ACCESS  BY ACCESS
                      DROP PROFILE                 BY ACCESS  BY ACCESS
                      ALTER PROFILE                BY ACCESS  BY ACCESS
                      DROP ANY PROCEDURE           BY ACCESS  BY ACCESS
                      ALTER ANY PROCEDURE          BY ACCESS  BY ACCESS
                      CREATE ANY PROCEDURE         BY ACCESS  BY ACCESS
                      ALTER DATABASE               BY ACCESS  BY ACCESS
                      GRANT ANY ROLE               BY ACCESS  BY ACCESS
                      CREATE PUBLIC DATABASE LINK  BY ACCESS  BY ACCESS
                      DROP ANY TABLE               BY ACCESS  BY ACCESS
                      ALTER ANY TABLE              BY ACCESS  BY ACCESS
                      CREATE ANY TABLE             BY ACCESS  BY ACCESS
                      DROP USER                    BY ACCESS  BY ACCESS
                      ALTER USER                   BY ACCESS  BY ACCESS
                      CREATE USER                  BY ACCESS  BY ACCESS
                      CREATE SESSION               BY ACCESS  BY ACCESS
                      AUDIT SYSTEM                 BY ACCESS  BY ACCESS
                      ALTER SYSTEM                 BY ACCESS  BY ACCESS

SELECT * FROM DBA_OBJ_AUDIT_OPTS;

OWNER OBJECT_NAME          OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
----- -------------------- ----------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
SYS   AUD$                 TABLE       -/- -/- -/- S/S -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-
SYS   VERIFY_FUNCTION      PROCEDURE   -/- -/- -/- -/- S/S -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-
SYS   VERIFY_FUNCTION_11G  PROCEDURE   -/- -/- -/- -/- S/S -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-

SELECT * FROM ALL_DEF_AUDIT_OPTS;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA
--- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
S/S -/- -/- -/- S/S -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-

Audit trail housekeeping

So far we’ve spend quite some time on setting up the audit. Let’s come to the actual part of the post, the housekeeping. The plan is to have regular database job, which purge the audit data as required. For this I defined the following steps:

  • Create a report of your audit trail (optional)
  • Export your audit trail information for longterm archiving (optional)
  • Set the archive timestamp as soon as audit data has been archived
  • Purge archived audit data

I the current example I’ll skip the first to steps.

Create daily archive timestamp job. This job will be a regular dbms_scheduler job.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name   => 'DAILY_AUDIT_ARCHIVE_TIMESTAMP',
    job_type   => 'PLSQL_BLOCK',
    job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE =>
                   DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,LAST_ARCHIVE_TIME => sysdate-10); END;',
    start_date => sysdate,
    repeat_interval => 'FREQ=HOURLY;INTERVAL=24',
    enabled    =>  TRUE,
    comments   => 'Create an archive timestamp'
  );
END;
/

Create daily purge job

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    AUDIT_TRAIL_TYPE           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
    AUDIT_TRAIL_PURGE_NAME     => 'Daily_Audit_Purge_Job',
    USE_LAST_ARCH_TIMESTAMP    => TRUE
  );
END;
/

commit;

The defined AUDIT JOB’s are then visible in DBA_AUDIT_MGMT_CLEANUP_JOBS.

col JOB_NAME FOR a30
col JOB_FREQUENCY FOR a40
SELECT JOB_NAME,JOB_STATUS,AUDIT_TRAIL,JOB_FREQUENCY FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;

JOB_NAME               JOB_STAT AUDIT_TRAIL          JOB_FREQUENCY
---------------------- -------- -------------------- ------------------------
DAILY_AUDIT_PURGE_JOB  ENABLED  STANDARD AUDIT TRAIL FREQ=HOURLY;INTERVAL=24

As we can see in dba_scheduler_jobs, DBMS_SCHEDULER.CREATE_JOB is just creating a other scheduler job.

col next_run_date FOR a50
SELECT job_name, next_run_date, state, enabled FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%';

JOB_NAME                       NEXT_RUN_DATE                                      STATE           ENABL
------------------------------ -------------------------------------------------- --------------- -----
DAILY_AUDIT_ARCHIVE_TIMESTAMP  31-MAY-11 01.46.58.000000 PM +02:00                SCHEDULED       TRUE
DAILY_AUDIT_PURGE_JOB          31-MAY-11 01.49.34.900000 PM EUROPE/VIENNA         SCHEDULED       TRUE

Conclusion

For new Oracle 11g database auditing is enabled by default. Unfortunately Oracle does not setup a housekeeping. To make sure, that you do not end up with a full AUDIT_TRAIL you have to either switch off auditing or define some housekeeping jobs. With DBMS_AUDIT_MGMT this is a pretty easy job. It is that easy, that it is worth to think about having some kind of rolling audit window. Define a short retention time to save disk space, but long enough to have access to the audit information of the last hours or days. You may then be able to answer questions like “Did anybody change something?”, “I can not access XY but I could yesterday…”. When enabling audit for sys operation you will also be able to review what user SYS did all day long. Depending on the amount of audit data it will be quite a task to create nice audit reports.

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