Login failure processing

If OceanBase Database is in MySQL mode, for users who have multiple failed logon attempts, the system will lock the user to prevent malicious password attacks, thereby improving database security.

Logon failure processing policy

In MySQL mode, OceanBase Database controls the threshold of failed logon attempts by using a tenant-level configuration item called connection_control_failed_connections_threshold. When a user exceeds the value defined by this configuration item for consecutive failed logon attempts, the system locks the account.

The default value of the connection_control_failed_connections_threshold configuration item is 0, and the value range is [0, 2147483647]. Note the following:

• When the configuration item is set to 0, it means that this feature is turned off, and the system will not process the user's failed logon attempts.

• When the configuration item is not set to 0, if a user's failed logon attempts exceed the specified value, the system will lock the account. The specific lock time is calculated using the following formula:

  MIN(MAX((current_failed_login_num + 1 - connection_control_failed_connections_threshold) * 1000, connection_control_min_connection_delay), connection_control_max_connection_delay)

  where

  • current_failed_login_num: the number of consecutive failed logon attempts by the user. The current_failed_login_num is greater than or equal to connection_control_failed_connections_threshold.

  • connection_control_min_connection_delay: the minimum lock time after exceeding the threshold of consecutive failed logon attempts. The value range is [1000, 2147483647] and the default value is 1000, measured in milliseconds.

    For more information about connection_control_min_connection_delay, see connection_control_min_connection_delay.

  • connection_control_max_connection_delay: the maximum lock time after exceeding the threshold of consecutive failed logon attempts. The value range is [1000, 2147483647] and the default value is 2147483647, measured in milliseconds.

    For more information about connection_control_max_connection_delay, see connection_control_max_connection_delay.

Configuration example

1. Log on to a MySQL tenant of a cluster using the root user.

   obclient -uroot@mysql -h127.1 -P2881 -p****1***
   

2. Execute the following statement to set the maximum number of failed logon attempts for a user to 5. The minimum lock time for failed logon attempts after exceeding the threshold is set to 60,000 milliseconds and the maximum lock time is 360,000 milliseconds.

   Example:

   obclient> ALTER SYSTEM SET connection_control_failed_connections_threshold=5;
   
   obclient> ALTER SYSTEM SET connection_control_min_connection_delay=60000;
   
   obclient> ALTER SYSTEM SET connection_control_max_connection_delay=360000;
   

   For more information about the parameters, see Modify cluster parameters.

3. Create a user.

   obclient> CREATE USER 'test' IDENTIFIED BY '***1***';
   Query OK, 0 rows affected (0.04 sec)
   

4. Check whether the logon failure processing policy is effective. If 5 consecutive incorrect passwords are entered, the user will be locked.

   obclient -h127.1 -P2881 -utest@mysql -p**123**;
   obclient: [Warning] Using a password on the command line interface can be insecure.
   ERROR 5039 (01007): User locked
   

   Log on to a MySQL tenant of OceanBase Database using the root user, to view the logon failure information.

   obclient> SELECT * FROM information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
   +-------------+-----------------+
   | USERHOST    | FAILED_ATTEMPTS |
   +-------------+-----------------+
   | 'test'@'%' |               5 |
   +-------------+-----------------+
   1 row in set (0.005 sec)
   

5. Execute the following SQL statement to unlock the user.

   Notice:

   The operation of unlocking a user is generally performed by the administrator. If an ordinary user needs to perform lock and unlock operations, they must have global ALTER USER privileges. For information about how to view and grant user privileges, see View user privileges and Modify user privileges.

      obclient> ALTER USER test ACCOUNT UNLOCK;
      Query OK, 0 rows affected (0.03 sec)