System Administrator -> Security -> AuditTrail -> Groups

Setup the Oracle AuditTrail to Enhance the Security of the Oracle Application System.

Purpose
Purpose of this document is to instruct the user to setup the Oracle AuditTrail on the following tables:
• FND_PROFILE_OPTION_VALUES (Profile Option Updates)
• FND_USER (New User Creations or Updates)
• WF_LOCAL_USER_ROLES (Responsibility Assignments)

Getting Started
For each audited table (i.e. FND_USER), the system will create a shadow table named tablename_A (i.e. FND_USER_A). The maximum size of the shadow table name is 26 characters.
The columns to be audited should be selected carefully to avoid an impact on the performances of the system.

Configure the Profile Options
To enable the Audit Trail, it’s required to set 2 profile options at site level:
System Administrator -> Profile -> System
1. Profile option ‘Sign-On:Audit Level’ set to ‘FORM’

2. Profile Option ‘AuditTrail:Activate’ set to ‘YES’

Enable Audit on the Table Owner
According to which table we want to audit, there can be a different table owner on which the auditing should be enabled.
To table owner can be checked running the following query:

SELECT OWNER, TABLE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME = 'FND_PROFILE_OPTION_VALUES'
OR TABLE_NAME = 'FND_USER'
OR TABLE_NAME = 'WF_LOCAL_USER_ROLES'

The query above will show that the owner of all the tables we want to audit is the user ‘APPLSYS’.
To auditing on the user ‘APPLSYS’ can be enabled in the following way:
System Administrator -> Security -> AuditTrail -> Install
Querying the username ‘APPLSYS’ and making sure that the Audit Checkbox is enabled.

Create an Audit Group
The Audit Group will contain the list of the tables to be audited for our purpose.
System Administrator -> Security -> AuditTrail -> Groups
The Group State should be set to ‘Enable Requested’
Then we create the table list adding the following User Table Names:
• FND_PROFILE_OPTION_VALUES
• Define an Application User
• WF_LOCAL_USER_ROLES
The User Table Name of the FND_USER table is ‘Define an Application User’.

Selecting the Columns to Audit in FND_PROFILE_OPTION_VALUES
The columns can be set opening this form:
System Administrator -> Security -> AuditTrail -> Tables
First Table to query is FND_PROFILE_OPTION_VALUES.

Selecting the Columns to Audit in FND_USER
Second Table to query is FND_USER. The columns to audit are in the image below.

Concurrent Program AuditTrail Update Tables
After selecting the column we can run a concurrent that will enable the auditing.
System Administrator – Requests -> Run -> Single Request -> ‘AuditTrail Update Tables’ -> Submit

Testing the AuditTrail Setup
After enabling the AuditTrail it’s needed to test that everything is working properly. The test can be performed executing the following actions:
• Create a new user ‘TEST01’
• Edit the description of the user ‘TEST01’
• Assign a responsibility to the user ‘TEST01’
• Set a profile option for the user ‘TEST01’

Running the following queries, the output should contain relevant information related to what was done.

SELECT * FROM APPLSYS.FND_USER_A
SELECT * FROM APPLSYS.WF_LOCAL_USER_ROLES_A
SELECT * FROM APPLSYS.FND_PROFILE_OPTION_VALUE_A

Purging the Auditing Tables
It would be wise to create some policy establishing how often the auditing tables should be purged and where and how the data should be archived.
To Purge the auditing table it’s enough to change the ‘Group State’ of the Audit Group setting the value ‘Disable – Purge Table’
System Administrator -> Security -> AuditTrail -> Groups