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.
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.
Login into the database administrator:
$ sqlplus sys as sysdba;
Enter password:Once logged in, alter the session to enable Oracle scripting mode. Scripting mode allows us to execute multiple SQL statements without executing sequentially. It may also prevent various errors when creating a User:
ALTER SESSIO SET "_ORACLE_SCRIPT"=true;Next, create a user:
CREATE USER test_user IDENTIFIED BY password;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:
SQL> ALTER USER test_user ACCOUNT LOCK;
User altered.Once the account is locked, we can unlock it by replacing the LOCK keyword in the command above to UNLOCK.
ALTER USER test_user ACCOUNT 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 do this by adding the IDENTIFIED BY clause followed by the new password. Example:
ALTER USER test_user IDENTIFIED BY new_password ACCOUNT UNLOCK;This will reset the password and allow the user to set a new password.
Summary
In this post, you encountered 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.
