Category Archives: 10gR2

Posts related to Oracle 10g Release 2

Oracle released CPU / PSU October 2013

As announced yesterday in my post Oracle CPU / PSU Pre-Release Announcement October 2013, Oracle has now released the last Critical Patch Updates for 2013. Overall this CPU contains 126 new security fixes across several Oracle products like Database Server, MySQL Server, Sun Product Suite, WebLogic Server etc. For Oracle Database it contains only 2 security fixes with a rather medium CVSS rating. Although the Core RDBMS is affected, it is probably not necessary to run a fire drill. If you have planned to patch anyway, it makes sense to consider the latest PSU or SRU. And if you plan to install Oracle 11.2.0.4.0 patch set, this critical patch update can even be skip, since there is no PSU or SPU for 11.2.0.4 available. According to the patch read-me, it seems that CVE-2013-5771 is fixed in 11.2.0.4. But I can’t confirm this, because I could not find a Bug-ID to compare.

By the way, Oracle has changed a few thing in database security patching for 12c. They will not publish any separate security patch updates (SPU) anymore but solely patch set update (PSU)

CPU Release Dates

The next four Critical Patch Updates will be released at the following dates:

  • 14 January 2014
  • 15 April 2014
  • 15 July 2014
  • 14 October 2014

References

Links all around Critical Patch Update:

Oracle database binaries with perl

Perl and Oracle has not always an easy past. Depending on the OS type and Oracle Version it can be quite nerve racking to compile DBI and DBD::Oracle. In addition to DBD::Oracle there are also other binary Perl modules that are not so easy to compile. On operating systems such as Microsoft Windows it is necessary to invest a little more effort to compile Perl. Alternatively one can use precompiled packages like Active Perl or Strawberry Perl. But this is basically not necessary at all if Oracle is already installed. Since Oracle 10g Perl is part of the Oracle binaries for the client and Database server. Oracle does use it for various tools itself. This allows it to easily create and execute custom perl scripts even on an Oracle Client installation. I do this regularly when I create Oracle Database security reviews. Instead of manually collecting all sorts of information, I’m running a few Perl scripts. This also works if I only have access to an Oracle client installation.

Available Perl Versions

Consequently, the different Oracle versions contains different versions of Perl. With the latest Oracle Database 12c Release 1 it just got update.

  • Oracle 10g Release 2 contains Perl 5.8.3
  • Oracle 10g Release 2 contains Perl 5.10.0
  • Oracle 12c Release 1 contains Perl 5.14.1

As you see this are not realy the latest stable version of Perl. The following Picture show’s the latest release for each branch of Perl.

LatestPerlReleases

Depending on what you want to do with Perl, this is generally not a problem. Nevertheless, it is useful to check what is supported in the corresponding release or not.

With perldoc you’ll get all kind of perl documentation. For instance the user contributed perl modules aka additional perl modules

$ORACLE_HOME/perl/bin/perldoc perllocal

With corelist you’ll get information on core perl modules perl.

$ORACLE_HOME/perl/bin/corelist -a utf8

utf8 was first released with perl 5.006
5.006 undef
5.006001 undef
5.006002 undef
5.007003 1.00
5.008 1.00
5.008001 1.02
5.008002 1.02
5.008003 1.02
5.008004 1.03
5.008005 1.04
5.008006 1.04
5.008007 1.05
5.008008 1.06
5.009 1.02
5.009001 1.02
5.009002 1.04
5.009003 1.06
5.009004 1.06
5.009005 1.07
5.01 1.07

Restrictions

But before you start to develop your big perl applications be aware, that you shouldn’t relay on it. According to the Oracle Metalink Note 342754.1 You should not use it for your own applications.

Note:- Perl and other 3rd party tools such as the Sun JRE are provided in the ORACLE_HOME for Oracle tool usage only. PERL libraries which are part of the Oracle RDBMS CD (Client / Database) are not meant for PERL custom application development, but they are used by various Oracle tools that are shipped along with Oracle RDBMS software such as EM DB Console etc.,

Using it for just a bunch of admin and reports scripts it shouldn’t be a big issues. Especially because you save quite some time when you not have to install Perl and DBD::Oracle yourself.

How tu use it

A few example how to use it will follow later on…

References

Some links related to this post.

  • Perl Source Readme on CPAN with information on the latest version on each branch of Perl
  • DBI – Database independent interface for Perl
  • DBD::Oracle Oracle database driver for the DBI module
  • Oracle Support of PHP, Perl, DBD/DBI and other 3rd party products [342754.1]
  • Active Perl from ActiveState
  • Strawberry Perl

Oracle released CPU / PSU July 2013

About a week ago Oracle has released the July Critical Patch Updates. Overall this CPU contains 89 new security fixes across several Oracle products like Database Server, MySQL Server, Sun Product Suite, WebLogic Server etc. For Oracle Database Server it does contain 6 fixes, but none of them is for client-only installation. 1 of these vulnerabilities may be remotely exploitable without authentication. According the Database risk matrix all supported versions are affected. Since the critical patch update does mainly fix vulnerabilities in the core RDBMS and Oracle executables, it is worth to have a closer look. I’ll test the critical patch update on my test systems as usual. But I do not expect problems, since MOS Note 1546428.1 does not yet list any known issues. Be aware that Critical Patch Update (CPU) are usually cumulative and do contain previous security fixes. If you do not regularly apply Critical Patch Updates, it is essential to check previous patch notes.

First Testing

The Critical Patch Update could easily be installed on Linux x86-64bit, but opatch does fail with a few warnings. None of them prevents a successful installation. According to the Known Issues section in the Patch ReadMe and the two Metalink Notes 1448337.1 and 854711.1 the output can be safely ignored.

First Findings

After installing the patch and run catbundle I could identify a few changes on the hidden parameters. The following hidden parameters have been updated on my test system

SQL> @hip _db_flash_cache_keep_limit

Parameter                  SESSION Instance   S I D Description
-------------------------- ------- ---------- - - - --------------------------------------------------
_db_flash_cache_keep_limit         217751120        Flash cache keep buffer UPPER LIMIT IN percentage

SQL> @hip _fastpin_enable

Parameter                 SESSION Instance   S I D Description
------------------------- ------- ---------- - - - --------------------------------------------------
_fastpin_enable                   217827585        enable reference COUNT based fast pins

The following hidden parameter has been removed

SQL> @hip _db_flash_cache_keep_limit

Parameter                   SESSION Instance   S I D Description
--------------------------- ------- ---------- - - - --------------------------------------------------
_thirteenth_spare_parameter                          thirteenth spare parameter - string

But the strange thing is, the following new hidden parameters.

SQL> @hip _july2013_cpu_admin_user_fix

Parameter                    SESSION Instance   S I D Description
---------------------------- ------- ---------- - - - --------------------------------------------------
_july2013_cpu_admin_user_fix                          july2013 cpu admin USER fix

So far I could not figure out the purpose of _july2013_cpu_admin_user_fix parameter. It look’s somehow like a temporary fix for something. I assume it will disappear in the next Critical Patch Update on october 2014.

CPU Release Dates

The next four Critical Patch Updates will be released at the following dates:

  • 15 October 2013
  • 14 January 2014
  • 15 April 2014
  • 15 July 2014

References

Links all around Critical Patch Update:

  • Oracle Critical Patch Update Advisory – July 2013
  • Patch Set Update and Critical Patch Update July 2013 Availability Document [1548709.1]
  • Critical Patch Update July 2013 Database Known Issues [1546428.1]
  • Opatch warning: overriding commands for target xxxx [1448337.1]
  • Oracle Critical Patch Update July 2013 Documentation Map [1563067.1]
  • Use of Common Vulnerability Scoring System (CVSS) by Oracle [394487.1]
  • Risk Matrix Glossary — terms and definitions for Critical Patch Update risk matrices [394486.1]
  • Oracle Critical Patch Updates and Security Alerts on OTN including links to Critical Patch Update since january 2005

Oracle CPU / PSU Pre-Release Announcement October 2012

Today Oracle has published the Pre-Release Announcement for the october CPU Patch. This Critical Patch Update contains 109 new security vulnerability fixes for several Oracle products. 5 of these fixes are just for the Oracle Database Server including 2 fixes for client-only installations. What frighten me a bit, is the CVSS Base Score of 10 for the core RDBMS. Oracle apparently has to close another big security issue. The core RDBMS is by the way the only component which has to be patched by this CPU. In combination with this severity everybody will have to patch. SCN flaw, TNS poisoning, Oracle Password Hashing Algorithm Weaknesses, etc obviously it’s the oracle-year of critical issues. Any way we will see it next week in detailed. As mentioned just the following Database Server Products are affected.

  • Core RDBMS

So far the Database Server Patch’s are planned for Oracle Database 11g Release 2 (11.2.0.2,11.2.0.3), Oracle Database 11g Release 1 (11.2.0.7) and Oracle Database 10g Release 2 (10.2.0.3, 10.2.0.4, 10.2.0.5).

The official release for the CPU / PSU is planned for next week 16 October 2012. More details about the patch will follow soon on the Oracle Security Pages.

Oracle hidden init.ora parameter

This post focuses on init.ora parameters. It is not really new topic, but rather a personal reference to some practical queries and scripts. If you are the customer, it’s always handy when you can easily access your own queries.

It is quite simple to get some information on init.ora parameters from SQLPlus. Using a tool like TOAD or SQL Developer make it even easier. Unfortunately I work often at the customer without my own tools and scripts. So commandline and SQLPlus is the only “tools” available to work on the database. It is not an issue to dig through the data dictionary to get any kind of information as long as there is 1-2 view involved. But for querying multiple View’s, X$ views etc it is easier to have something on hand.

OK, what’s different with my queries? Not much, they just fit my needs 🙂 Instead of just querying v$parameter I’ll query as well the X$ views to see as well the hidden parameter and simple description for each parameter.

The first query does a select on X$KSPPI, X$KSPPCV, X$KSPPSV and V$PARAMETER to display all init.ora parameter including the hidden parameters. The result can be limited by adding a part of the parameter name or specify % to see all which then would be a little over 2500 parameters. S stands for it is session modifiable, I stands for it is system modifiable and D show if the parameter does still have the default value or not. I’ve added the query as hip.sql (stands somehow for hidden init parameter) to my small script collection which can be downloaded in the script section.

SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90

SELECT  
  a.ksppinm  "Parameter",
  decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
  c.ksppstvl "Instance",
  decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
  decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
  decode(p.isdefault,'FALSE','F','TRUE','T') "D",
  a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
  AND p.name(+) = a.ksppinm
  AND UPPER(a.ksppinm) LIKE UPPER('%&1%')
ORDER BY a.ksppinm;

The second script does the same as the first one exempt that it limit the result to the list of parameter which are not default. I’ve added the query as hipf.sql (stands somehow for hidden init parameter false) to my small script collection which can be downloaded in the script section.

SET linesize 235 pagesize 200
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90

SELECT * FROM (SELECT  
  a.ksppinm  "Parameter",
  decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
  c.ksppstvl "Instance",
  decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
  decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
  decode(p.isdefault,'FALSE','F','TRUE','T') "D",
  a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
  AND p.name(+) = a.ksppinm
  AND UPPER(a.ksppinm) LIKE UPPER('%&1%')
ORDER BY a.ksppinm) WHERE d='F';

A few information on hidden init.ora parameter can be found in the Metalink Note How To Query And Change The Oracle Hidden Parameters In Oracle 10g [315631.1]

Update: Oracle released CPU / PSU October 2011

Oracle has just officially released the CPU / PSU Patches for october 2011. In contrast to the previously announced 56 bug fixes, there are now 57 bug fix. It looks like another bug fix for databases has been added to the CPU / PSU bundle. Never the less none of them is remote exploitable without authentication. None of these fixes are applicable to client-only installations. The maximum CVSS rating for the database vulnerabilities is still 6.5.

The following Database Server Products are affected.

  • Application Express
  • Core RDBMS
  • Database Vault
  • Oracle Text

As I mentioned in a previous post Oracle CPU / PSU Pre-Release Announcement October 2011 the CPU / PSU patches are available for 10g and 11g. Whereby the download of 10g patches is only possible with a corresponding Extended Support contract. Brief overview of the available versions

A bunch of useful links around the current CPU / PSU:

As well as a few generic links about CPU / PSU:

Oracle CPU / PSU Pre-Release Announcement October 2011

Oracle has recently published the Pre-Release Announcement for the CPU Patch. This Critical Patch Update contains 56 new security vulnerability fixes for several Oracle products. 4 of these fixes are just for the Oracle Database Server, but none of them is for client-only installations. The maximum CVSS base score for pure Oracle Server vulnerabilities is 6.5, which is high but not critical. The following Database Server Products are affected.

  • Application Express
  • Core RDBMS
  • Database Vault
  • Oracle Text

So far the Database Server Patch’s are planned for Oracle Database 11g Release 2 (11.2.0.2), Oracle Database 11g Release (11.2.0.7), Oracle Database 10g Release 2 (10.2.0.3, 10.2.0.4, 10.2.0.5) and Oracle Database 10g Release 1 (10.1.0.5). There seems to be no CPU patch for 11.2.0.3.

The official release for the CPU / PSU is planned for next week 18 October 2011. More details about the patch will follow soon on the Oracle Security Pages:

Tablespace quotas are forever

Today I came across a strange behavior of tablespace quotas. I’ve had to create new tablespaces in a test and a development database. So I’ve created a tablespace in each of the databases and informed the developer. His answer: “Great, in development I can create tables, but in test I do not have quota on the tablespace” Ok, I did not set a quota on the newly create tablespaces nor does the user has the privilege UNLIMITED TABLESPACE. But what happened? It seems that tablespace quotas remain even if a tablespace has been deleted. Ok, lets have a closer look.

The initial situation

We assume, that somebody has created a tablespace TEST and granted unlimited quota on this to user SCOTT.

CREATE tablespace TEST datafile '/u01/oradata/PT1120/test01PT1120.dbf' SIZE 5M;

ALTER USER SCOTT quota unlimited ON TEST;

SELECT TABLESPACE_NAME,USERNAME,MAX_BYTES,DROPPED
FROM DBA_TS_QUOTAS WHERE username='SCOTT';

TABLESPACE_NAME      USERNAME    MAX_BYTES DROPPED
-------------------- ---------- ---------- ----------
TEST                 SCOTT              -1 NO

Let’s drop the tablespace TEST and see what happens to the tablespace and the quota.

DROP tablespace TEST including contents AND datafiles;

Tablespace dropped.

SELECT TS#,NAME,ONLINE$ FROM TS$;

       TS# NAME                              ONLINE$
---------- ------------------------------ ----------
         0 SYSTEM                                  1
         1 SYSAUX                                  1
         2 UNDOTBS1                                1
         3 TEMP                                    1
         4 USERS                                   1
         5 EXAMPLE                                 1
         6 TEST                                    3

7 ROWS selected.

SELECT TABLESPACE_NAME,USERNAME,MAX_BYTES,DROPPED
FROM DBA_TS_QUOTAS WHERE username='SCOTT';

TABLESPACE_NAME      USERNAME    MAX_BYTES DROPPED
-------------------- ---------- ---------- ----------
TEST                 SCOTT              -1 YES

As you can see the tablespace status in TS$ has chanted from 1 to 3 (dropped). Querying DBA_TS_QUOTAS is now showing in the column DROPPED that the tablespace for which this quota is valid has been dropped. The information in DBA_TS_QUOTA is taken from TS$ and TSQ$.

Somewhen later

Lets create again a tablespace TEST and verify SCOTT’s quota on this new tablespace. In real world this could be after a going live. Or just after quite some time when nobody remember that there use to be a tablespace TEST.

CREATE tablespace test datafile '/u01/oradata/PT1120/test01PT1120.dbf' SIZE 10M;

Tablespace created.

SELECT TABLESPACE_NAME,USERNAME,MAX_BYTES,DROPPED
FROM dba_ts_quotas WHERE username='SCOTT';

TABLESPACE_NAME      USERNAME    MAX_BYTES DROPPED
-------------------- ---------- ---------- ----------
TEST                 SCOTT              -1 NO

É voila, SCOTT has again access to the newly created tablespace.

Conclusion

From my understanding it looks like an expected behavior. But is it good, bad or ugly? As usual it depends. If a test somewhen becomes a productive database, it its worth to have a look if there are still some quotas hanging around. Just to make sure, that a low privilege user can not suddenly fill up a tablespace. On the other hand it may be handy when the quota remains and we do not have to grant again quota to x users on a newly created tablespace. I tend more towards the safe side. When a quota is not needed or makes no sense, it should be deleted. What are your opinion and experience on this topic?

By the way, the post has started with a movie title as well ended with one. Do you know which two?

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