Systems Engineering and RDBMS

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;

User created.

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
ERROR:
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;

User altered.

Now we can connect to user successfully.

SQL>  CONN DECIPHER/DECIPHER@orcl
Connected.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: