Lock or unlock a user

If a user temporarily does not need access to OceanBase Database, the administrator can lock the user. A locked user cannot log on to the database. The administrator can also unlock users to reactivate them.

Prerequisites

The operation of locking or unlocking a user is generally performed by the administrator. If a regular user needs to perform the locking or unlocking operation, they must have the global ALTER USER privilege. For information about how to view user privileges, see View user privileges. If the user does not have the global ALTER USER privilege, contact the administrator to obtain the privilege. For information about how to grant privileges to a user, see Modify user privileges.

Procedure

1. Log on to an Oracle tenant of the cluster.

2. Lock or unlock a user.

   The syntax is as follows:

   ALTER USER user_name ACCOUNT LOCK | UNLOCK;
   

   Here are some examples:

   • Lock a user

     obclient> ALTER USER demo ACCOUNT LOCK ;
     Query OK, 0 rows affected
     
     obclient -udemo@demo0_111 -P2881 -h10.10.10.1   -p******
     obclient: [Warning] Using a password on the command line interface can be insecure.
     ERROR 3118 (HY000): User locked
     

   • Unlock a user

     obclient> ALTER USER demo ACCOUNT UNLOCK ;
     Query OK, 0 rows affected
     
     obclient -udemo@demo0_111 -P2881 -h10.10.10.1 -p -A
     Enter password:
     Welcome to the OceanBase.  Commands end with ; or \g.
     Your OceanBase connection id is 3221584177
     Server version: OceanBase 4.0.0.0 (r100000172022101218-6ab80a3950710941946c004d805fcfded7a4aa2c) (Built Oct 12 2022 18:43:39)
     
     Copyright (c) 2000, 2018, OceanBase Corporation Ab and others.
     
     Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
     

3. Query the is_locked field in the ALL_VIRTUAL_USER_REAL_AGENT table to verify the locking status of the user as the administrator.

   Here is an example:

   obclient> SELECT user_name,is_locked FROM ALL_VIRTUAL_USER_REAL_AGENT WHERE user_name='DEMO';
   +-----------+-----------+
   | USER_NAME | IS_LOCKED |
   +-----------+-----------+
   | DEMO      |         1 |
   +-----------+-----------+
   1 row in set
   

   If the value of the is_locked field is 1, the user is locked. If the value of the is_locked field is 0, the user is unlocked.

References

For more information about the ALTER USER statement, see ALTER USER.