SIGN UP MEMBER LOGIN:
   
ARTICLE

How to lock and unlock user account in Oracle Database

Posted by Pamilla Articles | DB Administration March 24, 2011
We will create a new user account in Oracle database and we will use lock and unlock option for Oracle database user account. We will use Oracle enterprise manager and SQL command in this article.
Reader Level:

In this article, we will learn how to lock and unlock user account in Oracle Database. We will create a new user account in Oracle database and we will use lock and unlock option for Oracle database user account. If your account is lock then you can not work any types in database.

Syntax: First of all, we will create a new user. We can create new user through the admin.

CREATE USER <user_name>
IDENTIFIED BY [<password> | EXTERNALLY]
[DEFAULT TABLESPACE<def_tablespace_name>]
[TEMPORARY TABLESPACE <tmp_tablespace_name>]
[QUOTA (<integer> (K | M) ON <tablespace_name>],...
[PROFILE<profile_name>] [PASSWORD EXPIRE]
[ACCOUNT [LOCK |UNLOCK]];

Example:

SQL> Create user abc identified by abc;

SQL> Grant connect,resource to abc;

Syntax: We can lock Oracle database user account through the "account lock".

Alter user user_name account lock;

Example:

SQL> Alter user abc account lock;

SQL> Connect abc/abc

Output:

Account-lock1.gif

We can check our account is lock and unlock through the dba_users data dictionary.

SQL> Connect sys/oracle as sysdba

SQL> Desc dba_users

SQL> Select username,account_status,Lock_date
2 from dba_users
3 where username='ABC';

Output:

Account-lock2.gif

Syntax: We can unlock Oracle database user account through the "account unlock".

Alter user user_name account unlock;

Example:

SQL> Alter user abc account unlock;

SQL> Select username,account_status,Lock_date
2 from dba_users
3 where username='ABC';

SQL> Connect abc/abc

Output: 

Account-lock3.gif

Lock and unlock user account in Oracle enterprise manager.

First of all, we will go to Start=>All Programs=> Oracle-OraDb10g_home1=> Database Control-OMFDATA and specifying the system username and password after that we will click Login button.

The home page is open and we will click Administration tab. The Administration page is displaying. We will click Users option under the Users & Privilege.

Image1

Account-lock4.gif

The Users page is displaying. We will select the username and click the actions dropdown arrow button. This is show the list you can select any one.

1. Lock User:  The lock user option we will select from the actions list, and then click Go.

2. Unlock User:  The unlock user option we will select from the actions list, and then click Go.

Image2

Account-lock5.gif

The Confirmation page is displaying. You want to change then click Yes otherwise No.

Image3

Account-lock6.gif

The Update Message page is displaying. This page give the message " User ABC has been Locked successfully".

Image4

Account-lock7.gif 

Login to add your contents and source code to this article
This Feature is Sponsored By
post comment
COMMENT USING