Ways to Audit Oracle Applicaitons

There are two auditing types in Oracle Applications

Users
Database Changes

Users-We can audit users at four different levels. The level is determined by the prole option Sing-On, Audit Level,. The four levels are

None - No Auditing
User- Audit the sign on application user name, time and the terminal.
Responsibility- Audit the sign on application user name, time, the terminal, the responsibilities chosen, and the time used for each responsibility.
Form-Audit the sign on application user name, time the terminal, the responsibilities chosen, the time used for each responsibility, the forms the user utilizes, and the time used for each form.

If we set the sign-on, Audit level option to a level other than None, we can then monitor users online using the Monitor Users form. Follow the navigation path
Security/User /Monitor.

Database Changes
We can set up audit trails to track changes users make in the Oracle database. To enable the audit trail, we must

Define audit Installations
Define audit groups
Define audit tables
Run the audit trail update tables request

Define Audit Installations- During installation, we must enable audit trails for an Oracle database user ID. Follow the navigation path Security/Audit trial / Install. Select the Oracle User name for which we want to enable audit trails and check the Audit enabled on checkbox.

Define Audit Groups-Next, we must define audit groups to identify what tables we wish to audit. For custom tables to appear in the List of Values we select form, we must first register them. To define audit groups, follow the navigation path Security/AuditTrail/Groups. On the Audit Groups form, select the application name and enter the audit group name. The combination must be unique across the entire Oracle Applications. Again, the application name is for information purposes only and will not restrict any table to be audited. The group state displays the state of the audit group. Valid audit group states are Enable Requested, Disable-Interrupt Audit, Disable-prepare for Archive, Disable Purge table and enabled.

When we first create and audit group, the state is Enable Requested; after we run the audit trail update tables request, the state becomes enabled. To disable auditing, we have three choices. We can disable audit by changing the state to disable-Interrupt Audit. This allows we to interrupt auditing and write one final row into the shadow table whenever the row is being modified(this is slow disable). Or, we can change the state to Disable-Prepare for Archive. This allows we to copy the existing values for all audited rows to the shadow table immediately and disable auditing right away. We usually use this disable option to perform an achieve and enable auditing again. Before we enable auditing again, we must manually purge the shadow table. We can also change the state to Disable -Purge table to purge the shadow table and disable auditing immediately. We can also enter an optional description for our audit group.

Next, enter the audit tables to be included in our audit group. Select the user table name. The system table name, and the application , and the description will be automatically filled in. For our online exercise, create an audit group called New_Audit group and select a few GL tables such as GL_BALANCES and GL_CODE_COMBINATIONS.

Define Audit Tables- Next select the columns to be audited for each audited table. Once the columns are selected, they cannot be deleted. Columns can be selected from a list of values, where the primary keys column will have the primary key checkbox checked. All of the primary keys will be automatically audited. We can add the columns using the Define Audit tables form. To define audit tables, we can follow the navigation path Security/AuditTrail/Tables.

Run the Audit Trail Update Tables Request-The audit trail update tables request generates database triggers and shadow tables for our audit groups. Whenever we change the audit groups or audit tables definitions, we must rerun this request. Run this request through SRS and the audit trail will be enabled.