MySQL的密码策略

文章目录

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

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

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

将user用户的host值设置为%

mysql>update mysql.userset host='%'whereuser='root';
Query OK,1row affected (0.01 sec)Rowsmatched: 1  Changed: 1Warnings: 0

刷新权限

mysql> flush privileges;  
Query OK,0rows 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,0rows 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|+--------------------------------------+--------+7rowsinset(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|+--------------------------------------+--------+7rowsinset(0.00 sec)

validate_password 插件参数解释：

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

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

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

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

三、修改密码过期时间

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

使test用户密码立即过期

mysql>createuser test identified by'Huawei12#$';
Query OK,0rows affected (0.05 sec)

mysql>selectuser,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-3011:28:07| N              || test             |%| N                |NULL|2022-05-1622:52:51| N              || mysql.infoschema | localhost | N                |NULL|2021-12-3011:14:57| Y              || mysql.session| localhost | N                |NULL|2021-12-3011:14:57| Y              || mysql.sys        | localhost | N                |NULL|2021-12-3011:14:57| Y              |+------------------+-----------+------------------+-------------------+-----------------------+----------------+5rowsinset(0.00 sec)

mysql>alteruser'test'@'%' password expire;
Query OK,0rows affected (0.01 sec)

mysql>selectuser,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-3011:28:07| N              || test             |%| Y                |NULL|2022-05-1622:52:51| N              || mysql.infoschema | localhost | N                |NULL|2021-12-3011:14:57| Y              || mysql.session| localhost | N                |NULL|2021-12-3011:14:57| Y              || mysql.sys        | localhost | N                |NULL|2021-12-3011:14:57| Y              |+------------------+-----------+------------------+-------------------+-----------------------+----------------+5rowsinset(0.00 sec

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

修改账号密码永不过期

mysql>alteruser'test'@'%' password expire never;
Query OK,0rows affected (0.15 sec)

mysql>selectuser,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-3011:28:07| N              || test             |%| N                |0|2022-05-1622:57:55| N              || mysql.infoschema | localhost | N                |NULL|2021-12-3011:14:57| Y              || mysql.session| localhost | N                |NULL|2021-12-3011:14:57| Y              || mysql.sys        | localhost | N                |NULL|2021-12-3011:14:57| Y              |+------------------+-----------+------------------+-------------------+-----------------------+----------------+5rowsinset(0.01 sec)--test用户的password_lifetime列值立即变为了0。

设置账号密码90天过期

mysql>ALTERUSER'test'@'%' PASSWORD EXPIRE INTERVAL90DAY;
Query OK,0rows affected (0.01 sec)

mysql>selectuser,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-3011:28:07| N              || test             |%| N                |90|2022-05-1622:57:55| N              || mysql.infoschema | localhost | N                |NULL|2021-12-3011:14:57| Y              || mysql.session| localhost | N                |NULL|2021-12-3011:14:57| Y              || mysql.sys        | localhost | N                |NULL|2021-12-3011:14:57| Y              |+------------------+-----------+------------------+-------------------+-----------------------+----------------+5rowsinset(0.00 sec)

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

mysql>ALTERUSER'test'@'%' PASSWORD EXPIRE DEFAULT;
Query OK,0rows affected (0.06 sec)

mysql>selectuser,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-3011:28:07| N              || test             |%| N                |NULL|2022-05-1622:57:55| N              || mysql.infoschema | localhost | N                |NULL|2021-12-3011:14:57| Y              || mysql.session| localhost | N                |NULL|2021-12-3011:14:57| Y              || mysql.sys        | localhost | N                |NULL|2021-12-3011:14:57| Y              |+------------------+-----------+------------------+-------------------+-----------------------+----------------+5rowsinset(0.00 sec)

设置密码全局过期策略

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

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

mysql>show variables like'default_password_lifetime';+---------------------------+-------+| Variable_name             |Value|+---------------------------+-------+| default_password_lifetime |90|+---------------------------+-------+1rowinset(0.01 sec)# 写入配置文件使得重启生效[mysqld]
default_password_lifetime =90

四、修改root密码

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

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

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

--用户表里面有root@'%'和root@'localhost'，user表的user和host列是双主键。
mysql>selectuser,host from mysql.user;+---------------+-----------+|user| host      |+---------------+-----------+| root          |%|| mysql.session| localhost || mysql.sys     | localhost || root          | localhost |+---------------+-----------+4rowsinset(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 endwith;or \g.
Your MySQL connection id is7
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密码