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.

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.

Enabling the audit feature in an Oracle 12c database typically involves a few key steps. However, it's important to note that the specific details can vary depending on the exact configuration and requirements of your database environment. Here is a general approach that you might consider:

1. Determine Audit Needs:First, it might be beneficial to assess what specific actions or areas of the database you wish to audit. This assessment could help in configuring the audit settings more effectively.
2. Enable Auditing at the Database Level:You could begin by enabling auditing at the database level. This might be done by setting the `AUDIT_TRAIL` parameter in the database. For instance, you might set `AUDIT_TRAIL` to `DB` for standard auditing or `DB, EXTENDED` for more comprehensive auditing, which includes SQL text and SQL bind information. This setting can usually be modified in the database's initialization parameter file or dynamically using the `ALTER SYSTEM` command.
3. Restart the Database:After changing the `AUDIT_TRAIL` parameter, a database restart might be necessary for the changes to take effect. This is typically done by issuing a shutdown and then a start command.
4. Configure Specific Audit Policies:Once the audit trail is enabled, you could proceed to define specific audit policies. This might involve using the `AUDIT` statement to specify particular actions or user activities to be audited. For example, auditing activities like logon or logoff events, or specific actions of particular users or roles.
5. Manage Audit Trail:After enabling auditing, it might be important to regularly monitor and manage the audit trail. This could include reviewing the audit data, ensuring that the audit trail does not grow excessively large, and possibly archiving older audit records.
6. Consider Fine-Grained Auditing:If you have more specific auditing needs, Oracle 12c also offers Fine-Grained Auditing (FGA). This feature allows for more detailed auditing criteria, such as auditing actions based on the content of the data being accessed.
7. Regularly Review Security Policies:Finally, it could be beneficial to regularly review and update your auditing and security policies to ensure they continue to meet the needs of your organization and comply with any relevant regulations or standards.

Remember, these steps are a general guideline and it's advisable to consult Oracle documentation or a database administrator experienced with Oracle 12c for more detailed instructions tailored to your specific environment.

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.

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