Auditing and Monitoring

Monitor Users

Auditing User Activity

Oracle E-Business Suite provides a Sign-On Audit feature that allows you to:

Track what your users are doing and when they do it.
Choose who to audit and what type of information to audit.
View quickly online what your users are doing.
Check the security of your application.

With Sign-On Audit, you can record usernames, terminals, and the dates and times your users access Oracle E-Business Suite. Sign-On Audit can also track the responsibilities and forms your users use, as well as the concurrent processes they run.

Major Features

Selective Auditing

Sign-On Audit lets you choose who to audit and what type of user information to track. You can selectively determine what audit information you need, to match your organization's needs.

Monitor Application Users

The Monitor Users form gives you online, real-time information about who is using Oracle E-Business Suite and what they are doing.

You can see what users are signed on (application username and operating system login name), what responsibilities, forms, and terminals they are using, how long they have been working on forms, and what Oracle database processes they are using.

Sign-On Audit Reports

Sign-On Audit Reports give you historical, detailed information on what your users do in your application.

You can give search criteria to narrow your search for information. You can also sort your Sign-On Audit information to create easy-to-read reports.

Setting Up Sign-On Audit

You use the Sign-On:Audit Level user profile option to control who Sign-On Audit tracks and the level at which they are audited.

Use the Monitor Users form to view online what your users are doing.

Use the Submit Reports form to submit Sign-On Audit Reports that give you detailed audit information.

Enabling Sign-On Audit

Use the System Profile Values form to enable Sign-On Audit. Choose the scope of your audit and who to audit by setting the user profile level at the user, responsibility, application, or site profile levels.

Note: Users cannot see or change this profile option.

After you set or change audit levels, the new audit levels for a user take effect the next time the user signs onto Oracle E-Business Suite from the operating system.

Selecting Audit Levels

The Sign-On:Audit Level profile option allows you to select a level at which to audit users who sign on to Oracle E-Business Suite.

Four audit levels provide increasing levels of monitoring: None, User, Responsibility, and Form.

Auditing level None is the default, and tracks:

No activities by any users who sign on to Oracle E-Business Suite

Auditing at the User level tracks:

Who signs on to your system
The times users log on and off
The terminals in use

Auditing at the Responsibility level performs the User level audit functions and also tracks:

The responsibilities users choose
How much time users spend using each responsibility

Auditing at the Form level performs the Responsibility and User level audit functions, and also tracks:

The forms users choose
How long users spend using each form

Auditing Levels and System Overhead

In planning your organization's Sign-On Audit implementation, you should consider the additional system overhead required to monitor and audit your users as they access Oracle E-Business Suite. The more users you audit, and the higher the level of auditing, the greater the system overhead such as processing costs and disk space. You should balance your organization's auditing needs with the resources available, obtaining additional resources if the existing ones are insufficient to support the required auditing activities as well as the actual workload.

Example - Audit Users, Responsibilities, and Forms

An example implementation of Sign-On Audit would be to audit all of your users' sign-ons, the responsibilities they select, and the forms they access.

To accomplish this, you would set Sign-On:Audit Level to:

Form audit
At the Site profile level

Example - Audit a specific responsibility, except for one user

Another example of using Sign-On Audit is for an organization to audit all users of the Personnel Manager responsibility, except for MJONES.

In this example, you do not need to audit the forms the user accesses, or the responsibilities they select.

To set up this implementation, set Sign-On:Audit Level to:

User audit
At the responsibility profile level for the Personnel Manager responsibility

You also set Sign-On:Audit Level to:

None
At the user profile level for the application user MJONES

Using the Application Monitor

Use the Monitor Users form to monitor who is using Oracle E-Business Suite and what they are doing. You can monitor users at any time.

The Application Monitor lets you see what users are signed on, what responsibilities, forms, and terminals they are using, how long they have been working on forms, and what Oracle database processes they are using.

Important: You can only monitor those users that are being audited by Sign-On Audit. The Application Monitor also reflects the level of auditing you define for your users.

Notifying of Unsuccessful Logins

Sign-On Audit can track user logins and provide users with a warning message if anyone has made an unsuccessful attempt to sign on with their application username since their last sign-on. This warning message appears after a user signs on.

You or your users can activate this feature using the Personal Profile Values form by setting the "Sign-On:Notification" user profile option to Yes.

You do not have to audit the user with Sign-On Audit to use this notification feature.

Sign-On Audit Reports

Use the Submit Requests form to print standard audit reports.

You can generate reports detailing which users are signing on; the responsibilities they are accessing; the forms they are using; concurrent requests they are submitting; and details of any attempts to log on to other users' accounts.

Oracle E-Business Suite provides the following Sign-On Audit reports:

Signon Audit Concurrent Requests (shows who submitted what requests)

Signon Audit Forms (shows who accessed what forms)

Signon Audit Responsibilities (shows who accessed what responsibilities)

Signon Audit Unsuccessful Logins (shows who unsuccessfully attempted to sign on as another user)

Signon Audit Users (shows who signed on to Oracle E-Business Suite)

For each report, you can also specify search criteria that makes your report as brief as you need.

Related Topics

Monitor Users

Reporting On AuditTrail Data

AuditTrail lets you keep a history of changes to your important data: what changed, who changed it, and when. With AuditTrail, you can easily determine how any data row or element obtained its current value. You can track information on most types of fields, including character, number and date fields.

When you enter or update data in your forms, you change the database tables underlying those forms. AuditTrail tracks which rows in the database were updated at what time, and which user was logged in using the associated form(s).

AuditTrail

Oracle E-Business Suite provides a auditing mechanism based on Oracle database triggers. AuditTrail stores change information in a "shadow table" of the audited table. This mechanism saves audit data in an uncompressed but "sparse" format, and you enable auditing for particular tables and groups of tables ("audit groups").

Audit Trail Update Tables Report

This program creates database triggers on the tables in your audit groups for your installations. It also creates shadow tables, one for each audited table, to contain the audit information. If you have changed your audit definitions or disabled auditing for an audit group, the program drops or modifies the auditing triggers and shadow tables appropriately.

The program also builds special views you can use to retrieve your audit data for reporting.

Changing Your Audit Tables

You may add additional columns to audit after auditing has begun on a table. However, the shadow table does not track the column changes that occurred before the column(s) were added. If you add columns you must rerun the AuditTrail Update Tables Report to:

Add the necessary column(s) to the shadow table
Regenerate the audit triggers and procedures for the table so that they now audit the additional column(s)

Related Topics

Setting Up AuditTrail

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 group tables to be audited. For a table to be audited, it must be included in an enabled audit group.

The steps for setting up AuditTrail are as follows.

Verify Select Privileges on SYS.DBA_TABLES

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.

Define Audit Groups

These 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. See: Audit Groups.

Define Audit Installations

You choose the registered Oracle IDs at your site that you want to audit. 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. See: Audit Installations.

Run the Audit Trail Update Tables Report to Enable Auditing

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

Important: AuditTrail requires two database connections. If your operating system does not automatically support two database connections (e.g. VMS or MPE/XL), then add to your environment file the environment variable FDATDB=<database connect string>.

AuditTrail Tables, Triggers and Views

When auditing is enabled for the first time, a shadow table to the audited table is automatically created in the same Oracle ID as the audited table. 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.

The name of the shadow table is the first 24 characters of the original table name plus the suffix "_A" (Audit).

Shadow Table Columns

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

AUDIT_USER_NAME (the Application User ID, except when changes are applied using SQL*Plus, in which case it is the Oracle ID).
AUDIT_TIMESTAMP (the date/time when the insertion occurred).
AUDIT_TRANSACTION_TYPE (I for Insert, U for Update, D for Delete, L for Last, and C for Current).
AUDIT_TRUE_NULLS (VARCHAR2(250) column containing a delimited list of column names that have changed from NULL).
The primary key for the table. This is not a special column, but rather all the columns comprising the primary key of the audited table. Note that, by convention, all audited columns are stored when a row is deleted. Likewise, an insert results in a row of NULL values in the shadow table. Changes to the primary key are marked as deletes, but new primary key values are inserted also.

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)

Auditing Triggers and Procedures

When auditing is enabled, the automatically-generated database trigger in the "After" event on the audited table performs the auditing.

This 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 AuditTrail 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.

Views

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.

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)

How Data Appears in Tables and Views

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 Information

Report on Your Audit Data

You should write audit reports as needed. AuditTrail 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.

Important: Because the structure of the audited table may change between product versions, AuditTrail 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.

Related Topics

Setting Up Release AuditTrail

Disabling AuditTrail and Archiving Audit Data

You may report on your audits or disable auditing at any time. When you disable auditing, you should do the following procedure:

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	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. There is no longer any recording of any changes. You should archive the shadow table before you purge it.
Disable - Interrupt Audit	Modifies the triggers to store one “final" row in the shadow table for each row that 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). If a row in the table being audited is changed again (a second time), that change is not recorded. The shadow table grows slowly, until it contains one row for each row in the table being audited. Then there is no longer any recording of any changes.

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. There is no longer any recording of any changes. You should archive the shadow table before you purge it.

Disable - Interrupt Audit

Modifies the triggers to store one “final" row in the shadow table for each row that 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). If a row in the table being audited is changed again (a second time), that change is not recorded. The shadow table grows slowly, until it contains one row for each row in the table being audited. Then there is no longer any recording of any changes.

Archive Your Audit Data

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

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	Description
Disable - Purge Table	Drops the auditing triggers and views and deletes all data from the shadow table.

Restart Auditing (If Desired)

You 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, AuditTrail 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.

Related Topics

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
Audit Hierarchy Navigator
Audit Query Navigator
Running the Audit Report

Audit Industry Template

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

Before using this window, perform the following:

Define Audit Tables and Audit columns using Oracle Application Audit under the System Administrator responsibility
Define Audit Groups using Oracle Application Audit under the System Administrator responsibility

Audit Industry Template Procedure

Use this procedure in completing the Industry Template.

Navigate to the Industry Template window.
Complete the fields as described.
Save your changes.

Audit Industry Template Fields

These are the fields in the Audit Industry templates.

Template Name

Enter 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 Editor

Auditing Navigation

In addition to the standard menu and toolbar, 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:

Define Audit Tables and Audit columns using the Oracle Application Audit under the System Administrator responsibility
Define Audit Groups using Oracle Application Audit under the System Administrator responsibility
Define Industry Audit Templates under the OPM System Administrator responsibility
Enable Audit Trail, a concurrent process under the System Administrator responsibility

Audit Hierarchy Navigation Procedures

Navigate to the Audit Hierarchy window.

To view table information:

Use the tree navigator to view the table names.
Select the table name and right-click to display the pop-up menu.
Select Display Columns. The Define Query Navigator Display for the Table window displays.

To use the Find Audit Hierarchy function:

Use the tree navigator to view the column names.
Select the column name and right-click to display the pop-up menu.
Select Find. The Find Audit Hierarchy window displays.
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 toolbar, a navigator tree provides a hierarchical display of the objects in a treelike framework.

Nodes and Leaves

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:

Define Audit Tables and Audit columns using the Oracle Application Audit under the System Administrator responsibility.
Define Audit Groups using Oracle Application Audit under the System Administrator responsibility.
Define Industry Audit Templates under the OPM System Administrator responsibility.
Define the display look up using the Audit Hierarchy Navigator (Admin Mode). This setup step is not mandatory.
Enable Audit Trail, a concurrent process under the System Administrator responsibility.

Audit Query Navigation Procedures

Navigate to the Audit Query window.

To use the Find Functional Groups function:

Use the tree navigator to view the table names.
Select the table name and right-click to display the pop-up menu.
Select Find. The Find Functional Groups window displays.
Select criteria and click Find. A list of templates displays. You can save these as a new audit.

To view the Audit Results window:

Use the tree navigator to view the column names.
Select a column name. The Audit Results window automatically displays.
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, (e.g. to support legal or regulatory requirements), a single report request resulting in a single comprehensive report is desirable. This report can then be printed, emailed, 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

Navigate to the Audit Report window. The Enter Report Parameters window is displayed.
Select the functional group, or a functional group and audit table name.
Complete the optional fields as necessary.
Click Select Columns. The Select Reporting Columns window is displayed.
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.
Select Print Options. The Select Printing Options window is displayed.
Enter the necessary print information.
Select OK.
Run the report by selecting Run Report.

Enter Report Parameters Field Reference

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.

the picture is described in the document text

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

Which responsibilities and forms (windows) they are using
How long they have been logged in
What Oracle database processes they are using

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: Overview of User and Data Auditing

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

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.

Login

The user's login name.

Time

The length of time the user has been logged on to this application.

Oracle Process

The ORACLE process of the user.

Related Topics

Audit Installations Window

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

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 AuditTrail Data.

Before using this form, register your Oracle username. See: ORACLE Users, Oracle E-Business Suite System Administrator's Guide - Configuration.

Oracle Username

Select the Oracle username that owns the tables you wish to audit.

Audit Enabled

Check the Audit Enabled check box to enable AuditTrail for an Oracle username. Before auditing takes effect you must define one or more audit groups and run the Audit Trail Update Tables report.

Related Topics

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.

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.

Ensure you have done the following before defining your audit groups:

Define 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:
Register your table and its primary key columns using Oracle Application Object Library's Tables window (Application Developer Responsibility).
Run the Register Tables concurrent program from the Submit Requests window.

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

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.

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.

Ensure the following is done before defining your audit tables:

Define 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:
Register your table and its primary key columns using Oracle Application Object Library's Tables window (Application Developer Responsibility).
Run the Register Tables concurrent program from the Submit Requests window.

Define AuditTables 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.

You cannot delete a column from auditing once it has been selected.
You may add additional columns to be audited.
Each time you select a column to be audited, that change affects every audit group that includes the table which owns the column.

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.

Related Topics

AuditTrail Tables, Triggers, and Views

Signon Audit Concurrent Requests Report

Use this report to view information about who is requesting what concurrent requests and from which responsibilities and forms.

Important: You can only generate Signon Audit Concurrent Requests Reports for those users you are auditing.

Report Parameters

Sort By

Sort the information in your report by operating system login name, the requested start date, and/or application username.

Login Name

Search for a specific login name that meets your other search criteria. If you leave this parameter blank, your report contains all login names that meet your other search criteria.

User Name

Search for a specific application username that meets your other search criteria. If you leave this parameter blank, your report contains all application usernames that meet your other search criteria.

From Request Start Time/To Request Start Time

Search for concurrent requests that meet your other search criteria and have requested start times in a specific time period. Use these parameters to specify the start and end of your time period. If you leave these parameters blank, your report contains concurrent requests from any date that also meet your other search criteria to the current date for this parameter.

Report Heading

The report heading displays the search criteria you entered as parameter values.

Column Headings

Login Name

The operating system login name of the user who submitted the concurrent request.

Request ID

The concurrent request ID of the submitted concurrent request. Use the Concurrent Requests form to view completion information for a concurrent request ID.

Concurrent Program Name

The name of the concurrent program the user submitted. Use the Concurrent Programs form to view detail information about a concurrent program.

User Name

The Oracle E-Business Suite username of the user who submitted the concurrent request. Use the Users form to view detail information about an application user. See: Users.

Responsibility Name

The name of the responsibility from which the user submitted the concurrent request. The responsibility displays only if you audited the user at the responsibility or form Sign-on Audit level. Use the Responsibilities form to view detailed information about a responsibility. See: Responsibilities.

Form Name

The name of the form from which the user submitted the concurrent request. The form name displays only if you audited the user at the form Sign-On Audit level.

Requested Start Time

The date and time the concurrent request started running.

Related Topics

Signon Audit Forms Report

Use this report to view who is navigating to what form and when they do it.

Important: You can only generate a Signon Audit Forms Report for those users you are auditing.

Report Parameters

Sort By

Sort the information in your report by the time users entered or left a form, the name of the form that users access, the operating system login name of the user, the responsibility users access, the terminal that users are on, and/or the application username.

Login Name

Search for information about a specific login name that meets your other search criteria. If you leave this parameter blank, your report contains all login names that meet your other search criteria.

User Name

Search for information about a specific application username that meets your other search criteria. If you leave this parameter blank, your report contains all application usernames that meet your other search criteria.

Terminal Name

Search for information about a specific terminal that meets your other search criteria. If you leave this parameter blank, your report contains all terminal names that meet your other search criteria.

Responsibility Name

Search for information about a specific responsibility that meets your other search criteria. If you leave this parameter blank, your report contains all responsibilities that meet your other search criteria.

Form Name

Search for information about a specific form that meets your other search criteria. If you leave this parameter blank, your report contains all forms that also meet your other search criteria.

From Active Date/To Active Date

Search for information about forms accessed by users within a specific time period and that meet your other search criteria. Use these parameters to specify the start and end of your time period. If you leave these parameters blank, your report contains forms accessed from any date that also meet your other search criteria to the current date for this parameter.

Report Heading

The report heading displays the search criteria you entered as parameter values.

Column Headings

Username

The Oracle E-Business Suite username of the user who accessed the form. Use the Users form to view detailed information about an application user. See: Users.

Login Name

The operating system login name of the user who accessed the form.

Terminal Name

The operating system ID of the terminal from which the user accessed the form.

Responsibility Name

The name of the responsibility from which the user accessed the form. The responsibility displays only if you audited the user at the responsibility or form Sign-on Audit level. Use the Responsibilities form to view detailed information about a responsibility. See: Responsibilities.

Start Active Time/End Active Time

The dates and times when the user accessed/exited the form. The start active time and end active time display only if you audited the user at the form Sign-on Audit level.

Form Name

The name of the form that the user accessed. The form name displays only if you audited the user at the form Sign-on Audit level.

Related Topics

Signon Audit Responsibilities Report

Use this report to view who is selecting what responsibility and when they do it.

Important: You can only generate Signon Audit Responsibilities Reports for those users you are auditing.

Report Parameters

Sort By

Sort the information in your report by the time users entered or left a responsibility, the operating system login name of the user, the responsibility name, the terminal that users are on, and/or the application username.

Login Name

Search for information about a specific login name that meets your other search criteria. If you leave this parameter blank, your report contains all login names that meet your other search criteria.

User Name

Terminal Name

Responsibility Name

From Active Date/To Active Date

Search for information about responsibilities accessed by users within a specific time period and that meet your other search criteria. Use these parameters to specify the start and end of your time period. If you leave these parameters blank, your report contains responsibilities accessed from any date that also meet your other search criteria to the current date for this parameter.

Report Heading

The report heading displays the search criteria you entered as parameter values.

Column Headings

Username

The Oracle E-Business Suite username of the user who selected the form. Use the Users form to view detail information about an application user. See: Users.

Login Name

The operating system login name of the user who selected the responsibility.

Terminal Name

The operating system ID of the terminal from which the user selected the responsibility.

Responsibility Name

The name of the responsibility the user used. The responsibility displays only if you audited the user at the responsibility or form Sign-on Audit level. Use the Responsibilities form to view detailed information about a responsibility. See: Responsibilities.

Start Active Time/End Active Time

The dates and times when the user selected/exited the responsibility. The start active time and end active time display only if you audited the user at the responsibility or form Sign-On Audit level.

Related Topics

Signon Audit Unsuccessful Logins Report

Use this report to view who unsuccessfully attempted to sign on to Oracle E-Business Suite as another user. An unsuccessful login occurs when a user enters a correct username but an incorrect password.

You can generate Signon Audit Unsuccessful Logins Reports for any users, regardless of whom you are auditing.

Report Parameters

Sort By

Sort the information in your report by the time users attempt to login, operating system login name of the user, the terminal that users are on, and/or the application username.

Login Name

Search for information about a specific login name that meets your other search criteria. If you leave this parameter blank, your report contains all login names that meet your other search criteria.

User Name

Terminal Name

Search for information about a specific terminal that meets your other search criteria to make your report as brief as you need. If you leave this parameter blank, your report contains all terminal names that meet your other search criteria.

From Attempt Date/To Attempt Date

Search for information about unsuccessful logins within a specific time period and that meet your other search criteria. Use these parameters to specify the start and end of your time period. If you leave these parameters blank, your report contains unsuccessful logins from any date that also meet your other search criteria to the current date for this parameter.

Report Heading

The report heading displays the search criteria you entered as parameter values.

Column Headings

Username

The Oracle E-Business Suite username of the user who unsuccessfully tried to sign on. Use the Users form to view detail information about an application user. See: Users.

Login Name

The operating system login name of the user who unsuccessfully tried to sign on.

Terminal

The operating system ID of the terminal from which the user unsuccessfully tried to sign on.

Attempt Time

The date and time when the user unsuccessfully tried to sign on. See: Monitor Users.

Related Topics

Signon Audit Users Report

Use this report to view who signs on and for how long.

Important: You can only generate Signon Audit Users Reports for those users you are auditing.

Report Parameters

Sort By

Sort the information in your report by the time users start or finish using an application username, the operating system login name of the user, the terminal that users are on, and/or the application username.

Login Name

Search for information about a specific login name that meets your other search criteria to make your report as brief as you need. If you leave this parameter blank, your report contains all login names that meet your other search criteria.

User Name

Search for information about a specific application username that meets your other search criteria to make your report as brief as you need. If you leave this parameter blank, your report contains all application usernames that meet your other search criteria.

Terminal Name

From Active Date/To Active Date

You can search for information about users logged into Oracle E-Business Suite within a specific time period and that meet your other search criteria. Use these parameters to specify the start and end of your time period. If you leave these parameters blank, your report contains user information from the first date that also meets your other search criteria to the current date.

Report Heading

The report heading displays the search criteria you entered as parameter values.

Column Headings

Session Number

The Oracle E-Business Suite session number that uniquely identifies each application user sign-on.

User Name

The Oracle E-Business Suite username of the user who signed on. Use the Users form to view detailed information about an application user. See: Users.

Login Name

The operating system login name of the user who signed on.

Terminal Name

The operating system ID of the terminal from which the user signed on.

Start Active Time/End Active Time

The dates and times when the user signed on and off from Oracle E-Business Suite. The start active time and end active time display only if you audited the user at the user Sign-On Audit level.

Oracle Process

The Oracle database process ID used during the user's sign-on. Consult your database administrator for more information concerning Oracle processes.

System Process

The operating system process ID used during the user's sign-on. Consult your operating system administrator for more information concerning your operating system process ID.

Related Topics