Using Oracle E-Business Suite Application Auditing and Logging Features

Introduction

This chapter describes how to configure and use Oracle E-Business Suite audit and logging features. It provides an explanation of the features available, configuration steps and best practices for auditing. It also suggests which common application objects (such as foundation objects, users, and responsibilities) to audit.

Unsuccessful Login Attempts

Oracle E-Business Suite automatically stores unsuccessful local logon attempts in APPLSYS.FND_UNSUCCESSFUL_LOGINS. This functionality cannot be disabled. Only names of valid users in FND_USERS will be recorded. Unsuccessful logins are not recorded by Oracle E-Business Suite if you have integrated with Oracle Access Manager for single sign-on.

The following options are available for reviewing the information recorded by Unsuccessful Login Attempts:

Data Changes Tracked with Who Columns

Oracle E-Business Suite tracks data changes automatically within a record. For most Oracle E-Business Suite tables, database rows are updated with the creation and last update information. The system stores this information in the following columns known as "Who Columns":

Who Column Descriptions
Who Column Name Description
CREATION_DATE Date and Time row was created
CREATED_BY Oracle Applications user ID from FND_USER
LAST_UPDATE_LOGIN Login ID from FND_LOGINS
LAST_UPDATE_DATE Date and Time row as last updated
LAST_UPDATED_BY Oracle Applications user ID from FND_USERS

Join with FND_USERS table to identify the application user tracked in the audit record. Or join with FND_LOGINS for the associated login record. Note that only the last update to record is saved.

For example, if you want to see the Who column information of changes to Profile Values for the last ten days, you could use the following SQL:

select p.profile_option_name "Internal name",

        fpv.PROFILE_OPTION_VALUE value,

        cr.user_name "Created",

        to_char(fpv.creation_date,'DD-MON-RRRR HH24:MI:SS') "Creation Date",

        upd.user_name "Updated",

        to_char(fpv.last_update_date,'DD-MON-RRRR HH24:MI:SS') "Update Date",

        to_char(ll.start_time,'DD-MON-RRRR HH:MI:SS') "Login Time"

from fnd_profile_options p,

         fnd_profile_option_values fpv,

     fnd_user upd,

     fnd_user cr,

     fnd_logins ll

where p.profile_option_id = fpv.profile_option_id (+)

and fpv.last_updated_by=upd.user_id (+)

and fpv.created_by=cr.user_id (+)

and fpv.last_update_login=ll.login_id (+)

and fpv.last_update_date > sysdate-10;

Sign-On Audit

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

Note: Most of the Sign-On Audit reports are specific to the Oracle Forms interfaces. Information about Oracle Application Framework (OAF) and Oracle CRM Technology Foundation (JTF) session information can be found through the SQL queries and Page Access Tracking described in the following sections.

With Sign-On Audit, you can record user names, terminals, and the dates and times your Oracle Forms 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. You also have the ability to monitor user activity and submit reports for detailed audit information. Use the Submit Reports form to submit Sign-On Audit Reports that give you detailed audit information.

Sign-On Audit Reports give you historical, detailed information on what your Forms 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. You will only generate reports for users that are being audited by Sign-On Audit.

Enabling 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. 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.

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.

Select Audit Levels

The Sign-On:Audit Level profile provides varying levels of Oracle E-Business Suite sign-on information. Based on the audit level chosen, Sign-On audit records user names, dates, and times of system access, as well as users' terminals, Forms, and responsibilities.

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

All user logins, responsibility selections, and form accesses will be logged to APPLSYS.FND_LOGINS, APPLSYS.FND_LOGIN_RESPONSIBILITIES, and APPLSYS.FND_LOGIN_RESP_FORMS, respectively.

Auditing Level None Tracks:

Auditing at the User Level Tracks:

Auditing at the Responsibility Level Performs the User Level Audit Functions and Also Tracks:

Auditing at the Form Level is Default, Performs the Responsibility and User Level Audit Functions and Also Tracks:

It is recommended to set "Sign-On:Audit Level" to "Form" so the application will track every log on to the application, every responsibility used by the users, and every form opened by the users. This profile option tracks Forms usage only.

Audit 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:

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:

You also set Sign-On:Audit Level to:

Notification Upon 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.

Monitor Users

Monitor Users gives you online, real-time information about Oracle E-Business Suite Forms and HTML-based session users. You can see:

Monitoring features include current and historic user activity down to the page access level and current and historical Concurrent Manager activity.

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

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.

Within the user interface, click Audit Cleanup to submit the concurrent program Disable Inactive Sessions (which is described in the following section).

There are two methods to access the Monitor Users application:

Disabling Inactive Sessions

Use the Disable Inactive Sessions concurrent program to end sessions that may have been abandoned and exceed the ICX Limit Time. Run this concurrent program on a daily basis.

Disable Inactive Sessions can be submitted by clicking Audit Cleanup within the Monitor Users user interface. It can also be submitted by using the Submit Request form.

If user activity times out, the existing audit data associated with the session will be end dated. If the user re-authenticates and resumes the session, then the audit data will be updated on access. Any open forms and responsibilities will remain end dated until they are re-accessed.

Purging Session Information

Purge session information using the Purge Inactive Session (FNDDLTMP) concurrent program. This program purges all information for inactive sessions from the following tables:

R12.ATG_PF.C.DELTA.9 introduces a new retention period for ICX_SESSIONS that specifies how much data from ICX_SESSIONS should be retained. This defaults to 180 days.

The Purge Inactive Session concurrent program also deletes unsuccessful login information older than 30 days from the ICX_FAILURES table.

The program also end dates any pending audit data associated with the session - see Disabling Inactive Sessions.

Purging Sign-On Audit Data

Purge end-user access data using the Purge Sign-On Audit Data concurrent program. The current program purges all Sign-On Audit information created before a specified date. Run this concurrent program between once a week and once a month, retaining 30 to 90 days of records.

This concurrent program purges the following tables:

The following data is deleted:

This program will delete all Sign-On Audit information created before specified by the audit date parameter.

Sign-On Audit Reports

Oracle E-Business Suite ships standard 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. The reports are accessible through the system administrator responsibility. Oracle E-Business Suite standard Sign-On Audit reports include the following:

For each report, you can also specify search criteria to customize the result set. Details regarding these reports are available in Appendix F: Sign-On Audit Concurrent Manager Reports.

Session Audit Information

Although there are no prebuilt reports for reporting on OAF and JTF based applications, quite a lot of information is captured automatically in the login and session tables. These tables can be queried to retrieve information on what is currently happening in the system, or what was recently done.

See My Oracle Support Knowledge Document 2069190.1, Security Configuration and Auditing Scripts for Oracle E-Business Suite, for some example queries on these tables. These can be leveraged to determine what users are currently logged in, as well as what they are currently doing.

Page Access Tracking

Page Access Tracking in Oracle Applications Manager reports aggregate data for OAF- and JTF-based applications, with Sign-on Audit data gathered for Forms-based applications. Consequently, you can view usage flow paths that span all three technology stacks.

Page Access Tracking allows administrators to track application usage statistics and perform Web site traffic analysis, aggregate data about user flows as well as drill down to a particular user session.

Page Access Tracking transparently captures application-rich context information for every user click. In terms of performance, the data capture has negligible overhead.

In the Page Tracking Administration UI, You Can:

Examples of Available Reports:

Configuring Page Access Tracking

Perform the following steps as described to configure Page Access Tracking for your site.

  1. Navigate to the UI.

    Use the following navigation paths to reach the Page Access Tracking Configuration Page:

    Oracle Applications Manager: Site Map > Monitoring > Applications Usage Reports > Page Access Tracking and Sign-on Audit Configuration

  2. Turn Page Access Tracking on or off.

    On the Page Access Tracking Configuration page, you can enable or disable Page Access Tracking for your site. This is the primary site-level switch for Page Access Tracking -- setting this field to Off overrides any other configurations.

  3. (Optional) Configure logging for responsibilities or users.

    Optionally, you can configure logging according to user or responsibility. To do so, use Oracle Forms to set up the profile JTF_PF_ENABLED.

  4. Choose the applications to log.

    On the Page Access Tracking Configuration page, use the shuttle to move applications between the Disabled and Enabled lists. Only enabled applications will be logged. (Alternatively, this step can be performed through Oracle Forms by setting up the JTF_PF_ENABLED profile.)

  5. Select request attributes.

    On the Page Access Tracking Configuration page, you can choose how much data to collect by selecting one of the following combinations:

    • Session Information - which includes:

      • Page Information - Time stamp, JSP name (for example, jtflogin.jsp), JSP execution time, in milliseconds

      • Server Host Information - Host name, Apache port, Jserv port, Request method (POST, GET, PUT, HEAD, etc.), Return status (OK, Error, or Exception)

      • Session Context - Application ID, Responsibility ID, User ID, Language ID, Session ID

      • Client Browser Information - Client language, HTTP header, User-agent, Protocol, Referer, Authorization type

      • Client Language Information - Character coding, Language, Character set

    • Session Information and Cookies - Includes session information plus all incoming cookies.

    • Session Information, Cookies, and URL Parameters - Includes session information, all incoming cookies, and any GET parameters.

    • Session Information, Cookies, and All Parameters - Includes session information, all incoming cookies, any GET parameters, and any POST parameters.

    Oracle recommends that the Request Attributes be set to "Session Information" for normal auditing purposes. The other settings can end up logging sensitive data, and are only appropriate for diagnostic purposes.

    The "Session Information" setting collects the following information:

  6. Apply your changes.

    To apply the configuration changes that you have made, restart all the JVMs that use or will use Page Access Tracking.

Migrating Page Access Tracking Data

Page Access Tracking data is logged in the database in a staging area. It needs to be migrated and mined before the UI reports are refreshed. How frequently Page Access Tracking Data needs to be migrated and mined depends on how up-to-date the UI reports need to be. Generally speaking, the recommendation is to run the concurrent program at least once a day (for example at midnight) for the reports to be relatively up to date.

The "Page Access Tracking Data Migration" can be scheduled either in Oracle Forms, or within the OAM responsibility.

Schedule the concurrent program "Page Access Tracking Data Migration" using Concurrent Manager:

After the concurrent program completes, you should be able to view the latest data reports and statistics.

Viewing Page Access Tracking Reports

  1. Locate the UI.

    To reach the Page Access Tracking Reports page in Oracle Applications Manager, go to: Site Map > Monitoring > Applications Usage Reports > Page Access Tracking and Sign-on Audit Reports.

  2. View reports and report details.

    The default report provides statistical summaries for the past week of application usage and middle tier usage.

    Application Usage includes: Number of page hits, Sessions, Unique users, Unique applications, Unique responsibilities, and Languages.

    For each of these statistics, you can drill down to view the respective Details page.

    To see a table or graph of a complete session page flow, click an individual session ID on the Session Details page. The page indicates which tech stack each access belongs to. If during a session a user accesses both JSPs and Forms across tech stacks (Oracle Applications Framework and/or Oracle Forms), the Session Details page shows the complete flow across the tech stacks, from login to logout.

    Note: In the summary page, a tech stack of FORMS displays when the source is sign-on auditing. However, this data may be generated via Oracle Application Framework and Oracle CRM Technology Foundation technology stacks, and so get incorrectly reported as FORMS. The drill down correctly reports these as AUDIT.

    In session drill down, the login is sometimes repeated multiple times in the table. The graph view resolves this issue.

    Querying the Page Access Tracking tables directly may, depending on what your requirements are, be more convenient and provide a mechanism for preserving historical auditing data. This can be used to query and provide a record of users that access sensitive pages (for example, the sensitive administration pages referenced in My Oracle Support Knowledge Document 1334930.1, Sensitive Administrative Pages in Oracle E-Business Suite), or to track the UI page flow activities of applications administrators (for example, SYSADMIN).

    See My Oracle Support Knowledge Document 2069190.1, Security Configuration and Auditing Scriptsfor Oracle E-Business Suite, for some example scripts that query the Page Access Tracking tables directly.

    Middle Tier Usage includes: Host name, Server port, Number of JVMs, Average page execution time, Page hits, and Page failures.

    To define a specific date range, click Edit.

  3. Set the flush interval and maximum buffer size.

    Page Access Tracking data is buffered within each JVM and periodically asynchronously flushed to the database. The flush is triggered by the following site-level profiles:

    • JTF_PF_FLUSH_INTERVAL, which defines a time interval. The default value is 120 seconds.

    • JTF_PF_BUFFER_SIZE, which defines the maximum number of page log accesses in the buffer. The default value is 20 accesses.

    The data is flushed to the database when the specified time interval is reached, or when the number of page log accesses exceeds the configured buffer size. These parameters can be modified from their default values through Oracle Forms. The default values are used if the profiles are not set.

  4. Purge Page Access Tracking repository.

    The amount of data recorded by Page Access Tracking depends on what applications, responsibilities and users have tracking turned on, the tracking level selected and user traffic. On a regular basis, the System Administrator should purge the Page Access Tracking repository. The frequency of data purge also depends on how much historical tracking data is needed in the UI reports.

    Schedule the concurrent program "Page Access Tracking Purge Data" using Concurrent Manager.

Database Connection Tagging

The Database Connection Tagging feature utilizes several Oracle Database session attributes that allow Oracle E-Business Suite to record the database connection's current use. The Database Connection Tagging feature is controlled by the profile option FND: Connection Tagging (FND_CONNECTION_TAGGING).

By default, the profile option value is set to 'Enabled' (recommended), so Oracle E-Business Suite database connections are tagged with the information described in the previous section. If the feature is disabled, database connections will not be tagged and no information will be collected.

The CLIENT_IDENTIFIER, MODULE, and ACTION columns of the V$SESSION database table are used to track user and application context. These columns are populated as follows:

Note: This information is captured, and can be leveraged in Database Auditing, and will automatically be populated in Oracle Database Vault and Database Firewall if using that product.

You can also leverage this information for querying the v$session table for a specific FND user or for specific pages. For example you can query the v$session for SQL being currently by a current FND User, or for a particular page. See My Oracle Support Knowledge Document 2069190.1, Security Configuration and Auditing Scripts for Oracle E-Business Suite, for additional scripts that leverage the Database Connection Tagging in v$session.

Example:

Query select to_char(logon_time,'DD-MON-RRRR HH:MI:SS'),sid, client_identifier, module,action

from v$session

where client_identifier = '&fnd_user';

 

Results

LOGON_DATE             SID FND_USER   MODULE                                             ACTION

16-OCT-2015 05:49:39    50 JFROST     e:PER:fwk:per.selfservice.common.server.CommonAM   PER/EMPLOYEE_DIRECT_ACCESS_V4.0

 

16-OCT-2015 05:48:41   180 JFROST     e::fwk:fnd.framework.service.lookups.server.Look   /

Debug Logging (Unexpected Logging)

The Oracle E-Business Suite Debug Log is used for a variety of purposes. As the name suggests, it is often used to diagnosing and debugging problems encountered in Oracle E-Business Suite code. This log can also assist with diagnosing security problems and detecting security attacks. It can also be leveraged for post-attack / forensic analysis.

The debug log is documented in the Oracle E-Business Suite Maintenance Guide - Logging. The current recommendation in this chapter is to set this to "UNEXPECTED". This is important from a security auditing perspective, since this is the level at which many of the security errors are written out to the log.

The default configuration (and the current recommendation) for Debug Logging is set to log information to the database. This makes it easier to correlate and maintain logs in a multi-tier application environment.

The Debug Logging mechanism also supports logging to the file system using the following profile:

AFLOG_FILENAME FND: Debug Log Filename "/path/to/apps.log"

Customers may want to consider enabling logging on the file system, as there are some security benefits to maintaining the log on the file system. File system logging will generally provide better protection against an attacker being able to modify logging records via a SQL injection attack.

Oracle E-Business Suite Audit Trail

Oracle E-Business Suite has its own auditing mechanism called Audit Trail. Audit Trail lets you keep a history of changes to your important data: what changed, who changed it, and when. This feature keeps a complete history of changes made at a table and column level. With Audit Trail, 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 Oracle E-Business Suite, you change the database tables underlying those forms. Audit Trail tracks which rows in the database were updated at what time, and which user was logged in using the associated form(s).

Audit Trail 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").

Additional details for enabling the Audit Trail are found in Enabling Oracle E-Business Suite Audit Trail.