Sep 20, 2022 3:00:00 AM by Nisarg Upadhyay
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:
To demonstrate the scenarios, I have prepared a demo environment with the following details:
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.
Pluggable Database Name | User Name |
EltechDB |
|
I will demonstrate how we can lock and unlock the EltechApp user.
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,
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.
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.
Try Toad free for 30 days.Already in a trial? Talk to sales or buy now online. Already a loyal fan of Toad for Oracle? Renew now. |
In this article, we learned different methods to unlock a user’s account in an Oracle database.
Head over to the Toad for Oracle forum on Toad World®! Chat with Toad developers, and lots of experienced users.
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.
Tags: Toad for Oracle
Written by Nisarg Upadhyay
Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 11 years of experience with SQL Server administration and 2 years in managing oracle database. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com.
We use cookies to improve your experience with our site. By continuing to use this site, you consent to our use of cookies. Learn more.