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 privilege. 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.
