Oracle 10 Gb audit content that DBAs need to know

Oracle Database 10g audit captures user behavior at a very detailed level, which can eliminate manual, trigger-based audit. Assume that user Joe has the permission to update the table and updates a row of data in the table as follows.

Update SCOTT. EMP set salary = 12000 Where e-mapreduce = 123456;

How do you track such behavior in the database? In Oracle 9i Database and earlier versions, audit can only capture "who" to perform this operation, but cannot capture what "to execute. For example, it lets you know that Joe updated all SCOTT's tables EMP, but it does not show that he updated the table's salary column with employee number 123456. It does not display the value of the salary column before the change-to capture such detailed changes, you will have to write your own trigger to capture the value before the change, or use Log Miner to retrieve them from the archived logs.

Both methods allow you to track the changes and record the values before the changes, but the cost is very high. Writing audit data using triggers may have a major impact on performance. In this case, user-defined triggers are not allowed in some cases (such as in third-party applications. Log Miner does not affect performance, but it relies on the availability of archived logs to track changes.

Fine-grained auditing (FGA) is introduced in Oracle 9i. It can record SCN numbers and row-level changes to recreate old data, but they can only be used for select statements, not DML, such as update, insert, and delete statements. Therefore, for Oracle database versions earlier than 10 Gb, using triggers is the only reliable method, although it is not attractive for tracking users' initial changes at the row level.

With the arrival of Oracle 10 Gb, these restrictions also go away due to two major changes in audit capabilities. The two audit types involve standard audit (available in all versions) and fine-grained audit (available in Oracle 9i and later versions). We will process them separately, then let's see how they complement each other to provide a single and powerful tracking feature.

New Features

First, FGA supports not only select statements, but also DMA statements. These changes are recorded in the same position, that is, the table FGA_LOG $, and displayed in the DBA_FGA_AUDIT_TRAIL view. In addition to DML, you can now trigger a clue only after accessing all or even a few related columns. (For more information about how FGA works in Oracle 10g, see my technical article on this topic .)

Standard auditing is executed by the SQL command AUDIT and can be used to quickly and easily Set Tracing for specific objects. For example, if you want to track all updates to the table EMP owned by Scott, you can issue the following command:

Audit UPDATE on SCOTT. EMP by access;

Every time a user updates the SCOTT. EMP table, this command will record all updates to the audit trail table AUD $, which can be viewed in the DBA_AUDIT_TRAIL view.

This feature is also available for Oracle versions earlier than 10 Gb. However, in those versions, the information written to the trail is limited to a few related items, such as the user, time, and terminal ID number that issues the statement. It lacks some important information, for example, bind the variable value. In Oracle 10g, in addition to the content collected in earlier versions, audit operations also capture many important pieces of information. The original table AUD $ for auditing contains several new columns used to record them. Correspondingly, the DBA_AUDIT_TRAIL view also contains these columns. Let's take a closer look.

EXTENDED_TIMESTAMP. This column records the time stamp of the audit record in the format of TIMESTAMP (6). It uses the GMT Standard Time (also known as the globally unified time) to record the time, the number of seconds after the decimal point reaches 9 with time zone information. An example of the time stored in this format is as follows. 18.10.13.123456000-, dated March 13, 2004, is the standard Eastern time in the United States. It is five hours later than the unified time in the world (expressed as-5.0 ). The time displayed in extended format helps to precisely locate audit trails to a narrower interval, enhancing their usage, especially when databases span multiple time zones.

GLOBAL_UID and PROXY_SESSIONID. When an identity management component such as Oracle Internet Directory is used for identity authentication, the user's access permissions to the database are slightly different. For example, when they access the database, they may be treated as enterprise users. Auditing these users does not record their enterprise user ID in the USERNAME column of the DBA_AUDIT_TRAIL view, making this information useless. In the Oracle Database 10 Gb, the unique ID numbers of Global (or enterprise) users are recorded in the GLOBAL_UID column without further processing or setting. This column can be used to query the Directory Server to find complete details about the enterprise user.

Sometimes enterprise users may connect to the database through a proxy user, especially in multi-tier applications. You can run the following command to provide Proxy authentication for users:

Alter user scott grant connect to appuser;

This command allows SCOTT to connect to the database as a proxy user as APPUSER. In that case, the COMMENT_TEXT column will record the facts by storing the value PROXY; but for Oracle 9i, the session ID of the PROXY user will not be recorded. In Oracle 10g, the PROXY_SESSIONID Column records it for precisely identifying Proxy Sessions.

INSTANCE_NUMBER. In the real Oracle Application cluster (RAC) environment, it may help you to know which specific routine the user is connected to during the change. In Oracle 10g, this column records the routine number, which is specified by the initialization parameter file of this routine.

OS _PROCESS. In Oracle 9i and earlier versions, only the SID value is recorded in the audit trail, but the operating system process ID is not recorded. However, the operating system process ID of the server process may be necessary subsequently. For example, it is used to reference a clue file. In Oracle 10g, this value is also recorded in this column.

TRANSACTIONID. The most critical information price is generated here. Assume that the user sends the following command:

Update CLASS set size = 10 where CLASS id = 123; Commit;

This command gets a Transaction item and generates an audit record. But how do you know what the audit record actually records? If the record is a transaction, the transaction ID number is stored in the column. You can use it to link audit trail with the FLASHBACK_TRANSACTION_QUERY view. The following is a small example of columns in the View:

Select start_scn, start_timestamp, Commit_scn, commit_timestamp, undo_change #, row_id, undo_ SQL From flashback_transaction_query Where xid =' ';

In addition to recording the general statistics on the firm, such as undo change # And rowid, Oracle 10 Gb can also record and undo SQL commands for changing the firm in the UNDO_ SQL column, and the rowid of the affected row displayed in the ROW_ID column.

The system change number. Eventually, it records the value before the change. How do you perform this operation? As indicated by FGA in Oracle 9i, the pre-change value can be obtained through a flashback query. However, you need to know the system change number (SCN) of the change, which can be captured in this column of the audit trail. You can run the following command:

Select size from class as of SCN 123456 Where class_id = 123;

This displays the content you see or the value before the change.

Extended DB Audit

Keep in mind our initial interest: To capture SQL statements issued by users and bind variables that cannot be captured in standard audits. In the Oracle Database 10 Gb, enter the enhanced audit, and these tasks become as insignificant as changing a simple initialization parameter. You only need to put the following lines of code into the parameter file.

Audit_trail = db_extended

If this parameter is used, the SQL text and bound variable values are recorded in each column. This value is unavailable in earlier versions.

When is a trigger necessary?

Avoid mischecking. Audit Trail is generated by autonomous transactions from raw transactions. Therefore, they are committed even if the original transaction is rolled back.

A simple example demonstrates this. Suppose we have set audit for UPDATE in Table CLASS. The user issues a statement to update the data value from 20 to 10 and then roll it back, as shown below:

Update class set size = 10 where class id = 123; Rollback

Now the SIZE value of this column will change to 20 instead of 10, as if the user has never done anything. However, even if rollback is performed, the audit trail captures the change. In some cases, this may not be what people want, especially when users perform many rollback operations. In this case, you may have to use a trigger to capture only submitted changes. If a trigger on the table CLASS is used to insert records into user-defined audit leads, audit leads are also rolled back Based on rollback.

Capture the previously changed values. The audit trail provided by Oracle does not display the values before and after the change. For example, the above change will create an audit record that displays the statement and the changed SCN number, but does not display the value before the change (20 ). You can use the flashback query to obtain this value through the SCN number, but it depends on the information available in the Undo segment. If the information cannot be captured within the period specified by the undo_retention period, the previous value will never be retrieved. The trigger ensures that this value can be captured without relying on the undo_retention period, and is sometimes useful. In these two environments, you can decide to continue using triggers to record audit trails at a fine-grained level.

Unified audit tracking

Because FGA and standard audit capture the same type of information, many important information can be provided when combined. Oracle Database 10 Gb merges these traces into a common trace called DBA_COMMON_AUDIT_TRAIL. It is a union all view in the DBA_AUDIT_TRAIL view and DBA_FGA_AUDIT_TRAIL view. However, there are some major differences between the two audit types.

Conclusion

In Oracle 10g, the audit has grown from a simple "Operation Recorder" to a "fact record mechanism", which can capture user behavior at a very detailed level, this eliminates your need for manual, trigger-based auditing. It also integrates standard auditing and FGA tracking, which makes it easier to track database access without considering how it is generated.

(