OracleFree generatorPDF, Excel, Word exportOracle E-Business Suite

Oracle EBS Inactive User Cleanup SQL Generator

Dormant EBS accounts are an audit finding and, for some licensing models, a cost. This generator produces the queries to find them and a script to end-date them safely after review.

Fill in

Use % as a wildcard

Seeded and service accounts that must never be ended

Generated code
Download:

1. Users inactive for the chosen period

SELECT fu.user_name,
       fu.description,
       fu.last_logon_date,
       fu.creation_date,
       fu.end_date
FROM   fnd_user fu
WHERE  (fu.end_date IS NULL OR fu.end_date > SYSDATE)
AND    (fu.last_logon_date IS NULL OR fu.last_logon_date < SYSDATE - 120)
AND    fu.creation_date < SYSDATE - 120
AND    fu.user_name NOT IN ('SYSADMIN','GUEST','AUTOINSTALL','ASGADM')
AND    UPPER(fu.user_name) LIKE UPPER('%')
ORDER  BY fu.last_logon_date NULLS FIRST, fu.user_name;

2. Users with passwords expiring soon

SELECT fu.user_name,
       fu.password_date,
       fu.password_lifespan_days,
       fu.password_date + fu.password_lifespan_days AS expires_on
FROM   fnd_user fu
WHERE  (fu.end_date IS NULL OR fu.end_date > SYSDATE)
AND    fu.password_lifespan_days IS NOT NULL
AND    fu.password_date + fu.password_lifespan_days BETWEEN SYSDATE AND SYSDATE + 14
AND    UPPER(fu.user_name) LIKE UPPER('%')
ORDER  BY expires_on;

3. Disable a reviewed list of users

-- Review the output of query 1 first. Run in a TEST instance before production.
SET SERVEROUTPUT ON
BEGIN
  FOR r IN (SELECT fu.user_name
            FROM   fnd_user fu
            WHERE  (fu.end_date IS NULL OR fu.end_date > SYSDATE)
            AND    (fu.last_logon_date IS NULL OR fu.last_logon_date < SYSDATE - 120)
            AND    fu.creation_date < SYSDATE - 120
            AND    fu.user_name NOT IN ('SYSADMIN','GUEST','AUTOINSTALL','ASGADM')
            AND    UPPER(fu.user_name) LIKE UPPER('%'))
  LOOP
    fnd_user_pkg.disableuser(r.user_name);
    DBMS_OUTPUT.PUT_LINE('Disabled: ' || r.user_name);
  END LOOP;
  COMMIT;
END;
/

4. Verify the result

SELECT fu.user_name,
       fu.end_date,
       fu.last_update_date
FROM   fnd_user fu
WHERE  fu.end_date IS NOT NULL
AND    fu.last_update_date > SYSDATE - 1
ORDER  BY fu.last_update_date DESC;

Run the disable block only after the owners of each account have been contacted. FND_USER_PKG.DISABLEUSER end-dates the account and keeps history; it does not delete the user, since EBS does not support deleting users. LAST_LOGON_DATE is only maintained when sign-on auditing is enabled, so confirm SIGNONAUDIT:LEVEL first. Integration and batch accounts often have no interactive logon and must be excluded.

Scheduled access hygiene reports

Ask how Netray can run and explain dormant user checks on your EBS instance on a schedule.

Optional. The tool and every export work without this. No spam.

How to use the generator

Set the idle days, the user pattern and the accounts to exclude. Copy query 1 and run it, then send the list to managers for confirmation. Only after sign-off run the PL/SQL block in a test instance, check the result with query 4, and then repeat in production inside an approved change window. Keep the query output as audit evidence.

  • Exclude seeded and integration accounts every time
  • Contact account owners before disabling
  • Test the block in a cloned instance

How inactivity is measured

FND_USER.LAST_LOGON_DATE records the last successful sign-on when sign-on auditing is active. A null value means the user never logged in, or auditing was off. The creation date test prevents newly created accounts from being flagged before they have had time to log in. Choose idle days to match policy, often 90 to 180 days, and a shorter value for privileged accounts.

Password and expiry checks

Query 2 uses PASSWORD_DATE plus PASSWORD_LIFESPAN_DAYS to estimate when a password expires. Accounts with no lifespan never expire by password age, which is itself worth a review for service accounts. Interface accounts that expire silently are a common cause of failed integrations, so warn their owners before the date.

Common mistakes

Disabling a service account used by an interface and breaking an integration, trusting last logon dates when auditing was off, and ending users who still own scheduled concurrent requests. Check for scheduled requests by the user in FND_CONCURRENT_REQUESTS before ending an account, and reassign them first.

Frequently Asked Questions

How do I disable a user in Oracle EBS with SQL?

Call FND_USER_PKG.DISABLEUSER with the user name from an apps session, or end-date the user in the Define User form. Both set the end date. EBS does not support deleting users because history references them.

Why is LAST_LOGON_DATE empty for active users?

The value is populated only for sign-on events recorded while sign-on auditing is active, and some login paths do not update it. Check the SIGNONAUDIT:LEVEL profile option and treat null as unknown, not as proof the user never logged in.

Will ending a user cancel their scheduled requests?

No. Scheduled concurrent requests submitted by the user remain and may still run under that user's identity. Cancel or reassign them before ending the account.

How many idle days should I use?

Many organizations use 90 days for privileged users and 180 days for regular users, but follow your own security policy. Seasonal staff such as year end finance users may need a longer window.

If you want dormant-user and access reports to run on a schedule and be explained in plain language, Netray can set that up on top of your EBS data without moving it off site.