MySQL的密码策略
短信预约 -IT技能 免费直播动态提醒
文章目录
一、配置远程主机可登录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用来设置密码的最小长度，默认值是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> 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.cnfbasedir=/usr/local/mysqldatadir=/usr/local/mysql/dataskip-grant-tables--在my.cnf配置文件内添加skip-grant-tables跳过权限验证#service mysqld restart --重启mysql服务--直接输入mysql登录[root@hisdb etc]# mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 7Server version: 8.0.27 MySQL Community Server - GPLCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>--开始修改root密码来源地址：https://blog.csdn.net/zongzizz/article/details/129953066
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/42.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
