As a database administrator, you often encounter instances where you need to unlock a locked database account.
A locked account refers to an account that has been disabled either by the database administrator or in cases of excessive failed authentication requests. There are other reasons an account can be locked, but we will not discuss that in this tutorial.
Once an account has been locked or disabled, the said account cannot log in unless the database administrator unlocks that account.
This is what we are going to discuss in this tutorial. In this post, we will learn how to use the Oracle ALTER USER ACCOUNT UNLOCK statement.
Oracle Create Test User
To best illustrate how we can unlock a user in Oracle, let us start by creating a test user for demonstration purposes.
Log in to the database administrator:
Enter password:
Once logged in, alter the session to enable Oracle scripting mode. The scripting mode allows us to execute multiple SQL statements without executing sequentially. It may also prevent various errors when creating a User:
Next, create a user.
Once the user is created, let us manually lock the account to simulate an Oracle account.
We can do this by using the ALTER USER command as shown below:
User altered.
Once the account is locked, we can unlock it by replacing the LOCK keyword in the previous command to UNLOCK.
NOTE: If the account has failed due to incorrect password attempts or password expiration, it is recommended to reset the password. Otherwise, it will remain expired.
You can add the IDENTIFIED BY clause followed by the new password in the following example:
This will reset the password and allow the user to set a new password.
Conclusion
In this article, we discussed the Oracle ALTER USER ACCOUNT UNLOCK clause that allows you to unlock a locked database account. Locking a database user is an incredible security feature when used correctly. However, it can lead to the locking of legitimate accounts when misused. Take this into consideration before implementing it.
