Oracle Database 10g: The Top 20 Features for DBAs

Join Oracle Magazine's 2003 "DBA of the Year" Arup Nanda over the next 20 weeks as he presents his list of the top Oracle Database 10g features for database administrators

Follow along! Download Oracle Database 10g


Week 10
Auditing Tells All

Oracle Database 10g auditing captures user activities at a very detailed level, which may obviate manual, trigger-based auditing

Suppose user Joe updates a row of the table as shown below, assuming he has update privileges on that table.

update SCOTT.EMP set salary = 12000 where empno = 123456;

How do you track this activity in the database? In Oracle9i Database and below, auditing captures only the "who" part of the activity, not the "what." For instance, it lets you know that Joe updated the table EMP owner by SCOTT, but it does not show that he updated the salary column for the table for employee number 123456. It does not show the value of the salary column before the change, either—to capture such detailed changes, you either have to write your own triggers to capture values before the change or fish them out of archived logs using Log Miner.

Both approaches allow you to track what was changed and record the values before the change but only at significant costs. Using triggers to write audit data can have a major performance impact; for that reason, in some cases (such as in third-party applications) user-defined triggers are forbidden. Log Miner does not affect performance but it does rely on the availability of archived logs to track changes.

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 my 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.

This capability was available prior to 10g, too. However, in those releases, the information written to the trail was limited to a few pertinent items such as the user who issued the statement, the time, the terminal id, and so on; it lacked important information such as the value of the bind variables. In 10g, the auditing action captures many of these pieces of important information, in addition to what was collected in prior versions. 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).

The presence of time in this extended format helps pinpoint audit trails to a much narrower time span, enhancing their usefulness especially with databases that span multiple time zones.

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.

Capturing Before-change Values. Oracle-provided audit trails do not show the values before and after the change. For instance, the above change will create an audit record that shows the statement and the SCN number at the change, but not the value before the change (20). The value can be obtained from the SCN number using flashback query, but it depends on the information being available in the undo segments. If the information is not captured within the limit specified by the undo_retention period, the prior values can never be retrieved. Using a trigger guarantees that the values are captured without dependence on the undo_retention period, and may prove useful at times. Under these two circumstances you may decide to continue using triggers to record the audit trails at a granular detail.

Uniform Audit Trail

Because FGA and standard auditing capture similar types of information, they provide a lot of significant information when used together. Oracle Database 10g combines the trails to a common trail known as DBA_COMMON_AUDIT_TRAIL, which is a UNION ALL view of the views DBA_AUDIT_TRAIL and DBA_FGA_AUDIT_TRAIL. However, there are some significant differences between the two types of audit.

Conclusion

In 10g, auditing has matured from a mere "action recorder" to a "fact-recording mechanism" that captures user activities at a very detailed level, which may obviate your need for manual trigger-based auditing. It also combines the trails of standard auditing and FGA, making it easier to track database access regardless of how it was generated.

For additional information, see Chapter 11 of the Oracle Database Security Guide 10g Release 1 (10.1).

Next Week: Wait Interface

Back to Series Index


Please rate this document:

Excellent Good Average Below Average Poor

Send us your comments