Aged User Accounts

Much like the Accounts Receivable ages debtor accounts function in any business, we can age application user accounts that have never connected to the application or have not connected in some time via the full and self-service screens.

Why would we want to do this? Having people who have long left the business retaining active accounts is a security issue, and all the best managed sites have processes in place to check that such accounts are being closed on a timely basis.

It is possible to generate a list of application user accounts that have either never connected or have not connected in the last 120 days.

The first step is to identify the number of accounts you have that are candidates for review:

SELECT count(*)
  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.user_name not in ('SYSADMIN','GUEST');

The second step is to produce a list of application accounts that are candidates for review:

SELECT fu.user_id,
             fu.user_name,
             substr(fu.description, 1, 40)
             decode(sign(length(fu.description) - 40), 1, '...') description,
             nvl(to_char(fu.last_logon_date, 'DD-Mon-YY HH24:MI'), 'Never Connected')
                       last_connect_date,
             decode(fu.last_logon_date, null, null,
             round( (sysdate - fu.last_logon_date),0) ) 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.user_name not in ('SYSADMIN','GUEST')
ORDER by decode(fu.last_logon_date, null, (sysdate - 10000), fu.last_logon_date),
                fu.user_name;

The output will look something like the following example:

Remember: Not all Aged User Accounts are candidates for end dating:

There are of course exceptions, those accounts that must exist but are never used and those that are used very infrequently. You can exclude these by simply adding these accounts to your SQL statement:

             and fu.user_name not in (‘SYSADMIN’,’GUEST’)

You should run the second report say once per month as part of your normal application maintenance routine and send it to your HR department or whoever manages your application user accounts. It shows the business you are on top of managing the application and the business loves anything to do with security...