MySQL的密码策略

---

一、配置远程主机可登录mysql数据库

用户可从哪台主机连接mysql数据库是由mysql.user表中的host值来确定的。

默认情况下host值都为localhost，如用户需要从任何主机都可连接mysql数据库，可将host值置为%，host值也可为固定IP地址，表示该用户只能从该IP地址所属主机登录连接mysql。
mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

将user用户的host值设置为%

mysql> update mysql.user set host='%' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

刷新权限

mysql> flush privileges;  
Query OK, 0 rows affected (0.01 sec)

二、MySQL的密码复杂度

MySQL 系统自带有 validate_password 插件，此插件可以验证密码强度，未达到规定强度的密码则不允许被设置。MySQL 5.7版本默认是不启用该插件的，8.0 版本默认情况下启用该插件。

5.7的密码复杂度是由validate_password_policy参数控制

mysql> show variables like'validate_password%';
Empty set (0.00 sec)
--查询参数后发现参数不存在，我们需要安装一下validate_password 插件。

安装插件

mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected (0.05 sec)
--插件安装完成后，即可看到相关参数。
mysql> show 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)
--安装插件后密码强度默认值是MEDIUM，不影响我们现有弱密码用户登录数据库

8.0的密码复杂度是由validate_password.policy参数控制

mysql> show variables like'validate_password%';
+--------------------------------------+--------+
| 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 插件参数解释：

1、validate_password_policy
代表的密码策略，默认是MEDIUM 可配置的值有以下：
0 or LOW 仅需需符合密码长度（由参数validate_password_length指定）
1 or MEDIUM 满足LOW策略，同时还需满足至少有1个数字，小写字母，大写字母和特殊字符
2 or STRONG 满足MEDIUM策略，同时密码不能存在字典文件（dictionary file）中

2、validate_password_dictionary_file
用于配置密码的字典文件，当validate_password_policy设置为STRONG时可以配置密码字典文件，字典文件中存在的密码不得使用。

3、validate_password_length
用来设置密码的最小长度，默认值是8

4、validate_password_mixed_case_count
当validate_password_policy设置为MEDIUM或者STRONG时，密码中至少同时拥有的小写和大写字母的数量，默认是1最小是0；默认是至少拥有一个小写和一个大写字母。

5、validate_password_number_count
当validate_password_policy设置为MEDIUM或者STRONG时，密码中至少拥有的数字的个数，默认1最小是0

6、validate_password_special_char_count
当validate_password_policy设置为MEDIUM或者STRONG时，密码中至少拥有的特殊字符的个数，默认1最小是0

三、修改密码过期时间

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-12-30 11:28:07   | N              |
| mysql.infoschema | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
| mysql.session    | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
| mysql.sys        | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
4 rows in set (0.00 sec)
--用户密码过期状态由password_expired控制，过期时间由password_lifetime控制。

使test用户密码立即过期

mysql> create user test identified by'Huawei12#$';
Query OK, 0 rows affected (0.05 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-12-30 11:28:07   | N              |
| test             | %         | N                |              NULL | 2022-05-16 22:52:51   | N              |
| mysql.infoschema | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
| mysql.session    | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
| mysql.sys        | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
5 rows in set (0.00 sec)

mysql> alter user 'test'@'%' password expire;
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 | 2021-12-30 11:28:07   | N              |
| test             | %         | Y                |              NULL | 2022-05-16 22:52:51   | N              |
| mysql.infoschema | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
| mysql.session    | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
| mysql.sys        | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
5 rows in set (0.00 sec

--test用户的password_expired列值立即变为了Y，重新用test用户登录会提示你修改密码。

修改账号密码永不过期

mysql> alter user 'test'@'%' password expire never;
Query OK, 0 rows affected (0.15 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-12-30 11:28:07   | N              |
| test             | %         | N                |                 0 | 2022-05-16 22:57:55   | N              |
| mysql.infoschema | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
| mysql.session    | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
| mysql.sys        | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
5 rows in set (0.01 sec)
--test用户的password_lifetime列值立即变为了0。

设置账号密码90天过期

mysql> ALTER USER 'test'@'%' 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 | 2021-12-30 11:28:07   | N              |
| test             | %         | N                |                90 | 2022-05-16 22:57:55   | N              |
| mysql.infoschema | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
| mysql.session    | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
| mysql.sys        | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
5 rows in set (0.00 sec)

将账号test使用默认的密码过期全局策略

mysql> ALTER USER 'test'@'%' PASSWORD EXPIRE DEFAULT;
Query OK, 0 rows affected (0.06 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-12-30 11:28:07   | N              |
| test             | %         | N                |              NULL | 2022-05-16 22:57:55   | N              |
| mysql.infoschema | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
| mysql.session    | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
| mysql.sys        | localhost | N                |              NULL | 2021-12-30 11:14:57   | Y              |
+------------------+-----------+------------------+-------------------+-----------------------+----------------+
5 rows in set (0.00 sec)

设置密码全局过期策略

mysql> show variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 0     |
+---------------------------+-------+
1 row in set (0.00 sec)
--默认为0，表示密码不过期。

mysql> SET GLOBAL default_password_lifetime = 90;
Query OK, 0 rows affected (0.00 sec)
--设置密码90天过期

mysql> show variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 90    |
+---------------------------+-------+
1 row in set (0.01 sec)


# 写入配置文件使得重启生效
[mysqld]
default_password_lifetime = 90

四、修改root密码

修改密码有很多种方法，这里记录最简单的一种。

--5.7和8.0均可用这种方式修改。
mysql> alter user root@'%' identified with mysql_native_password by'Huawei12#$';

这里要注意一个问题，我们这修改的是root@’%’这个用户的密码，也就是修改的root远程登录时候的密码，本底登录也就是root@’localhost’的密码并没有修改。

--用户表里面有root@'%'和root@'localhost'，user表的user和host列是双主键。
mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
--(root,%)，表示可以远程登录，并且是除服务器外的其他任何终端，%表示任意IP都可登录。
--(root,localhost)，  表示可以本地登录，即可以在服务器上登陆，localhost则只允许本地登录。
--(root,127.0.0.1 )，表示可以本机登陆，即可以在服务器上登陆

忘记root密码

--MySQL 5.7.6 and later
#vi /etc/my.cnf
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
skip-grant-tables
--在my.cnf配置文件内添加skip-grant-tables跳过权限验证
#service mysqld restart           --重启mysql服务
--直接输入mysql登录
[root@hisdb etc]# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 7
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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.

mysql>
--开始修改root密码