Locking and Unlocking User Accounts in Oracle Database
In this article, I am going to discuss Locking and Unlocking User Accounts in Oracle Database. Please read our previous article where we discussed Connecting to Oracle Database using tnsnames.ora.
Locking and Unlocking User Account in Oracle Database:
In this article, we try to play with a user account. Let us try to unlock the account, reset the password, and log in through that user. There is a user called hr we will try to unlock the user. This user exists in the pluggable database. When we do the installation of the database there will be a checkbox of install sample schema. If we didn’t select the check box the sample schema doesn’t exist. There is another way to install a sample schema in the database.
Step 1: Open the Linux machine and log in as an oracle user.
Open a terminal and go to the below location.
Once you found the location check the list of files present in the folder.
Right below you can see hr_main.sql, you need to run that file in the database to create sample schema and sample tables. For further information, you can visit this oracle website. Installing Sample Schemas (oracle.com)
Step 2: Unlock the hr user.
Log in to the database as “sqlplus / as sysdba”. Checked whether the pluggable database is open or not. If the database is not open then the database. In my case, I have already opened the pluggable database.
Move to the pluggable database using the command “alter session set container=jayapdb;”
Once you switched to the pluggable database, we need to check the list of all the users present inside the database. There is a query to check the list of users present inside the pluggable database.
Query: select username from all_users;
We can see there is hr is present inside the database. So, let’s try to unlock the user using the following command.
Command: alter user hr account unlock;
Note: There will always be a syntax to unlock the account. The syntax of the account is as follows.
Syntax: alter user username account unlock;
Step3: Reset the password for the hr user.
If any user forgot the password only DBA can unlock the password for the user. To change the password, we need to log in as DBA i.e. (sysdba). So, we already have logged in as sysdba. Let’s try to change the password for the hr user by the following command. The syntax for resetting the password is as follows.
Syntax: alter user username identified by password;
Command: alter user hr identified by hr;
Step 4: Connect to hr user.
In the previous sessions, we connected to only the sys users or system users. Now, let’s try to connect to another user. We are going to connect to the hr user.
Command: conn hr/hr@jayapdb;
Step 5: Query to find the list of tables of hr user.
As this is the sample schema created by oracle. There are tables present inside this hr user. We will try to list out all the tables present inside the hr user.
Query: select table_name from user_tables;
There are seven tables present inside the hr user.
Step 6: Connect to hr user remotely using SQL developer.
Open the SQL developer and enter the user as hr and password for hr user.
Enter the service name as jayapdb.com and the hostname as oracletest.com and the port number as 1521. We are using the basic connection type as this is easy to use.
Once you have entered all the details click on test. If the test is a success, then click on connect.
Once you click on connect. On the left side, you can see connections. If you check there, you can see hr user.
Click on the ‘+’ icon present. There you can see a list of objects present. Click on tables.
This shows all the list of tables present inside the hr user. Now, we are on the employees tab. Besides to employee’s tab, there is a tab called hr user. If you click on the hr user tab it displays an empty page where we can write queries in the database.
In the above image, you can see have marked the tabs we used to switch between the employees table and the hr user query builder. We have executed a query “select * from employees;”. After entering the query there is a green triangle click on it. This will execute all the queries written in the query builder. Below is the output.
We have gone through how to unlock a user and change the password for a user and run a sample query in the database.
In the next article, I am going to discuss Saving and Running Scripts in Oracle Database. Here, in this article, I try to explain Locking and Unlocking User Account in Oracle Database and I hope you enjoy this Locking and Unlocking User Account in Oracle Database article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.