代码改变世界

使用连接控制插件保护MySQL连接安全

2023-05-18 15:56  abce  阅读(727)  评论(0编辑  收藏  举报

connection_control插件是在MySQL 8.0中引入,并支持向后移植到MySQL 5.7和MySQL 5.6。

在一定次数的连续登录失败尝试后,连接控制插件允许管理员增加服务器对连接的响应延迟。没有得到服务器的响应之前,未经授权的用户或客户端不知道密码是否正确。因此,如果攻击者通过生成多个连接请求来攻击服务器,那么这些连接必须处于活动状态,直到服务器响应为止。引入延迟使攻击者更难攻击,因为现在资源被用于确保连接请求处于活动状态。这种技术可以减缓针对MySQL用户帐户的暴力攻击。

连接控制插件库包含两个插件:

1.connection_control:检查进来的连接尝试,根据需要增加延迟响应。

2.connection_control_failed_login_attempts:information_schema中增加了一个表,记录失败连接的具体信息。

 

安装连接控制插件

运行时安装

>install plugin connection_control soname 'connection_control.so';
Query OK, 0 rows affected (0.01 sec)
>install plugin connection_control_failed_login_attempts soname 'connection_control.so';
Query OK, 0 rows affected (0.01 sec)

也可以在配置文件中加入连接控制插件

[mysqld]
plugin-load-add=connection_control.so			#加载connection_control.so库
connection-control=FORCE_PLUS_PERMANENT			#使用连接控制插件,如果初始化失败,MySQL启动会失败
connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT	#使用connection-control-failed-login-attempts插件,如果初始化失败,MySQL启动会失败

检查安装结果

>select plugin_name, plugin_status from information_schema.plugins where plugin_name like '%connection%';
+------------------------------------------+---------------+
| plugin_name                              | plugin_status |
+------------------------------------------+---------------+
| CONNECTION_CONTROL                       | ACTIVE        |
| CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE        |
+------------------------------------------+---------------+
2 rows in set (0.00 sec)

 

配置连接控制的阈值

现在,使用这些服务器参数为失败的连接尝试配置服务器响应延迟。我们将尝试连续失败连接的阈值设置为3,并添加至少1秒的连接延迟。

> set global connection_control_failed_connections_threshold = 3;#在增加延迟响应之前,允许连接失败尝试的次数;0表示禁用该属性
set global connection_control_min_connection_delay = 1000;  #延迟响应的最小毫秒阈值
set global connection_control_max_connection_delay = 90000;#延迟响应的最大毫秒阈值

持久化配置:

SET PERSIST connection_control_failed_connections_threshold = 3;
SET PERSIST connection_control_min_connection_delay = 1000;
SET PERSIST connection_control_max_connection_delay = 90000;

也可以在配置文件中配置:

[mysqld]
connection_control_failed_connections_threshold=3
connection_control_min_connection_delay=1000 
connection_control_max_connection_delay=90000

>show variables like '%connection_control%';
+-------------------------------------------------+-------+
| Variable_name                                   | Value |
+-------------------------------------------------+-------+
| connection_control_failed_connections_threshold | 3     |
| connection_control_max_connection_delay         | 90000 |
| connection_control_min_connection_delay         | 1000  |
+-------------------------------------------------+-------+
3 rows in set (0.00 sec)

 

测试过程

第一个终端:

>select * from information_schema.connection_control_failed_login_attempts;
Empty set (0.00 sec)

>show global status like 'connection_control_%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 0     |
+------------------------------------+-------+
1 row in set (0.01 sec)

>

目前是没有延迟响应产生。

在第二个终端,尝试使用错误的密码进行登录:

# for i in `seq 60`;do time mysql mysql? -uroot -p"try_an_incorrect_password" 2>&1 >/dev/null | grep real;done

real    0m0.005s
user    0m0.005s
sys     0m0.000s

real    0m0.004s
user    0m0.001s
sys     0m0.004s

real    0m0.003s
user    0m0.001s
sys     0m0.003s

real    0m1.004s
user    0m0.004s
sys     0m0.000s

real    0m2.007s
user    0m0.005s
sys     0m0.003s

real    0m3.006s
user    0m0.006s
sys     0m0.000s

real    0m4.007s
user    0m0.008s
sys     0m0.000s

real    0m5.005s
user    0m0.005s
sys     0m0.000s

real    0m6.005s
user    0m0.006s
sys     0m0.000s

real    0m7.007s
user    0m0.004s
sys     0m0.003s

real    0m8.007s
user    0m0.006s
sys     0m0.002s

real    0m9.005s
user    0m0.005s
sys     0m0.000s

real    0m10.005s
user    0m0.005s
sys     0m0.000s

real    0m11.006s
user    0m0.006s
sys     0m0.001s

real    0m12.006s
user    0m0.004s
sys     0m0.003s

real    0m13.006s
user    0m0.006s
sys     0m0.000s

real    0m14.005s
user    0m0.000s
sys     0m0.006s

real    0m15.006s
user    0m0.001s
sys     0m0.006s

real    0m16.006s
user    0m0.003s
sys     0m0.003s

real    0m17.006s
user    0m0.006s
sys     0m0.000s

real    0m18.006s
user    0m0.006s
sys     0m0.000s

real    0m19.005s
user    0m0.001s
sys     0m0.004s

real    0m20.006s
user    0m0.007s
sys     0m0.000s

real    0m21.005s
user    0m0.005s
sys     0m0.000s

real    0m22.005s
user    0m0.001s
sys     0m0.005s

real    0m23.005s
user    0m0.001s
sys     0m0.005s

real    0m24.009s
user    0m0.008s
sys     0m0.002s

real    0m25.006s
user    0m0.004s
sys     0m0.003s

real    0m26.005s
user    0m0.005s
sys     0m0.000s

real    0m27.005s
user    0m0.004s
sys     0m0.002s

real    0m28.004s
user    0m0.005s
sys     0m0.000s

real    0m29.005s
user    0m0.005s
sys     0m0.000s

real    0m30.005s
user    0m0.005s
sys     0m0.000s

real    0m31.006s
user    0m0.006s
sys     0m0.000s

real    0m32.004s
user    0m0.005s
sys     0m0.000s

real    0m33.006s
user    0m0.004s
sys     0m0.003s

real    0m34.005s
user    0m0.005s
sys     0m0.000s

real    0m35.006s
user    0m0.006s
sys     0m0.000s

real    0m36.006s
user    0m0.003s
sys     0m0.004s

real    0m37.005s
user    0m0.005s
sys     0m0.000s

real    0m38.005s
user    0m0.001s
sys     0m0.004s

real    0m39.005s
user    0m0.000s
sys     0m0.006s

real    0m40.006s
user    0m0.006s
sys     0m0.000s

real    0m41.006s
user    0m0.006s
sys     0m0.000s

real    0m42.008s
user    0m0.008s
sys     0m0.001s

real    0m43.009s
user    0m0.002s
sys     0m0.008s

real    0m44.006s
user    0m0.006s
sys     0m0.000s

real    0m45.005s
user    0m0.005s
sys     0m0.000s

real    0m46.007s
user    0m0.003s
sys     0m0.005s

real    0m47.007s
user    0m0.007s
sys     0m0.001s

real    0m48.006s
user    0m0.006s
sys     0m0.000s

real    0m49.006s
user    0m0.005s
sys     0m0.002s

real    0m50.005s
user    0m0.005s
sys     0m0.000s

real    0m51.006s
user    0m0.003s
sys     0m0.003s

real    0m52.006s
user    0m0.006s
sys     0m0.000s

real    0m53.006s
user    0m0.006s
sys     0m0.000s

real    0m54.008s
user    0m0.005s
sys     0m0.004s

real    0m55.006s
user    0m0.006s
sys     0m0.000s

real    0m56.009s
user    0m0.006s
sys     0m0.004s

real    0m57.006s
user    0m0.005s
sys     0m0.002s

查看mysql中的进程,可以看到,连接的状态是"Waiting in connection_control plugin"

>show processlist;
+----+-----------------+-----------+------+---------+------+--------------------------------------+------------------+---------+-----------+---------------+
| Id | User            | Host      | db   | Command | Time | State                                | Info             | Time_ms | Rows_sent | Rows_examined |
+----+-----------------+-----------+------+---------+------+--------------------------------------+------------------+---------+-----------+---------------+
|  5 | event_scheduler | localhost | NULL | Daemon  |  323 | Waiting on empty queue               | NULL             |  323577 |         0 |             0 |
|  8 | root            | localhost | NULL | Query   |    0 | init                                 | show processlist |       0 |         0 |             0 |
| 27 | root            | localhost | NULL | Connect |    1 | Waiting in connection_control plugin | NULL             |    1935 |         0 |             0 |
+----+-----------------+-----------+------+---------+------+--------------------------------------+------------------+---------+-----------+---------------+

三个连接之后的连接,开始经过一段时间的响应延迟。直至尝试结束。每个尝试的延迟都会增加一秒。

第二个终端的脚本运行结束后,回到第一个终端:

>show global status like 'connection_control_%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 57    |
+------------------------------------+-------+
1 row in set (0.00 sec)
>select failed_attempts from information_schema.connection_control_failed_login_attempts;
+-----------------+
| failed_attempts |
+-----------------+
|              60 |
+-----------------+
1 row in set (0.00 sec)

多次错误尝试失败后,如果一个正确的密码尝试连接进来,因为之前已经有N个尝试失败,正确的连接也需要等待N秒的延迟。

# date; mysql -uroot -p'xxx' -e "select now();";date
Thu 18 May 2023 02:25:09 PM CST
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------+
| now()               |
+---------------------+
| 2023-05-18 14:26:08 |
+---------------------+
Thu 18 May 2023 02:26:08 PM CST

接下来正确的连接就不用延迟响应了:

# date; mysql -uroot -p'xxx' -e "select now();";date
Thu 18 May 2023 02:27:07 PM CST
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------+
| now()               |
+---------------------+
| 2023-05-18 14:27:07 |
+---------------------+
Thu 18 May 2023 02:27:07 PM CST

 

找出是哪个用户在尝试暴力破解登录

>select * from information_schema.connection_control_failed_login_attempts;
+--------------------+-----------------+
| USERHOST           | FAILED_ATTEMPTS |
+--------------------+-----------------+
| 'root'@'localhost' |              60 |
+--------------------+-----------------+
1 row in set (0.01 sec)

 

重新设置阈值

如果想重新设置这些计数器,重新给connection_control_failed_connections_threshold设置一个值即可:

>SET GLOBAL connection_control_failed_connections_threshold = 3;
Query OK, 0 rows affected (0.00 sec)

>select * from information_schema.connection_control_failed_login_attempts;
Empty set (0.00 sec)

>show global status like 'connection_control_%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 0     |
+------------------------------------+-------+
1 row in set (0.00 sec)