🔐 How to Retain the Same Password for an Expired Oracle Schema (12c & 19c)

🔐 How to Retain the Same Password for an Expired Oracle Schema (12c & 19c)

Sometimes, you might encounter an expired user account in Oracle and want to restore it without asking the end user for their password. This is a common scenario when automating user restores, clones, or refreshes across environments.

Here’s a step-by-step guide for both Oracle 12c and Oracle 19c to extend the password of an expired user account without changing it or involving the user.

---

🔧 Oracle 12c – Steps to Restore Expired User Without Reset

1️⃣ Check the Account Status

SELECT username, account_status 
FROM dba_users 
WHERE username = 'TEST';

2️⃣ Retrieve the Password Hash from SYS.USER$

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

3️⃣ Reapply the Same Password Hash

ALTER USER TEST IDENTIFIED BY VALUES '5C3EFBA4ED100B47';

4️⃣ Recheck the Account Status

SELECT username, account_status 
FROM dba_users 
WHERE username = 'TEST';

---

🧩 Oracle 19c – Password Retention Using SHA-512 Hash

1️⃣ Check the Account Status

SELECT username, account_status 
FROM dba_users 
WHERE username = 'TEST';

2️⃣ Extract the Hashed Password from SPARE4

SELECT spare4 
FROM sys.user$ 
WHERE name = 'TEST';

3️⃣ Reapply the Combined Password Hash

ALTER USER TEST IDENTIFIED BY VALUES 
'S:27D95B70...;T:26FB34A3...';

4️⃣ Confirm Account is Open

SELECT username, account_status 
FROM dba_users 
WHERE username = 'TEST';

---

🔐 Why This Works

• Oracle stores password hashes in SYS.USER$ (12c and 19c).
• Using ALTER USER IDENTIFIED BY VALUES, you can set a user’s password using the existing hash.
• This avoids prompting the user to change or re-enter the password.

⚠️ Security Tips

• Restrict access to SYS.USER$ – password hashes are sensitive.
• Use this method only for internal automation or disaster recovery scenarios.
• Ensure proper auditing and logging when applying password changes via hash.

---

🎯 This method is extremely useful for Oracle DBAs managing automated clones, refreshes, or disaster recovery without compromising user confidentiality.