Unlock Oracle Users Without Changing Passwords: Bypass Expired Password Locks

Case Introduction

A client hardened their Oracle database by setting PASSWORD_LIFE_TIME in a profile but omitted PASSWORD_REUSE_TIME. When the password expired, the account was automatically locked, causing business downtime. Resetting the password would require changing many middleware configurations, which is risky.

Key Oracle Profile Parameters

Oracle manages password policies through profile settings. Important parameters include:

PASSWORD_LIFE_TIME 180 – defines the password’s lifetime before it may expire.

PASSWORD_GRACE_TIME 7 – after expiration, allows the password to be used for an additional grace period (in days) before a mandatory change.

PASSWORD_REUSE_TIME UNLIMITED – the minimum number of days before a previously used password can be reused.

PASSWORD_REUSE_MAX UNLIMITED – the minimum number of distinct password changes required before a password can be reused.

FAILED_LOGIN_ATTEMPTS 10 – number of consecutive failed logins before the account is locked.

PASSWORD_LOCK_TIME 1 – duration (in days) the account remains locked after reaching the failed‑login threshold.

User Account Statuses

Oracle provides nine possible statuses, viewable via USER_ASTATUS_MAP. They are grouped into basic and composite states. The five basic states most relevant for routine administration are:

0 OPEN

1 EXPIRED

2 EXPIRED(GRACE)

4 LOCKED(TIMED)

8 LOCKED

Composite states such as EXPIRED & LOCKED combine these flags and require special handling.

Bypassing Password Expiration Without Changing the Password

When a user is in the EXPIRED & LOCKED state, a simple unlock command is insufficient because the password is still considered expired. The workaround is to use the stored password hash to reset the account.

In Oracle 10g, the PASSWORD column in DBA_USERS contains the hashed password. By querying this column, a DBA can obtain the hash value:

SELECT username, password FROM dba_users WHERE username = 'TARGET_USER';

In Oracle 11g and later, the PASSWORD column is hidden (returns NULL). The same hash can be retrieved from the internal USER$ table:

SELECT name, password FROM sys.user$ WHERE name = 'TARGET_USER';

With the hash in hand, the DBA can execute an ALTER USER statement that re‑uses the existing hash, effectively unlocking the account without needing the original clear‑text password:

ALTER USER TARGET_USER IDENTIFIED BY VALUES 'hashed_value_from_user$';

After this operation, the account status changes from EXPIRED & LOCKED to EXPIRED. The user will be prompted to set a new password on next login, but the immediate business impact is removed because the middleware can continue using the existing credentials.

Technical Conclusion

The described method allows DBAs to restore access for locked, expired accounts without knowing the original password, leveraging Oracle’s internal password hash storage. While effective, it should be used sparingly; a robust password‑management policy that includes regular rotation and proper reuse settings is recommended to avoid such situations.

Tags: oracle, password‑expiration, database‑security, dba, user‑unlock