MySQL安全插件-密码复杂度+密码过期策略+登录失败处理
一、等保简介
信息安全等级保护,简称等保,是对信息和信息载体按照重要性等级分级别进行保护的一种工作,信息安全等级保护广义上为涉及到该工作的标准、产品、系统、信息等均依据等级保护思想的安全工作,狭义上一般指信息系统安全等级保护。《信息安全等级保护信息安全等级保护管理办法》规定,国家信息安全等级保护坚持自主定级、自主保护的原则。信息系统的安全保护等级应当根据信息系统在国家安全、经济建设、社会生活中的重要程度,信息系统遭到破坏后对国家安全、社会秩序、公共利益以及公民、法人和其他组织的合法权益的危害程度等因素确定。
数据库安全要求:
(1)设置口令位数至少为8位,至少一位大小写字母,一位数字,一位特殊字符。
(2)设置当密码错误超过一定次数后(建议3次或5次),自动锁定账户(建议30分钟以上)。
二、密码复杂度插件
2.1 插件安装
mysql> install plugin validate_password soname 'validate_password.so'; Query OK, 0 rows affected (0.00 sec) mysql> show plugins; +----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL | # 安装成功后插件 +----------------------------+----------+--------------------+----------------------+---------+
#持久化修改配置文件 [root@ansible ~]# vim /etc/my.cnf [mysqld] plugin-load-add=validate_password.so #服务器在启动时加载插件,并防止在服务器运行时删除插件 validate-password=FORCE_PLUS_PERMANENT
2.2 卸载插件
mysql> uninstall plugin validate_password; Query OK, 0 rows affected (0.00 sec)
2.3 安装插件后变量参数查看
mysql> show global variables like '%validate_password%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password_check_user_name | OFF | | validate_password_dictionary_file | | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | MEDIUM | | validate_password_special_char_count | 1 | +--------------------------------------+--------+ 7 rows in set (0.00 sec)
参数定义:
validate_password_check_user_name #设置为ON的时候表示能将密码设置成当前用户名。
validate_password_dictionary_file #validate_password用于检查密码的字典文件的路径名。
validate_password_length #validate_password所需密码的最小字符数。
validate_password_mixed_case_count #如果密码策略是中等或更强的,validate_password要求密码具有的小写和大写字符的最小数量。对于给定的这个值密码必须有那么多小写字符和那么多大写字符。
validate_password_number_count #如果密码策略是中等或更强的,validate_password要求密码具有的数字(数字)字符的最小数量。
validate_password_policy #validate_password强制执行的密码策略,validate_password_policy影响validate_password如何使用它的其他策略设置系统变量,除了根据用户名检查密码之外,后者由validate_password_check_user_name独立控制。
validate_password_policy #可以使用数值0、1、2或相应的符号值LOW、MEDIUM、STRONG来指定。
三、密码过期时间设置
3.1 系统表查看数据库账号状态
mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user; +----------------+-----------+------------------+-------------------+-----------------------+----------------+ | user | host | password_expired | password_lifetime | password_last_changed | account_locked | +----------------+-----------+------------------+-------------------+-----------------------+----------------+ | root | % | N | NULL | 2019-09-28 23:50:09 | N | | stargao | % | N | NULL | 2020-07-27 16:46:46 | N | +----------------+-----------+------------------+-------------------+-----------------------+----------------+
3.2 设置stargao账号密码立即过期
mysql> ALTER USER 'stargao'@'%' PASSWORD EXPIRE; Query OK, 0 rows affected (0.00 sec) mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user; +----------------+-----------+------------------+-------------------+-----------------------+----------------+ | user | host | password_expired | password_lifetime | password_last_changed | account_locked | +----------------+-----------+------------------+-------------------+-----------------------+----------------+ | root | % | N | NULL | 2019-09-28 23:50:09 | N | | stargao | % | Y | NULL | 2020-07-27 16:46:46 | N | +----------------+-----------+------------------+-------------------+-----------------------+----------------+ 4 rows in set (0.00 sec)
3.3 设置stargao账号密码永不过期
mysql> ALTER USER 'stargao'@'%' PASSWORD EXPIRE NEVER; Query OK, 0 rows affected (0.00 sec) mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user; +----------------+-----------+------------------+-------------------+-----------------------+----------------+ | user | host | password_expired | password_lifetime | password_last_changed | account_locked | +----------------+-----------+------------------+-------------------+-----------------------+----------------+ | root | % | N | NULL | 2019-09-28 23:50:09 | N | | stargao | % | Y | 0 | 2020-07-27 16:46:46 | N | +----------------+-----------+------------------+-------------------+-----------------------+----------------+ 4 rows in set (0.00 sec)
3.4 单独设置该账号密码90天过期
mysql> ALTER USER 'stargao'@'%' PASSWORD EXPIRE INTERVAL 90 DAY; Query OK, 0 rows affected (0.01 sec) mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user; +----------------+-----------+------------------+-------------------+-----------------------+----------------+ | user | host | password_expired | password_lifetime | password_last_changed | account_locked | +----------------+-----------+------------------+-------------------+-----------------------+----------------+ | root | % | N | NULL | 2019-09-28 23:50:09 | N | | stargao | % | Y | 90 | 2020-07-27 16:46:46 | N | +----------------+-----------+------------------+-------------------+-----------------------+----------------+ 4 rows in set (0.00 sec)
3.5 默认密码过期全局策略
mysql> ALTER USER 'stargao'@'%' PASSWORD EXPIRE DEFAULT; Query OK, 0 rows affected (0.00 sec) mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user; +----------------+-----------+------------------+-------------------+-----------------------+----------------+ | user | host | password_expired | password_lifetime | password_last_changed | account_locked | +----------------+-----------+------------------+-------------------+-----------------------+----------------+ | root | % | N | NULL | 2019-09-28 23:50:09 | N | | stargao | % | Y | NULL | 2020-07-27 16:46:46 | N | +----------------+-----------+------------------+-------------------+-----------------------+----------------+ 4 rows in set (0.00 sec)
【注】mysql.user 系统表记录着每个账号的相关信息,当 password_expired 字段值为 Y 时,代表此密码已过期,使用过期密码仍可以登录,但不能进行任何操作,进行操作会提示:ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. 必须更改密码后才能进行正常操作。
mysql> ALTER USER 'stargao'@'%' IDENTIFIED WITH mysql_native_password BY 'ucloud.cn'; Query OK, 0 rows affected (0.00 sec) mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user; +----------------+-----------+------------------+-------------------+-----------------------+----------------+ | user | host | password_expired | password_lifetime | password_last_changed | account_locked | +----------------+-----------+------------------+-------------------+-----------------------+----------------+ | root | % | N | NULL | 2021-08-02 13:27:05 | N | | stargao | % | N | NULL | 2021-08-02 13:27:24 | N | +----------------+-----------+------------------+-------------------+-----------------------+----------------+ 4 rows in set (0.00 sec)
四、登录失败处理插件
4.1 安装插件
mysql> INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so'; Query OK, 0 rows affected (0.00 sec) mysql> INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so'; Query OK, 0 rows affected (0.00 sec) mysql> show plugins; +------------------------------------------+----------+--------------------+-----------------------+---------+ | Name | Status | Type | Library | License | +------------------------------------------+----------+--------------------+-----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL | | CONNECTION_CONTROL | ACTIVE | AUDIT | connection_control.so | GPL | # 失败处理插件 | CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE | INFORMATION SCHEMA | connection_control.so | GPL | +------------------------------------------+----------+--------------------+-----------------------+---------+ 47 rows in set (0.00 sec) 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)
4.2 修改配置文件
[root@ansible ~]# vim /etc/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 = 5 connection_control_max_connection_delay = 2147483647 connection_control_min_connection_delay = 1500
4.3 变量参数查看
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 | +-------------------------------------------------+------------+ 3 rows in set (0.00 sec)
参数含义:
connection_control_failed_connections_threshold # 单个用户登录失败(由于密码错误引起)次数上限,默认3次
connection_control_max_connection_delay # 失败上限之后再次尝试登录前最小等待时间,单位ms
connection_control_min_connection_delay # 失败上限之后再次尝试登录前最小等待时间,默认1秒(1000ms)
作者:UStarGao
链接:https://www.starcto.com/mysql/178.html
来源:STARCTO
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
UCloud云平台推荐
随便看看
- 2022-01-14Docker镜像加速配置
- 2021-11-19Linux本地SSD磁盘IO性能压测教程-fio
- 2021-08-22UCloud UHub容器镜像仓库使用教程
- 2021-02-12MySQL主从同步异常1864
- 2022-10-01MongoDB切换主节点技巧