Oracle Applications DBA Blog

Auditing In Oracle Applications

Auditing in any production application is required due to various statutory or security obligations. Oracle Applications, an ERP which is backbone of the Organization implementing  it, it becomes all the more indispensable. Auditing aims at detection, prevention as well as track down any change which requires further investigation.

In Oracle Applications auditing can be done at two levels

• Oracle Applications Users Level.
• Oracle Applications Database/Schema Level.

In addition to the above you could also enable the standard Oracle RDBMS audit feature using 
the SYS.AUD$ table. In the current post however we will talk about only the auting 
capabilities of Oracle Applications.

Oracle Applications User Auditing

Sign On Audit
In Oracle Applications user level auditing can be done by enabling the Sign Audit profile option. This option can either be set at the user level, responsibility level or the Form level. To enable this kind of auditing you must set a value for the system profile option Sign-On: Audit Level.



Based upon the level of Sign Auditing selected the System capture all or some of the 
following information.

• The users who sign on to the system
• The Sign in and Sign Out times
• Responsiblities they choose
• The forms they use
• Duration of using the form or responsibility
• Terminals used for sign on.

You can get this information from the system by running one of the standard Oracle  Applications reports which are

• Signon Audit Users
• Signon Audit Responsibilities
• Signon Audit Forms
• Signon Audit Concurrent Requests
• Signon Audit Unsuccessful Logins

Additionally you could also setup notifications for unsuccessful logins by setting the  Sign-On: Notification system profile option to YES.



You can also use the monitor users window to have a user level auditing. However you can view the information only of those users for whom sign on auditing has been enabled.



Oracle Applications Database/Schema Level

Oracle Applications also allows to track down changes made to the data in oracle applications. For this purpose Audit Trails are setup and used. The trails track the audited  table by creating a shadow table for each of the audited table. Internally this mechanism uses database triggers to populate the shadow tables. The Following steps are involved for  setting up a audit trail in oracle applications.

Define Audit Groups
The first step involves defining your audit group. An audit group is a set of tables and  columns that you wish to audit. You can choose either to enable auditing of all or some of the  columns of a table except LONG,RAW and LONG RAW. You could have multiple audit groups and a  same object can also be a part of different audit groups.

Define Audit Installations
In this stage you choose the registered ORACLE IDs that you wish to audit. Before you can 
define your audit installation you must have denied your audit groups.

Define Audit Table and Columns
After defining the audit groups and audit installations you can choose the tables and columns 
for which you wish to enable auditing. You can use the audit tables to do this.



Run the Audit Trail Update Tables Report
You must run a concurrent request Audit Trail Update Tables Report so that your audit trail  definitions and the required shadow tables are created. This report is also responsible for building database triggers for your audit installations.



Disabling Auditing
You can disable auditing at anytime by changing the audit group state through the Audit Groups 
window to either Disable - Interrupt Audit, Disable -  Prepare for Archive or Disable - Purge Table. You must run the Audit Trail Update Tables Report after making the selection.