Locking a user account

In this recipe, you'll learn to lock and unlock user accounts.

Getting ready

To complete this recipe, you'll need an existing (for example, OS-authenticated) user who has alter user privilege (you may use user who has a DBA role) and another existing user (for example, mike).

How to do it...

1. Connect to the database as a user who has alter user privilege:

   $ sqlplus /
   
   

2. Lock the account of user mike:

   SQL> alter user mike account lock;
   
   

3. Unlock the account of user mike:

   SQL> alter user mike account unlock;
   
   

How it works...

In step 1, you used OS authentication to connect to the database.

In step 2, you locked the account of user mike. This means that user mike cannot connect to the database:

    SQL> alter user mike account lock;

    User altered

    SQL> connect mike/welcome1

    ERROR: ORA-28000: the account is locked

However, objects in mike's schema are available, so users can access them (considering that they have necessary privileges):

    SQL> select a, b from mike.table1;
      A         B 
     ---------- --------- 
             1         3 
             2         4 
             4         9

Tip

It is recommended that you lock the accounts of users that own your application objects (application schemas).

In step 3, you unlocked the account of user mike. Now user mike can successfully connect to the database:

    SQL> alter user mike account unlock;

    User altered.

    SQL> conn mike/welcome1

    Connected.

See also

• Creating and using OS-authenticated users