使用连接控制插件保护MySQL连接安全
2023-05-18 15:56 abce 阅读(552) 评论(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)
