Oracle Audit

The following sections explain how to enable Oracle Auditing in Oracle 8i and Oracle 9i (or higher)

Enabling Auditing in Oracle 9i (and higher)

To enable auditing in Oracle 9i (and higher) you must change the startup parameter ‘AUDIT_TRAIL’ in the SPFILE startup parameters file. This must be done using SQL Statements as the file is binary. Use, for example, SQLPlus, to perform the following queries.

Check the current status of the audit

 
SQL > Select name, value from v $ parameter where name = ‘audit_trail’

Running this statement returns one of three alternative results:

  • NONE - The audit is not active
  • DB - The audit is active and saves the events in a system table (SYS.AUD $)
  • OS - The audit is active and logs events in the eventlog Windows applications

Changing the parameters within SPFILE

For the Windows environment - Applications EventLog

The audit must be saved in the 3rd option above (OS), so run the following command:

SQL>alter system set spfile audit_trail = OS scope
For the Database -- Table SYS.AUD $ UNIX environment

It is necessary that the audit is stored in the 2nd option above (DB), so run the following command:

SQL>alter system set spfile scope audit_trail = DB;

This modifies the startup parameter binary file.

It is also possible to audit user SYS in the same way. To query the current value use command:

SQL>SELECT name, value FROM v $ parameter WHERE name = ‘audit_sys_operations’;

To modify this entry use command:

SQL>alter system set spfile scope audit_sys_operations = TRUE;

Windows Environment - Auditing Superusers

The activity of SYS (SYSDBA or SYSOPA) users in an Oracle database are recorded in Windows EventLog. The following screen shot shows the possible event properties that can be recorded.

These types of events can be audited by an agent eventlog, requiring read access through Windows WMI events within the required Oracle database.

Unix Environment - Auditing Superusers

The activity of SYS (SYSDBA or SYSOPER) users in an Oracle on UNIX system are recorded in a log file, which by default is located in a path similar to the following:

$ ORACLE_BASE / admin / $ DB_UNIQUE_NAME / adump

For example:

/app/oracle/admin/PROD/adump/ora_446678.aud
 
(where ora_446678.aud is the name of the log file)

The rotation policy of these logs is defined by the DBA of the system. This is configured with the audit_sys_operations command. To read more about this command, please reference:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams015.htm

The following is an example of the log format:

For these events to be audited by an agent File Reader, read access via SFTP protocol to the corresponding log file path is required.

WARNING: The audit user profile should not have any restriction for session duration or the number of logins (UNLIMITED by default). Normally the DEFAULT profile should be sufficient but in some companies, it is changed and some restrictions are applied. If this is the case, a new user is required to retrieve the audit events.

Restarting the Database

For the required changes to take effect it is necessary to log off the Oracle database and back on again. You must then run the following commands:

SQL > SHUTDOWN IMMEDIATE

followed by

SQL > STARTUP

Upon completion of these commands, further events can be audited using the Application Event Log.

Audit Options

Options are specified using the AUDIT statement. Oracle allows you to configure auditing on three levels:

  • JUDGEMENTS: Audit specific SQL statements or groups of statements that affect a particular type of object database. For example AUDIT TABLE (audits Create Table, Truncate Table, Delete from Table and so on).
  • PRIVILIGES: SQL statements that are audited by authorized system privileges. For example AUDIT CREATE ANY TRIGGER (audited statements using the system privilege Create Any Trigger).
  • OBJECTS: Audit specific statements on specific objects. For example AIUDIT ALTER TABLE on the EMP table.

In order to use the AUDIT statement, you must have the system privilege AUDIT SYSTEM. To use it to audit objects you should be either the object owner or have the system privilege AUDIT ANY.

By auditing sentences or privileges, you can include a BY clause (to limit the scope of the audit).

  • BY SESSION: Oracle writes a single record for all SQL statements of the same type executed in the same session.
  • By ACCESS: A registration is written for each access,. (if audit_trail = OS is used, multiple events of the same type are written as Oracle can write to the OS file bu cannot read from it)

To determine whether the audit result is a success or a failure:

  • WHENEVER SUCCESSFUL - include only the sentences that were successfully audited
  • WHENEVER NOT SUCCESSFUL - only include statements that fail or result in an error

What events can be audited?

Some examples of what can be audited from the Oracle database are shown in the table below:

SDL Group Statements

Audited Commands

CLUSTER

All statements regarding clusters

DATABASE LINK

All statements regarding database links

EXISTS

All sentences are completed because an object already exists in the database

INDEX

All statements regarding indexes

NOT EXISTS

All sentences are completed because a specific object does not exist

PROCEDURE

All statements regarding procedures

PROFILE

All statements regarding profiles

PUBLIC DATABASE LINK

All statements regarding public database links

PUBLIC SYNONYM

All statements regarding public synonyms

ROLE

All statements regarding roles

SDL Group Statements

Audited Commands

ROLLBACK SEGMENT

All statements regarding rollback segments

SEQUENCE

All statements regarding sequences

SESSION

All database logins

SYNONYM

All statements regarding synonyms

SYSTEM AUDIT

All statements AUDIT and NOAUDIT

GRANT SYSTEM

All GRANT and REVOKE commands that affect roles and system privileges

TABLE

All statements regarding tables

TABLESPACE

All statements regarding tablespaces

TRIGGER

All statements regarding triggers, including ALTER TABLE commands that enable or disable triggers

USER

All statements regarding user accounts

VIEW

All statements regarding views

Auditing Examples

The following are example statements that can be run to audit specific events:

To audit logins and logoff for each user, you should run (as user SYS) the following statement:

SQL>AUDIT SESSION

To disable this audit, run:

SQL>NOAUDIT SESSION

To audit a specific user:

SQL>AUDIT SESSION BY SCOTT BY ACCESS (where SCOTT is the username)

To omit a specific user from the audit:

SQL>NOAUDIT SESSION BY SCOTT (where SCOTT is the username)
NOTE: You must exit the system for the statement to take effect.

To cover all categories in Oracle, the following statements can be executed to indicate the start of the audit.

For Management Profiles:

SQL>AUDIT PROFILE BY ACCESS;

For Management Roles:

SQL>AUDIT ROLE BY ACCESS;

For User Management:

SQL>AUDIT BY USER ACCESS;

For Grant and Revoke Roles and Privileges System:

SQL>GRANT AUDIT SYSTEM BY ACCESS;

For User Logon and Logoff:

SQL>AUDIT SESSION;

For Judgements on Tables:

SQL>AUDIT TABLE;
 
SQL> ALTER TABLE AUDIT;

For Audit Changes:

SQL>AUDIT SYSTEM AUDIT;
 
SQL>ALTER SYSTEM AUDIT;

Per user for required user monitoring (in these examples, the user profile is SCOTT)

SQL>AUDIT DELETE TABLE BY ACCESS BY SCOTT;
 
SQL>AUDIT INSERT TABLE ACCESS BY SCOTT;
 
SQL>SELECT TABLE BY SCOTT AUDIT BY ACCESS;
 
SQL>AUDIT UPDATE TABLE BY ACCESS BY SCOTT;

After these steps, the audit events should start writing in the eventlog or audit table as appropriate.

NOTE: You do not need to restart the database after executing the AUDIT statements, since changes are dynamic.

Current Audit Status

It is possible to see the current status of the audit by running a SELECT command to see all the configured values.

View

Description

DBA_STMT_AUDIT_OPS

Describes current audit options for the system and user

DBA_PRIV_AUDIT_OPS

Describes the system privileges being audited, for the entire system and user

DBA_OBJ_AUDIT_OPTS

USER_OBJ_AUDIT_OPTS

Describes auditing options on all objects. With USER, objects in which the owner is the user of the session are described

AUDIT_ACTIONS

Contains the description of the codes for all options audit actions