Auditing in Oracle Applications

Most of data in oracle Applications is quite important but some tables are very critical & sensitive . From security reason or Audit compliances you as an Apps DBA should know

– Who changed sensitive tables ?
– At what time data was changed ?
– What was value before & after change ?

If you wish to track this information in Oracle Applications, I am sure most of you will say this can achieved by enabling Audit Trail in Oracle Applications but you need to take care of following things w.r.t. Audit Trail in Apps

– Make sure you plan purging policy for Audit/Shadow tables which will be created during Audit trail
– Consider for performance overheads if Audit Trails is enabled on too many applications tables
– Better to disable Audit Trails during major Upgrades (Shadow tables may change during version upgrade so its better to archive before upgrade )

Steps to enable Auditing
Define Audit Installation (Security -> AuditTrail -> Install)
Define Audit Group (Security -> AuditTrail -> Groups)
Run Audit Trail Update Tables Report to Enable Auditing (Submit Concurrent Screen)

Few key points for Auditing
–Auditing in Apps can be done on Row by Row basis or Individual Column of a table
–If you change any of your definitions for auditing, you must rerun Audit Trail Update Tables
–“AuditTrail Update Tables” report creates shadow table with first 26 characters of table & appended by _A

Metalink Notes related to Auditing

105630.1 Setup & Usage
60828.1 Overview of Oracle Applications AuditTrails
69660.1 Understanding Data Auditing in Oracle Application Tables
105624.1 Troubleshooting

Related blog
If you wish to know about users accessing Apps, you can refer nicely written post by Fadi on his Post About “Connected users in Apps” .

About the Author Atul Kumar

Leave a Comment:

5 comments

fhasweh says February 28, 2007

hi atul,
thank you my firend for refering to my blog. by the way i treid to call you but seems the no. is worng can you please check it

take care
fadi
http://oracle-magic.blogspot.com

Atul Kumar says March 1, 2007

Fadi,
You can try out 00447701030035 (Don’t use 0 after 44 )

Rajeshwari says February 28, 2008

Hi,

I have a question regarding auditing oracle apps file systems – APPL_TOP,ORA_TOP,COMMON_TOP,IAS_TOP.
What are the main files/directories that needs to be monitored for modifed files?

Thanks,
Rajeshwari

yossi says October 15, 2009

Hi,

There’s OraPlayer product, which performs auditing on Oracle formswithout being intrussive.
maybe that could help you

Sneha says August 23, 2013

A shadow table was created PER_ALL_PEOPLE_F_A
But I was not able to access that table.
How can I use that table in my view
Any other steps to be done to make use of that table

Add Your Reply