Auditing in 10g

Fine-grained auditing (FGA), introduced in Oracle9i, allowed recording of these row-level changes along with SCN numbers to reconstruct the old data, but they work for select statements only, not for DML such as update, insert, and delete. Therefore, prior to Oracle Database 10g, using triggers is the only reliable albeit unattractive choice for tracking user-initiated changes at the row level. With the arrival of 10g, these limitations are gone, thanks to two significant changes to the auditing facility. Because two types of audits are involved—the standard audit (available in all versions) and the fine-grained audit (available in Oracle9i and up)—we'll address them separately and then see how they complement each other to provide a single, powerful tracking facility.

The New Stuff
First, FGA now supports DML statements in addition to selects. These changes are recorded in the same place, the table FGA_LOG$, and displayed through the view DBA_FGA_AUDIT_TRAIL. In addition to DMLs, you can now choose to trigger a trail only if all relevant columns are accessed, or even when a few of them are accessed. (For a detailed explanation of how FGA works in 10g, see Technical Article on that subject.)
Standard auditing, implemented by the SQL command AUDIT, can be used to quickly and easily set up tracking for a specific object. For instance, if you wanted to track all the updates to the table EMP owned by Scott, you would issue:

audit UPDATE on SCOTT.EMP by access;

This command will record all updates on the table SCOTT.EMP by any user each time it occurs, in the audit trail table AUD$, visible through the view DBA_AUDIT_TRAIL. The primary table for auditing, AUD$, contains several new columns to record them, and consequently the view DBA_AUDIT_TRAIL, as well. Let's take a look at each of them in detail.
EXTENDED_TIMESTAMP. This column records the timestamp of the audit record in the TIMESTAMP (6) format, which records time in Greenwich Mean Time (also known as Universal Coordinated Time) with seconds up to 9 places after the decimal point and with the Time Zone information. An example of the time stored in this format is shown below.

2004-3-13 18.10.13.123456000 -5:0

This indicates a date of March 13, 2004, at Eastern Standard Time in the U.S., which is 5 hours after the UTC (as denoted by -5.0).

GLOBAL_UID and PROXY_SESSIONID. When an identity management component such as Oracle Internet Directory is used for authentication, the users may be visible to the database in a slightly different manner. For example, they may be authenticated as enterprise users when presented to the database. Auditing these users will not record their enterprise userid in the USERNAME column of the view DBA_AUDIT_TRAIL, making that information useless. In Oracle Database 10g, the global (or enterprise) user uniqueid is recorded in the columns GLOBAL_UID without any further processing or setup. This column could be used to query the directory server to find complete details about the enterprise user. Sometimes the enterprise users might connect to the database via a proxy user, especially in multi-tier applications. A user could be given proxy authentication through the command

alter user scott grant connect to appuser;

This command will allow the user SCOTT to connect as APPUSER to the database, as the proxy user. In that case, the column COMMENT_TEXT will record that fact by storing the value PROXY; but the session id of the proxy user will not be recorded anywhere, as of Oracle9i. In 10g, the column PROXY_SESSIONID records it for exact identification of the proxy session.

INSTANCE_NUMBER. In an Oracle Real Application Clusters (RAC) environment, it might be helpful to know to which specific instance the user was connected while making the changes. In 10g, this column records the instance number as specified by the initialization parameter file for that instance.

OS_PROCESS. In Oracle9i and below, only the SID values are recorded in the audit trail; not the operating system process id. However, the OS process id of server process may be necessary later in order to cross-reference a trace file, for example. In 10g, this value is also recorded in this column.

TRANSACTIONID. Here comes the most critical price of information. Suppose the user issues

update CLASS set size = 10 where class_id = 123;
commit;

This command qualifies as a transaction entry and an audit record is generated. However, how do you know what the audit record really recorded? If the record was a transaction, the transaction id is stored in this column. You can use it to join the audit trail with the view FLASHBACK_TRANSACTION_QUERY. Here is a small sample of columns in this view.

select start_scn,  start_timestamp, 
   commit_scn, commit_timestamp, undo_change#, row_id, undo_sql
from flashback_transaction_query
where xid = '<the transaction id>';

In addition to the usual statistics on that transaction, undo change#, rowid, and so on, 10g also records the SQL to undo the transaction changes, in the column UNDO_SQL and the rowid of the affected row shown in the column ROW_ID.

System Change Number. Finally, it comes to recording the values before the change. How do you do that? Taking a cue from FGA in Oracle9i, the values before the change can be obtained through flashback queries. But you need to know the System Change Number (SCN) for the change and it is captured in this column in audit trail. You could issue

select size from class as of SCN 123456
where where class_id = 123;

This will show what the user saw or what the value was prior to the change.

Extended DB Auditing
Remember our original interest: to capture user-issued SQL statements and bind variables that are not captured in standard auditing. Enter the enhanced auditing in Oracle Database 10g, in which these tasks become as trivial as making a simple initialization parameter change. Just place the following line in parameter file.

audit_trail = db_extended

This parameter will enable recording of SQL text and the values of the bind variables, if used, in the columns. This value was not available in the earlier versions.

When Triggers Are Necessary
Avoiding False Positives. Audit trails are generated through autonomous transactions from the original transactions. Hence they are committed even if the original transactions are rolled back. Here is a simple example to illustrate the point. Assume that we have set up auditing for UPDATEs on table CLASS. A user issues a statement to update a data value from 20 to 10 and then rolls it back as shown below.

update class set size = 10 where class_id = 123;
rollback

Now the value of the column SIZE will be 20, not 10, as if the user never did anything. However, the audit trail will capture the change, even if it's rolled back. This may be undesirable in some cases, especially if there are lots of rollbacks by users. In such a case, you may have to use the trigger to capture only committed changes. If there were a trigger on the table CLASS to insert records into the user defined audit trail, upon rollback the audit trails would have been rolled back too.