Data Auditing in Oracle Applications – Audit Trail

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

Untitled.jpg

3) Check the audited table’s owner

select owner

from dba_tables

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

Untitled.jpg

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:

select fav.application_name

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

 

Untitled.jpg

 

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.

 

 

Thanks

Sajal Agarwal

Advertisements