How to reset an Oracle database user password using an encrypted password

When a user's account is marked as expired in Oracle Database, it indicates the account is in a state where the user cannot log in until the issue is resolved. This typically happens because of password expiration policies.If you want to reset the password using an encrypted value then we can follow below steps

1) Query the Existing Encrypted Password

If you want to reset the password to its previous encrypted value, you can retrieve it from the dba_users or user$ tables.

Older Version of DB

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

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

We might use spare4 column if the password field is blank.

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

2) Reset Password Using the Encrypted Value

ALTER USER <username> IDENTIFIED BY VALUES '<encrypted_password>';

Example:

ALTER USER TARGET_USER IDENTIFIED BY VALUES '4CFD12416C11EC7D75C2F3F4AEF3C93D8C94F1AC3823A38ED603A9427';

3) Validate the User account

SELECT

username,

account_status,

expiry_date,

lock_date

FROM

dba_users

WHERE

username = 'TARGET_USER';

On more way:

Get the command directly using below

with u as

( select (dbms_metadata.get_ddl('USER','SCOTT')) ddl from dual ) select replace(substr(ddl,1,instr(ddl,'DEFAULT')-1),'CREATE','ALTER')||';' from u;

SCOTT- is the username whose password needs to be reset

Output :

ALTER USER "APPS" IDENTIFIED BY VALUES 'S:94FF9647DA884FC554E6E7851403B6939C4

460430';

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

How to reset an Oracle database user password using an encrypted password