MySQL教程FG199-MySQL账号锁定

Part01-基础概念与理论知识

MySQL账号锁定是一种重要的安全机制，用于防止未授权访问和暴力破解攻击。通过锁定可疑账号，可以有效保护数据库的安全。MySQL 8.0提供了多种账号锁定方式，包括手动锁定和基于失败登录次数的自动锁定。更多学习教程www.fgedu.net.cn

1.1 账号锁定的作用

防止暴力破解：通过自动锁定多次失败登录的账号，阻止暴力破解攻击
增强安全性：可以临时禁用可疑账号，防止未授权访问
便于管理：可以在用户离职或系统维护期间锁定账号
审计追踪：可以记录账号锁定和解锁事件，便于安全审计

1.2 账号锁定的实现原理

MySQL在mysql.user表中使用account_locked字段来存储账号的锁定状态：

Y：账号已锁定
N：账号未锁定

Part02-生产环境规划与建议

MySQL支持两种主要的账号锁定类型：手动锁定和自动锁定。学习交流加群风哥微信: itpux-com

2.1 手动锁定

由数据库管理员手动锁定或解锁账号，适用于以下场景：

用户离职或调岗
系统维护期间
发现可疑活动时
账号长期不使用

2.2 自动锁定

基于失败登录次数的自动锁定，适用于以下场景：

防止暴力破解攻击
保护弱密码账号
减少未授权访问风险

Part03-生产环境项目实施方案

数据库管理员可以使用ALTER USER语句手动锁定或解锁账号。

3.1 手动锁定账号

使用ALTER USER语句锁定账号。

# 锁定单个账号
mysql> ALTER USER ‘webshop’@’%’ ACCOUNT LOCK;
Query OK, 0 rows affected (0.01 sec)

# 锁定多个账号
mysql> ALTER USER ‘report_user’@’%’, ‘test_user’@’localhost’ ACCOUNT LOCK;
Query OK, 0 rows affected (0.01 sec)

3.2 测试锁定账号

尝试使用锁定的账号连接数据库。

# 尝试使用锁定的账号连接
$ mysql -uwebshop -p’WebShop@2026′ -h192.168.1.10
ERROR 3118 (HY000): Access denied for user ‘webshop’@’192.168.1.50’. Account is locked.

3.3 手动解锁账号

使用ALTER USER语句解锁账号。

# 解锁单个账号
mysql> ALTER USER ‘webshop’@’%’ ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.01 sec)

# 解锁多个账号
mysql> ALTER USER ‘report_user’@’%’, ‘test_user’@’localhost’ ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.01 sec)

3.4 创建已锁定的账号

可以在创建账号时直接将其锁定。

# 创建已锁定的账号
mysql> CREATE USER ‘new_user’@’%’ IDENTIFIED WITH caching_sha2_password BY ‘NewUser@2026’ ACCOUNT LOCK;
Query OK, 0 rows affected (0.01 sec)

MySQL 8.0支持基于失败登录次数的自动账号锁定功能，这是通过connection_control插件实现的。

4.1 安装connection_control插件

首先需要安装connection_control插件。

# 安装connection_control插件
mysql> INSTALL PLUGIN connection_control SONAME ‘connection_control.so’;
mysql> INSTALL PLUGIN connection_control_failed_login_attempts SONAME ‘connection_control.so’;
Query OK, 0 rows affected (0.01 sec)

4.2 配置自动锁定参数

配置失败登录自动锁定的相关参数。

# 修改配置参数
mysql> SET GLOBAL connection_control_failed_connections_threshold = 5;
mysql> SET GLOBAL connection_control_min_connection_delay = 3000;
mysql> SET GLOBAL connection_control_max_connection_delay = 30000;
Query OK, 0 rows affected (0.00 sec)

# 永久保存配置
# 在my.cnf文件中添加
[mysqld]
connection-control-failed-connections-threshold = 5
connection-control-min-connection-delay = 3000
connection-control-max-connection-delay = 30000

参数说明：

connection_control_failed_connections_threshold：触发锁定的失败登录次数阈值
connection_control_min_connection_delay：第一次锁定的最小延迟时间（毫秒）
connection_control_max_connection_delay：锁定的最大延迟时间（毫秒）

4.3 测试自动锁定功能

测试失败登录自动锁定功能。

# 多次尝试使用错误密码登录
$ mysql -uwebshop -p’WrongPassword’ -h192.168.1.10
ERROR 1045 (28000): Access denied for user ‘webshop’@’192.168.1.50′ (using password: YES)

$ mysql -uwebshop -p’WrongPassword’ -h192.168.1.10
ERROR 1045 (28000): Access denied for user ‘webshop’@’192.168.1.50′ (using password: YES)

$ mysql -uwebshop -p’WrongPassword’ -h192.168.1.10
ERROR 3955 (HY000): Access denied for user ‘webshop’@’192.168.1.50’. Account is blocked for 3 second(s) (3000 milliseconds) due to many failed login attempts.

# 使用正确密码登录（会有延迟）
$ mysql -uwebshop -p’WebShop@2026′ -h192.168.1.10
… (3秒延迟后)
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.4.0 MySQL Community Server – GPL

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.

mysql>

4.4 查看失败登录尝试信息

通过information_schema视图查看失败登录尝试信息。

Part05-风哥经验总结与分享

在生产环境中，需要制定合理的账号锁定策略，以平衡安全性和可用性。

5.1 手动锁定策略

制定手动锁定的规则和流程：

锁定条件：用户离职、调岗、长期不使用、发现可疑活动
锁定流程：提交申请→审批→执行锁定→记录日志
解锁流程：提交申请→审批→执行解锁→记录日志
定期审查：每月审查一次锁定的账号，清理不再需要的账号

5.2 自动锁定策略

配置自动锁定的参数：

# 生产环境推荐配置
[mysqld]
# 5次失败登录后锁定
connection-control-failed-connections-threshold = 5
# 初始锁定3秒
connection-control-min-connection-delay = 3000
# 最大锁定30秒
connection-control-max-connection-delay = 30000
# 启用自动锁定
plugin-load-add = connection_control.so
connection-control = FORCE_PLUS_PERMANENT
connection-control-failed-login-attempts = FORCE_PLUS_PERMANENT

5.3 锁定通知机制

配置账号锁定通知机制，及时发现和处理锁定事件：

# 启用审计日志记录锁定事件
[mysqld]
audit-log = ON
audit-log-format = JSON
audit-log-file = /var/log/mysql/audit.log

5.4 紧急解锁流程

制定紧急解锁流程，以应对生产环境中的紧急情况：

建立紧急解锁联系人列表
设置备用DBA账号，用于紧急解锁
记录所有紧急解锁操作，事后审计

6. 生产环境案例分析

本部分通过实际案例展示账号锁定在生产环境中的应用。

6.1 案例1：防止暴力破解攻击

配置自动锁定功能，防止暴力破解攻击。

# 1. 安装connection_control插件
mysql> INSTALL PLUGIN connection_control SONAME ‘connection_control.so’;
mysql> INSTALL PLUGIN connection_control_failed_login_attempts SONAME ‘connection_control.so’;

# 2. 配置自动锁定参数
mysql> SET GLOBAL connection_control_failed_connections_threshold = 3;
mysql> SET GLOBAL connection_control_min_connection_delay = 5000;
mysql> SET GLOBAL connection_control_max_connection_delay = 60000;

# 3. 永久保存配置
# 在my.cnf文件中添加
[mysqld]
plugin-load-add = connection_control.so
connection-control = FORCE_PLUS_PERMANENT
connection-control-failed-login-attempts = FORCE_PLUS_PERMANENT
connection-control-failed-connections-threshold = 3
connection-control-min-connection-delay = 5000
connection-control-max-connection-delay = 60000

6.2 案例2：员工离职账号管理

当员工离职时，手动锁定其账号。

# 1. 查找离职员工的账号
mysql> SELECT user, host FROM mysql.user WHERE user LIKE ’emp_%’;
+———+———–+
| user | host |
+———+———–+
| emp_123 | % |
| emp_456 | % |
| emp_789 | 192.168.1.% |
+———+———–+
3 rows in set (0.00 sec)

# 2. 锁定离职员工的账号
mysql> ALTER USER ’emp_123’@’%’ ACCOUNT LOCK;
Query OK, 0 rows affected (0.01 sec)

# 3. 验证账号已锁定
mysql> SELECT user, host, account_locked FROM mysql.user WHERE user = ’emp_123′;
+———+——+—————-+
| user | host | account_locked |
+———+——+—————-+
| emp_123 | % | Y |
+———+——+—————-+
1 row in set (0.00 sec)

# 4. 记录锁定操作
mysql> INSERT INTO dba_audit.account_lock_logs (username, host, locked_by, lock_time, reason)
VALUES (’emp_123′, ‘%’, ‘dba_feng’, NOW(), ‘Employee resigned’);
Query OK, 1 row affected (0.00 sec)

6.3 案例3：系统维护期间账号管理

在系统维护期间，临时锁定所有非DBA账号。

# 1. 创建锁定脚本
$ cat lock_non_dba_accounts.sql
SET @dba_users = ‘root,dba_feng,dba_smith’;

SELECT CONCAT(‘ALTER USER ”’, user, ”’@”’, host, ”’ ACCOUNT LOCK;’)
INTO @lock_commands
FROM mysql.user
WHERE user NOT IN (SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(@dba_users, ‘,’, numbers.n), ‘,’, -1))
FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) numbers
WHERE numbers.n <= 1 + (LENGTH(@dba_users) - LENGTH(REPLACE(@dba_users, ',', '')))); PREPARE stmt FROM @lock_commands; EXECUTE stmt; DEALLOCATE PREPARE stmt; # 2. 执行锁定脚本 mysql> SOURCE lock_non_dba_accounts.sql;
Query OK, 0 rows affected (0.01 sec)

# 4. 维护完成后解锁账号
mysql> ALTER USER ‘webshop’@’%’, ‘report_user’@’%’ ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.01 sec)

7. 最佳实践与经验总结

作为资深DBA，风哥在MySQL账号锁定方面积累了丰富的经验。

风哥提示：账号锁定是MySQL安全防护体系的重要组成部分，合理配置和使用账号锁定功能，可以有效防止未授权访问和暴力破解攻击。

7.1 账号锁定最佳实践

启用自动锁定：在生产环境中，建议启用基于失败登录次数的自动锁定功能
合理设置阈值：根据实际情况设置失败登录次数阈值，一般建议3-5次
设置适当延迟：初始延迟时间不宜过长（建议3-5秒），最大延迟时间不宜超过1分钟
定期审查锁定账号：每月审查一次锁定的账号，及时清理不再需要的账号
记录锁定事件：配置审计日志，记录所有账号锁定和解锁事件
制定解锁流程：建立清晰的账号解锁流程，包括申请、审批和执行

7.2 常见问题与解决方案

问题1：自动锁定不生效
解决方案：检查connection_control插件是否已安装；检查相关参数是否已正确配置；检查是否有其他安全插件冲突。
问题2：忘记解锁账号
解决方案：建立定期审查机制；使用自动化工具监控锁定的账号；设置解锁提醒。
问题3：合法用户被误锁定
解决方案：适当提高失败登录次数阈值；为重要用户配置白名单；建立紧急解锁通道。
问题4：锁定延迟影响用户体验
解决方案：合理设置延迟时间；为内部用户配置更高的阈值或白名单；提供自助解锁功能。

7.3 生产环境注意事项

生产环境注意事项：

不要将所有DBA账号同时锁定，至少保留一个可用的DBA账号
定期备份mysql.user表，以防止权限数据丢失
在配置自动锁定前，先在测试环境中验证功能
为自动锁定配置适当的监控和告警
考虑使用第三方工具增强账号锁定功能，如Fail2ban

7.4 风哥的账号锁定建议

风哥提示：账号锁定是一把双刃剑，合理使用可以提高安全性，过度使用可能影响可用性。建议根据实际业务需求和安全要求，制定平衡的账号锁定策略。同时，账号锁定只是安全防护的一部分，还需要结合其他安全措施，如强密码策略、SSL连接、访问控制等，构建完整的安全防护体系。更多学习教程公众号风哥教程itpux_com

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html