Introduction
In a typical organization data is of utmost importance and tremendous care and efforts are made by organizations to ensure that their data is safe and secure. The question becomes more vital if the ERP implementation is in a Cloud Deployment Model. It is for this primary reason that “Auditing” an ERP Implementation project becomes very important. As of now when this article is being written “Auditing” is achieved in Oracle HCM Cloud Application using either of the below mentioned four tools:
- a) Auditing Business Objects
- b) Standard Security Reports
- c) Security Console
- d) OTBI and BIP Reports
In this post we will focus on understanding some of the “Standard Security Reports” and one of the delivered security report which can be utilized is “Inactive Users Report”.
Purpose of Inactive Users Report
Inactive Users Report gives the list of all users along with the days since when a user is inactive. Say for example we want to find the list of all users who are inactive since say for last 30 days then we can run this report. But there are some pre-requisite before we can run this report and retrieve data.
Pre-requisites of running Inactive Users Report
It is mandatory to run the Import User Login History process. This process imports information that the Inactive Users Report process uses to identify inactive users.
Worked-Out Example
We would run the “Import User Login History” process.
Now, we will run the “Inactive Users Report”
When we run this process we will get two output files. The first output file displays the underlying SQL which was utilized to return data based on the input parameters passed in the scheduled process
SQL Used for “Inactive Users Report” |
SELECT * FROM (select distinct aul.USER_GUID GUID, aul.LAST_LOGIN_DATE LAST_LOGIN_DATE, trunc(SYSDATE)-trunc(LAST_LOGIN_DATE) AS DAYS_SINCE_LAST_LOGIN, UserPEO.USERNAME USERNAME, UserPEO.PERSON_ID PERSON_ID, UserPEO.PARTY_ID PARTY_ID, PersonNameDPEO.FIRST_NAME FIRST_NAME, PersonNameDPEO.LAST_NAME LAST_NAME, LocationDPEO.LOCATION_CODE LOCATION_CODE, LocationDPEO.LOCATION_NAME LOCATION_NAME, LocationDPEO.TOWN_OR_CITY TOWN, LocationDPEO.COUNTRY COUNTRY, DepartmentDPEO.NAME DEPTARTMENT from ASE_USER_LOGIN_INFO aul join PER_USERS UserPEO on ( aul.USER_GUID = UserPEO.USER_GUID AND UserPEO.ACTIVE_FLAG = 'Y' AND (UserPEO.SUSPENDED is null or UserPEO.SUSPENDED = 'N') AND UserPEO.USERNAME NOT LIKE 'FUSION%APPID%' ) left join PER_PERSON_NAMES_F_V PersonNameDPEO on ( UserPEO.PERSON_ID = PersonNameDPEO.PERSON_ID AND PersonNameDPEO.name_type = 'GLOBAL' AND TRUNC(sysdate) between PersonNameDPEO.EFFECTIVE_START_DATE and PersonNameDPEO.EFFECTIVE_END_DATE ) left join PER_ALL_ASSIGNMENTS_M AssignmentDPEO on ( UserPEO.PERSON_ID = AssignmentDPEO.PERSON_ID AND AssignmentDPEO.PRIMARY_FLAG = 'Y' and AssignmentDPEO.EFFECTIVE_LATEST_CHANGE = 'Y' and TRUNC(sysdate) between AssignmentDPEO.EFFECTIVE_START_DATE and AssignmentDPEO.EFFECTIVE_END_DATE and AssignmentDPEO.ASSIGNMENT_TYPE in ('E','C','N') AND AssignmentDPEO.Assignment_status_type in ('ACTIVE', 'SUSPENDED') ) left join HR_LOCATIONS_ALL_F_VL LocationDPEO on ( AssignmentDPEO.LOCATION_ID = LocationDPEO.LOCATION_ID AND trunc(sysdate) between LocationDPEO.EFFECTIVE_START_DATE AND LocationDPEO.EFFECTIVE_END_DATE ) left join PER_DEPARTMENTS DepartmentDPEO on ( DepartmentDPEO.ORGANIZATION_ID = AssignmentDPEO.ORGANIZATION_ID AND trunc(sysdate) between DepartmentDPEO.EFFECTIVE_START_DATE AND DepartmentDPEO.EFFECTIVE_END_DATE )) QRSLT WHERE ( ( (DAYS_SINCE_LAST_LOGIN >= :daysThreshold ) ) ) ORDER BY DAYS_SINCE_LAST_LOGIN DESC |
Screen-shot of Output retrieved from the process
Summary
So this is how one can run the “Inactive Users Report” and get the required info from the application. Similar to this delivered report there are also “Locked Users Report” and “User Password Changes Audit Report” which can be used to find the list of all Locked Users as well as audit details of the user password changes related data. Both of these processes also have the similar pre-requisite of running “Import User Login History” process. Also, one more important point to note here is that in order to run “User Password Changes Audit Report” one must have the ASE_USER_PASSWORD_CHANGE S_AUDIT_REPORT_PRIV function security privilege to run this report. The predefined IT Security Manager job role has this privilege by default.
Comments
attempting to to find things to improve my web site!I assume its
adequate to use some of your ideas!!
I'll bookmark your weblog and test once more here regularly.
I'm quite certain I'll learn many new stuff proper right here!
Good luck for the following!
this blog and I am impressed! Very helpful info specifically the
last part :) I care for such information a lot. I was seeking this particular info
for a very long time. Thank you and best of luck.
to pay a quick visit this blog all the time.
put things off a whole lot and don't manage to get anything done.
RSS feed for comments to this post