Posted by: Monish | October 10, 2008

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.

About these ads

Responses

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

    • # 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;

      • Hi sir
        My oracle account is locked.I tried the unlock commands ,then its showing not connected.Plz suggest me some ideas.
        Thank you

      • Try to unlock it using sys then login using ur user from diff session

  2. 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…?

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

  3. 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?

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

  4. unlock me

  5. Mostly thankes my dear I

    • 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

      • 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’;

      • 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…..

  6. THANQ VERY MUCH,IT’S REALLY WORKING.

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

  8. thank u sir

  9. Very Helpful !!!

  10. dats gud, and also its better to give different ways to unlock the table

  11. thank you so much to solve my problem

  12. how to check database in my oracle DB by run Command prompt

    • Hi Naeem,

      Your question is not clear to me. Can you pls elaborate.

      ~ Monish

    • select name from V$DATABASE
      BY DEEPAK

  13. Super

  14. 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?….

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

  15. can we find out at what time was tha account locked…?

    • select username, account_status, lock_date from dba_users where account_status like ‘%LOCKED%’
      /

  16. showing error account is locked when using scott tiger

  17. Thanks for u r support now i can easily solved this type of query, once again thanks

  18. A lead developer called the other day to lock an account. Outline the steps for me. Thank!

  19. Can you please explain PATH file not found
    OGJA file not found

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

  21. if you are connected with system then that will not give you and error.

  22. thank you,
    The above syntax really help me for unloking my account


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

Follow

Get every new post delivered to your Inbox.