Auditing Capabilities

Auditing, in Oracle, is used to assist the DBA with investigating suspicious database activity or to gather information about certain specific database activities.

You may determine that an unauthorized user is deleting data. In that case, the DBA, or a security administrator, might decide that she needs to audit all the connections to the database and all the successful or unsuccessful deletions of data from all the tables in the database for a given period of time. Oracle provides facilities to enable the DBA to gather statistics not only on who has been connecting to the database to learn about the unauthorized deletions but also to gather information on how many logical I/O operations have been performed at peak times or how many concurrent users have been connected during that peak time.

First, you need to determine exactly what you want to monitor. Auditing information can become voluminous, so you want to turn on auditing judiciously. Do you want to audit users, statements, or objects? Do you want information on all statement executions or just successful statement executions or just unsuccessful statement executions?

After you have an idea of what you want to audit, you need to consider how you are going to monitor and manage the growth of the audit trail and also how you are going to protect that audit trail from unauthorized access.

You want to first identify the requirements and then set the minimal auditing options that will meet your predetermined requirements. Specific object auditing should be used when possible to reduce the volume of data generated. If you determine that you really do need statement or broader yet privilege auditing, you can minimize the impact of this auditing by following these guidelines:

• Limit the users you are auditing.

• Audit by session not by access.

• Audit by success or by failure but not both success and failure.

• Audit only when absolutely necessary.

• Be highly selective of the audit options.

• Be aware that users can turn on auditing for objects that they own.

• Use the AUDIT ANY privilege judiciously because it enables the user to turn on auditing at any level.

• Periodically purge audit records from the audit trail. This can be done either with DELETE or trUNCATE (if you want to maintain a backup, insert the records into a backup table first). The audit files are located in the %ORALE_HOME%\rdbms\audit directory.

• Protect the audit trail from manipulation by either auditing the deletions on SYS.AUD$ by access (AUDIT DELETE ON SYS.AUD$ BY ACCESS) or by only allowing the DBA to have the DELETE_CATALOG_ROLE.

You can specify that records be written to SYS.AUD$, or to the operating system's audit trail, depending on the operating system that you are on.

If the audit trail becomes too full, no more records can be inserted, and all future audited statements will no longer perform successfully. Errors will be returned to all users that the audited statement has failed (making the auditing less effective), and you will need to free space in the audit trail for the statements to execute.

It is important to remember that you should always temper the need to audit and track who's doing what in your database with the knowledge that turning on auditing at any level adds overhead to the database and will have some impact on performance. Understanding exactly what you are trying to accomplish with the auditing is critical.

Do you really care who queries all the tables in the database and what queries they are running? Or do you only care who is querying the salary table? Better yet, don't you want to know mostly when a row is inserted or updated?

Balancing the necessary overhead with the added security is a job almost in itself.

Moving the Audit Trail Out of the SYSTEM Tablespace

As a general rule, you shouldn't move around the objects in the SYSTEM tablespace. However, as records begin to be inserted in the audit trail, the AUD$ table can begin to grow considerably. Although you should also not drop the AUD$ table, you can delete from it or truncate it to limit its growth. Because this table can grow and shrink, it should be placed outside the SYSTEM tablespace. To move the table, take the following steps:

The step should be run either as the system user, the SYS user, or the user who is the owner of the tables depending on what level of auditing you want to enable.

1.	Make sure that auditing is turned off.
2.	Issue the following command: ALTER TABLE aud$ MOVE TABLESPACE audit_data;
3.	Create an index on the table with the command CREATE INDEX i_aud1 ON aud$(sessionid, ses$tid) TABLESPACE audit_indexes;
4.	Reenable auditing.

Auditing Categories

By default, instance startup and shutdown and all administrator privileges are all audited. During startup, the user terminal used to start the database is recorded, the date and time stamp of the operation are recorded, and database auditing is enabled or disabled. At shutdown, the terminal and the date and time stamp of the operation are recorded. Administrator privileges record the operating user connecting to Oracle with administrator's privileges (SYSDBA, SYSOPER).

Database level auditing is enabled by the DBA and can record information about events against the database. The audit trail at this level can be used to investigate unauthorized deletion of data, or unauthorized manipulation of other structures. Another use of this level of auditing can be to determine growth of operations on the database. The DBA might gather statistics about which tables are being updated, how many logical I/O operations are being performed, and how many concurrent users are connected at highly active times. This level of auditing cannot record column values.

Value-based or application-level auditing can be implemented through custom code, can record column values, and can be used to track changes to tables. This level of auditing can be implemented often through the use of triggersfor example, before update triggers or through stored procedures.

Enabling and Disabling Auditing

To enable auditing for a given instance, you need to set the AUDIT_TRAIL initialization parameter. This parameter's purpose is to indicate whether the audit trail is written to the database table, to the operating system's audit trail, or whether auditing is turned off.

The following is a list of locations where the audit trail will be stored:

• AUDIT_TRAIL = DB enables auditing and causes all audit records to be sent to the database audit trail in the SYS.AUD$ table.

• AUDIT_TRAIL = OS enables auditing and sends all audit records to the operating system's audit trail if the operation is permitted by the operating system. These logs go to the $ORACLE_HOME/rdbms/audit or the %ORACLE_HOME%\rdbms\audit directory.

• AUDIT_TRAIL = NONE (the default) causes auditing to be disabled.

Although the statements AUDIT or NOAUDIT can be used at any time, the audit records will not actually be retained unless the DBA has set the AUDIT_TRAIL parameter to either OS or DB.

You use the NOAUDIT command to stop auditing the chosen AUDIT option.

You must use exactly the same syntax in the NOAUDIT command that you used in the AUDIT command for which it is intended to reverse. Further, it will only undo the specific auditing of the exact AUDIT command that it sets the NOAUDIT for.

Audit Options

After you have enabled the retention of audit records, you can set the specific auditing options using the AUDIT command. With this command, you indicate which commands, which users, which objects, or which privileges you want to audit. You can assign the generation time of audit records at the same time. You can either have records generated at each occurrence or have them generated only once per session, and you can turn off auditing on the object that you have been auditing with the NOAUDIT command.

You can selectively audit statements but not the underlying specific schema objects on which the statement operates. AUDIT TABLE tracks the DDL statements in the database regardless of the table on which those statements are issued.

You can audit privileges that correspond to selective system privileges such as auditing the creation of triggers in the database:

 AUDIT CREATE ANY TRIGGER; 

You can audit schema objects. This is the most narrow and selective of auditing options. With this, you can turn on auditing for specific statements on particular objects, as in the following command:

 AUDIT SELECT ON payroll; 

Schema object auditing applies to all users of the database performing the given action on the given object.

You can further narrow the criteria by adding the following conditions to any given AUDIT statement:

• WHENEVER SUCCESSFUL

• WHENEVER NOT SUCCESSFUL

• BY SESSION

• BY ACCESS

Statement Execution

Every time users execute a PL/SQL statement or a SQL statement, Oracle examines the auditing options and determines whether the statement should generate an audit record. SQL statements located inside PL/SQL programs are all examined individually and are individually audited if necessary. Because views, packages, and procedures may refer to other database objects, several audit records may be generated as a result of executing a single statement.

Audit Data Generation

The generation of an audit record is independent of a user's transaction. This means that if a user's transaction is rolled back, the audit trail of that transaction remains intact. In this way, you are alerted to the attempt at the statement. However, because the audit record is generated while running the execute phase, a syntax error that occurs during the parse phase will not cause a record to be generated.

Reviewing Audit Information

You can examine the information generated during auditing by selecting directly from the audit trail data dictionary views, or you can use the operating system's utilities to view the operating system's audit trail. Because this is an ordinary flat file, you can at least look at the file's contents with any flat file text editor.

Use the information that you have gathered to investigate database activity either for suspicious activity or for planning information for future growth.

Fine-Grained Auditing

Fine-grained auditing provides for the monitoring of data access based on content and is implemented through the use of the DBMS_FGA package. When the DBA uses the DBMS_FGA package, she can create an audit policy on the target table, and, if any rows returned form a query block match the audit condition, an audit event entry (complete with username, SQL text, bind variables, policy name, session ID, date, and time stamp) and other attributes of the event are inserted into the audit trail. This can be used to alert someone of unauthorized access of salary information or unauthorized access of sensitive medical history data.