一、配置远程主机可登录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’的密码并没有修改。文章来源：https://www.toymoban.com/news/detail-778558.html
--用户表里面有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密码文章来源地址https://www.toymoban.com/news/detail-778558.html
--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密码
到了这里，关于MySQL的密码策略的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
