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.
