Wednesday, 19 June 2013

Lock and Unlock the User


This post will describe how to [lock] and [unlock] the user in oracle database.

command to lock and unlock the user in oracle below

there are two type of scenario which has been described here under,

1. Existing User 
2. New User

If suppose you have [HR] user which has to be unlocked, then you follow this step, because it is already in your database but while creating database user did not enable it, means it is not unlocked. Now he/she needs to unlock the [HR] user.

oracle@localhost~]$sqlplus /NOLOG
sql>show user

probably it will show the "sys" user. Now connect with [System] user, because it has DBA rights.
Sql>connect system/manager@orcl 
[here my system user password is manager and orcl is connection string]
if you do not have the connection string then, user normal connect like as follows.

Sql>connect system/manager

Sql>show user 

Now write the command to unlock the [HR] user.

Sql> alter user HR account unlock;
user altered.

if suppose you want to change the password of HR while unlocking the user then use this command.

Sql>alter user HR identified by HR123 account unlock.
user altered

For new user follow the following step.
your supervisor told you to create an user with the name "OraEmp113" and password is "emp" and initially status should be locked.

oracle@localhost~]sqlplus /as sysdba
Sql> show user
probable it show the [SYS] user.
now you connect with the [System] user .
Sql>connect System/manager
Sql> show user

Now create a user here with given name
command to create user is here under,

create user <usrename> identified by <password> account lock|unlock;

Sql>create user OraEmp113 identified by emp account lock;
user created.
grant this user connect and resource here.
Sql>Grant connect,resource to OraEmp113

now unlock the user.

Sql>Alter user OraEmp113 account unlock;
user altered.

Thanking you

Mohammad Shahnawaz

1 comment:

  1. Hi,
    Nice article, very helpful, it is clear defined.

    thanking u