User Password Expire Solution

Oracle User Password Expire Solution 

 Step 1 : To create the specific user to implement the scenario

Create User Nathan Identified by “Nathan@123456”;

Step 2 : Check the status of the user using following query,

SELECT username, account_status, expiry_date

 FROM dba_users

 WHERE username = ‘Nathan’;

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE

—————————— ——————————– ———

Nathan OPEN                             12-AUG-20

Step 3 : We require to Expire the User using following command

ALTER USER Nathan PASSWORD EXPIRE;

Now the account of Nathan is Expired. We require to check the status of the account,

SELECT username, account_status, expiry_date

 FROM dba_users

 WHERE username = ‘Nathan’;

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE

—————————— ——————————– ———

Nathan EXPIRED 12-AUG-20

If you can check the account_status it is showing as Expired.

The above scenario will explain about how do we expire the user password and account.

Resolution of this scenario :

Step 1 : We require to fetch the current password of the user Nathan as first step. You need to log in with the DBA privilleges,

SELECT username, account_status, expiry_date

 FROM dba_users

 WHERE username = ‘Nathan’; 

You will get the status of the user using above query.

Query to get password in encrypted format :

SELECT DBMS_METADATA.get_ddl (‘USER’, ‘Nathan’) as “User_Info”

FROM DUAL;

You will get the output as below :

User_Info

——————————————————————————–

CREATE USER “Nathan” IDENTIFIED BY VALUES ‘S:1256778ASDESADDADDFFFDFDFSFT24FFFASSDFFFBB2;B9C89A643D04449E’

TEMPORARY TABLESPACE “TEMP”

PASSWORD EXPIRE

Step 2 : You will get password in encrypted format and following will be the password :

S:1256778ASDESADDADDFFFDFDFSFT24FFFASSDFFFBB2;B9C89A643D04449E

How to recover the expired user in Oracle?

Step 3 : Alter the specified user

ALTER USER “Nathan”

IDENTIFIED BY VALUES ‘S:1256778ASDESADDADDFFFDFDFSFT24FFFASSDFFFBB2;B9C89A643D04449E‘;

User altered.

Now check the status of the user which will show OPEN.

Step 4 : Other Query to alter the user

                                                      

select name, password from user$ where name=’Nathan’;

NAME                           PASSWORD

—————————— ——————————

Nathan B9C89A643D04449E

alter user Nathan identified by values ‘ B9C89A643D04449E‘;

User altered.