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';

OR

SELECT name, password 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';




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