ORA-28001: The Password Has Expired

ORA-28001: The Password Has Expired

Error Description

ORA-28001 occurs when a user attempts to connect to Oracle database with a password that has expired according to the assigned password profile. This error prevents login until the password is changed or the profile is modified.

Complete Error Message

ORA-28001: the password has expired

Severity Level

🟡 MEDIUM - Prevents user authentication but doesn’t affect database operations for other users.

Common Causes

1. Profile-Based Password Expiration

• Default profile settings causing automatic expiration
• Custom profiles with short password lifetimes
• PASSWORD_LIFE_TIME parameter reached
• No password change before expiration

2. Administrative Actions

• DBA manually expiring passwords
• Account maintenance procedures
• Security policy enforcement
• Bulk password expiration

3. Account Maintenance Issues

• Long periods without login
• Service accounts with expired passwords
• Application accounts not properly maintained
• Forgotten or unused accounts

4. Profile Configuration Issues

• Overly restrictive password policies
• Incorrect profile assignments
• Inconsistent password management
• Missing grace period configuration

Immediate Diagnostic Steps

1. Check User Account Status

-- Check specific user account status
SELECT username, account_status, expiry_date, profile
FROM dba_users
WHERE username = 'YOUR_USERNAME';

-- Check all expired accounts
SELECT username, account_status, expiry_date, profile, created
FROM dba_users
WHERE account_status LIKE '%EXPIRED%'
ORDER BY expiry_date;

-- Check grace period status
SELECT username, account_status, expiry_date, profile
FROM dba_users
WHERE account_status = 'EXPIRED(GRACE)'
ORDER BY expiry_date;

2. Analyze Password Profile Settings

-- Check profile limits for specific user
SELECT
  u.username,
  u.profile,
  p.resource_name,
  p.limit
FROM dba_users u, dba_profiles p
WHERE u.profile = p.profile
  AND u.username = 'YOUR_USERNAME'
  AND p.resource_name IN (
    'PASSWORD_LIFE_TIME',
    'PASSWORD_GRACE_TIME',
    'PASSWORD_REUSE_TIME',
    'PASSWORD_REUSE_MAX'
  )
ORDER BY u.username, p.resource_name;

-- Check all profiles and their password settings
SELECT
  profile,
  resource_name,
  resource_type,
  limit
FROM dba_profiles
WHERE resource_name IN (
  'PASSWORD_LIFE_TIME',
  'PASSWORD_GRACE_TIME',
  'PASSWORD_REUSE_TIME',
  'PASSWORD_REUSE_MAX',
  'FAILED_LOGIN_ATTEMPTS'
)
ORDER BY profile, resource_name;

3. Check Password History

-- Check when password was last changed
SELECT
  username,
  profile,
  account_status,
  expiry_date,
  ROUND(SYSDATE - expiry_date) as days_since_expiry
FROM dba_users
WHERE username = 'YOUR_USERNAME';

-- Check password change frequency for users
SELECT
  username,
  profile,
  created,
  expiry_date,
  ROUND(expiry_date - created) as password_lifetime_days
FROM dba_users
WHERE account_status LIKE '%EXPIRED%'
ORDER BY expiry_date DESC;

Immediate Solutions

1. Reset Password for Specific User

-- Connect as DBA and reset user password
ALTER USER your_username IDENTIFIED BY new_password;

-- Reset and unlock if account is also locked
ALTER USER your_username IDENTIFIED BY new_password ACCOUNT UNLOCK;

-- Force immediate password change on next login
ALTER USER your_username PASSWORD EXPIRE;

-- Reset service account password
ALTER USER app_service_user IDENTIFIED BY new_secure_password;

2. Extend Password Expiration

-- Extend password validity by modifying profile
-- First check current profile settings
SELECT profile, resource_name, limit
FROM dba_profiles
WHERE profile = (
  SELECT profile FROM dba_users WHERE username = 'YOUR_USERNAME'
)
AND resource_name = 'PASSWORD_LIFE_TIME';

-- Temporarily extend password life for specific profile
ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME 90;  -- 90 days

-- Create new profile with longer password life
CREATE PROFILE long_life_profile LIMIT
  PASSWORD_LIFE_TIME 365  -- 1 year
  PASSWORD_GRACE_TIME 7   -- 7 days grace
  FAILED_LOGIN_ATTEMPTS 5;

-- Assign new profile to user
ALTER USER your_username PROFILE long_life_profile;

3. Disable Password Expiration

-- Disable password expiration for specific profile
ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME UNLIMITED;

-- Create profile with no password expiration
CREATE PROFILE no_expire_profile LIMIT
  PASSWORD_LIFE_TIME UNLIMITED
  PASSWORD_GRACE_TIME UNLIMITED
  FAILED_LOGIN_ATTEMPTS 10
  PASSWORD_LOCK_TIME 1;

-- Apply to service accounts
ALTER USER service_account PROFILE no_expire_profile;

-- Verify changes
SELECT username, profile, account_status, expiry_date
FROM dba_users
WHERE username IN ('SERVICE_ACCOUNT', 'YOUR_USERNAME');

Long-term Solutions

1. Implement Password Management Strategy

-- Create different profiles for different user types

-- Profile for regular users
CREATE PROFILE regular_user_profile LIMIT
  PASSWORD_LIFE_TIME 60        -- 60 days
  PASSWORD_GRACE_TIME 7        -- 7 days grace
  PASSWORD_REUSE_TIME 90       -- Can't reuse for 90 days
  PASSWORD_REUSE_MAX 5         -- Remember 5 previous passwords
  FAILED_LOGIN_ATTEMPTS 5      -- Lock after 5 failures
  PASSWORD_LOCK_TIME 1         -- Lock for 1 day
  SESSIONS_PER_USER 3;         -- Max 3 concurrent sessions

-- Profile for service accounts
CREATE PROFILE service_account_profile LIMIT
  PASSWORD_LIFE_TIME UNLIMITED -- No expiration
  PASSWORD_GRACE_TIME UNLIMITED
  FAILED_LOGIN_ATTEMPTS 10     -- More lenient
  PASSWORD_LOCK_TIME 1/24      -- Lock for 1 hour
  SESSIONS_PER_USER UNLIMITED;

-- Profile for administrative users
CREATE PROFILE admin_user_profile LIMIT
  PASSWORD_LIFE_TIME 30        -- 30 days for admins
  PASSWORD_GRACE_TIME 3        -- 3 days grace
  PASSWORD_REUSE_TIME 180      -- Can't reuse for 6 months
  PASSWORD_REUSE_MAX 10        -- Remember 10 previous passwords
  FAILED_LOGIN_ATTEMPTS 3      -- Stricter security
  PASSWORD_LOCK_TIME 7         -- Lock for 7 days
  SESSIONS_PER_USER 2;

2. Password Verification Functions

-- Create custom password verification function
CREATE OR REPLACE FUNCTION verify_password_strength(
  username VARCHAR2,
  password VARCHAR2,
  old_password VARCHAR2
) RETURN BOOLEAN IS
  v_length NUMBER;
  v_upper_count NUMBER;
  v_lower_count NUMBER;
  v_digit_count NUMBER;
  v_special_count NUMBER;
BEGIN
  -- Check minimum length
  v_length := LENGTH(password);
  IF v_length < 8 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Password must be at least 8 characters');
  END IF;

  -- Check complexity
  v_upper_count := LENGTH(password) - LENGTH(TRANSLATE(password, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', ''));
  v_lower_count := LENGTH(password) - LENGTH(TRANSLATE(password, 'abcdefghijklmnopqrstuvwxyz', ''));
  v_digit_count := LENGTH(password) - LENGTH(TRANSLATE(password, '0123456789', ''));
  v_special_count := LENGTH(password) - LENGTH(TRANSLATE(password, '!@#$%^&*()_+-=[]{}|;:,.<>?', ''));

  IF v_upper_count = 0 OR v_lower_count = 0 OR v_digit_count = 0 OR v_special_count = 0 THEN
    RAISE_APPLICATION_ERROR(-20002, 'Password must contain uppercase, lowercase, digit, and special character');
  END IF;

  -- Check against username
  IF UPPER(password) = UPPER(username) THEN
    RAISE_APPLICATION_ERROR(-20003, 'Password cannot be same as username');
  END IF;

  -- Check if different from old password
  IF password = old_password THEN
    RAISE_APPLICATION_ERROR(-20004, 'New password must be different from old password');
  END IF;

  RETURN TRUE;
END;
/

-- Apply verification function to profile
ALTER PROFILE regular_user_profile LIMIT
  PASSWORD_VERIFY_FUNCTION verify_password_strength;

3. Automated Password Management

-- Create password expiration monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_password_expiry AS
  CURSOR c_expiring_soon IS
    SELECT username, expiry_date, profile
    FROM dba_users
    WHERE expiry_date BETWEEN SYSDATE AND SYSDATE + 7
      AND account_status = 'OPEN'
      AND username NOT IN ('SYS', 'SYSTEM', 'SYSMAN');

  CURSOR c_expired IS
    SELECT username, expiry_date, profile
    FROM dba_users
    WHERE account_status LIKE '%EXPIRED%'
      AND username NOT IN ('SYS', 'SYSTEM', 'SYSMAN');
BEGIN
  -- Log expiring passwords
  FOR rec IN c_expiring_soon LOOP
    INSERT INTO password_alerts (username, alert_type, expiry_date, alert_time)
    VALUES (rec.username, 'EXPIRING_SOON', rec.expiry_date, SYSDATE);
  END LOOP;

  -- Log expired passwords
  FOR rec IN c_expired LOOP
    INSERT INTO password_alerts (username, alert_type, expiry_date, alert_time)
    VALUES (rec.username, 'EXPIRED', rec.expiry_date, SYSDATE);
  END LOOP;

  COMMIT;
END;
/

-- Schedule regular monitoring
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'password_expiry_monitor',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN monitor_password_expiry; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY;BYHOUR=9',
    enabled         => TRUE
  );
END;
/

Prevention Strategies

1. User Education and Communication

-- Create notification system for upcoming expirations
CREATE OR REPLACE PROCEDURE notify_password_expiry AS
  v_message VARCHAR2(4000);
BEGIN
  FOR rec IN (
    SELECT username, expiry_date,
           ROUND(expiry_date - SYSDATE) as days_until_expiry
    FROM dba_users
    WHERE expiry_date BETWEEN SYSDATE AND SYSDATE + 14
      AND account_status = 'OPEN'
      AND username NOT LIKE '%$'  -- Exclude Oracle internal accounts
  ) LOOP
    v_message := 'Password for user ' || rec.username ||
                 ' will expire in ' || rec.days_until_expiry || ' days on ' ||
                 TO_CHAR(rec.expiry_date, 'YYYY-MM-DD');

    -- Log the notification
    INSERT INTO password_notifications (username, message, notification_date)
    VALUES (rec.username, v_message, SYSDATE);
  END LOOP;

  COMMIT;
END;
/

2. Service Account Management

-- Create service account management framework
CREATE TABLE service_accounts (
  username VARCHAR2(30),
  application VARCHAR2(100),
  owner VARCHAR2(100),
  last_password_change DATE,
  next_scheduled_change DATE,
  contact_email VARCHAR2(100)
);

-- Procedure to manage service account passwords
CREATE OR REPLACE PROCEDURE manage_service_accounts AS
  v_new_password VARCHAR2(30);
BEGIN
  FOR rec IN (
    SELECT username, application, owner
    FROM service_accounts
    WHERE next_scheduled_change <= SYSDATE
  ) LOOP
    -- Generate new password (simplified - use proper password generation)
    v_new_password := 'NewPass' || TO_CHAR(SYSDATE, 'YYYYMMDD') ||
                      SUBSTR(rec.username, 1, 3);

    -- Change password
    EXECUTE IMMEDIATE 'ALTER USER ' || rec.username ||
                     ' IDENTIFIED BY "' || v_new_password || '"';

    -- Update tracking table
    UPDATE service_accounts
    SET last_password_change = SYSDATE,
        next_scheduled_change = SYSDATE + 90  -- Next change in 90 days
    WHERE username = rec.username;

    -- Log the change
    INSERT INTO password_change_log (username, change_date, changed_by)
    VALUES (rec.username, SYSDATE, USER);
  END LOOP;

  COMMIT;
END;
/

3. Grace Period Management

-- Create procedure to handle grace period users
CREATE OR REPLACE PROCEDURE handle_grace_period_users AS
  v_count NUMBER;
BEGIN
  -- Check for users in grace period
  SELECT COUNT(*) INTO v_count
  FROM dba_users
  WHERE account_status = 'EXPIRED(GRACE)';

  IF v_count > 0 THEN
    -- Log warning
    INSERT INTO system_alerts (alert_time, alert_type, message)
    VALUES (SYSDATE, 'PASSWORD_GRACE',
            v_count || ' users are in password grace period');

    -- For each grace period user
    FOR rec IN (
      SELECT username, expiry_date
      FROM dba_users
      WHERE account_status = 'EXPIRED(GRACE)'
    ) LOOP
      -- Send notification
      INSERT INTO user_notifications (username, message, notification_date)
      VALUES (rec.username,
              'Your password expired on ' || TO_CHAR(rec.expiry_date, 'YYYY-MM-DD') ||
              '. Please change it immediately to avoid account lockout.',
              SYSDATE);
    END LOOP;

    COMMIT;
  END IF;
END;
/

Troubleshooting Tools

1. Password Status Analysis

-- Comprehensive password status report
SELECT
  u.username,
  u.profile,
  u.account_status,
  u.expiry_date,
  u.created,
  CASE
    WHEN u.expiry_date IS NULL THEN 'NEVER_EXPIRES'
    WHEN u.expiry_date < SYSDATE THEN 'EXPIRED'
    WHEN u.expiry_date BETWEEN SYSDATE AND SYSDATE + 7 THEN 'EXPIRES_SOON'
    ELSE 'ACTIVE'
  END as password_status,
  ROUND(SYSDATE - u.expiry_date) as days_since_expiry,
  ROUND(u.expiry_date - SYSDATE) as days_until_expiry,
  p1.limit as password_life_time,
  p2.limit as password_grace_time
FROM dba_users u
LEFT JOIN dba_profiles p1 ON (u.profile = p1.profile AND p1.resource_name = 'PASSWORD_LIFE_TIME')
LEFT JOIN dba_profiles p2 ON (u.profile = p2.profile AND p2.resource_name = 'PASSWORD_GRACE_TIME')
WHERE u.username NOT LIKE '%$'  -- Exclude Oracle internal accounts
ORDER BY u.expiry_date NULLS LAST;

2. Profile Usage Analysis

-- Analyze profile usage and effectiveness
SELECT
  p.profile,
  COUNT(u.username) as user_count,
  COUNT(CASE WHEN u.account_status LIKE '%EXPIRED%' THEN 1 END) as expired_count,
  COUNT(CASE WHEN u.account_status = 'EXPIRED(GRACE)' THEN 1 END) as grace_count,
  ROUND(AVG(CASE WHEN u.expiry_date IS NOT NULL
                 THEN u.expiry_date - u.created END), 1) as avg_password_life_days
FROM dba_profiles p
LEFT JOIN dba_users u ON p.profile = u.profile
WHERE p.resource_name = 'PASSWORD_LIFE_TIME'
GROUP BY p.profile
ORDER BY user_count DESC;

3. Password Change Tracking

-- Create password change audit table
CREATE TABLE password_change_audit (
  username VARCHAR2(30),
  change_timestamp TIMESTAMP,
  changed_by VARCHAR2(30),
  change_method VARCHAR2(50),
  from_ip VARCHAR2(15)
);

-- Create trigger to track password changes
CREATE OR REPLACE TRIGGER trg_password_change_audit
  AFTER ALTER ON DATABASE
DECLARE
  v_sql_text VARCHAR2(4000);
BEGIN
  v_sql_text := ORA_SQL_TXT(1);

  IF v_sql_text LIKE '%IDENTIFIED BY%' AND
     v_sql_text LIKE '%ALTER USER%' THEN

    INSERT INTO password_change_audit (
      username,
      change_timestamp,
      changed_by,
      change_method,
      from_ip
    ) VALUES (
      REGEXP_SUBSTR(v_sql_text, 'ALTER USER\s+(\w+)', 1, 1, 'i', 1),
      SYSTIMESTAMP,
      LOGIN_USER,
      'ALTER_USER_SQL',
      SYS_CONTEXT('USERENV', 'IP_ADDRESS')
    );
  END IF;
END;
/

Application Integration

1. Connection String Handling

// Java example for handling expired passwords
public class OracleConnectionManager {

    public Connection getConnection(String username, String password, String url)
        throws SQLException {

        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            if (e.getErrorCode() == 28001) {  // ORA-28001
                logger.warn("Password expired for user: " + username);

                // Option 1: Prompt for new password
                String newPassword = promptForNewPassword();
                return changePasswordAndConnect(username, password, newPassword, url);

                // Option 2: Use service account
                // return getServiceAccountConnection();

                // Option 3: Notify admin and fail gracefully
                // notifyAdministrator("Password expired: " + username);
                // throw new ServiceUnavailableException("Authentication failed");
            }
            throw e;
        }
    }

    private Connection changePasswordAndConnect(String username, String oldPassword,
                                              String newPassword, String url) throws SQLException {
        // Change password using admin connection
        try (Connection adminConn = getAdminConnection()) {
            Statement stmt = adminConn.createStatement();
            stmt.execute("ALTER USER " + username + " IDENTIFIED BY \"" + newPassword + "\"");
        }

        // Connect with new password
        return DriverManager.getConnection(url, username, newPassword);
    }
}

2. PL/SQL Password Management

-- Create package for password management
CREATE OR REPLACE PACKAGE password_manager AS
  FUNCTION check_password_expiry(p_username VARCHAR2) RETURN VARCHAR2;
  PROCEDURE force_password_change(p_username VARCHAR2);
  FUNCTION generate_secure_password RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY password_manager AS
  FUNCTION check_password_expiry(p_username VARCHAR2) RETURN VARCHAR2 IS
    v_status VARCHAR2(100);
    v_days_until_expiry NUMBER;
  BEGIN
    SELECT
      account_status,
      ROUND(expiry_date - SYSDATE)
    INTO v_status, v_days_until_expiry
    FROM dba_users
    WHERE username = UPPER(p_username);

    CASE
      WHEN v_status LIKE '%EXPIRED%' THEN
        RETURN 'EXPIRED';
      WHEN v_days_until_expiry <= 7 THEN
        RETURN 'EXPIRES_SOON';
      ELSE
        RETURN 'ACTIVE';
    END CASE;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN 'USER_NOT_FOUND';
  END;

  PROCEDURE force_password_change(p_username VARCHAR2) IS
  BEGIN
    EXECUTE IMMEDIATE 'ALTER USER ' || p_username || ' PASSWORD EXPIRE';
  END;

  FUNCTION generate_secure_password RETURN VARCHAR2 IS
    v_password VARCHAR2(20);
  BEGIN
    -- Simple password generation (use more sophisticated method in production)
    SELECT 'Pass' || ROUND(DBMS_RANDOM.VALUE(1000, 9999)) ||
           CHR(ROUND(DBMS_RANDOM.VALUE(65, 90))) ||  -- Random uppercase
           CHR(ROUND(DBMS_RANDOM.VALUE(97, 122))) || -- Random lowercase
           '#'
    INTO v_password
    FROM dual;

    RETURN v_password;
  END;
END;
/

Related Oracle Errors

• ORA-28000: Account is Locked - Account lockout issues
• ORA-01017: Invalid Username/Password - Authentication failures
• ORA-01031: Insufficient Privileges - Permission issues
• ORA-12154: TNS Could Not Resolve Service Name - Connection issues

Best Practices

1. Implement tiered password policies for different user types
2. Use automated monitoring for password expiration
3. Provide adequate grace periods for critical accounts
4. Document password procedures for all stakeholders
5. Regular review of profiles and password policies
6. Implement secure password generation for service accounts
7. Maintain audit trail of password changes
8. Test password procedures before implementing

Quick Resolution Checklist

1. ✅ Identify affected user and their profile settings
2. ✅ Determine user type (regular user, service account, admin)
3. ✅ Check grace period status and remaining time
4. ✅ Reset password immediately if critical account
5. ✅ Update profile settings if policy needs adjustment
6. ✅ Notify user of password change and new policy
7. ✅ Document incident and review prevention measures

Password expiration is a security feature that requires proper management and user communication to avoid service disruptions.