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 126.96.36.199456000 -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
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.
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;
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,
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.
commit_scn, commit_timestamp, undo_change#, row_id, undo_sql
where xid = '<the transaction 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
This will show what the user saw or what the
value was prior to the change.
where where class_id = 123;
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.
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;
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.