Aged Logins Analysis for Oracle E-Business Suite

This presents a User Responsibility Login Analysis aging report that analyzes when and how many times Oracle E-Business Suite (EBS) Users log on to their authorized Responsibilities.   You create the aging buckets, and then count how many times the User logs in to each Responsibility in each of the buckets.   The report is similar to aged accounts receivable reports that identify collections risks.

Why analyze login history?  Auditors look for excess user access rights, which presents an application governance risk.  Might your auditor’s findings include comments like these? 

“There are numerous Users with more access than required for efficient operation of the business.”

“Excessive EBS User-Responsibility assignments reflect ineffective internal controls.”

The report identifies usage over time, particularly which Users are not using Responsibilities.  This data enables you to remove the User’s access, and thus make the environment more secure and streamlined.

The report is a key component for periodic User Access Rights audits, also known as re-certification.

If you’d like to use the report for your organization’s periodic User Access Rights audits, it is available.  Contact Absolute Technologies for the report SQL source.

Periodic Re-Certification / User Access Rights Audit

Organizations have manual or automated processes for Users to request Responsibilities and Approvers to grant (assign) them.  The Approver may be a business process owner, super user, or departmental manager who is supposed to be familiar with the User’s job role and appropriate Responsibilities.   They may not be familiar with details of what a Responsibility enables. When approving a Responsibility assignment, hey may have to rely on the Responsibility Name, but that may be misleading.

User Access Rights audits generally include periodic reviews in which the auditor checks and re-certifies that the User should still be authorized for each of their assigned Responsibilities.  In our experience with EBS customers, the periodic User Access Rights audits are not automated, or are supported by spreadsheet and email tools that are not coupled with live EBS access data. 

Information Technology (IT) DBAs or Business Analysts may provide a list of User Responsibility Assignments in spreadsheet form to the Auditors.  Auditors then massage the data, present it to the Approvers, and wait for decisions. Auditors collate the decisions and produce the certification statement, as well as administration tickets to end date assignments and remove access. 

The aged User Responsibility Login Analysis report is a tool for Auditors that supports these re-certification and access rights audits.

Absolute Technologies Approach for the Analysis

An interactive reporting (IR) page is backed by an SQL query that analyzes the Sign On Audit data.  The auditor can select from a few default report variations or completely tailor what they want to see.  They can print or download the data, and even email it to others on a defined schedule.

Absolute’s ProView application platform includes a menu for auditors to research EBS Users and Responsibilities.  The set of interactive reporting pages simplifies the auditors’ work.  There is no need for the tedious navigation and lookup within EBS itself to answer audit context questions.  Requests for ad hoc reports or extracts from the auditors to IT are greatly reduced, reducing the duration of audits and workload on IT.

Selection of User Responsibility Logins Analysis calls up the analysis page with the sign on audit data interactive report.

The User Responsibility Login Analysis Page

This example screen shot of the User Responsibility Login Analysis page is an example of customer data for one Operating Unit and Responsibility. Scroll down to see the explanation of the parts of the page and interpretation of the representative data.

Customer Example of User Responsibility Login Analysis

Explanation of the Analysis Page

   1    The Parameter region includes Lists of Values to select and filter the report by Operating Unit, Responsibility Name, User Name, and a date picker for the Start Date beginning of the date windows. 

  • The example looks at the MX Operating Unit NEW operating unit’s .m GL JE Posting responsibility.
  • The Start Date default is the beginning of the current month a year ago.
  • No User selection, therefore all Users are in scope for the report.

There are pick lists to select the date window bucket in which the User’s Last Login occurred, and to select a range of the number of user responsibility logins over the entire analysis period.

   2    The Report Source and Most Recent Login among all logins are for informational purposes.  The default source is a Materialized View for faster interactive reporting.

   3    There are expandable help regions readily available for new or infrequent auditors.

   4    APEX Interactive Reports provide user settings to select saved report variations, as well as to filter, group and highlight the data.  In the example, the customer has excluded Responsibilities that only provide view (not update) or inquiry functionality. They may not consider it excess access if a User does not use these Responsibilities.

   5    Data and Interpretation  The report body includes the User and Responsibility names for each assignment.   You see the last login and how many days ago it was.  The example time windows are Within 30 Days, Between 31 and 180 Days, and Over 180 Days. 

We find the three aging windows sufficient for the aging analysis to determine the usage pattern and make decisions about the User’s actual need for the Responsibility.

Detail from page: Each row is from a different User-Responsibility assignment login history.

   1    No usage within 180 days. A possible candidate for removing the assignment.

   2    Usage within 30 days and a steady usage pattern in the last year.  Not candidates for removal.

   3    No usage within the past year. A definite candidate for removal.  The assignment should be end dated.

   4    No usage within 30 days, but some usage between 31 and 180 days.  Infrequent usage decreasing over the year.  Reconsider these assignments at the next re-certification.


The User Responsibility Login Analysis gives Auditors a data-driven method to identify where excess access is granted to EBS Users. 

  • It supports Approvers when they re-certify their prior decisions about Users and Responsibilities in their domain. 
  • Since the report is interactive and uses actual EBS data, it eliminates the need for IT support, spreadsheets, and email.  It can become a standard tool for User Access Rights Management. 
  • You can identify unused access according to your policies continuously, without waiting for the periodic audit.  This minimizes risk compared to going through the motions of an annual audit fire drill.
  • It is easy to identify Responsibilities no one uses, and inactivate them, further streamlining the EBS environment.

Contact Absolute Technologies for the report SQL source or the APEX page, or to discuss EBS user access rights management.  

by Skip Straus
Vice President and APEX Product Manager
Absolute Technologies, Inc.

Background Information

This background information is useful if you are not familiar with EBS technical details. The login history aging relies on a native EBS Profile Option feature to capture User Login history. EBS Responsibilities and Menus are the primary way organizations control access to EBS functional components for business transactions, viewing data, and configuring EBS.

EBS Sign On Audit

EBS includes a Profile Option to enable and configure which User Responsibility logins are recorded in a set of audit tables.  It’s called SIGNONAUDIT:LEVEL.  The tables are:

  • FND_LOGIN_RESP_FORMS, not used for this analysis.

We recommend auditing all Users and all Responsibilities.  This lets you examine the data over an extended period with the widest view of Users and Responsibilities, with the most analysis options via reporting tools.  The Profile Option allows more granular audits, but this would limit analysis and complicate reporting and interpretation.

EBS User Access Rights Through Responsibilities

Let’s look at access rights components in EBS and the process of periodic reviews of who can do what, through assigned Responsibilities.

User Access Rights Management

User access rights management is a dynamic balancing act in an EBS environment.  Give business Users access to transactions and data to accomplish their job duties, but limit access when the business User has no need to execute transactions or see data outside the scope of their jobs.  Users’ job duties and business conditions change over time.   “Use It or Lose It” is a best practice policy.  Monitor whether a User actually uses an authorized access right, and if they do not use it, remove the access right assignment. 

Responsibilities and Menus

The primary method to control user access in EBS is through Responsibilities.  User access rights are a combination of authentication and authorization.  EBS authenticates the User account at initial logon.  EBS authorizes access to functional components by checking a User’s assigned Responsibilities.  Once logged in, the User selects any assigned Responsibility to get access to a Menu of screens, transaction forms, and data reports.


The EBS business and configuration activities enabled by the Responsibility Menus are controlled by Functions.  A thorough review of User Access Rights analyzes the Functions available in Menus.  Such reviews include audits of Segregation Of Duties (SOD) and sensitive business transactions.  That detail is beyond the scope of typical User Access Rights assignment and re-certification processes. 

Leave a Reply

Your email address will not be published. Required fields are marked *