Pages

Thursday, March 2, 2023

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.

No comments:

Post a Comment