Logon failure processing

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

Logon failure processing policy

In the MySQL mode of OceanBase Database, the tenant-level parameter connection_control_failed_connections_threshold controls the threshold of failed logon attempts. The value range is [0,2147483647], and the default value is 0.

Note the following:

• When this parameter is set to 0, no action will be taken against the failed logon attempts.

• When this parameter is not set to 0, if the number of a user's consecutive failed logon attempts exceeds the value of this parameter, the user will be locked. The lock duration is calculated by 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 indicates the number of consecutive failed logon attempts by the user. The value of the current_failed_login_num parameter is greater than or equal to that of the connection_control_failed_connections_threshold parameter.

  • connection_control_min_connection_delay indicates the minimum lock duration after the number of consecutive failed logon attempts exceeds the specified threshold. The value range is [1000,2147483647], and the default value is 1000, in milliseconds.

    For more information about the connection_control_min_connection_delay parameter, see connection_control_min_connection_delay.

  • connection_control_max_connection_delay indicates the maximum lock duration after the number of consecutive failed logon attempts exceeds the specified threshold. The value range is [1000,2147483647], and the default value is 2147483647, in milliseconds.

    For more information about the connection_control_max_connection_delay parameter, see connection_control_max_connection_delay.

Configuration example

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

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

2. Set the threshold of failed logon attempts to 5, and set the minimum lock duration and maximum lock duration after the number of consecutive failed logon attempts reaches the specified threshold to 60,000 ms and 360,000 ms respectively.

   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 '*******';
   Query OK, 0 rows affected (0.04 sec)
   

4. Check whether the logon failure processing policy has taken effect.

   If five consecutive incorrect passwords are entered, the user will be locked.

   obclient -h127.1 -P2881 -utest@mysql -p*******;
   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 and view information about failed logon attempts.

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

5. Unlock the user.

   Notice

   The operation of unlocking a user is generally performed by the administrator. If a regular user needs to perform the unlocking operation, they must have the global ALTER USER privilege. 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)