ORA-28000 account locked and ORA-28001 password expired: fix and prevent

#The errors

#1. Check the account

SELECT username, account_status, lock_date, expiry_date, profile
FROM dba_users WHERE username = 'APP_USER';

#2. Unlock and/or reset

ALTER USER app_user ACCOUNT UNLOCK;
ALTER USER app_user IDENTIFIED BY "N3w-Str0ng-Passw0rd" ACCOUNT UNLOCK;   -- if expired

Warning

If an application server still has the old password, it will lock the account again within seconds. Find the source before unlocking, or you'll be back here in a minute.

#3. Who keeps failing to log in?

With unified auditing (12c+, default in 19c+):

SELECT event_timestamp, dbusername, userhost, client_program_name, return_code
FROM unified_audit_trail
WHERE action_name = 'LOGON' AND return_code = 1017 AND dbusername = 'APP_USER'
ORDER BY event_timestamp DESC FETCH FIRST 20 ROWS ONLY;

Also check the listener log for the client IP. Typical culprits: an old app server, a scheduled job, a monitoring tool or a developer's tool with a saved password.

#4. Fix it for application accounts

Give service accounts a dedicated profile:

CREATE PROFILE app_svc LIMIT
  PASSWORD_LIFE_TIME UNLIMITED
  FAILED_LOGIN_ATTEMPTS 50
  PASSWORD_LOCK_TIME 1/96            -- auto-unlock after 15 minutes
  PASSWORD_GRACE_TIME 7;

ALTER USER app_user PROFILE app_svc;

Agree this with security. Compensate with strong passwords stored in a vault and rotated by a process, not by expiry surprises.

#5. Expired, and the app can't change its password today

If you know the current password, set it again: ALTER USER app_user IDENTIFIED BY "<current password>"; restarts the password life time (allowed unless the profile's PASSWORD_REUSE_* limits block it). If nobody knows it, set a new password and update the application, or the vault, in the same change window.

#Prevent it

• Monitor dba_users for EXPIRY_DATE < SYSDATE + 14 and alert the account owners.
• Store database passwords in OCI Vault, Azure Key Vault or AWS Secrets Manager, and have applications read them at startup.

Tip

Separate human and application accounts. Humans get expiry and lockout; applications get rotation via a vault and a profile that doesn't take production down.