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 strategy

In the MySQL mode of OceanBase Database, you can set the connection_control_failed_connections_threshold parameter for a tenant to specify the threshold for control over failed logon attempts. If the number of consecutive failed logon attempts exceeds the value of this parameter, the system locks the account.

The default value of the connection_control_failed_connections_threshold parameter is 0, and the value range is [0,2147483647]. Take note of the following items when you specify a parameter value:

• A value of 0 indicates that the feature is disabled. In this case, no action is taken against failed logon attempts.

• If you set the parameter to a value other than 0, the account of a user is locked when the number of failed logon attempts by the user exceeds the specified value. 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 in milliseconds after the number of consecutive failed logon attempts exceeds the specified threshold. The value range is [1000,2147483647], and the default value is 1000.

    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 in milliseconds after the number of consecutive failed logon attempts exceeds the specified threshold. The value range is [1000,2147483647], and the default value is 2147483647.

    For more information about the connection_control_max_connection_delay parameter, see connection_control_max_connection_delay.

Examples

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.

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

4. Verify the logon failure handling strategy. If you enter an incorrect password five consecutive times, your account 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 as 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. Unlock the user.

   Notice

   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 more information about how to view and grant user privileges, see View user privileges and Grant direct privileges.

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