ORACLE DB AUDIT in Oracle 9i

ORACLE DB AUDIT in Oracle 9i


Actions Audited by Default

Regardless of whether database auditing is enabled, Oracle always audits certain
database-related operations and writes them to the operating system audit file.
These operations include the following:

-- Connections to the instance with administrator privileges

An audit record is generated that lists the operating system user connecting to
Oracle as SYSOPER or SYSDBA. This provides for accountability of users with
administrative privileges.

-- Database startup

An audit record is generated that lists the operating system user starting the
instance, the user’s terminal identifier, the date and time stamp, and whether
database auditing was enabled or disabled. This is stored in the operating
system audit trail because the database audit trail is not available until after
startup has successfully completed. Recording the state of database auditing at
startup helps detect when an administrator has restarted a database with
database auditing disabled (thus enabling the administrator to perform
unaudited actions).

-- Database shutdown
An audit record is generated that lists the operating system user shutting down
the instance, the user’s terminal identifier, and the date and time stamp.


Auditing Administrative Users


Sessions for users who connect as SYS, this includes all users connecting as SYSDBA
or SYSOPER, can be fully audited. Use the AUDIT_SYS_OPERATIONS initialization
parameter to specify if user SYS is audited. For example, the following setting
specifies that SYS is to be audited:

AUDIT_SYS_OPERATIONS = TRUE

A value of FALSE, which is the default, disables SYS auditing.

All audit records for SYS are written to the operating system file that contains the
audit trail, and not to SYS.AUD$. All SYS issued SQL statements are audited
indiscriminately and regardless of the setting of the AUDIT_TRAIL initialization
parameter.



Managing the Audit Trail

Setting the AUDIT_TRAIL Initialization Parameter


Parameter Value Meaning

DB Enables database auditing and directs all audit records to the
database audit trail, except for records that are always written to
the operating system audit trail

OS Enables database auditing and directs all audit records to an
operating system file

NONE Disables auditing (This value is the default.)



Setting the AUDIT_FILE_DEST Initialization Parameter

The AUDIT_FILE_DEST initialization parameter specifies an operating system
directory into which the audit trail is written when AUDIT_TRAIL=OS is specified.

If the AUDIT_FILE_DEST parameter is not specified, the default location is
$ORACLE_HOME/rdbms/audit.


Setting Auditing Options
You specify auditing options using the AUDIT statement. The AUDIT statement
allows you to set audit options at three levels:

BY SESSION/BY ACCESS

BY SESSION causes Oracle to write a single record for all SQL statements of
the same type issued in the same session. BY ACCESS causes Oracle to write
one record for each access. --> BY ACCESS produces many recods ATTENTION...

WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL

WHENEVER SUCCESSFUL chooses auditing only for statements that succeed.
WHENEVER NOT SUCCESSFUL chooses auditing only for statements that fail or
result in errors.


Auditing Connections and Disconnections

To audit all successful and unsuccessful connections to and disconnections from the
database, regardless of user,

AUDIT SESSION;

You can set this option selectively for individual users also
AUDIT SESSION
BY scott, lori;

Specifying Privilege Auditing

the option to audit use of the DELETE ANY TABLE privilege is DELETE ANY TABLE.

AUDIT DELETE ANY TABLE
BY ACCESS
WHENEVER NOT SUCCESSFUL;

To audit all successful and unsuccessful uses of the DELETE ANY TABLE system
privilege, enter the following statement:

AUDIT DELETE ANY TABLE;

To audit all unsuccessful SELECT, INSERT, and DELETE statements on all tables
and unsuccessful uses of the EXECUTE PROCEDURE system privilege, by all
database users, and by individual audited statement, issue the following statement:

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE
BY ACCESS
WHENEVER NOT SUCCESSFUL;


Specifying Object Auditing

To audit all successful and unsuccessful DELETE statements on the scott.emp
table, BY SESSION (the default value), enter the following statement:

AUDIT DELETE ON scott.emp;

To audit all successful SELECT, INSERT, and DELETE statements on the dept table
owned by user jward, BY ACCESS, enter the following statement:

AUDIT SELECT, INSERT, DELETE
ON jward.dept
BY ACCESS WHENEVER SUCCESSFUL;


Turning Off Audit Options

You can use a NOAUDIT statement to disable an audit option selectively using the
WHENEVER clause. If the clause is not specified, the auditing option is disabled
entirely, for both successful and unsuccessful cases.

Turning Off Statement and Privilege Auditing

The following statements turn off the corresponding audit options:
NOAUDIT session;
NOAUDIT session BY scott, lori;
NOAUDIT DELETE ANY TABLE;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,
EXECUTE PROCEDURE;
The following statement turns off all statement audit options:
NOAUDIT ALL;
The following statement turns off all privilege audit options:
NOAUDIT ALL PRIVILEGES;


Turning Off Object Auditing

The following statements turn off the correspo nding auditing options:

NOAUDIT DELETE
ON emp;

NOAUDIT SELECT, INSERT, DELETE
ON jward.dept;

Controlling the Growth and Size of the Audit Trail

If the audit trail becomes completely full and no more audit records can be inserted,
audited statements cannot be successfully executed until the audit trail is purged.
Warnings are returned to all users that issue audited statements.

The maximum size of the database audit trail (SYS.AUD$ table) is determined by
the default storage parameters of the SYSTEM tablespace, in which it is stored. You
should not move SYS.AUD$ to another tablespace as a means of controlling the
growth and size of the audit trail. However, you can modify the storage parameters
for SYS.AUD$.

Purging Audit Records from the Audit Trail

For example, to delete all audit records from the audit trail, enter the following
statement:

DELETE FROM SYS.AUD$;


If audit trail information must be archived for historical purposes, the security
administrator can copy the relevant records to a normal database table (for
example, using INSERT INTO table SELECT ... FROM SYS.AUD$ ...) or
export the audit trail table to an operating system file.

Only the user SYS, a user who has the DELETE ANY TABLE privilege, or a user to
whom SYS has granted DELETE privilege on SYS.AUD$ can delete records from the
database audit trail.

Reducing the Size of the Audit Trail

Truncate SYS.AUD$ using the TRUNCATE statement.

To protect the database audit trail from unauthorized deletions, grant the DELETE
ANY TABLE system privilege to security administrators only.

To audit changes made to the database audit trail, use the following statement:

AUDIT INSERT, UPDATE, DELETE
ON sys.aud$
BY ACCESS;