文章目录
mysql数据库安全策略
- MySQL 系统本身可以设置密码复杂度及自动过期策略的,可能比较少用,最近也是被通知调整密码,才开始了解
mysql密码复杂度策略
- MySQL 系统自带有 validate_password 插件,此插件可以验证密码强度,未达到规定强度的密码则不允许被设置。MySQL 5.7 及 8.0 版本默认情况下貌似都不启用该插件,这也使得我们可以随意设置密码,比如设置为 123、123456等。如果我们想从根源上规范密码强度,可以启用该插件
查看是否已安装此插件
-
进入 MySQL 命令行,通过 show plugins 或者查看 validate_password 相关参数可以判断是否已安装此插件。若没有相关参数则代表未安装此插件
# 安装前检查 为空则说明未安装此插件 mysql> show variables like 'validate%'; Empty set (0.00 sec)
安装 validate_password 插件
-
进入 MySQL 命令行,通过 show plugins 或者查看 validate_password 相关参数可以判断是否已安装此插件。若没有相关参数则代表未安装此插件
# 通过 INSTALL PLUGIN 命令可安装此插件 # 每个平台的文件名后缀都不同 对于 Unix 和类 Unix 系统,为.so,对于 Windows 为.dll mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so'; Query OK, 0 rows affected, 1 warning (0.28 sec) # 查看 validate_password 相关参数 mysql> show variables like 'validate%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password_check_user_name | ON | | 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 插件后,多了一些密码强度相关参数
- validate_password_policy
代表的密码策略,默认是MEDIUM 可配置的值有以下:
0 or LOW 仅需需符合密码长度(由参数validate_password_length指定)
1 or MEDIUM 满足LOW策略,同时还需满足至少有1个数字,小写字母,大写字母和特殊字符
2 or STRONG 满足MEDIUM策略,同时密码不能存在字典文件(dictionary file)中 - validate_password_length
用来设置密码的最小长度,默认值是8 - validate_password_mixed_case_count
当validate_password_policy设置为MEDIUM或者STRONG时,密码中至少同时拥有的小写和大写字母的数量,默认是1最小是0;默认是至少拥有一个小写和一个大写字母。 - validate_password_number_count
当validate_password_policy设置为MEDIUM或者STRONG时,密码中至少拥有的数字的个数,默认1最小是0 - validate_password_special_char_count
当validate_password_policy设置为MEDIUM或者STRONG时,密码中至少拥有的特殊字符的个数,默认1最小是0
- validate_password_policy
密码复杂度策略具体设置
-
学习完以上参数,我们就可以根据自身情况来具体设置密码复杂度策略了,比如我想让密码至少 15 位且包含大小写字母、数字、特殊字符,则可以这样设置。
# 设置密码长度至少15位 mysql> set global validate_password_length = 15; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'validate%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password_check_user_name | ON | | validate_password_dictionary_file | | | validate_password_length | 15 | | 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)
测试密码复杂度
-
密码复杂度策略只对生效后的操作有效,比如说你之前有个账号,密码是 123456 ,则该账号还是可以继续使用的,不过若再次更改密码则需满足复杂度策略。下面我们来测试下密码复杂度策略的具体效果。
mysql> create user 'test-password'@'%' identified by '123'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> create user 'test-password'@'%' identified by 'ab123'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> create user 'test-password'@'%' identified by 'password123456!'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> create user 'test-password'@'%' identified by 'password123456!A'; Query OK, 0 rows affected (0.00 sec)
密码自动过期策略
-
查看用户密码状态
# 查看用户密码状态,为N(no)未过期,为Y(已过期) 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 | localhost | N | NULL | 2020-10-27 13:21:25 | N | | mysql.session | localhost | N | NULL | 2020-10-27 13:14:58 | Y | | mysql.sys | localhost | N | NULL | 2020-10-27 13:14:58 | Y | | root | % | N | NULL | 2020-10-27 13:22:52 | N | | cmf | % | Y | NULL | 2021-06-08 15:39:36 | N | | amon | % | N | NULL | 2020-10-28 13:11:34 | N | | hue | % | N | NULL | 2020-10-28 15:03:35 | N | | hive | % | N | NULL | 2021-06-08 18:35:08 | N | | oozie | % | N | NULL | 2020-10-28 15:05:40 | N | | nacos | % | N | NULL | 2020-10-29 14:58:15 | N | +---------------+-----------+------------------+-------------------+-----------------------+----------------+
-
设置单个用户密码立即过期
mysql> ALTER USER 'oozie'@'%' 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 where user = 'oozie'; +---------------+-----------+------------------+-------------------+-----------------------+----------------+ | user | host | password_expired | password_lifetime | password_last_changed | account_locked | +---------------+-----------+------------------+-------------------+-----------------------+----------------+ | oozie | % | Y | NULL | 2020-10-28 15:05:40 | N | +---------------+-----------+------------------+-------------------+-----------------------+----------------+ 1 rows in set (0.00 sec)
-
密码过期用户无法进行任何操作
mysql> select user,host,password_expired,password_lifetime,password_last_changed,account_locked from mysql.user; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
设置单个用户密码过期时间
# 设置用户密码过期时间为60天
mysql> ALTER USER 'oozie'@'%' PASSWORD EXPIRE INTERVAL 60 DAY;
Query OK, 0 rows affected (0.00 sec)
设置全局用户密码过期时间
- 先手动更改再加入配置文件
mysql> SET GLOBAL default_password_lifetime = 60; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'default_password_lifetime'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | default_password_lifetime | 60 | +---------------------------+-------+ 1 row in set (0.02 sec)
写入配置文件并重启生效
[mysqld]
# 密码过期策略
default_password_lifetime = 60
# 密码校验策略
plugin-load = validate_password.so
validate_password_length = 15
validate_password_policy = 1
validate-password = FORCE_PLUS_PERMANENT
validate_password_mixed_case_count = 1
validate_password_number_count = 1
validate_password_special_char_count = 1
重启mysql
[root@node90 mysql]# systemctl stop mysqld.service
[root@node90 mysql]# systemctl start mysqld.service
限制IP远程访问
-
配置用户只能限制ip字段访问
# 现在用户是所有主机都能访问 mysql> select user, host from mysql.user where user='LimitIP'; +---------+-----------+ | user | host | +---------+-----------+ | LimitIP | * | +---------+-----------+ 1 row in set (0.00 sec) mysql> update mysql.user set host='192.168.103.17' where user='LimitIP'; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> select user, host from user where user='LimitIP'; ERROR 1046 (3D000): No database selected mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed # 现在用户只允许这个IP地址192.168.103.17访问 mysql> select user, host from user where user='LimitIP'; +---------+----------------+ | user | host | +---------+----------------+ | LimitIP | 192.168.103.17 | +---------+----------------+ 1 row in set (0.00 sec)
-
上面测试发现,如果这样只修改mysql.user表, 那么之前的权限没有了,如下所示,如果你查询mysql.db、 mysql.tables_priv 发现Host的字段值依然为*
mysql> select * from mysql.db where user='LimitIP'\G; *************************** 1. row *************************** Host: * Db: MyDB User: LimitIP Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Execute_priv: N Event_priv: N Trigger_priv: N 1 row in set (0.00 sec) ERROR: No query specified mysql> select * from mysql.tables_priv where user='LimitIP'\G; *************************** 1. row *************************** Host: * Db: MyDB User: LimitIP Table_name: kkk Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Insert,Update,Delete Column_priv: 1 row in set (0.00 sec) ERROR: No query specified
-
所以继续修改 mysql.db、 mysql.tables_priv 表,,当然如果账户的权限不止这几个层面,可能还必须修改例如mysql.columns_priv、mysql.procs_priv等表
-
参考博文:相关文章