MySQL为“等保测评”实现用户多次恶意登录账户锁定功能

适用范围

  MySQL社区版8.0.36，实际上8.0.19以后的版本都适用。
 

方案概述

  最近协助客户做等保测评的漏洞整改，实现 MySQL8.0 多次连续登录失败之后账号锁定功能，客户MySQL版本为8.0.36，查询官方文档发现确实有相关实现方式：官方叫法为Failed-Login Tracking and Temporary Account Locking 既 登录失败跟踪和临时账户锁定。
  MySQL从8.0.19版本开始，在配置用户的时候，可以配置用户因连续登录失败指定次数后对用户实现锁定指定天数的功能。这里讲的 “登录失败” 是指因客户端与MySQL服务器端连接的时候没有提供正确的密码，不包含因未知用户或者网络原因导致的连接失败。
  以上MySQL的这个功能恰好可以解决因恶意攻击导致的这种安全隐患问题，具体实现方式可以在创建用户create user 的时候进行设置也可以在现有用户通过alter user来实现。
 

实施举例

实现方式：创建/修改用户时指定以下参数选项。

FAILED_LOGIN_ATTEMPTS N
此选项表明是否跟踪用户错误密码的尝试登录次数。N 表示连续错误密码多少次对账号进行临时锁定。

PASSWORD_LOCK_TIME {N | UNBOUNDED}
此选项表明在连续多次错误密码登录后进行账号锁定的时间，N 的单位为天数，也可以使用关键字UNBOUNDED，代表一直锁定，没有时间限制，直到帐户解锁为止。


 
创建新用户时：

root@localhost:(none) 04:40:54 >create user test_user@'%' identified by 'Test_123' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 3;
Query OK, 0 rows affected (0.01 sec)
root@localhost:(none) 04:41:54 >
说明：创建用户并限制用户连续错误密码登录尝试次数为5次，锁定用户时间为3天！
既如果用户使用密码错误续的进行登录超过5次，就自动锁定该用户3天时间。


 模拟使用错误密码进行测试验证：

连续使用5次错误密码进行登录：
[root@iZf8zcal7db1wnlk0w6a8lZ ~]# /usr/local/mysql/bin/mysql -utest_user -p'1111111';
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)

[root@iZf8zcal7db1wnlk0w6a8lZ ~]# /usr/local/mysql/bin/mysql -utest_user -p'2222222';
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)

[root@iZf8zcal7db1wnlk0w6a8lZ ~]# /usr/local/mysql/bin/mysql -utest_user -p'3333333';
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)

[root@iZf8zcal7db1wnlk0w6a8lZ ~]# /usr/local/mysql/bin/mysql -utest_user -p'4444444';
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)

[root@iZf8zcal7db1wnlk0w6a8lZ ~]# /usr/local/mysql/bin/mysql -utest_user -p'5555555';
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3955 (HY000): Access denied for user 'test_user'@'localhost'. Account is blocked for 3 day(s) (3 day(s) remaining) due to 5 consecutive failed logins.
说明：当第5次连续使用错误密码登录时，账号被blocked！


说明：当第5次连续使用错误密码登录时，进行了错题提示：
Account is blocked for 3 day(s) (3 day(s) remaining) due to 5 consecutive failed logins.
由于连续5次登录失败，用户被阻止3天！

 
修改现有用户时：

root@localhost:(none) 05:40:40 >alter user userB@'%' FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 2;
Query OK, 0 rows affected (0.01 sec)

root@localhost:(none) 05:43:09 >select user,host,user_attributes from mysql.user where user='userB';
+-------+------+----------------------------------------------------------------------------------+
| user | host | user_attributes |
+-------+------+----------------------------------------------------------------------------------+
| userB | % | {"Password_locking": {"failed_login_attempts": 4, "password_lock_time_days": 2}} |
+-------+------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)


  说明：在mysql.user表中的user_attributes列中会记录用户的这个登录限制详细信息，可以查到该值。

 模拟使用错误密码进行测试验证：

[root@iZf8zcal7db1wnlk0w6a8lZ ~]# /usr/local/mysql/bin/mysql -uuserB -p'userB_111';
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'userB'@'localhost' (using password: YES)

[root@iZf8zcal7db1wnlk0w6a8lZ ~]# /usr/local/mysql/bin/mysql -uuserB -p'userB_222';
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'userB'@'localhost' (using password: YES)

[root@iZf8zcal7db1wnlk0w6a8lZ ~]# /usr/local/mysql/bin/mysql -uuserB -p'userB_333';
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'userB'@'localhost' (using password: YES)

[root@iZf8zcal7db1wnlk0w6a8lZ ~]# /usr/local/mysql/bin/mysql -uuserB -p'userB_444';
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3955 (HY000): Access denied for user 'userB'@'localhost'. Account is blocked for 2 day(s) (2 day(s) remaining) due to 4 consecutive failed logins.
[root@iZf8zcal7db1wnlk0w6a8lZ ~]#
说明：当第4次连续使用错误密码登录时，账号被blocked！

这时即使用正确密码登录，依然不被允许：
[root@iZf8zcal7db1wnlk0w6a8lZ ~]# /usr/local/mysql/bin/mysql -uuserB -p'userB_123';
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3955 (HY000): Access denied for user 'userB'@'localhost'. Account is blocked for 2 day(s) (2 day(s) remaining) due to 4 consecutive failed logins.


 解锁用户

root@localhost:(none) 05:53:18 >alter user userB account unlock;
Query OK, 0 rows affected (0.01 sec)

[root@iZf8zcal7db1wnlk0w6a8lZ ~]# /usr/local/mysql/bin/mysql -uuserB -p'userB_123';
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 50
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

userB@localhost:(none) 05:54:01 >

说明：解锁后，用户可以正常登录。


 

相关总结

• 对于账号的连续错误登录后锁定功能，必须 FAILED_LOGIN_ATTEMPTS和PASSWORD_LOCK_TIME选项都同时为非零值。
• 创建用户时如果未指定FAILED_LOGIN_ATTEMPTS 或 PASSWORD_LOCK_TIME选项，则对于该语句指定帐户，其隐式默认值为0。这意味着登录失败跟踪和临时帐户锁定被禁用。
• 如果临时帐户锁定，密码失败必须是连续的。在达到失败登录值之前发生的任何成功登录都会导致失败计数（FAILED_LOGIN_ATTEMPTS）重置。
• 如果临时锁定生效，即使使用正确的密码也无法成功登录，直到锁定持续时间已过或通过alter user xxx unlock; 或者重启MySQL实例来解锁。

 

参考文档

https://dev.mysql.com/doc/refman/8.0/en/password-management.html#failed-login-tracking