How To Setup Database Row Changes Audit Trial

An 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

2. Audit Group

Enter the name of the audit group.

Value: – FND_USER Audit Demo

3. Group State

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

Advertisements

One thought on “How To Setup Database Row Changes Audit Trial

  1. Hi, thanks for article.

    I have a problem. I want to use mtl_system_items_fvl but i see that we just use tables, mtl_system_items_fvl is a view. So i used mtl_system_items_b, i created mtl_system_items_b_a but i can’t insert data when changing has been. I’m in Inventory Responsabitilities-> Master Items. and at this window, in record history table_name is mtl_system_items_fvl. Can u help me?

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s