# Oracle EBS Inactive User Cleanup SQL Generator

> Source: https://netray.co/tools/oracle-ebs-inactive-user-cleanup-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Set a number of idle days and generate queries to list inactive users, expiring passwords, and a reviewed script to disable them.

## Category

oracle

## Erp

Oracle E-Business Suite

## Audience

- Oracle EBS DBA
- IT security administrator
- Oracle apps administrator

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Idle days before a user is inactive

**Key:** idle_days

**Type:** number

**Default:** 120

#### User name pattern

**Key:** user_pattern

**Type:** text

**Default:** %

**Help:** Use % as a wildcard

#### Users to exclude (quoted, comma separated)

**Key:** exclude_users

**Type:** text

**Default:** 'SYSADMIN','GUEST','AUTOINSTALL','ASGADM'

**Help:** Seeded and service accounts that must never be ended

#### Password expiry look-ahead in days

**Key:** expiry_window

**Type:** number

**Default:** 14

**Blocks:**

#### 1. Users inactive for the chosen period

**Body:** 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 - {{idle_days}})
AND    fu.creation_date < SYSDATE - {{idle_days}}
AND    fu.user_name NOT IN ({{exclude_users}})
AND    UPPER(fu.user_name) LIKE UPPER('{{user_pattern}}')
ORDER  BY fu.last_logon_date NULLS FIRST, fu.user_name;

#### 2. Users with passwords expiring soon

**Body:** 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 + {{expiry_window}}
AND    UPPER(fu.user_name) LIKE UPPER('{{user_pattern}}')
ORDER  BY expires_on;

#### 3. Disable a reviewed list of users

**Body:** -- 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 - {{idle_days}})
            AND    fu.creation_date < SYSDATE - {{idle_days}}
            AND    fu.user_name NOT IN ({{exclude_users}})
            AND    UPPER(fu.user_name) LIKE UPPER('{{user_pattern}}'))
  LOOP
    fnd_user_pkg.disableuser(r.user_name);
    DBMS_OUTPUT.PUT_LINE('Disabled: ' || r.user_name);
  END LOOP;
  COMMIT;
END;
/

#### 4. Verify the result

**Body:** 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;

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the generator

**Body:** 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.

**Bullets:**

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

#### How inactivity is measured

**Body:** 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

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

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

oracle ebs inactive users sql, disable inactive users oracle apps, fnd_user last_logon_date, fnd_user_pkg.disableuser, oracle ebs end date user script, oracle ebs password expiry query, oracle ebs license cleanup users, oracle ebs locked users query

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json