PIPER-Rx - Aged User Accounts

PIPER-Rx
Oracle E-Business Suite Resource Centre

Aged User Accounts

Aged User Accounts

All too often accounts are created but rarely closed (end dated) when a person leaves.

One option is to treat management of your application user account in a similar manner to aged debtors. That is, to identify any accounts that are either not being used or have not been used in the past 120 days. We deliberately use aging buckets of current, 30, 60, 90 and 120+ as these buckets are generally understood by all.

If you want to do this all the information you need is in the table APPLSYS.FND_USER in the column last_logon_date

Whenever an account is accessed the last_logon_date is updated.
Note: In some very earlier versions of 11i self service connections did not update this field.

If the last_logon_date value is null then the account has never been used or if the last_logon_date value is older than 120 days the account is a candidate for investigation

The following SQL will list all those active accounts that have either never been used or have not been used in the last 120 days:

    SELECT fu.user_id,
           fu.user_name,
           fu.description,
           fu.email_address,
           to_char(fu.last_logon_date, 'DD-Mon-YY HH24:MI') last_logon_date,
           trunc((sysdate - fu.last_logon_date)) days_since_last_connect
      FROM applsys.fnd_user fu     
     WHERE (fu.last_logon_date is null
               OR (sysdate - fu.last_logon_date) >= 120 )
       and (fu.end_date is null
               OR fu.end_date > sysdate )
     ORDER by fu.user_name;
  
A simple "Active Accounts" report has been provided in the Application Accounts Section of the free reports page

Run the report and send it to your HR department or the group that manages user accounts.

There are some modules that require user accounts to exist but they may never be accessed. I came across a site that had expenses, so all employees need a user account. The majority of these accounts were never used.

If this is the case it is a good idea to append some text to the user’s description; eg (UA) indicating an Unused Account. In this case a description of Gary Piper would become Gary Piper (UA) clearly indicating the account is unused.

So the SQL to show aged active accounts, but exclude all known unused accounts now becomes:

    SELECT fu.user_id,
           fu.user_name,
           fu.description,
           fu.email_address,
           to_char(fu.last_logon_date, 'DD-Mon-YY HH24:MI') last_logon_date,
           trunc((sysdate - fu.last_logon_date)) days_since_last_connect
      FROM applsys.fnd_user fu     
     WHERE (fu.last_logon_date is null
               OR (sysdate - fu.last_logon_date) >= 120 )
       and (fu.end_date is null
               OR fu.end_date > sysdate )
       and fu.description not like '%(UA)'
     ORDER by fu.user_name;
  
Where this is the case it is worthwhile checking the “Unused Accounts” to see if any are actually being used.
    SELECT fu.user_id,
           fu.user_name,
           fu.description,
           fu.email_address,
           to_char(fu.last_logon_date, 'DD-Mon-YY HH24:MI') last_logon_date,
           trunc((sysdate - fu.last_logon_date)) days_since_last_connect
      FROM applsys.fnd_user fu     
     WHERE fu.last_logon_date is not null
       and (sysdate - fu.last_logon_date) <= 120 
       and (fu.end_date isnull
               OR fu.end_date > sysdate)
       and fu.description  like '%(UA)'
     ORDER by fu.user_name;
  

This is a great way to make yourself look good by being on top of user audit!

Want to know more? User Account Management is something many of sites could do a lot better so I have now devoted PAMtutorials to the subject so why not check this out as well!

Last update: May 2009