Lock/Unlock Oracle Users

In spite of deleting an oracle user, we can LOCK it. In that way the user won’t be accessible. In future if we want we can UNLOCK it.

create a user named JACK which is LOCKED:

SQL> create user jack identified by jack account lock;

User created.

Now grant him the CONNECT & RESOURCE privilege:

SQL> grant connect, resource to jack;

Grant succeeded.

Now, try to connect the user:

SQL> conn jack/jack;
ERROR:
ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

Now, connect to the SYS accout:

SQL> conn sys as sysdba
Enter password:
Connected.
SQL>

Now, see the account status:

SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE from dba_users where USERNAME=’JACK’;

USERNAME                       ACCOUNT_STATUS                   LOCK_DATE
------------------------------ -------------------------------- ---------
JACK                           LOCKED                           10-OCT-08

To, unlock the Jack:

SQL> alter user jack account unlock;

User altered.

SQL>
SQL> conn jack/jack;
Connected.

==================

Otherwise we can do one thing, we can revoke its CONNECT privilage. In that way the user can’t connect to the db.

SQL> revoke connect from jack;

Revoke succeeded.

SQL> conn jack/jack;
ERROR:
ORA-01045: user JACK lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
SQL> grant connect to jack;

Grant succeeded.

SQL>
SQL> conn jack/jack;
Connected.

>>>Please leave your commant on the post.