Categories: SQL Complex Queries

How to recover the expired user in Oracle?

In my previous articles i have given idea about the different questions related to oracle. In this article i would like to throw light on most important interview question – How to recover the expired user in Oracle?Think of the situation where you have an expired oracle database account and we have to recover that account on urgent basis.

Real life situation :

Sometimes DBA dont know about the situation of production users and by mistake they used expired account for production application environment and assign that to particular user. We do not have any documented resolution for these kind of situations but we could deal with the situation and follow the following steps :

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

Create User Amit Identified by “Amiet@123456”;

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

SELECT username, account_status, expiry_date
 FROM dba_users
 WHERE username = ‘Amit’;

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE
—————————— ——————————– ———
Amit OPEN                             12-AUG-20

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

ALTER USER Amit PASSWORD EXPIRE;

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

SELECT username, account_status, expiry_date
 FROM dba_users
 WHERE username = ‘Amit’;

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE
—————————— ——————————– ———
Amit 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 Amit as first step. You need to log in with the DBA privilleges,

SELECT username, account_status, expiry_date
 FROM dba_users
 WHERE username = ‘AMIT’; 

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

Query to get password in encrypted format :

SELECT DBMS_METADATA.get_ddl (‘USER’, ‘AMIT’) as “User_Info”
FROM DUAL;

You will get the output as below :

User_Info
——————————————————————————–
CREATE USER “Amit” 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

Step 3 : Alter the specified user

ALTER USER “AMIT”
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=’AMIT’;

NAME                           PASSWORD
—————————— ——————————
AMIT B9C89A643D04449E

alter user AMIT identified by values ‘ B9C89A643D04449E‘;

User altered.

You may test the user status again and it will be now open. So if these kind of situation occurs you may just alter the user and update it to current password in oracle. I hope you understand the answer of the question –How to recover the expired user in Oracle?

Amit S

Oracle Consultant with vast experience in Oracle BI and PL/SQL Development. Amiet is the admin head of this website who contributes by preparing tutorials and articles related to database technologies. He is responsible to manage the content and front-end of the website.

Share
Published by
Amit S

Recent Posts

What is Root Cause Analysis (RCA) With real examples

In my previous article I have given details about application support engineer day to day…

1 month ago

Application Support Engineer Day to day responsibilities

In my previous articles I have given the roles and responsibilities of L1,L2 and L3…

1 month ago

What is mean by SLA ( Service Level Agreement) with Examples?

In my previous articles i have given the hierarchy of production support in real company…

1 month ago

What is Production support Hierarchy in organization?

In this article i would like to provide information about production support organization structure or…

1 month ago

What are roles and responsibilities for L3 Support Engineer?

In my previous article I have given roles for L1 and L2 support engineer with…

1 month ago

What are roles and responsibilities of L2 Engineer?

I have started this new series of how to become application support engineer. This article…

1 month ago