ORA-28002: Password Will Expire - Manage Oracle Profiles

ORA-28002: The Password Will Expire Within N Days

Error Overview

Error Text: ORA-28002: the password will expire within string days

The ORA-28002 error (technically a warning) is returned when a user successfully authenticates but their password is within the expiry grace period defined by the PASSWORD_GRACE_TIME parameter in their assigned profile. The message does not prevent login — the user can continue working — but if the password is not changed before expiry, subsequent logins will raise ORA-28001 (Password Has Expired) and block access entirely. This warning is often seen with service accounts, batch users, and application connection pools where password changes are disruptive.

Common Causes

1. Default Profile PASSWORD_LIFE_TIME Setting

• Oracle’s DEFAULT profile has PASSWORD_LIFE_TIME set to 180 days in most versions
• Users assigned the DEFAULT profile automatically inherit this limit
• Service accounts and application users are frequently overlooked during password rotation

2. Security Policy Enforcement

• DBA has configured profiles with short PASSWORD_LIFE_TIME values for compliance
• CIS Oracle benchmark or internal security standard requires periodic rotation
• Auditors require all accounts to have a defined expiry policy

3. Migration or Clone Environments

• Database was cloned from production and all passwords inherited the original creation timestamp
• The grace period fires for many accounts simultaneously after a clone
• Test or staging environments that mirrored production account ages

4. Application Users Without Rotation Automation

• Service accounts used by applications, ETL jobs, or middleware that lack automated rotation
• Connection pool accounts that are never interactively logged in, so the warning goes unnoticed
• Scheduler job owners with no human reviewing the alert

Diagnostic Queries

Find Users Near or Past Expiry

-- Find all users whose passwords are expiring soon or have expired
SELECT
  username,
  account_status,
  expiry_date,
  TRUNC(expiry_date - SYSDATE) AS days_until_expiry,
  profile,
  created
FROM dba_users
WHERE account_status NOT IN ('LOCKED', 'EXPIRED & LOCKED')
  AND expiry_date IS NOT NULL
  AND expiry_date < SYSDATE + 30  -- Expiring within 30 days
ORDER BY expiry_date;

-- Find all expired accounts
SELECT
  username,
  account_status,
  expiry_date,
  TRUNC(SYSDATE - expiry_date) AS days_expired,
  profile
FROM dba_users
WHERE account_status LIKE '%EXPIRED%'
ORDER BY expiry_date;

Inspect Profile Password Settings

-- View password parameters for all profiles
SELECT
  profile,
  resource_name,
  limit
FROM dba_profiles
WHERE resource_type = 'PASSWORD'
  AND profile IN (
    SELECT DISTINCT profile FROM dba_users
    WHERE username NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN')
  )
ORDER BY profile, resource_name;

-- Summary of PASSWORD_LIFE_TIME by profile
SELECT
  p.profile,
  p.limit AS password_life_time,
  COUNT(u.username) AS user_count
FROM dba_profiles p
LEFT JOIN dba_users u ON u.profile = p.profile
WHERE p.resource_name = 'PASSWORD_LIFE_TIME'
GROUP BY p.profile, p.limit
ORDER BY p.profile;

Identify Application Accounts by Profile

-- Find service/application accounts and their expiry status
SELECT
  username,
  profile,
  account_status,
  expiry_date,
  last_login,
  created
FROM dba_users
WHERE username NOT IN (
  SELECT username FROM dba_users
  WHERE oracle_maintained = 'Y'
)
ORDER BY expiry_date NULLS LAST, username;

Step-by-Step Resolution

1. Reset the Password for the Expiring Account

The most direct fix is to change the password before it expires:

-- Reset password as DBA
ALTER USER app_user IDENTIFIED BY new_secure_password;

-- Verify the account status after reset
SELECT username, account_status, expiry_date
FROM dba_users
WHERE username = 'APP_USER';

-- If the account is already expired, unlock and reset
ALTER USER app_user IDENTIFIED BY new_secure_password ACCOUNT UNLOCK;

2. Extend or Remove Password Expiry for Service Accounts

For application service accounts where password rotation must be coordinated with application teams:

-- Option A: Set PASSWORD_LIFE_TIME to UNLIMITED for a specific profile
-- Create a dedicated profile for application service accounts
CREATE PROFILE app_service_profile LIMIT
  PASSWORD_LIFE_TIME     UNLIMITED
  PASSWORD_GRACE_TIME    UNLIMITED
  PASSWORD_REUSE_TIME    UNLIMITED
  PASSWORD_REUSE_MAX     UNLIMITED
  FAILED_LOGIN_ATTEMPTS  10
  PASSWORD_LOCK_TIME     1/24;  -- Lock for 1 hour after 10 failed attempts

-- Assign the profile to the service account
ALTER USER app_user PROFILE app_service_profile;

-- Verify
SELECT username, profile, expiry_date FROM dba_users WHERE username = 'APP_USER';

-- Option B: Modify the existing profile (affects all users on that profile)
ALTER PROFILE default_app_profile LIMIT
  PASSWORD_LIFE_TIME  365  -- 1 year instead of 180 days
  PASSWORD_GRACE_TIME 14;  -- 14-day warning period

3. Create a Proper Password Rotation Procedure

For environments that require regular rotation:

-- Automated password rotation helper
CREATE OR REPLACE PROCEDURE rotate_service_password(
  p_username     VARCHAR2,
  p_new_password VARCHAR2
) AS
  v_status VARCHAR2(32);
BEGIN
  -- Check current status
  SELECT account_status INTO v_status
  FROM dba_users
  WHERE username = UPPER(p_username);

  -- Change password
  EXECUTE IMMEDIATE
    'ALTER USER ' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_username) ||
    ' IDENTIFIED BY "' || p_new_password || '"';

  -- Unlock if it was locked/expired
  IF v_status LIKE '%EXPIRED%' OR v_status LIKE '%LOCKED%' THEN
    EXECUTE IMMEDIATE
      'ALTER USER ' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_username) ||
      ' ACCOUNT UNLOCK';
  END IF;

  DBMS_OUTPUT.PUT_LINE('Password rotated for: ' || UPPER(p_username));
  DBMS_OUTPUT.PUT_LINE('New expiry date: ' ||
    TO_CHAR(SYSDATE + (
      SELECT TO_NUMBER(DECODE(limit, 'UNLIMITED', 99999, limit))
      FROM dba_profiles p
      JOIN dba_users u ON u.profile = p.profile
      WHERE u.username = UPPER(p_username)
        AND p.resource_name = 'PASSWORD_LIFE_TIME'
    ), 'DD-MON-YYYY'));
END;
/

4. Set Up Proactive Expiry Monitoring

-- Create a monitoring view for password expiry
CREATE OR REPLACE VIEW v_password_expiry_monitor AS
SELECT
  username,
  profile,
  account_status,
  expiry_date,
  TRUNC(expiry_date - SYSDATE)  AS days_until_expiry,
  last_login,
  CASE
    WHEN expiry_date < SYSDATE          THEN 'EXPIRED'
    WHEN expiry_date < SYSDATE + 7      THEN 'CRITICAL'
    WHEN expiry_date < SYSDATE + 30     THEN 'WARNING'
    WHEN expiry_date < SYSDATE + 60     THEN 'NOTICE'
    ELSE                                     'OK'
  END AS expiry_status
FROM dba_users
WHERE expiry_date IS NOT NULL
  AND oracle_maintained = 'N'
ORDER BY expiry_date;

-- Scheduled alert job
CREATE OR REPLACE PROCEDURE alert_password_expiry AS
BEGIN
  FOR u IN (
    SELECT username, days_until_expiry, expiry_status
    FROM v_password_expiry_monitor
    WHERE expiry_status IN ('EXPIRED', 'CRITICAL', 'WARNING')
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(
      u.expiry_status || ': ' || u.username ||
      ' - ' || u.days_until_expiry || ' days until expiry'
    );
    -- Add email alert via UTL_MAIL or DBMS_SCHEDULER notification here
  END LOOP;
END;
/

-- Schedule the alert to run daily
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'JOB_PASSWORD_EXPIRY_ALERT',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'alert_password_expiry',
    repeat_interval => 'FREQ=DAILY;BYHOUR=8;BYMINUTE=0',
    enabled         => TRUE,
    comments        => 'Daily password expiry check'
  );
END;
/

5. Handle ORA-28002 in Application Connection Pools

JDBC and other connection pools may surface this as an exception. Configure the pool to handle it:

-- Oracle JDBC: capture ORA-28002 as a warning, not an error
-- In connection pool configuration, set:
-- oracle.jdbc.J2EE13Compliant=true
-- validateConnectionOnBorrow=true

-- Alternatively, check for warning in JDBC:
-- SQLWarning warning = conn.getWarnings();
-- while (warning != null) {
--   if (warning.getErrorCode() == 28002) {
--     // Log and trigger password rotation alert
--   }
--   warning = warning.getNextWarning();
-- }

Prevention Strategies

1. Separate Profiles for Interactive and Service Accounts

-- Best practice: dedicated profiles for different account types
-- Profile for interactive human users (enforce rotation)
CREATE PROFILE human_user_profile LIMIT
  PASSWORD_LIFE_TIME     90
  PASSWORD_GRACE_TIME    14
  PASSWORD_REUSE_TIME    365
  PASSWORD_REUSE_MAX     10
  FAILED_LOGIN_ATTEMPTS  5
  PASSWORD_LOCK_TIME     1/96;  -- 15-minute lockout

-- Profile for service/application accounts (no expiry, lockout protection)
CREATE PROFILE service_account_profile LIMIT
  PASSWORD_LIFE_TIME     UNLIMITED
  PASSWORD_GRACE_TIME    UNLIMITED
  FAILED_LOGIN_ATTEMPTS  10
  PASSWORD_LOCK_TIME     1/24;

2. Maintain a Password Rotation Calendar

-- Query to generate a rotation schedule
SELECT
  username,
  expiry_date,
  TRUNC(expiry_date - SYSDATE) AS days_to_expiry,
  'Rotate before: ' || TO_CHAR(expiry_date - 14, 'DD-MON-YYYY') AS action_by
FROM dba_users
WHERE expiry_date IS NOT NULL
  AND oracle_maintained = 'N'
  AND expiry_date BETWEEN SYSDATE AND SYSDATE + 90
ORDER BY expiry_date;

3. Integrate With Secrets Management

• Use Oracle Wallet or a secrets vault (HashiCorp Vault, AWS Secrets Manager) for service account passwords
• Configure automatic rotation via the vault’s Oracle plugin
• Remove hard-coded passwords from application configuration files

4. Audit and Review Profile Assignments Regularly

-- Quarterly audit: users with no expiry and no recent login
SELECT
  u.username,
  u.profile,
  u.last_login,
  u.created,
  p.limit AS password_life_time
FROM dba_users u
JOIN dba_profiles p
  ON p.profile = u.profile
  AND p.resource_name = 'PASSWORD_LIFE_TIME'
WHERE p.limit = 'UNLIMITED'
  AND (u.last_login < SYSDATE - 90 OR u.last_login IS NULL)
  AND u.oracle_maintained = 'N'
ORDER BY u.last_login NULLS FIRST;

Related Errors

• ORA-28001 - The password has expired
• ORA-28000 - The account is locked
• ORA-01017 - Invalid username/password
• ORA-01045 - User lacks CREATE SESSION privilege