Lock and unlock a user

You can lock a user that does not need to access OceanBase Database. The locked user cannot log on to OceanBase Database. You can unlock the user as an administrator.

Prerequisites

If you log on as the administrator, you can directly lock and unlock users. If you log on as a regular user, you must have the global ALTER USER privilege to lock and unlock users. For information about how to view your privileges, see View user privileges. If you do not have the global ALTER USER privilege, request the administrator to grant you the privilege. For more information, see Modify user privileges.

Procedure

1. Log on to a MySQL tenant of the cluster.

2. Execute the following statement to lock or unlock a user.

   Sample statement:

   ALTER USER user_name ACCOUNT LOCK | UNLOCK;
   

   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 (0.02 sec)
     
     obclient> obclient -udemo@demo0_111 -P2881 -h10.10.10.1   -p******
     Welcome to the OceanBase.  Commands end with ; or \g.
     Your OceanBase connection id is 3221583856
     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.
     
     obclient>
     

3. After you lock or unlock a user, you can check the is_locked field in the DBA_OB_USERS view to verify the lock status of the user as the administrator.

   Sample code:

   obclient [(none)]> SELECT user_name,is_locked FROM oceanbase.DBA_OB_USERS WHERE user_name='demo';
   +-----------+-----------+
   | user_name | is_locked |
   +-----------+-----------+
   | demo      | NO        |
   +-----------+-----------+
   1 row in set
   

   If the value of the is_locked field is YES, the user is locked. If the value of the is_locked field is NO, the user is unlocked.

More information

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