RelationalDBDesign RelationalDBDesign

Auditing Features  «Prev  Next»
Lesson 3 Enabling auditing
Objective Enable the audit feature.

Enabling the Oracle Auditing Feature

As we mentioned earlier in this module, auditing consumes resources. Although you may want to allow individual users the ability to perform the different types of audits, you also typically want to be able to control whether any audits can be performed on the database. For this reason, Oracle allows you to use an initialization parameter to enable or disable auditing for the database instance as a whole.

Enabling auditing

You must enable auditing for your Oracle database by setting the AUDIT_TRAIL initialization parameter in the INIT.ORA file for your database.
You can specify storing audit records to an internal database trail with the value DB for this parameter or to an external operating system file with the value OS for this parameter, or you may disable auditing altogether by not specifying a value for this parameter or by using the value of NONE.
When you store audit records in your Oracle database, they are stored in the table AUD$ in the SYS schema.
Allowing auditing through the proper setting of the AUDIT_TRAIL parameter gives only authorized users the ability to use Oracle’s auditing features. Users must still specifically turn on the different types of auditing.
Like all initialization parameters, the AUDIT_TRAIL parameter takes effect when a database instance is first started. If you want to change the value of this parameter, you must change it in the INIT.ORA file for the database instance and then restart the instance for the changed parameter to take effect.

Managing Oracle audit trails

The Oracle audit command will write the audit information to specific data dictionary views.
Note: The following audit trail rows can quickly fill-up your SYSTEM tablespace, and special care needs to be taken to ensure that you do not "lock-up" your database, by filling the SYSTEM tablespace. Check the following tables:
  1. dba_audit_exists
  2. dba_audit_object
  3. dba_audit_session
  4. dba_audit_statement
  5. dba_audit_trail
We also have these metadata views for Oracle auditing options:
  1. dba_obj_audit_opts
  2. dba_priv_audit_opts
  3. dba_stmt_audit_opts
In the next lesson, you will learn how to audit specific SQL statements.

Auditing Oracle Database - Quiz

Click the Quiz link below to test your knowledge of auditing your Oracle database.
Auditing Oracle Database - Quiz