How to unlock an account in the Oracle database

This article explains how we can unlock a user account in an Oracle database. As a database administrator, managing the security of the database is a crucial task. The DBAs must keep track of all successful and failed authentication in an Oracle database.

In a previous organization, we had to remove the employee's credentials when they resigned. As a part of the process, we dropped the user from the database. Sometimes, the same employee rejoins the company. As part of the onboarding process, we had to re-create the user account in the database.

Recreating the user was complicated because we had to reassign access to the schemas and database objects. Therefore, to make the process more efficient, we decided to lock the users who left the company. We keep the user for a certain time, and once we get confirmation, we drop the user.

In this article, I’ll explain how we used to unlocka user account in an Oracle database. We will learn the following:

1. How to unlock the user using SQL*Plus
2. How to unlock the user using Oracle SQL Developer.

To demonstrate the scenarios, I have prepared a demo environment with the following details:

Environment Setup

I have installed Oracle 21c express edition on my workstation and created a pluggable database named EltechDB. I have created three users, and the details are following.

I will demonstrate how we can lock and unlock the EltechApp user.

How to unlock a user using SQL*Plus.

To unlock an existing user account in an Oracle databse, we can use the ALTER USER ACCOUNT UNLOCK statement. The syntax is following.

Alter user [user_name] identified by [new_password] account unlock;

In the syntax,

• user_name: Provide the user name that you want to lock. The user must exist on the pluggable database.
• new_password: When we unlock any user, we must specify the new password because the old password is expired and must be reset.

First, connect to the pluggable database using an SYS user to unlock any user. We are connecting to a pluggable database; therefore, the syntax of the sqlplus command is different. The syntax is following:

sqlplus [user_name]@[host_name]:[port_number]/[pluggable_database]

In our case, we are connecting to the EltechDB database; hence the SQL*Plus connect command is as follows

sqlplus sys@Nisarg-PC:1521/eltechDB as sysdba

Screenshot

Alternatively, you can access the pluggable database by using the ALTER SESSION command. Suppose you are connected to the XE instance using the SYS account; run the following statement to use the EltechDB. The command to alter the session is following:

SQL> ALTER SESSION SET CONTAINER = EltechDB;

Screenshot

Now, let’s populate the list of locked users.

We can use the dba_users data dictionary view, but it shows normal and system users. The system users are created and maintained by Oracle. Therefore to filter, the Oracle maintained users, we will use a data dictionary view named all_users. In the all_users data dictionary view, you can filter the oracle-maintained users by setting the value of oracle_maintened=’N’.

We are going to join both data dictionary views to populate the locked users. The final query is following:

COLUMN USERNAME HEADING "USER NAME" Format a15

COLUMN ACCOUNT_STATUS HEADING "ACCOUNT STATUS" Format a15

select dbauser.username,dbauser.account_status from all_users alluser inner join dba_users dbauser on alluser.user_id=dbauser.user_id where alluser.oracle_maintained = 'N';

Query output

As you can see in the above screenshot, the ELTECHAPP account is locked. Let’s try to access the EltechDB using the ELTECHAPP account.

sqlplus ELTECHAPP@Nisarg-PC:1521/eltechdb

Output

As you can see, the command returned an error. Now, to unlock the account in the database, run the following query.

SQL> Alter user ELTECHAPP identified by ELTECHAPP account unlock;

Screenshot

Once the account is unlocked, try to access EltechDB using the ELTECHAPP account.

sqlplus ELTECHAPP@Nisarg-PC:1521/eltechdb

As you can see, the ELTECHAPP account can access the database.

Now, let’s understand how to unlock the user using the SQL Developer tool.

How to unlock a user using SQL Developer.

The SQL Developer is software supplied by Oracle and used to create and manage various database objects and users. Also, we can run the PL/SQL scripts and queries to populate the data from the tables.

To unlock the user account in an Oracle databse, launch the SQL Developer tool and configure the connection as shown in the following image.

In the connection pan, expand the Eltech à Expand Other Users à Right-click on EltechAPP à Select Edit User.

The Dialog box opens.

In the dialog box, you can see that the option Account is Locked is checked. Un-check the Account is locked option and specify the new password in the New Password and Confirm Password text box. Click Apply to unlock the user and close the dialog box.

To verify the changes, run following query

COLUMN USERNAME HEADING "USER NAME" Format a15

COLUMN ACCOUNT_STATUS HEADING "ACCOUNT STATUS" Format a15

select dbauser.username,dbauser.account_status from all_users alluser inner join dba_users dbauser on alluser.user_id=dbauser.user_id where alluser.oracle_maintained = 'N';

Query Output

As you can see, the EltechAPP account is unlocked.

Summary

In this article, we learned different methods to unlock a user’s account in an Oracle database.

Have questions, comments? 

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users. 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post.