Normal 0
false false false MicrosoftInternetExplorer4
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.
>>>Please leave your comment on the post.
=========
Readers are leaders. You can only master the craft if you constantly study and keep up the hands-on practice. Following are some of the great books for your reference.
Please use the below links to grab your copy. Thank you! 🙂
Expert Oracle Database Architecture (by Thomas Kyte and Darl Kuhn) –> https://amzn.to/2EZ8eUE
Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions –> https://amzn.to/2F17qPh
Oracle Database 12c DBA Handbook (Oracle Press) –> https://amzn.to/2F0tJoh
RMAN Recipes for Oracle Database 12c-A Problem-Solution Approach –> https://amzn.to/2EYY3ja
Oracle Database 12c Performance Tuning Recipes –> https://amzn.to/2F01uGj
Oracle RMAN 11g Backup and Recovery (Oracle Press) –> https://amzn.to/2NQ3OT9
OCA/OCP Oracle Database 12c All-in-One Exam Guide (Exams 1Z0-061, 1Z0-062, & 1Z0-063) –> https://amzn.to/2F0jTD0
OCA/OCP Oracle Database 11g All-in-One Exam Guide with CD-ROM –> https://amzn.to/2SPPJWJ
Expert Oracle RAC Performance Diagnostics and Tuning –> https://amzn.to/2NTXxWr
Pro Oracle Database 11g Administration (Expert’s Voice in Oracle) –> https://amzn.to/2NRj0iD
Monish's DBA Blog 
ALTER USER USERNAME ACCOUNT UNLOCK/LOCK NOT WORKING IN ORACLE 9I
SO PLEASE PROVIDE A ANSWER THAT MATCH THE ORACLE 9I
THE ERROR IS OCCURED WHEN WE RUN THIS COMMAND “INSUFFICIENT PRIVILIGES.
By: Manish on December 29, 2008
at 3:03 PM
# Start SQL*Plus:
$ sqlplus /NOLOG
# Connect as SYSDBA:
SQL> CONNECT / AS SYSDBA
Change the password according to SQL commands indicated below:
Unlock a password
ALTER USER username ACCOUNT UNLOCK;
Lock a password
ALTER USER username ACCOUNT LOCK;
Change password of an unlocked account
ALTER USER username IDENTIFIED BY password;
Change password of a locked account
ALTER USER username IDENTIFIED BY password ACCOUNT UNLOCK;
By: Monish on December 29, 2008
at 8:02 PM
Hi sir
My oracle account is locked.I tried the unlock commands ,then its showing not connected.Plz suggest me some ideas.
Thank you
By: resh on March 20, 2014
at 1:10 PM
Try to unlock it using sys then login using ur user from diff session
By: Monish on March 20, 2014
at 6:11 PM
alter user username identified by password;
this command worked but the second ithink have some problem i.e. alter user username account unlock;
it display the message INSUFFICIENT PRIVILIGES
what to do…?
By: Aman on February 11, 2009
at 10:40 PM
you can use OS authentication to login as sysdba
sqlplus / as sysdba
the OS user need to be in dba group or ORA_DBA group if Windows.
By: Monish on February 12, 2009
at 11:12 AM
when i connect as sysdba in SQL*Plus ,
it is accepting any username and password .
i.e it is accepting any word for username and any word for password.
so what is the solution?
By: Raju on March 30, 2009
at 8:50 PM
This is snippet from Oracle Docs
Operating system authentication takes precedence over password file authentication. Specifically, if you are a member of the OSDBA or OSOPER group for the operating system, and you connect as SYSDBA or SYSOPER, you will be connected with associated administrative privileges regardless of the username/password that you specify.
This means if You are logging in with OS Authentication it doen’t matter what Username / Password you provide to login as SYSDBA or SYSOPER.
By: SANDESH on May 16, 2012
at 4:50 PM
unlock me
By: ena on April 5, 2009
at 6:40 PM
Mostly thankes my dear I
By: Mohsin on June 1, 2009
at 9:08 AM
I m getting this error in Oracle 7.3.2.3.2
SQL> alter user abc account unlock;
alter user abc account unlock
*
ERROR at line 1:
ORA-00922: missing or invalid option
What could be the issue
By: Zulfi on June 3, 2011
at 10:19 AM
Please confirm:
1. Are you logged in as sys or DBA user ?
2. Please send output of the following query:
select username, account_status from dba_users where username=’ABC’;
By: Monish on June 3, 2011
at 12:16 PM
1. i m logged in as system user.
2. As i mentioned it is an Oracle 7.3.2.3.2 version so account_status and other columns do not exist in the dba_users view…..
also how can i check the status of users in lower version of Oracle…..
By: Zulfi on June 6, 2011
at 9:25 AM
THANQ VERY MUCH,IT’S REALLY WORKING.
By: gouthami on September 25, 2010
at 1:17 PM
SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE from dba_users where USERNAME=’JACK’;
USERNAME ACCOUNT_STATUS LOCK_DATE
—————————— ——————————– ———
JACK LOCKED 10-OCT-08To, unlock the Jack:
SQL> alter user jack account unlock;
User altered.
( bhai heatrly thanks for this querylaunch )
By: farhan on June 23, 2011
at 2:06 PM
thank u sir
By: swapnil on August 6, 2011
at 9:41 AM
Very Helpful !!!
By: SANDESH on May 16, 2012
at 2:15 PM
dats gud, and also its better to give different ways to unlock the table
By: nanda reddy on May 30, 2012
at 8:24 AM
thank you so much to solve my problem
By: naeem marwat on July 13, 2012
at 4:01 PM
how to check database in my oracle DB by run Command prompt
By: naeem marwat on July 13, 2012
at 4:26 PM
Hi Naeem,
Your question is not clear to me. Can you pls elaborate.
~ Monish
By: Monish on August 22, 2012
at 12:13 PM
select name from V$DATABASE
BY DEEPAK
By: deepak on September 22, 2012
at 2:11 PM
Super
By: Nanda Gopal on August 21, 2012
at 8:01 AM
If we can unlock the user then what’s the use of locking user? I know that we can lock user using create user u_name identified by passwd account lock….then if we unlock it. No use of locking right?….
By: Vel on November 3, 2012
at 12:41 AM
Intention behind locking a user account is to prevent the access. There could be many reasons behind locking an account. As you know only a sys user or user with DBA privileges can perform this task; so unless it’s been done the account will remain inaccessible.
By: Monish on November 10, 2012
at 1:28 AM
can we find out at what time was tha account locked…?
By: question on November 21, 2012
at 5:04 PM
select username, account_status, lock_date from dba_users where account_status like ‘%LOCKED%’
/
By: Monish on November 26, 2012
at 10:53 AM
showing error account is locked when using scott tiger
By: Sreeram on December 5, 2012
at 1:02 PM
wat can I do for dat ?
By: Sreeram on December 5, 2012
at 1:03 PM
Thanks for u r support now i can easily solved this type of query, once again thanks
By: Vighnesh on February 5, 2013
at 11:35 AM
A lead developer called the other day to lock an account. Outline the steps for me. Thank!
By: Doude on February 14, 2013
at 7:04 AM
Can you please explain PATH file not found
OGJA file not found
By: Idowu Williams on May 7, 2013
at 7:05 PM
Select user_name,user_status from dba_users where user_status in not null;
for unlocking the user use the following command.
sql>show user
—
scott
if you are connected other “system” user, first connect with that.
sql>connect system
password – *********
sql>show user
—
Manager
now you write the following command to unlock the “HR” user.
sql>alter user HR account unlock;
if you wanna change the password with unlocking the “HR” user.
sql>alter user HR identified by “ME123” account unlock;
user altered
now you can connect with the HR user
sql>connect HR/ME123
By: Mohammad Shahnawaz on June 19, 2013
at 6:45 PM
if you are connected with system then that will not give you and error.
By: Mohammad Shahnawaz on June 19, 2013
at 6:46 PM
thank you,
The above syntax really help me for unloking my account
By: sangram on August 8, 2013
at 12:20 PM
Pls i need admin help
By: Faruk on June 18, 2014
at 12:50 PM
Yes Faruk tell me
By: Monish on June 18, 2014
at 11:50 PM