In Oracle and SQL Server databases, you can set up some complex account password policies, such as locking an account more than N times after a failed login, so can you do the same in MySQL?The answer is that MySQL has similar capabilities, except in MySQL it delays the response time when its login fails beyond the threshold, instead of locking the account, in MySQL5.7.17A Connection-Control plug-in is provided to control client response delays after a number of consecutive login failures.The plug-in effectively prevents the risk of violent login (attack) by clients.The plug-in contains the following two components

CONNECTION_CONTROL: Used to control the number of login failures and delay response times

CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS: This table records Login failures to the IS Library

 

 

 

Let's take a look at Connection-Control Plugins. The version of the following experiment is MySQL8.0.18.

 

First look at the system variable plugin_dir, find the path to the plug-in (Plugins), as shown below

 

mysql> select version() from dual;

+-----------+

| version() |

+-----------+

| 8.0.18    |

+-----------+

1 row in set (0.00 sec)

mysql> show variables like 'plugin_dir';

+---------------+--------------------------+

| Variable_name | Value                    |

+---------------+--------------------------+

| plugin_dir    | /usr/lib64/mysql/plugin/ |

+---------------+--------------------------+

1 row in set (0.01 sec)

[root@KerryDB ~]# cd /usr/lib64/mysql/plugin/

[root@KerryDB plugin]# ls -lrt

total 76388

-rwxr-xr-x. 1 root root   106696 Sep 20  2019 rewrite_example.so

-rwxr-xr-x. 1 root root   104432 Sep 20  2019 mysql_no_login.so

-rwxr-xr-x. 1 root root   111464 Sep 20  2019 mypluglib.so

-rwxr-xr-x. 1 root root   106648 Sep 20  2019 auth_socket.so

-rwxr-xr-x. 1 root root   163560 Sep 20  2019 adt_null.so

-rwxr-xr-x. 1 root root   409032 Sep 20  2019 validate_password.so

-rwxr-xr-x. 1 root root  9054776 Sep 20  2019 libpluginmecab.so

-rwxr-xr-x. 1 root root   344696 Sep 20  2019 authentication_ldap_sasl_client.so

-rwxr-xr-x. 1 root root  1145832 Sep 20  2019 rewriter.so

-rwxr-xr-x. 1 root root   625944 Sep 20  2019 ha_example.so

-rwxr-xr-x. 1 root root   388992 Sep 20  2019 semisync_slave.so

-rwxr-xr-x. 1 root root    32368 Sep 20  2019 component_log_sink_json.so

-rwxr-xr-x. 1 root root   235200 Sep 20  2019 component_audit_api_message_emit.so

-rwxr-xr-x. 1 root root   494720 Sep 20  2019 keyring_udf.so

-rwxr-xr-x. 1 root root   149280 Sep 20  2019 component_log_sink_syseventlog.so

-rwxr-xr-x. 1 root root  1446024 Sep 20  2019 semisync_master.so

-rwxr-xr-x. 1 root root  2277480 Sep 20  2019 mysql_clone.so

-rwxr-xr-x. 1 root root  1231376 Sep 20  2019 libmemcached.so

-rwxr-xr-x. 1 root root   454096 Sep 20  2019 component_mysqlbackup.so

-rwxr-xr-x. 1 root root   193336 Sep 20  2019 component_log_filter_dragnet.so

-rwxr-xr-x. 1 root root  1177352 Sep 20  2019 ha_mock.so

-rwxr-xr-x. 1 root root    83936 Sep 20  2019 locking_service.so

-rwxr-xr-x. 1 root root  1269784 Sep 20  2019 connection_control.so

-rwxr-xr-x. 1 root root  1280936 Sep 20  2019 innodb_engine.so

-rwxr-xr-x. 1 root root   442304 Sep 20  2019 component_validate_password.so

-rwxr-xr-x. 1 root root  1206024 Sep 20  2019 version_token.so

-rwxr-xr-x. 1 root root  2338880 Sep 20  2019 keyring_file.so

-rwxr-xr-x. 1 root root  2031912 Sep 20  2019 ddl_rewriter.so

-rwxr-xr-x. 1 root root 49246400 Sep 20  2019 group_replication.so

drwxr-xr-x. 2 root root     4096 Nov  6  2019 debug

 

Install Plugins

 

mysql> INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';

Query OK, 0 rows affected (0.02 sec)

mysql> INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';

Query OK, 0 rows affected (0.00 sec)

 

Check for successful installation

 

mysql> show plugins;

mysql> SELECT PLUGIN_NAME, PLUGIN_LIBRARY, PLUGIN_STATUS, LOAD_OPTION 

    -> FROM INFORMATION_SCHEMA.PLUGINS 

    -> WHERE PLUGIN_LIBRARY = 'CONNECTION_CONTROL.SO';

+------------------------------------------+-----------------------+---------------+-------------+

| PLUGIN_NAME                              | PLUGIN_LIBRARY        | PLUGIN_STATUS | LOAD_OPTION |

+------------------------------------------+-----------------------+---------------+-------------+

| CONNECTION_CONTROL                       | connection_control.so | ACTIVE        | FORCE       |

| CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | connection_control.so | ACTIVE        | FORCE       |

+------------------------------------------+-----------------------+---------------+-------------+

2 rows in set (0.00 sec)

mysql> 

 

Setting System Variables

 

 

mysql> show variables like 'connection_control%';

+-------------------------------------------------+------------+

| Variable_name                                   | Value      |

+-------------------------------------------------+------------+

| connection_control_failed_connections_threshold | 3          |

| connection_control_max_connection_delay         | 2147483647 |

| connection_control_min_connection_delay         | 1000       |

+-------------------------------------------------+------------+

 

Connection_Control_Fail_Connections_Threshold #Logon Failure Limit, default 3

 

Connection_Control_Max_Connection_Maximum limit retry time in milliseconds, default 2147483647

 

Connection_Control_Min_Connection_Minimum #limit retry time in milliseconds, default 1000 milliseconds, or 1 second

 

 

Matters needing attention:

 

1:connection_Control_Min_Connection_The value of delay must be less than connection_control_max_connection_delay, connection_control_max_connection_delay cannot be less than connection_Control_Min_Connection_The value of delay.

 

 

mysql> set global connection_control_min_connection_delay=60000;

Query OK, 0 rows affected (0.00 sec)

 

 

Note that command-mode settings for global system variables are lost after the server restarts, so the best way to do this is in the parameter fileMy.cnfSetting global system variables

 

 

-- Configuration file to add the following configuration 

[mysqld] 

plugin-load-add = connection_control.so          #Not required

connection-control = FORCE                       #Not required

connection-control-failed-login-attempts = FORCE #Not required

connection_control_min_connection_delay = 60000 

connection_control_max_connection_delay = 1800000 

connection_control_failed_connections_threshold = 3

 

 

After three consecutive password failures, it will hang up after the fourth password entry

 

 

[root@lnx02 ~]# mysql -h 10.20.57.24 -u test -p

Enter password: 

ERROR 1045 (28000): Access denied for user 'test'@'192.168.27.180' (using password: YES)

[root@lnx02 ~]# mysql -h 10.20.57.24 -u test -p

Enter password: 

ERROR 1045 (28000): Access denied for user 'test'@'192.168.27.180' (using password: YES)

[root@lnx02 ~]# mysql -h 10.20.57.24 -u test -p

Enter password: 

ERROR 1045 (28000): Access denied for user 'test'@'192.168.27.180' (using password: YES)

[root@lnx02 ~]# mysql -h 10.20.57.24 -u test -p

Enter password: 

 

 

 

 

 

Note that after the MySQL service restarts, INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_All data in ATTEMPTS is up-to-date.

 

 

·         The CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS plugin must be activated for this table to be available, and the CONNECTION_CONTROL plugin must be activated or the table contents will always be empty. See Section 6.4.2, "The Connection-Control Plugins".

 

CONNECTION_must be activatedCONTROL_FAILED_LOGIN_ATTEMPTS plug-in can use the table CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS and either activate CONNECTION_CONTROL plug-in, or the contents of the table are always empty.See6.4.2Section Connection Control Plugin.

 

·         The table contains rows only for clients that have had one or more consecutive failed connection attempts without a subsequent successful attempt. When a client connects successfully, its failed-connection count is reset to zero and the server removes any row corresponding to the client.

   

The table only contains rows for clients who have made one or more consecutive failed connection attempts without subsequent successful attempts.When a client successfully connects, its failed connection count is reset to zero, and the server deletes any rows corresponding to that client.

 

·         Assigning a value to the connection_control_failed_connections_threshold system variable at runtime resets all accumulated failed-connection counters to zero, which causes the table to become empty.

   

Connection_at runtimeControl_Fail_Connections_Assigning a value to the thresholds system variable resets all accumulated failed connection counters to zero, which causes the table to become empty.

 

 

Remove Account Delay Response Limit

 

Method 1: Restart the MySQL instance

 

Method 2: Adjust the system variable connection_Control_Fail_Connections_The value of the threshold.

 

mysql> SELECT * FROM 

    -> INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;

+-------------------+-----------------+

| USERHOST          | FAILED_ATTEMPTS |

+-------------------+-----------------+

| 'test'@'192.168%' |               5 |

+-------------------+-----------------+

1 row in set (0.00 sec)

mysql> 

mysql> set global connection_control_failed_connections_threshold=2;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM 

    -> INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;

Empty set (0.00 sec)

 

3: Unload plugin

 

mysql> UNINSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;

mysql> UNINSTALL PLUGIN CONNECTION_CONTROL;

 

 

 

 

Reference material:

 

https://dev.mysql.com/doc/refman/5.7/en/connection-control-installation.html

https://dev.mysql.com/doc/refman/5.7/en/connection-control-failed-login-attempts-table.html

https://dev.mysql.com/doc/refman/5.7/en/connection-control-variables.html