Oracle 10g New Features: Audit Enhancements

Oracle databases have many types of auditing features. In mandatory auditing, certain actions are always audited, regardless of the other audit options or parameters. Database activities, such as system startup and shutdown, are always recorded.

In the standard auditing method, auditing is set at the system level using the audit_trail initialization parameter. Once auditing is turned on, you have the option of selecting the objects and privileges you wish to audit.

In the fine-grained auditing (FGA) method, the audit is based on the data content. FGA provides better control and is a more granular method of auditing. This method creates audit records based on the exact query, condition, and data retrieved or manipulated by the statement. This method also provides a facility to audit only those statements (including actual values of possible bind-variables) that reference a particular column. The FGA method was introduced in Oracle9i. Oracle Database 10g enhances the FGA capability. The Extended SQL Support in FGA now supports the granular auditing of queries, as well as UPDATE, INSERT, and DELETE operations.

The next sections examine and discuss the new audit features introduced in the Oracle database 10g.

Uniform Audit Trails

In Oracle Database 10g, it becomes possible to track the same fields for standard and fine-grained auditing. This allows you to easily examine and monitor security standards. Any unauthorized access can be effectively tracked. In order to assure uniformity between the standard and fine-grained auditing trail records, many new attributes have been added to complement the methods.

Oracle Database 10g collects the following extra FGA information into the same audit trail table, such as:

• The system change number (SCN) records every change to the system.

• The exact SQL text executed by the user.

• The bind variables used with the SQL text.

Again, in the case of fine-grained auditing, the following extra fields of information are collected:

• A serial number for each audit record.

• A statement number links together multiple audit entries that originate from a single statement. For example, in a case where an UPDATE hits three different FGA policies, the fine-grained audit trail will show three entries (each with its own entry ID) and one statement number.

• A statement_type, telling which kind of statement was audited. For example, SELECT for select statements.

• An object identifier that is a unique identifier for every object in the database.

The following queries show the new fields added:

For Standard Auditing -

SELECT username, owner, obj_name, sql_text FROM dba_audit_trail

For Fine-Grained Auditing -

SELECT db_user, object_schema, object_name, sql_text FROM dba_fga_audit_trail;

In addition to the above, other significant changes have been introduced with the Oracle Database 10g database release. The following fields have been modified.

• A global timestamp in Universal Coordinated Time (UTC) replaces GMT. This field is useful for monitoring across servers in separate geographic locations and time zones.

• An instance number that is unique. The database uses this field to combine audit records in Real Application Clusters (RAC) environments.

• A transaction identifier that is unique for each operation. This field assists you in grouping audit records belonging to a single transaction.

Fine-Grained Auditing (FGA)

In general, the FGA method of auditing monitors the data access based on the information retrieved or modified by the query or DML. With the help of the FGA method, it becomes easier to focus on security-relevant columns and rows and ignore areas that are less important. Another advantage with the FGA method is that it produces far less audit records, while being more useful in keeping a safe security track.

The FGA method was introduced in Oracle9i Enterprise Edition. However, this method provided only support for the "SELECT" statements. With Oracle Database 10g, it becomes possible to extend the FGA method to cover UPDATE, INSERT, and DELETE statements as well.

FGA helps in detecting the potential misuse of user privileges and possible security breaches, without having to make changes in the actual application code. With the help of FGA event handlers, policy violations may generate notifications to alert the administrators.

We have covered more details on the topic of FGA in Chpter 15, Security Enhancements.

Response File

When using the Oracle Universal Installer (OUI), a response file is generated. This file contains the responses you enter to the various prompts in the OUI.

With Oracle Database 10g, you now have true silent capability. When running OUI in silent mode on a character mode console, you no longer need to specify an XServer or set the DISPLAY environment variable on UNIX. No GUI classes are instantiated, making the silent mode truly silent. The generated Response file can be used for future install needs.

The response file format has been changed. The changes make it less likely that you will need to edit the file for deployment to other systems.

These changes include:

• Only the variables used in dialogs needing user inputs in interview phases are recorded.

• Minimized differences in response files generated between releases.

• New header format to make it easier for you to edit the file.

---

The above is an excerpt from the bestselling Oracle10g book Oracle Database 10g New Features by Mike Ault, Madhu Tumma and Daniel Liu, published by Rampant TechPress.