ENABLING THE ORACLE APPLICATIONS AUDIT FUNCTION

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.

DATABASE AUDITING
CONFIGURING

There are basically two steps required to enable database-level auditing

1. set the audit_trail parameter in init.ora
2. execute audit statements

Step 1 – Set “audit_trail” parameter

In the init.ora file for the instance, set the audit_trail parameter to true. This will enable auditing to the SYS.AUD$ table. Optionally, audit information can be written to the operating system by setting audit_trail parameter to OS. The database must be restarted for this parameter to take effect.

Step 2 – Execute audit statements

Execute the following SQL statements as the SYSTEM user

audit session ---> Session auditing – connects to the database
audit user ---> Create, alter, and drop user
audit database link ---> Create or drop database links
audit public database link ---> Create or drop public database links
audit system audit ---> Audit and noaudit statements

To review the existing system-level audits
SQL> select * from DBA_STMT_AUDIT_OPTS;
SQL> select * from DBA_PRIV_AUDIT_OPTS;

PURGING

The SYS.AUD$ table needs to be purged on a periodic basis, at least every 90 days. The database connection entries can be significant. All rows should be backed up prior to being purged.


APPS AUDITING – END USER ACCESS

Oracle Applications User Auditing

Sign On Audit

In Oracle Applications user level auditing can be done by enabling the Sign Audit profile option.

1. User level
2. Responsibility level
3. Form level

All user signons, responsibility selections, and form accesses will be logged to

APPLSYS.FND_LOGINS
APPLSYS.FND_LOGIN_RESPONSIBILITIES
APPLSYS.FND_LOGIN_RESP_FORMS

APPS AUDITING – END USER ACCESS

Unsuccessful Logins
Unsuccessful password attempts are automatically recorded in the

APPLSYS.FND_UNSUCCESSFUL_LOGINS and ICX.ICX_FAILURES

Concurrent Requests

All concurrent requests are recorded in the FND_CONCURRENT_REQUESTS table. There is no configuration required.

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.

We 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

Using SQL

The end-user access data is stored in the following tables

APPLSYS.FND_LOGINS
APPLSYS.FND_LOGIN_RESPONSIBILITIES
APPLSYS.FND_LOGIN_RESP_FORMS
FND_CONCURRENT_REQUESTS
APPLSYS.FND_UNSUCCE

PURGING

The end-user access data can be purged using the Purge Signon Audit Data concurrent program. The only parameter is a date which all audit data older this date is purged. This program should be scheduled to be run weekly or monthly and saving at least 30-90 days of data.

The following tables are purged by this program

FND_LOGIN_RESP_FORMS
FND_LOGIN_RESPONSIBILITIES
FND_LOGINS
FND_UNSUCCESSFUL_LOGINS

APPS AUDITING – DATABASE ROW CHANGES

Minimal

FND_AUDIT_GROUPS
FND_AUDIT_SCHEMAS
FND_AUDIT_TABLES
FND_AUDIT_COLUMNS

Standard

ALR_ALERTS

Standard

FND_ORACLE_USERID

Above Average

FND_USER
FND_USER_RESP_GROUPS

High

FND_FORM_FUNCTIONS
FND_MENUS
FND_MENU_ENTRIES
FND_REQUEST_GROUPS
FND_REQUEST_GROUP_UNITS
FND_ENABLED_PLSQL
FND_RESP_FUNCTIONS
FND_CONCURRENT_PROGRAMS
FND_EXECUTABLES
FND_DATA_GROUPS
FND_DATA_GROUP_UNITS

CONFIGURING

Step 1 – Set AuditTrail Profile Option

The System Profile Option AuditTrail:Activate must be set to Yes. Be sure to log out of the applications to activate the profile option in your session.

Step 2 – Select the Audit Installations

 As System Administrator, select Security -> AuditTrail -> Install.

 Check all the schemas for which auditing should be enabled. For example, if you want to audit FND_USERS, you would check APPLSYS since the FND_USERS table is in the APPLSYS schema.

 Save your selections.

Step 3 – Create a New Audit Group

 As System Administrator, select Security -> AuditTrail -> Groups.

 Create a new audit group by setting the Application Name to the application that owns the table (e.g., Application Object Library for APPLSYS), the Audit Group to a new name (e.g., My Audits), and Group State should be set to Enable Requested.

 Add the tables to be audited. Columns will be defined in the next step.

 Save the new audit group.

Step 4 – Define Table Columns to be Audited
For each table defined in the above step, define the columns to be audited using these steps –

 As System Administrator, select Security -> AuditTrail -> Tables.

 Query the table name.

 The primary key columns will always be saved. Add the columns that need to be audited. Do not ever add the following columns as user information is automatically added –

Creation Date Created By Last Update Login Last Update Date Last Updated By

 Save the columns.

Step 5 – Run AuditTrail Update Program

Run the AuditTrail Update Tables program to activate the auditing. This program will create a shadow table for each audited table and create triggers on each audited column in the original table. The shadow table will have the same name as the audited table appended with “_A”. Two views will be created for each column with the names “_AC#” and “_AV#” where # is a sequential number.

Troubleshooting

See the Oracle Applications System Administration Manual Chapter 3 for more information on accessing the audit trail information. Metalink Note 105624.1 contains information on troubleshooting AuditTrail issues.

PURGING

The audit trail information should be purged on a periodic basis. There is no standard purge program and the AuditTrail must be manually disabled to permit purging. Use the following procedure to purge audit date –

1. As System Administrator, select Security -> Audit Trail -> Groups

a. Select the “Security Audit” group and set the group state to “Disable – Purge Table”

2. Run the “Audit Trail Update Tables” Report

3. Purge the data from the shadow table

4. Select Security -> Audit Trail -> Groups

a. Select the “Security Audit” group and set the group state to “Enable”

5. Run the “Audit Trail Update Tables” Report

PURGING

The audit trail information should be purged on a periodic basis. There is no standard purge program and the AuditTrail must be manually disabled to permit purging. Use the following procedure to purge audit date

1. As System Administrator, select Security -> Audit Trail -> Groups
a. Select the “Security Audit” group and set the group state to “Disable – Purge Table”
2. Run the “Audit Trail Update Tables” Report
3. Purge the data from the shadow table
4. Select Security -> Audit Trail -> Groups
a. Select the “Security Audit” group and set the group state to “Enable”
5. Run the “Audit Trail Update Tables” Report

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

We 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.

Navigator -> System Administrator -> Security: User -> Monitor -> Query F11 and Ctrl+F11

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.


FND_USER

FND_USER_RESPONSIBILITY

FND_USER_RESP_GROUPS

FND_SECURITY_GROUPS

FND_APPLICATION

FND_APPLICATION_TL

FND_RESPONSIBILITY

FND_RESPONSIBILITY_TL

FND_PRODUCT_INSTALLATIONS

WF_LOCAL_USER_ROLES

ICX_SESSIONS


REFERENCES

 Oracle8i Administrator’s Guide – Chapter 24 Auditing Database Use
 Oracle Applications System Administrator’s Guide – Chapter 3 User and Data Auditing
 Metalink Note 105624.1 – Troubleshooting (Audit Trail)
 Metalink Note 60828.1 – Overview of Oracle Applications AuditTrails
 Metalink Note 69660.1 – Understanding Data Auditing in Oracle Application Tables