How to enable Audit Trail?
Following my last post about Audit users in Oracle Applications, in this post I’m going to talk about the Audit Trail – the data auditing feature in Oracle Applications.
The Audit Trail enable us to know who, when and what was changed on each table we would like to audit.
For example: Some of the profiles in Oracle Applications are very critical and important for normal activity of the system (like MO: Operating Unit).
We might be interested to know who changed the value of critical profiles and the Audit Trail certainly can help us.
Now I’ll show a step by step demo how to start Audit Trail on a table, I will go on with my previous example on the profiles table.
The table we would like to audit is FND_PROFILE_OPTION_VALUES.
1) Logon to system with System Administrator responsibility.
2) Make sure the profile “AuditTrail:Activate” is set to Yes
3) Check the audited table’s owner
where table_name like ‘FND_PROFILE_OPTION_VALUES’;
The owner is APPLSYS.
4) Check that audit is enabled for APPLSYS user
Go to: Security -> AuditTrail -> Install -> Query for APPLSYS
5) Define new Audit Group
Since the audit enabled on groups of tables, we should define a new Audit Group which will contain the FND_PROFILE_OPTION_VALUES table.
Find the table’s application name by this query:
from fnd_application_vl fav, fnd_tables ft
where fav.application_id = ft.application_id
and ft.table_name = ‘FND_PROFILE_OPTION_VALUES’;
The query result: Application Object Library
Navigate to: Security -> AuditTrail -> Groups
Application: Application Object Library
Audit Group: XXXXX FND Profile Values Audit
Group State: Enable Requested
At this level, the columns that will be audited are the columns of the primary key or the first unique index on the audited table.
You can add columns to be audited as much as you want.
6) Check/Add column to audit
Navigate to: Security -> AuditTrail -> Tables
Query for FND_PROFILE_OPTION_VALUES table.