Enabling Oracle E-Business Suite Audit Trail

Overview

You can choose to store and retrieve a history of all changes users make on a given table. Auditing is accomplished using audit groups, which functionally registered Oracle IDs or group tables to be audited. For a table to be audited, it must be included in an enabled audit group.

Audit Trail Groups are groups of tables and columns. You do not necessarily need to include all the columns in a given table. You enable auditing for audit groups rather than for individual tables. You would typically group together those tables that belong to the same business process (for example, purchase order tables).

A given table can belong to more than one audit group. If so, the table is audited according to the highest level of enabling for any of its groups, where Enabled is the highest, followed by Disable Dump Data, Disable No Growth, and Disable Purge Table, in that order.

You can enable auditing for a maximum of 240 columns for a given table, and you can enable auditing for all types of table columns except LONG, RAW, or LONG RAW. Your audit group must include all columns that make up the primary key for a table; these columns are added to your audit group automatically. Once you have added a column to an audit group, you cannot remove it.

Steps to Enable Audit Trail

To enable Oracle E-Business Suite Audit Trail, review and perform the following steps as required:

  1. Granted required privileges.

    Have your database administrator grant SELECT privileges on SYS.DBA_TABLES to the APPLSYS account. Normally, this step will already have been done as part of the installation or upgrade.

  2. Register custom tables and primary keys.

    Your tables and their primary key information must already be registered and defined for successful auditing. If the table you want to audit is a custom table (not shipped as part of Oracle E-Business Suite), you should also perform the following two steps:

    1. Register your table and its primary key columns using Oracle Application Object Library's Tables window (Application Developer Responsibility).

    2. Run the Register Tables concurrent program from the Submit Requests window.

  3. Turn on Audit Trail.

    Turn on Oracle E-Business Suite Applications Audit Trail by setting the system profile Audit Trail: Activate to True

  4. Define Audit Installations (optional).

    You may optionally choose to audit by a registered Oracle ID. This allows you to audit across multiple application installations. When a table is added to an audit group, auditing will automatically be enabled for all installations of the table for which audit is enabled.

    Before you proceed, ensure that the desired Oracle username is registered. The installation process automatically registers Oracle E-Business Suite Oracle user names, but if you create a custom application, you should follow instructions in My Oracle Support Knowledge Document 1577707.1, Creating a Custom Application in Oracle E-Business Suite Release 12.2, for Oracle user names for custom applications.

    To audit by a registered Oracle ID, do the following:

    Navigate through Security > Audit Trail > Install and select the registered Oracle user names at your site that you wish to audit. Select the Audit Enabled check box to enable Audit Trail for an Oracle user name.

    Audit Installations Window

    the picture is described in the document text

    For auditing to take effect, you must perform the next steps in this section.

  5. Define Audit Groups.

    You can audit a table or Oracle ID by defining an audit group, which can consist of one or more tables. To create an Audit Group and assign specific tables and columns, perform the following:

    1. Navigate to Security > Audit Trail > Groups to create audit groups and set tables to be audited. Set audit group to Enabled Requested.

    2. Identify the tables you want to audit or tables owned by an Oracle ID selected for auditing in the previous step, "Define Audit Installations". See Tables to Audit with Audit Trail for a list of recommended tables to consider auditing.

      Audit Groups Window

      the picture is described in the document text

      • Audit Groups block - Identify your audit group and enable or disable auditing for this group.

      • Application Name - Select the name of an application to associate with your audit group. The combination of application name and group name uniquely identifies your audit group. An audit group may be used to audit tables in additional applications.

      • Audit Group - Enter the name of the audit group.

      • Group State - Choose Enable Requested if you are defining a new audit group. When you run the Audit Trail Update Tables report, the concurrent program creates database triggers for the tables in your audit group. Once you have run the program, this field displays Enabled for audit groups where Audit Trail is active.

    Important: All primary key columns in each table in an audit group are automatically selected for auditing, whether or not you use the Audit Tables window to select which columns you wish to audit.

  6. Active Audit Trail.

    Your Audit Trail definitions (and auditing) do not go into effect until you run the Audit Trail Update Tables Report. If you change any of your definitions later, you must rerun this program. Submit the Audit Trail Update Tables concurrent request from the standard submission (Submit Reports) form.

Audit Trail Shadow Tables, Triggers, and View

Upon execution, the Audit Trail Update Table concurrent request performs the following tasks:

Each of these tasks is described in detail in the following sections.

Shadow Tables

The shadow table is automatically created in the same Oracle ID as the audited table. The shadow table name contains the first 24 characters of the audited table name plus "_A" (Audit). For example, the Audit Trail shadow table is named as follows: Audit Trail shadow table name = <table_name>_A

The shadow table contains only the columns to be audited, and all columns in the shadow table are unconstrained, regardless of their status in the table to be audited. For example, NULLs are always permitted in the shadow table. All columns in the shadow table have the same data types and sizes as their counterparts in the audited table.

All Audit Trail shadow tables contain certain special auditing columns. These columns include:

For example, suppose you have the following table:

SQL> DESCRIBE AUDIT_DEMO

 NAME            NULL?    TYPE
 --------------- -------- ----
 PRIMARY_KEY              NUMBER(5)
 VALUE_ONE                VARCHAR2(5)
 VALUE_TWO                VARCHAR2(5)
 VALUE_THRE               VARCHAR2(5)

Its shadow table is as the following (assuming you audit all your table columns):

SQL> DESCRIBE AUDIT_DEMO_A

NAME                      NULL?       TYPE
----------------------    --------    ----
AUDIT_TIMESTAMP           NOT NULL    DATE
AUDIT_TRANSACTION_TYPE    NOT NULL    VARCHAR2(1)
AUDIT_USER_NAME           NOT NULL    VARCHAR2(100)
AUDIT_TRUE_NULLS                      VARCHAR2(250)
AUDIT_SESSION_ID          NOT NULL    NUMBER
AUDIT_SEQUENCE_ID         NOT NULL    NUMBER
AUDIT_COMMIT_ID           NOT NULL    NUMBER
PRIMARY_KEY                           NUMBER
VALUE_ONE                             VARCHAR2(5)
VALUE_TWO                             VARCHAR2(5)
VALUE_THREE                           VARCHAR2(5)

Database Triggers

When auditing is enabled, the automatically-generated database trigger in the "After" event on the audited table performs the auditing. The trigger calls a stored procedure to compare each column being audited to see if its value is changing. If so, the procedure saves the previous (old) value to the shadow table.

Auditing creates one row in the shadow table for each audited transaction against the table; thus, a single row in the shadow table represents all old values for all changed columns on that transaction.

The data is not compressed, since a table uses only one byte for a NULL, and Audit Trail represents all unchanged values as NULLs in the shadow table ("sparse" format).

The audit trigger names contain the first 24 characters of the audited table name plus "_AI," "_AU," or "_AD," where one of I, U or D indicates Insert, Update or Delete, respectively. Likewise, the audit procedure names use the first 24 characters of the table name plus "_AIP," "_AUP," or "_ADP." Your table names must be unique within the first 24 characters.

For example, the Audit Trail triggers are named as follows:

Audit Trail Update Trigger name = <table_name>_AU
Audit Trail Insert Trigger= <table_name>_AI
Audit Trail Insert Trigger= <table_name>_AD

Two Views for Each Column

After a shadow table is created, views onto the shadow table are created to allow easier access to the data in the "sparse" rows. These views simplify tasks such as querying a row/column's value on a given date and tracking changes to a row/column over time.

The view name contains the first 24 characters of the audited table name plus "_AC#" or "_AV#" where C or V indicates the type of view and # indicates a number. Due to limitations in creation size, the shadow table columns may need to be broken into multiple views, which are numbered sequentially.

For example, the Audit Trail views are named as follows:

Changes View = <table name>_AV#

Complete View = <table name>_AC#

Each view allows slightly different access to the data. One allows the user to reconstruct the value for a row at a given time (_AC), while the other provides simple access to when a value was changed (_AV).

For our example table, the _AV1 and _AC1 views are created as follows:

SQL> DESCRIBE AUDIT_DEMO_AV1

NAME                         NULL? TYPE
---------------------------  ----- ----
PRIMARY_KEY                        NUMBER 
AUDIT_TIMESTAMP                    DATE
AUDIT_SEQUENCE_ID                  NUMBER
AUDIT_SESSION_ID                   NUMBER
AUDIT_TRANSACTION_TYPE             VARCHAR2(1)
AUDIT_USER_NAME                    VARCHAR2(100)
VALUE_ONE                          VARCHAR2(5)
VALUE_TWO                          VARCHAR2(5)
VALUE_THREE                        VARCHAR2(5)

SQL> DESCRIBE AUDIT_DEMO_AC1

NAME                         NULL? TYPE
---------------------------  ----- ----
PRIMARY_KEY                        NUMBER
AUDIT_TIMESTAMP                    DATE
AUDIT_SEQUENCE_ID                  NUMBER
AUDIT_SESSION_ID                   NUMBER
AUDIT_TRANSACTION_TYPE             VARCHAR2(1)
AUDIT_USER_NAME                    VARCHAR2(100)
AUDIT_COMMIT_ID                    NUMBER
VALUE_ONE                          VARCHAR2(5)
VALUE_TWO                          VARCHAR2(5)
VALUE_THREE                        VARCHAR2(5)

Purging Audit Trail Records

Purge the audit trail information on a regular basis. Prior to purging, disable the Audit Trail. Use the following procedure to purge audit data:

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

  2. Select the Security Audit group and set the group state to Disable - Purge Table.

  3. Run the Audit Trail Update Tables Report.

  4. Purge the data from the shadow table.

  5. Select Security > Audit Trail > Groups.

  6. Select the Security Audit group and set the group state to Enable.

  7. Run the Audit Trail Update Tables Report.

Disabling an Enabled Audit Trail

You may disable auditing at any time. When you disable auditing, you should do the following procedure:

  1. Stop auditing new transactions.

    Disable auditing using either "Disable - Prepare for Archive" or "Disable - Interrupt Audit" and running the Audit Trail Update Tables report.

    Variable Descriptions
    Variable Description
    Disable - Prepare for Archive Copies the current values of all rows in the audited table into the shadow table, and then disables the auditing triggers. This option requires the most space, since there is at least one row in the shadow table for every row in the audited table (and another row in the shadow table for each transaction on the original row in the audited table). You should then archive the table before you empty the shadow table.
    Disable - Interrupt Audit Modifies the triggers to store one final row in the shadow table as the audited row is modified in the audit table (remember that a given row in the shadow table represents the data in the audited row before an update). Inserts or further changes are no longer audited. The shadow table then grows slowly, and the data may be accessed by the existing audit views.
    Disable - Purge Table Drops the auditing triggers and views and deletes all data from the shadow table.
  2. Archive your audit data.

    You should archive the information in the shadow tables according to your business needs.

  3. Clean out the shadow table.

    Before you restart auditing, you should clean out the shadow table. If there were transactions during the time auditing was disabled, and you did not clean out the shadow table, the data in the shadow table would be invalid because it would have a gap where transactions were not recorded. You purge the shadow table(s) by setting the audit group to Disable - Purge Table and running the Audit Trail Update Tables report.

    Variable Descriptions
    Variable Description
    Disable - Purge Table Drops the auditing triggers and views and deletes all data from the shadow table.

Restarting an Audit

If desired, you can restart auditing by setting the audit group to Enable Requested and running the Audit Trail Update Tables report again.

Important: If you disable using Disable Purge Table and then re-enable auditing for a table, Audit Trail flushes the contents of the shadow table when auditing is re-enabled. You should archive any shadow table data that you want to keep before you re-enable auditing.

Tables

Tables to Audit with Audit Trail

When enabling Audit Trail, you should consider auditing tables that control Oracle E-Business Suite system security. The following is a list of tables you should consider auditing:

How Data Appears in Tables and View

Here is an example of how data appears in your original table, your shadow table, and your audit views after a series of changes (starting with an empty AUDIT_DEMO table).

SQL> INSERT INTO AUDIT_DEMO VALUES (1,'A','A','A');
SQL> INSERT INTO AUDIT_DEMO VALUES (2,'X','X','X');
SQL> SELECT PRIMARY_KEY KEY, VALUE_ONE VAL_1,
     VALUE_TWO VAL_2, VALUE_THREE VAL_3 FROM AUDIT_DEMO;

 KEY VAL_1 VAL_2 VAL_3
---- ----- ----- -----  
   1 A     A     A
   2 X     X     X

SQL> UPDATE AUDIT_DEMO SET VALUE_ONE ='B' 
     WHERE PRIMARY_KEY = 1;

 KEY VAL_1 VAL_2 VAL_3
---- ----- ----- -----
   1 B     A     A
   2 X     X     X

SQL> UPDATE AUDIT_DEMO SET VALUE_TWO ='B' 
     WHERE PRIMARY_KEY = 1;

 KEY VAL_1 VAL_2 VAL_3
---- ----- ----- -----
   1 B     B     A
   2 X     X     X

SQL> UPDATE AUDIT_DEMO SET VALUE_THREE ='B' 
     WHERE PRIMARY_KEY = 1;
SQL> UPDATE AUDIT_DEMO SET VALUE_ONE ='Y' 
     WHERE PRIMARY_KEY = 2;
SQL> UPDATE AUDIT_DEMO SET VALUE_ONE = NULL 
     WHERE PRIMARY_KEY = 1;
SQL> UPDATE AUDIT_DEMO SET VALUE_ONE ='C' 
     WHERE PRIMARY_KEY = 1;

After our two inserts and six updates, the final values in the audited table are:

 KEY VAL_1 VAL_2 VAL_3
---- ----- ----- -----
   1 C     B     B
   2 Y     X     X

The final values in the corresponding shadow table are as follows. A row in the shadow table represents the state of the audited row before the audited row was changed. Note that if a value in a row doesn't change during the transaction, the shadow table records a null for that value in that transaction.

In our example, the first two rows in the shadow table represent the state where there was no data for our two audited rows before they were inserted. The "prior values" are null values for the two insert transaction (type I) rows. Similarly, when we update the first value of row 1 to be the value B instead of A, the shadow table records the value A in its third row:

SQL> SELECT TO_CHAR(AUDIT_TIMESTAMP, 'HH24:MI:SS') TIME,
     AUDIT_TRANSACTION_TYPE TYPE, AUDIT_USER_NAME NAME,
     PRIMARY_KEY KEY, VALUE_ONE VAL_1, VALUE_TWO VAL_2,
     VALUE_THREE VAL_3, AUDIT_TRUE_NULLS FROM AUDIT_DEMO_A;

TIME     TYPE NAME    KEY VAL_1 VAL_2 VAL_3 AUDIT_TRUE_NULLS
-------- ---- ------ ---- ----- ----- ----- ----------------
11:08:16 I    FND60     1
11:08:40 I    FND60     2
11:18:40 U    FND60     1 A
11:20:12 U    FND60     1       A
11:21:54 U    FND60     1             A
11:22:15 U    FND60     2 X
14:20:50 U    FND60     1 B
14:21:15 U    FND60     1                   NYNN

8 rows selected.

Given the current values of the row in the audited table, you can trace the changes made to the row by backing up through the corresponding rows in the shadow table.

In our example table, we made two insert and six update transactions, so we see those eight transactions in our shadow table. In the last row, the NYNN indicates that the value in the second table column (VALUE_ONE) has changed from an actual null value (the Y) rather than being an unchanged value (represented by null in the shadow table).

The following two views provide further ways of examining your audited data.

The rows with a transaction type of C in the view indicate the current value of the row when the data was selected (the view is a join between the shadow table and the audited table, so the current value row reflects the current state of the audited table).

The _AC view provides a "filled-in" version of the data, where unchanged values appear instead of being represented by null values. You can order this view by the primary key (rather than by timestamp), so all rows in the shadow table that correspond to a single audited row appear together, with a secondary ordering by timestamp.

SQL> SELECT TO_CHAR(AUDIT_TIMESTAMP, 'HH24:MI:SS') TIME,
     AUDIT_TRANSACTION_TYPE TYPE, AUDIT_USER_NAME NAME,
     PRIMARY_KEY KEY, VALUE_ONE VAL_1, VALUE_TWO VAL_2,
     VALUE_THREE VAL_3 FROM AUDIT_DEMO_AC1
     ORDER BY PRIMARY_KEY, AUDIT_TIMESTAMP;

TIME     TYPE NAME        KEY VAL_1 VAL_2 VAL_3
-------- ---- ---------- ---- ----- ----- -----
11:08:16 I    FND60         1 A     A     A
11:18:40 U    FND60         1 B     A     A
11:20:12 U    FND60         1 B     B     A
11:21:54 U    FND60         1 B     B     B
14:20:50 U    FND60         1       B     B
14:21:15 U    FND60         1 C     B     B
17:53:34 C                  1 C     B     B
11:08:40 I    FND60         2 X     X     X
11:22:15 U    FND60         2 Y     X     X
17:53:34 C                  2 Y     X     X

10 rows selected.

Important: If the changes to your audited table occur faster than one change per second (that is, more frequently than the one-second granularity provided by SYSDATE), you may see "blurring" of records (i.e. more than one record per transaction) in the _AC view, because of joins used in this view. However, the shadow table itself remains correct, and you can resolve the relevant transactions by referring to the shadow table directly.

The _AV1 view provides a more sparse view of the audit data, ordered by timestamp:

SQL> SELECT TO_CHAR(AUDIT_TIMESTAMP, 'HH24:MI:SS') TIME,
     AUDIT_TRANSACTION_TYPE TYPE, AUDIT_USER_NAME NAME,
     PRIMARY_KEY KEY, VALUE_ONE VAL_1, VALUE_TWO VAL_2,
     VALUE_THREE VAL_3, AUDIT_TRUE_NULLS
     FROM AUDIT_DEMO_AV1;

TIME     TYPE NAME    KEY VAL_1 VAL_2 VAL_3 AUDIT_TRUE_NULLS
-------- ---- ------ ---- ----- ----- ----- ----------------
11:08:16 I    FND60     1
11:08:40 I    FND60     2
11:18:40 U    FND60     1 A
11:20:12 U    FND60     1       A
11:21:54 U    FND60     1             A
11:22:15 U    FND60     2 X
14:20:50 U    FND60     1 B
14:21:15 U    FND60     1                   NYNN
17:58:31 C              1 C     B     B
17:58:31 C              2 Y     X     X

10 rows selected.

Here is an example of how you might use a view to determine who changed a particular value and when:

SQL> SELECT TO_CHAR(AUDIT_TIMESTAMP, 'HH24:MI:SS') TIME,
     AUDIT_TRANSACTION_TYPE TYPE, AUDIT_USER_NAME NAME
     FROM AUDIT_DEMO_AV1
     WHERE PRIMARY_KEY = 1
     AND VALUE_ONE = 'B';

TIME     TYPE NAME
-------- ---- ------
14:20:50 U    FND60

Similarly, you might want to determine who changed a value to null and when:

SQL> SELECT TO_CHAR(AUDIT_TIMESTAMP, 'HH24:MI:SS') TIME,
     AUDIT_TRANSACTION_TYPE TYPE, AUDIT_USER_NAME NAME
     FROM AUDIT_DEMO_AV1
     WHERE PRIMARY_KEY = 1
     AND VALUE_ONE  IS NULL
     AND SUBSTR(AUDIT_TRUE_NULLS,2,1) = 'Y';

TIME     TYPE NAME
-------- ---- ------
14:21:15 U    FND60

Reporting on Audit Data

Audit Trail reports are not provided with Oracle E-Business Suite. You may write audit reports as needed using SQL. Audit Trail provides the views of your shadow tables to make audit reporting easier; you can write your reports to use these views.

You may want to create one or more indexes to your shadow table to speed up your reporting. However, such indexes decrease performance during actual auditing of transactions, so you should drop your indexes from the shadow table when you have finished reporting. Another alternative is to move audit data to an operational data store for reporting purposes.

Implications of Upgrading an Audit Trail

Important: Because the structure of the audited table may change between product versions, Audit Trail does not support upgrading existing shadow tables or audited data. Before an upgrade, you should archive the shadow tables and perform all necessary reporting on the audited data.

Auditing database row changes is performance intensive. Limit auditing to non-transactional data. Auditing transactional data may cause significant performance degradation. Tables with more than a few changes an hour should not be considered for row level auditing. Plan and consult with a DBA before enabling Audit Trails.

Disabling Audit Trail

To disable auditing for a group, choose one of the following options and then run the Audit Trail Update Tables report to have your changes take effect.

Variable Descriptions
Variable Description
Disable - Prepare for Archive Copies the current values of all rows in the audited table into the shadow table, and then disables the auditing triggers. This option requires the most space, since there is at least one row in the shadow table for every row in the audited table (and another row in the shadow table for each transaction on the original row in the audited table). You should then archive the table before you empty the shadow table.
Disable - Interrupt Audit Modifies the triggers to store one final row in the shadow table as the audited row is modified in the audit table (remember that a given row in the shadow table represents the data in the audited row before an update). Inserts or further changes are no longer audited. The shadow table then grows slowly, and the data may be accessed by the existing audit views.
Disable - Purge Table Drops the auditing triggers and views and deletes all data from the shadow table.

Audit Tables Window

the picture is described in the document text

Additional Audit Trail Reporting

This section describes how to set up and manage Audit Trail Reporting functions that are used within OPM.

The following topics are covered:

Audit Industry Template

This window defines the Audit Industry templates. These templates facilitate binding of the required Audit groups together for easy querying and inquiries.

Before using this window, perform the following:

Audit Industry Template Procedure

Use this procedure in completing the Industry Template.

  1. Navigate to the Industry Template window.

  2. Complete the fields as described.

  3. Save your changes.

Audit Industry Template Fields

The following are the fields found in the Audit Industry templates.

Audit Industry Template Field Descriptions
Field Description
Template Name The name of the desired Audit Template
Functional Areas Functional Group - Enter the functional group associated with this template. This is the same as the Audit Group field on the Audit Group window in System Administration.

Audit Hierarchy Navigator

Auditing Navigation

In addition to the standard menu and tool bar, a navigator tree provides a hierarchical display of the objects in a treelike framework.

Nodes and Leaves

The higher level nodes in the navigator tree include windows and database objects. All other nodes, and the objects they contain, are indented to indicate that they belong to these higher level nodes. The terminal node is a leaf.

On the Hierarchy Navigator, the highest level is the Audit Template. The next level is the Audit Group (Functional Group), then the audit table, and finally, the columns being audited.

On the Query Navigator, the highest level is the Audit Group (Functional Group). The next level is the audit table, and below the audit table are the actual data being audited.

Using the Audit Hierarchy Editor

You can navigate to find what has been set up for auditing. This functionality is accomplished by a tree navigator that starts with the Industry template and drill down to groups, tables, and columns. The navigator lets you see a drill-down view of what columns are being audited. A search facility on the tree is provided to search a table or column.

The navigator fetches the data from the audit table to construct the tree, and relies on the Oracle E-Business Suite Object Library table, column registration and uses USER_TABLE_NAME and USER_COLUMN_NAME fields from the FND_TABLES and FND_COLUMNS, respectively.

Before using this window, perform the following:

Audit Hierarchy Navigation Procedures

Navigate to the Audit Hierarchy window.

To view table information:

  1. Use the tree navigator to view the table names.

  2. Select the table name and right-click to display the pop-up menu.

  3. Select Display Columns. The Define Query Navigator Display for the Table window appears.

To use the Find Audit Hierarchy function:

  1. Use the tree navigator to view the column names.

  2. Select the column name and right-click to display the pop-up menu.

  3. Select Find. The Find Audit Hierarchy window displays.

  4. Select criteria and click Find. A list of templates displays. You can save these as a new audit.

Audit Query Navigator

This interactive query window lets you investigate the changes to any functional group interactively, using a visual approach that is similar to Windows Explorer. When a particular node in the left frame is selected, audit trail details are displayed in the right frame. The right frame shows all columns set for auditing. This information is retrieved from the FND_AUDIT_COLUMNS table. The left tree is linked to the right frame with the primary key combination of the table.

Auditing Navigation

In addition to the standard menu and tool bar, a navigator tree provides a hierarchical display of the objects in a treelike framework.

Nodes and Leaves

The higher level nodes in the navigator tree include windows and database objects. All other nodes, and the objects they contain, are indented to indicate that they belong to these higher level nodes. The terminal node is a leaf.

On the Hierarchy Navigator, the highest level is the Audit Template. The next level is the Audit Group (Functional Group), then the audit table, and finally the columns being audited.

On the Query Navigator, the highest level is the Audit Group (Functional Group). The next level is the audit table, and below the audit table are the actual data being audited.

Before using this window, perform the following:

Audit Query Navigation Procedures

Navigate to the Audit Query window.

To use the Find Functional Groups function:

  1. Use the tree navigator to view the tables names.

  2. Select the table name and right-click to display the pop-up menu.

  3. Select Find. The Find Function Groups window displays.

  4. Select criteria and click Find. A list of templates displays. You can save these as a new audit.

To view the Audit Results window:

  1. Use the tree navigator to view the column names.

  2. Select a column name. The Audit Results window automatically displays.

  3. Use the Horizontal View and Vertical View buttons to toggle between the two views.

    In the horizontal view, you see the first ten auditing columns. In the vertical view, the column number is unlimited and can be viewed using the scroll bar.

Audit Report

In situations where comprehensive documentation is needed (such as to support legal or regulatory requirements), a single report request resulting in a single comprehensive report is desirable. This report can then be printed, sent by email, or archived.

Since this report could involve a considerable amount of data, a detailed parameter screen is available, allowing you to select only the items of interest.

Submitting the Report

  1. Navigate to the Audit Report window. The Enter Report Parameters window is displayed.

  2. Select the functional group, or a functional group and audit table name.

  3. Complete the optional fields as necessary.

  4. Click Select Columns. The Select Reporting Columns window is displayed.

  5. Enter at least one column to run the report. The columns displayed are based on the functional group, or a functional group and audit table name criteria selected on the Enter Report Parameters window.

  6. Select Print Options. The Select Printing Options window is displayed.

  7. Enter the necessary print information.

  8. Click OK.

  9. Run the report by selecting Run Report.

Enter Report Parameters Field Reference

Report Parameters Field Descriptions
Field Description
Functional Group Specify the name of the functional group for the report. This is the same as the Audit Group field on the Audit Group window in System Administration.
Audit Table Name Optional. Specify the table name from the functional group for the report.
Transacted By Optional. Specify the user who is requesting the report.
Transaction Type Optional. Specify the type of transaction.
From Date Optional. Specify the beginning date for the date range the report will run.
To Date Optional. Specify the end date for the date range the report will run.

Monitor Users Window

Use this window to monitor what your application users are currently doing.

Monitor Users Window

the picture is described in the document text

As well as seeing which users are signed on, you can see:

In addition, you can monitor all users at a site, all users accessing a specific application, or a specific responsibility, or individual users.

Note: You can only monitor those users for whom you have activated Sign-On Audit. See: Enabling Sign-On Audit.

Before using this form, select a value for the Sign-On:Audit Level profile option, using the Update System Profile Options Window.

To access, navigate to System Administration > User Monitor.

Monitor Users Window Field Descriptions
Field Description
User Name The user's login name.
Responsibility The user's responsibility only appears if you have enabled Sign-On Audit at either the Responsibility or Form audit level.
Form The user's form only appears if you have enabled Sign-On Audit at the Form audit level.
Time The length of time the user has been logged on to this application.
Process The ORACLE process of the user.
Client IP Address The IP address of the client server.

Audit Installations Window

Use this window to enable Audit Trail for an Oracle database user name at your installation. Such a user name grants access privileges to an application's tables and database objects.

Audit Installations Window

the picture is described in the document text

For auditing to take effect, you must also define one or more audit groups and run the Audit Trail Update Tables report. See: Reporting on Audit Trail Data.

Before using this form, ensure that the desired Oracle user name is registered. The installation process automatically registers Oracle E-Business Suite Oracle user names, but if you create a custom application, you should follow the instructions in My Oracle Support Knowledge Document 1577707.1, Creating a Custom Application in Oracle E-Business Suite Release 12.2, for Oracle user names for custom applications.

Audit Installations Window Element Descriptions
Element Description
Oracle Username Select the Oracle user name that owns the tables you wish to audit.
Audit Enabled Select the Audit Enabled check box to enable Audit Trail for an Oracle user name. Before auditing takes effect, you must define one or more audit groups and run the Audit Trail Update Tables report.

Audit Groups Window

Use this window to select the tables that you wish to audit. You audit a table by defining an audit group, which can consist of one or more tables.

Audit Groups Window

the picture is described in the document text

First, identify the tables you want to audit. Then, using the Audit Tables window, select which columns in each table you wish to audit. Or, select which columns in a particular table you wish to audit (using the Audit Tables window), then define your audit group (using this window).

To enable or disable auditing for the tables in your audit group, run the Audit Trail Update Tables program using the Submit Requests window. If you change the definition or audit state of your group later, you must rerun this program.

Before defining your audit groups, make sure you have defined an audit installation using the Audit Installations window.

Note: Your tables and their primary key information must already be registered and defined for successful auditing. If the table you want to audit is a custom table (not shipped as part of Oracle E-Business Suite), you should also perform the following two steps:

Audit Groups Block

Identify your audit group and enable or disable auditing for this group.

Application Name

Select the name of an application to associate with your audit group. The combination of application name and group name uniquely identifies your audit group. An audit group may be used to audit tables in additional applications.

Audit Group

Enter the name of the audit group.

Group State

Choose Enable Requested if you are defining a new audit group. When you run the Audit Trail Update Tables report, the concurrent program creates database triggers for the tables in your audit group. Once you have run the program, this field displays Enabled for audit groups where AuditTrail is active.

Important: All primary key columns in each table in an audit group are automatically selected for auditing, whether or not you use the Audit Tables window to select which columns you wish to audit.

To disable auditing for a group, choose one of the following options and then run the Audit Trail Update Tables report to have your changes take effect.

Variable Description
Disable - Prepare for Archive Copies the current values of all rows in the audited table into the shadow table, and then disables the auditing triggers. This option requires the most space, since there is at least one row in the shadow table for every row in the audited table (and another row in the shadow table for each transaction on the original row in the audited table). You should then archive the table before you empty the shadow table.
Disable - Interrupt Audit Modifies the triggers to store one final row in the shadow table as the audited row is modified in the audit table (remember that a given row in the shadow table represents the data in the audited row before an update). Inserts or further changes are no longer audited. The shadow table then grows slowly, and the data may be accessed by the existing audit views.
Disable - Purge Table Drops the auditing triggers and views and deletes all data from the shadow table.

Audit Tables Block

Identify the application tables you want to audit in your audit group.

User Table Name

Select the end user table name (frequently the same name as the table name) for your database table. Once you choose a table, you see its table name and associated application.

Table Name

This field displays the actual name for the table you have selected to include in your audit group.

Application

This field displays the application name for the table you have selected to include in your audit group.

Description

This field displays the description for the table you have selected to include in your audit group.

Audit Tables Window

Use this window to select which columns in a table you wish to audit.

Audit Tables Window

the picture is described in the document text

First, identify the columns in a table you want to audit. Then, using the Audit Groups window, include the table as part of an audit group. Or, you may define your audit group first (using the Audit Groups window), and then select which columns in the table you want to audit (using this window).

To enable or disable auditing for the tables in your audit group (i.e., the columns you have selected here), you must run the Audit Trail Update Tables program using the Submit Requests window. If you select additional columns to audit, or change the definition or audit state of your group later, you must rerun this program.

Before defining your audit tables, make sure that you have defined an audit installation using the Audit Installations window.

Important: Your tables and their primary key information must already be registered and defined for successful auditing. If the table you want to audit is a custom table (not shipped as part of Oracle E-Business Suite), you should also perform the following two steps:

Define Audit Tables Block

Identify the application table you want to audit. Successively selecting Go - Next Record from the menu or toolbar displays, in alphabetical order, the name of each application table registered at your installation site.

User Table Name

Select the end user table name (frequently the same name as the table name) for your database table. Once you choose a table, you see its table name and associated application.

Table Name

This field displays the actual name for the table you have selected to include in your audit group.

Application

This field displays the application name for the table you have selected to include in your audit group.

Audit Columns Block

Select the columns you want to audit. Successively selecting Go - Next Record from the menu or toolbar displays, in alphabetical order, the name of each application table registered at your installation site.

Column Name

Enter the name of the database column you want to audit. You should not explicitly enter the names of your table's primary key columns, since they are entered automatically, and you will get an error message if you try to save a duplicate column name. You can query to see which columns appear automatically.

Note that once you have chosen a column, you cannot delete it from the audit set, though you may add other columns to the set later.

Once you choose a column, you see its column type and whether it is part of the primary key for this table.

Column Type

This field describes the type of data the column stores, for example, varchar2.

Primary Key

This field displays Yes or No indicating whether the column you are auditing is a primary key column.

Any primary key columns you do not select to audit are automatically included when you save your column selections. For example, if the table you are auditing has two primary key columns, and you choose to audit one of them, the second primary key column is automatically selected when you save your column selections.

Audit Trail Search Pages

The Audit Trail Search pages allow you to query audit trail setup tables without launching the Forms-based Audit Trail windows.

You can navigate to these pages from the Auditing Manager responsibility. Choose Audit Trail Search from the menu.

Audit Trail Search Page

the picture is described in the document text

Search Audit Groups

You can search on any of the following criteria:

The search will retrieve the Master Record from the FND_AUDIT_GROUPS table; all audit groups that satisfy your search criteria will be returned.

In the Audit Groups search results table, the Group Name for each record is a link. This link allows you to get the rest of the details for the particular audit group.

Audit Group

This page lists the audit tables that are defined for the audit group. For each table, the following is shown:

Clicking on the Table Name link allows you to drill down for more information on that table.

Audit Table

This page lists the columns that are being audited for a particular table.

For each column, the Column Name and Column Type is listed.