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
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.
Related Tools
Oracle EBS User and Responsibility Audit SQL Generator
Fill in a user and responsibility pattern and get ready-to-run queries for active user access, administrator access and duplicate access reviews.
OracleOracle EBS Profile Options Reference
A searchable lookup of the profile options EBS administrators change most: sign-on security, concurrent processing, multi-org, logging and session settings.
OracleOracle EBS Concurrent Managers and Settings Reference
What each standard concurrent manager and service does, and what the main tuning settings mean, in one searchable table.
OracleOracle EBS Instance Health Assessment
A 10-question self-assessment that scores how well your EBS instance is run and points to the first areas to fix.
More Oracle tools
See all- Oracle EBS Inventory Tables Reference
- Oracle EBS WIP Tables Reference
- Oracle EBS Cycle Count Workload Calculator
- Oracle EBS Discrete Manufacturing Go-Live Checklist
- Oracle EBS Inventory Accuracy Readiness Assessment
- Oracle EBS Concurrent Request Phase and Status Reference
- Oracle EBS Responsibility Design Worksheet
- Oracle EBS GL Period Close Checklist
- Oracle EBS AP Invoice Interface Template