how to enable form and table audit in oracle

General Description
The Audit Trail is an Oracle inbuilt functionality that lets audit specific columns belonging to selected tables. This implementation enhances the security of the 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 and 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. The columns to audit are in the image below.


• LEVEL_VALUE_APPLICATION_ID – Set by Default
• LEVEL_VALUE – Set by Default
• LEVEL_ID – Set by Default
• PROFILE_OPTION_ID – Set by Default
• APPLICATION_ID – Set by Default
• PROFILE_OPTION_VALUE – Needed to log the old profile option value
• LAST_UPDATE_DATE – Needed to log Date and Time of when the profile option value was updated
The other columns are not relevant for the auditing purpose.
Selecting the Columns to Audit in FND_USER
Second Table to query is FND_USER. The columns to audit are in the image below.


• USER_ID – Set by Default
• USER_NAME – Needed to log the username updates
• ENCRYPTED_FOUNDATION_PASSWORD – Needed to identify when a password have been reset or when an account have been locked
• ENCRYPTED_USER_PASSWORD – Needed to identify when a password have been reset or when an account have been locked
• DESCRIPTION – Needed to log the accounts description updates
• END_DATE – Needed to log the updates of the accounts end-date
• START_DATE – Needed to log the updates of the accounts start-date
• PASSWORD_LIFESPAN_DAYS – Needed to log the updates of the accounts lifespan
• EMPLOYEE_ID – Needed to log the updates of the accounts lifespan
• EMAIL_ADDRESS – Needed to log the updates of the accounts email address
The other columns are optional or not relevant for the auditing purpose.
• (*) LAST_LOGON_DATE – Not to be Audited to avoid logging not needed records
Selecting the Columns to Audit in WF_LOCAL_USER_ROLES
Third Table to query is WF_LOCAL_USER_ROLES. The columns to audit are in the image below


• USER_NAME – Set by Default
• ROLE_NAME – Set by Default
• EXPIRATION_DATE – Needed to log the updates of the responsibility end-date
• START_DATE – Needed to log the updates of the responsibility start-date
• ASSIGNMENT_REASON – Needed to log the updates of the responsibility assignment justification
The other columns are optional or not relevant for the auditing purpose.
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


Then the concurrent program ‘AuditTrail Update Tables’ should be executed again.
If you have found this post helpful don’t forget your comment ;)