How To Setup Database Row Changes Audit Trial
August 29, 2007 — AhmadAn Audit Trail retains a history of changes to data. It includes the following information:
1. What changed
2. Who changed it
3. When was the data changed
When you enter or update data in your forms, you change the database tables underlying those forms. An Audit Trail tracks which row in the database was updated at what time, and which user was logged in using the associated form(s). (Metalink)
Following are the steps required to set audit trial
I. Set Audit Trail Profile Option
Login by Using System Administrator Responsibility
Navigate To
Profile –> System
And find the Audit Trail: Activate it should be set to Yes it is available on site level only. We Must Re Login into Application to Activate audit Trial in Current Session
II. Select the Installations for Audit
Navigate To
Security –> Audit Trail –> Install
You will have to check all schemas enable for which you required audit trial and save the record first click on find button and find schema. Here I took fnd_user table for audit trial so I have to check APPLSYS as this table is in APPLSYS schema. Save the work after finding and filling information as shown in picture below
III. Create a New Audit Group
Navigate To
Security –>Audit Trail–>Groups
Fill the Form on Basis of required Information
1. Application Name
Select the name of an application to associate with your audit group. The combination of application name and group name uniquely identifies your audit group. An audit group may be used to audit tables in additional applications.
Value: - Application Object Library
Enter the name of the audit group.
Value: - FND_USER Audit Demo
Choose Enable requested if you are defining a new audit group
Value: - Enable requested
When you run the Audit Trail Update Tables report, the concurrent program creates database triggers for the tables in your audit group. Once you have run the program, this field displays Enabled for audit groups where Audit Trail is active.
Fill the table name for which you required Audit here in my case it is FND_USER and Save the new audit group
IV Define Table Columns to be Audited
Navigate To
Security –> Audit Trail –>Tables
For every table defined in step 3, you need to define the columns by using these steps –
Find the table name.
Primary key, Creation Date, Created by, Last Update Login, Last Update Date and Last Updated by Columns will always be saved.
Now add the audit required column in detail block as per following picture and save the record.
V. Run Audit Trail Update Program
Navigate To
Requests –> Run
Run the Audit Trail Update Tables program to activate the auditing you can schedule this program depends upon available resources.
VI. How to get audit trial data
There are no standard Oracle Applications reports to access Audit Trail data you can only access audit trial data through SQL. All information related to auditing is stored in “shadow” tables for each audited table for which you enabled audit and these shadow tables named as tablename_A. Here in my case all information is stored in fnd_user_A.
I have created a new user by name of Pricing and change its password from older one to new one.
Now after this transaction run the following query
SELECT audit_timestamp, audit_transaction_type, audit_user_name,
audit_true_nulls, audit_session_id, audit_sequence_id, audit_commit_id,
row_key, user_id, encrypted_user_password, last_updated_by,
password_accesses_left, password_date, encrypted_foundation_password,
user_name, created_by, employee_id
FROM applsys.fnd_user_a
Result in TOAD
Query returns my user name and other information
VII. Purging Audit Trial Data
There is no standard purge program and the Audit Trail must be manually disabled to permit purging. Use the following procedure to purge audit date –
Navigate to
Security –> Audit Trail –> Groups
Select the “Security Audit” group and set the group state to “Disable – Purge Table”
Run the “Audit Trail Update Tables” Report as mentioned above
Purge the data from the shadow table
Troubleshooting
I. Note 105624.1 Troubleshooting Audit Trail issues.
II. Note 60828.1 - Overview of Oracle Applications Audit Trails
III. Note:105630.1 Setup & Usage (Audit Trail)
IV. Oracle Applications System Administration Guide
