Oracle Tips by Burleson

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

Get the complete Oracle10g story:

The above text is an excerpt from "Oracle Database 10g New Features: Oracle10g Reference for Advanced Tuning and Administration", by Rampant TechPress.  Written by top Oracle experts, this book has a complete online code deport with ready to use scripts. 

To get the code instantly, click here:

http://rampant-books.com/book_2003_2_oracle10g.htm

Need an Oracle Mentor?

BEI is now offering personal mentors for Oracle DBAs where you can have an Oracle expert right at your fingertips, anytime day or night. We work with hundreds of Oracle databases every year, so we know exactly how to quickly assist you with any Oracle question.

Why risk an unplanned outage? You can now get telephone access to Don Burleson or any of his Oracle Certified DBAs with more than 20 years of full-time IT experience. Click here for details:

http://www.dba-oracle.com/service_oracle_backup.htm