Lock or 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.

Lock and unlock a user by using SQL statements

You can use the ALTER USER statement to lock and unlock a user. In most cases, administrators lock and unlock users. If you need to lock and unlock a user, you must be granted the global ALTER USER privilege. After you lock a user, the locked user cannot log on to OceanBase Database.

For information about how to view your privileges, see View user privileges. If you do not have the required privileges, contact the administrator to obtain the privileges. For more information, see Modify user privileges.

The syntax is as follows:

ALTER USER user {ACCOUNT LOCK | ACCOUNT 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***1**
  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 3221583828
  Server version: OceanBase 3.2.4.0 (r100000072022102819-2a28da9e758e2d232c41fa1a1b0070a08b77dd7d) (Built Oct 28 2022 19:46:38)
  
  Copyright (c) 2000, 2018, OceanBase Corporation Ab and others.
  
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  

After you lock or unlock a user, you can query the is_locked field in the ALL_VIRTUAL_USER_REAL_AGENT table to check the locking status of the user. 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.

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

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

Lock and unlock a user in the OCP console

Prerequisites

Before you lock or unlock a user, ensure that:

• You have permissions of the TENANT_MANAGER role. Otherwise, request the OceanBase Cloud Platform (OCP) administrator to assign the role. For more information, see "Edit a user" in the OCP User Guide of the corresponding version.

• Your password box contains the password of the sys user under the tenant. For more information about the password box of an OCP user, see the OCP User Guide of the corresponding version.

Procedure

1. Log on to the OCP console.

2. In the left-side navigation pane, click Tenants to go to the Tenants page.

3. In the tenant list, find the target tenant to which the user belongs and click the tenant name. Then, the Overview page appears.

4. In the left-side navigation pane, click User Management.

5. In the user list, find the target user and set the switch to On or Off in the Lock column.

   Note

   If the lock switch is set to On, the user is not allowed to log on to the database. Proceed with caution.