Oracle Security Useful Scripts for Auditing

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.
 

---

Useful Scripts for Auditing

To see what statement and privilege auditing options have been set to in the database use the following script.

* show_stmt_priv_audit_opts.sql

--**********************************************
--
--   Copyright ?2003 by Rampant TechPress Inc.
--
--   Free for non-commercial use.
--   For commercial licensing, e-mail [e-mail]
--
-- *********************************************

column username format a15
column audit_option format a20
column privilege format a20
column success format a10
column failure format a10
select user_name, audit_option, success, failure
from dba_stmt_audit_opts
union
select user_name, privilege, success, failure
from dba_priv_audit_opts
/

The output is similar to what is shown below.

USER_NAME       AUDIT_OPTION         SUCCESS    FAILURE
--------------- -------------------- ---------- ----------
ANANDA          CREATE PROCEDURE     BY ACCESS  BY ACCESS
ANANDA          CREATE SEQUENCE      BY ACCESS  BY ACCESS
ANANDA          CREATE SESSION       BY ACCESS  BY ACCESS
ANANDA          CREATE TABLE         BY ACCESS  BY ACCESS
                CREATE SYNONYM       BY ACCESS  BY ACCESS
JUDY            ALTER ANY RULE       BY SESSION BY SESSION
JUDY            CREATE ANY RULE      BY SESSION BY SESSION
JUDY            CREATE PROCEDURE     BY ACCESS  BY ACCESS

Note the fourth record has no user name. This means the CREATE SYNONYM privilege is audited for all users.

To see the object auditing options set for objects, the following script can be used

* show_obj_audit_opts.sql

--**********************************************
--
--   Copyright ?2003 by Rampant TechPress Inc.
--
--   Free for non-commercial use.
--   For commercial licensing, e-mail [e-mail]
--
-- *********************************************

col owner format a10
col object_name format a15
select *
from dba_obj_audit_opts
where alt != '-/-'
aud != '-/-'
com != '-/-'
del != '-/-'
gra != '-/-'
ind != '-/-'
ins != '-/-'
loc != '-/-'
ren != '-/-'
sel != '-/-'
upd != '-/-'
ref != '-/-'
exe != '-/-'
cre != '-/-'
rea != '-/-'
wri != '-/-'
/

The output is similar to:

OWNER        OBJECT_NAME   OBJECT_TY
------------ ------------- -------------
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

CLAIM_SCHEMA CLAIMS         TABLE    
-/- -/- -/- -/- -/- -/- A/A -/- -/- A/A -/- -/- -/- -/- -/- -/-

CLAIM_SCHEMA CLAIM_LINE     TABLE  
-/- -/- -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/-

Note the column names have been left in their original form. The explanation of the columns has been given in their respective sections.

A value of hyphen (?? in the field indicates it is not set. The value left of the forward slash (?? is for SUCCESS, and the one to the right is for FAILURE. A value of 揂? indicates the audit records are generated once per access and 揝? indicates once per session. For instance in the above output, the value in the INS column for CLAIM_SCHEMA.CLAIMS is 揂/A? which indicates the audit records are generated once per insert statement into this table and are triggered when the statement succeeds or not.

Protecting the Audit Trail

If the audit destination is the database, then the DBA can also select as well as delete the data. This leaves at least one hole in the security framework. To protect the trails in this case, the OS audit destination may be used. A person other than the DBA, typically the security auditor, would protect the destination directory.

The other aspect of securing the trail is to audit the access of the aud$ table.

AUDIT AUD$;

This will enable the audit trail for any action on the table aud$, which can then be checked later.

Preserving the Audit Trail

The Need

To enforce accountability, the audit information is a valuable tool. It throws light on past actions by the user. However, as we saw earlier, this information grows fast in the database because it is a direct result of, and proportional to, the degree of activity in the database. Being inside the SYSTEM tablespace, the aud$ table contributes significantly to the increased space usage by that tablespace, often running out of room on the filesystem.

This is an interesting challenge. This table is the only table owned by SYS that grows proportionately to the user activity, not necessarily due to data growth. In fact, in a heavily accessed database, this table grows even if the user data size remains constant. If the table can抰 grow extents, all database activities that are being audited abort with errors. This is the reason the table should be purged periodically. This is the only SYS-owned table against which actions like DELETE and TRUNCATE are allowed.

Before purging the data from this table, an important point must be brought up ?audit records are valuable. Even if they have been summarized in some report, it may still be worth keeping the old data intact in the raw form, as the reports may not have extracted every bit of useful information. In the future, this seemingly unimportant information may provide clues to some investigation into malicious activities. Simply archiving the generated reports off to an archival medium like a tape satisfies HIPAA requirements. Although HIPAA does not recommend specifically keeping the raw audit log, it is prudent to archive the raw data, as a substitute or as a complement to the reports, for the following reasons:

* Raw audit logs are smaller in size compared to reports, and therefore cheaper to store.

* Raw audit logs contain all data; nothing is left out, which might be the case in reports.

* Raw audit logs can be used to format reports in any manner required for the investigation. The reports are pretty rigid.

* Raw audit logs can be put into the database as a copy of the aud$ table, which could then be UNIONed to produce a single coherent report using the scripts already in place. No new scripts will be required.

* Raw audit logs will be exactly that ?raw, not cooked. This may add a significant touch of security the auditors crave.

Therefore, it is vital to archive off the aud$ table in such a way that it can be reinstated later and with no loss of accuracy. We will cover that in this next section.