Back to the Basics: Locking/Unlocking a user account in Oracle
Posted by decipherinfosys on January 9, 2008
We can prevent user access in Oracle by locking the user account and can allow the access by unlocking the user account. When a database is created using dbca (database configuration assistant), all the users except sys and system are created in locked status. DBA has to unlock it for general availability. We can even create the user in locked status and can unlock it later on.
Connect to sys or system user using SQL*Plus. Create the user in locked status and grant it the connect privilege.
SQL> CREATE USER DECIPHER IDENTIFIED BY DECIPHER ACCOUNT LOCK;
SQL> GRANT CONNECT TO DECIPHER;
If we try to connect to user we get the error that account is locked.
SQL> CONNECT DECIPHER/DECIPHER@orcl
ORA-28000: the account is locked
By default whenever user is created manually, it is created in unlocked status unless otherwise specified. Now let us unlock the account to make it available to users.
SQL> ALTER USER DECIPHER ACCOUNT UNLOCK;
Now we can connect to user successfully.
SQL> CONN DECIPHER/DECIPHER@orcl
Sorry, the comment form is closed at this time.