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.
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:
Use the UnsuccessfulLogins.sql script provided My Oracle Support Knowledge Document 2069190.1, Security Configuration and Auditing Scripts for Oracle E-Business Suite.
Run the Signon Audit Unsuccessful Logins report.
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 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;
Oracle E-Business Suite provides a Sign-On Audit feature that allows you to:
Track what your users are doing, from where, 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.
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.
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.
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.
No activities by any users who sign on to Oracle E-Business Suite
Who signs on to your system
The times users log on and off
The terminals in use
Client IP addresses (starting with Release 12.2.10 and later or R12.ATG_PF.C.Delta.9)
The responsibilities users choose, including both responsibilities for Forms-based applications and responsibilities for HTML-based applications built with Oracle Application Framework or Oracle CRM Technology Foundation
How much time users spend using each responsibility
Note: For HTML-based applications, auditing tracks the time users spend in a responsibility context. Note that the responsibility context does not change when a user navigates back to the Oracle E-Business Suite home page from an HTML-based application page. The context changes only when the user goes to a page with a different responsibility.
The forms users choose
How long users spend using each form
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.
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.
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
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
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 user name 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 gives you online, real-time information about Oracle E-Business Suite Forms and HTML-based session users. You can see:
What users are signed on (by application user name and operating system login name)
Which responsibilities, forms (windows), and terminals they are using
How long they have been logged in
What Oracle database processes they are using
The IP address of the client machine (starting with Release 12.2.10 and later or R12.ATG_PF.C.Delta.9)
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:
To access the Monitor Users Forms-based application, navigate to navigate to System Administrator > Security > User > Monitor. Before using this form, select a value for the Sign-On:Audit Level profile option, using the Update System Profile Options window.
Release 12.2.10 introduces the HTML-based Monitor Users application. To view this, navigate to System Administration > User Monitor.
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.
Purge session information using the Purge Inactive Session (FNDDLTMP) concurrent program. This program purges all information for inactive sessions from the following tables:
ICX_SESSIONS
ICX_SESSION_ATTRIBUTES
ICX_TRANSACTIONS
ICX_TEXT
ICX_CONTEXT_RESULTS_TEMP
FND_SESSION_VALUES
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 400 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.
Purge end-user access data using the Purge Sign-On Audit Data (FNDSCPRG) concurrent program. This 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.
R12.ATG_PF.C.Delta.10 introduces a new retention period that specifies how much data from the previously listed tables should be retained. This defaults to 400 days. Note that in R12.ATG_PF.C.Delta.10, the previous date concurrent parameter is no longer visible.
This concurrent program purges the following tables:
FND_LOGIN_RESP_FORMS
FND_LOGIN_RESPONSIBILITIES
FND_LOGINS
FND_UNSUCCESSFUL_LOGINS
FND_APPL_SESSIONS
The following data is deleted:
Data for who signs on and for how long
Data for who is selecting what responsibility and when they do it
Data for who uses which forms in an application and when
This program will delete all Sign-On Audit information created before specified by the audit date parameter.
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:
Sign-On Audit Concurrent Requests: shows concurrent requests and who submitted it
Sign-On Audit Forms: shows all Forms and who accessed each Form
Sign-On Audit Responsibilities: shows all responsibilities and who used it
Sign-On Audit Unsuccessful Logins: shows the user id and the date of any unsuccessful logins
Sign-On Audit Users: shows who signed on
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.
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.
APPLSYS.FND_LOGINS
APPLSYS.FND_LOGIN_RESPONSIBILITIES
APPLSYS.FND_LOGIN_RESP_FORMS
APPLSYS.FND_UNSUCCESSFUL_LOGINS
ICX.ICX_FAILURES
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 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.
Enable and disable Page Access Tracking
Configure the granularity to which application context information is captured
View reports on the gathered data
Access reports for a given application, responsibility and/or user across the Oracle Applications Framework, JTF, and Forms tech stacks
Page Performance reports per application tier node
Page access flow chart for a given user session
Search reports based on several filter criteria
Perform the following steps as described to configure Page Access Tracking for your site.
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
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.
(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.
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.)
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 - Collected information from the Session Information setting 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 (such as POST, GET, PUT, HEAD), 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.
Apply your changes.
To apply the configuration changes that you have made, restart all the JVMs that use or will use Page Access Tracking.
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.
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.
Locate the UI.
To reach the Page Access Tracking Reports page in Oracle Applications Manager, go to: Site Map, the select Monitoring, then Applications Usage Reports, and then Page Access Tracking and Sign-on Audit Reports.
View reports and report details.
The default report provides statistical summaries for the past week of application usage as well as application 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 Objects and 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.
Application 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.
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.
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.
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:
CLIENT_IDENTIFIER - The client for a particular database session. The value allows the end user of that database connection to be identified. For context-insensitive standalone modules such as FNDLOAD or FNDCPASS, the value of CLIENT_IDENTIFIER is set to 'SYSADMIN'.
MODULE - Name of the currently executing module. The value indicates the application code where the database workload originates, and consequently allows identification of the specific application code (such as a user interface, program, or web service) that is currently using the connection.
ACTION - Name and context of the currently executing business action; for example, a payroll task being undertaken by a particular responsibility.
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 /
Note: This section covers the profile settings required to write recommended auditing and logging information to log files. For settings and requirements to write logging messages to the screen, see "Enabling Logging to Screen in Oracle Application Framework Pages" in the Oracle E-Business Suite Maintenance Guide.
The Oracle E-Business Suite debug log set by the profile option "FND: Debug Log Enabled" is used for a variety of purposes. As the name suggests, it is often used for diagnosing and debugging problems encountered in Oracle E-Business Suite code. This log can assist with diagnosing security problems, detecting security attacks, and can also be leveraged for post-attack, or forensic analysis, or both.
The debug log is documented in "Using Oracle Application Object Library Profile Options to Configure Logging" in the Oracle E-Business Suite Maintenance Guide. The current recommendation is to set FND: Debug Log Enabled 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:
| Profile Option Name | Code (Internal Name) | Value |
|---|---|---|
| FND: Debug Log Filename | AFLOG_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 by using a SQL injection attack.
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.