mysql8.0密码策略之当前密码验证策略设置

日常各种系统账户，包括各大银行app等重要软件 用户是可以修改自己密码的，通常安全策略都是要求用户输入自己之前的账户密码，然后在输入新的密码，进行重置密码，或者是就是要求收入注册手机号，获取手机验证码,然后进行修改个人的账户密码。然而这种安全策略模式在MySQL 数据库里一直不存在的。
在MySQL5.7 版本以及之前的版本，普通用户可以直接更改自己密码，不需要旧密码验证，也不需要知会管理员。
比如在下面mysql5.7.22版本测试:
创建测试用户:

登录后复制

root@testdb 16:32:  [(none)]>  grant select on *.* to wujianwei@'172.16.0.59' identified by 'wujianwei';flush privileges;
Query OK, 0 rows affected, 1 warning (0.00 sec)

[root@testdb ~]# mysql -uwujianwei -h 172.16.0.59 -pwujianwei -e "select version();"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| version()  |
+------------+
| 5.7.22-log |
+------------+
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

采用下面的sql指令修改自己的登陆账户密码:

登录后复制

[root@testdb ~]# mysql -uwujianwei -h 172.16.0.59 -pwujianwei -e "alter user wujianwei@'172.16.0.59' identified by 'wujianwei123';"
mysql: [Warning] Using a password on the command line interface can be insecure.
1.
2.

验证修改情况：修改成功

登录后复制

[root@testdb ~]# mysql -uwujianwei -h 172.16.0.59 -pwujianwei -e "select version();"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'wujianwei'@'172.16.0.59' (using password: YES)
[root@testdb ~]# mysql -uwujianwei -h 172.16.0.59 -pwujianwei123 -e "select version();"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| version()  |
+------------+
| 5.7.22-log |
+------------+
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

这个是非常不安全的，那天用户自己不小心把密码泄露了，被其他的人给修改了，岂不是非常危险的事情。 那这个问题如何解呢？？ MySQL8.0 引入了一系列密码验证策略

下面演示环境都是以GA版 mysql8.0.28进行的, root账户为默认的管理员账户

登录后复制

[root@testdb ~]# /usr/local/mysql8/bin/mysql -uroot -S /data1/mysql8/mysql.sock -e "select version();"
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1.
2.
3.
4.
5.
6.

当前密码验证策略设置
当前密码验证策略有两种方法来给到具体用户。
第一种，从管理员侧来设置单个用户的当前密码验证策略
创建用户或者更改用户设置时使用子句：password require current（表示强制此用户满足当前密码验证策略）
sql指令：

登录后复制

root@testdb 16:48:  [(none)]> create user wujianwei@'172.16.0.246' identified by 'wujianwei123' password require current;
Query OK, 0 rows affected (0.00 sec)

root@testdb 16:49:  [(none)]>  create user testuser identified by 'testuser123' password require current;
Query OK, 0 rows affected (0.00 sec)
root@testdb 16:50:  [(none)]> select user,host from mysql.user where user='testuser' or user='wujianwei';
+-----------+--------------+
| user      | host         |
+-----------+--------------+
| testuser  | %            |
| wujianwei | 172.16.0.246 |
+-----------+--------------+
3 rows in set (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

之后以用户 wujianwei/testuser 登录 MySQL 并且更改密码，提示需要提供旧密码才行。

登录后复制

[root@testdb ~]# /usr/local/mysql8/bin/mysql -utestuser -S /data1/mysql8/mysql.sock -ptestuser123 -e "select version();"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
[root@testdb ~]# 
1.
2.
3.
4.
5.
6.
7.
8.

直接修改密码报错，要求提供原始的密码：

登录后复制

[root@testdb ~]# /usr/local/mysql8/bin/mysql -utestuser -S /data1/mysql8/mysql.sock -ptestuser123 -e "alter user testuser identified by 'testuser';"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3892 (HY000) at line 1: Current password needs to be specified in the REPLACE clause in order to change it.
1.
2.
3.

下面采用replace指令替换原来的密码 进行修改密码:

登录后复制

[root@testdb ~]# /usr/local/mysql8/bin/mysql -utestuser -S /data1/mysql8/mysql.sock -ptestuser123 -e "alter user testuser identified by 'testuser' replace 'testuser123';"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@testdb ~]# /usr/local/mysql8/bin/mysql -utestuser -S /data1/mysql8/mysql.sock -ptestuser -e "select version();"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1.
2.
3.
4.
5.
6.
7.
8.
9.

验证原始密码修改成功:

登录后复制

[root@testdb ~]# /usr/local/mysql8/bin/mysql -utestuser -S /data1/mysql8/mysql.sock -ptestuser123 -e "select version();"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'testuser'@'localhost' (using password: YES)
1.
2.
3.

如果有的场景下需要保持 MySQL 旧版本的密码更改行为，管理员可以用子句：password require current optional 关闭新特性。

登录后复制

-- (optional 关键词可用default 替代，参考全局密码验证参数设置)
[root@testdb ~]#  /usr/local/mysql8/bin/mysql -uroot -S /data1/mysql8/mysql.sock -e "alter user testuser password require current optional;"
[root@testdb ~]# 
1.
2.
3.

来再次验证下用户 testuser 更改密码的行为：保持 MySQL 8.0 之前的的规则，不对该用户密码做校验。

登录后复制

[root@testdb ~]#  /usr/local/mysql8/bin/mysql -utestuser -S /data1/mysql8/mysql.sock -ptestuser -e "alter user testuser identified by 'testuser456'"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@testdb ~]# 
[root@testdb ~]#  /usr/local/mysql8/bin/mysql -utestuser -S /data1/mysql8/mysql.sock -ptestuser456 -e "select version();"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

第二种，设置全局参数，来强制所有用户使用当前密码验证策略:
MySQL 8.0 新版本内置的参数 password_require_current 定义一个全局密码策略，默认关闭。开启这个选项时，要求用户更改密码时必须提供旧密码。

登录后复制

[root@testdb ~]#  /usr/local/mysql8/bin/mysql -uroot -S /data1/mysql8/mysql.sock -e "set persist password_require_current=on;"
[root@testdb ~]# /usr/local/mysql8/bin/mysql -uwujianwei -S /data1/mysql8/mysql.sock -pwujianwei -e "alter user wujianwei identified by 'wujianwei123';"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3892 (HY000) at line 1: Current password needs to be specified in the REPLACE clause in order to change it.
1.
2.
3.
4.

即使修改的密码还设置的原来的 但是依然要求replace指令替换密码

登录后复制

[root@testdb ~]# 
[root@testdb ~]# /usr/local/mysql8/bin/mysql -uwujianwei -S /data1/mysql8/mysql.sock -pwujianwei -e "alter user wujianwei identified by 'wujianwei';"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3892 (HY000) at line 1: Current password needs to be specified in the REPLACE clause in order to change it.
1.
2.
3.
4.

普通用户采用下面的sql指令:

登录后复制

alter user wujianwei identified by 'wujianwei123' replace 'wujianwei';
[root@testdb ~]# /usr/local/mysql8/bin/mysql -uwujianwei -S /data1/mysql8/mysql.sock -pwujianwei -e "alter user wujianwei identified by 'wujianwei123' replace 'wujianwei';"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@testdb ~]# 
1.
2.
3.
4.

验证：

登录后复制

[root@testdb ~]# /usr/local/mysql8/bin/mysql -uwujianwei -S /data1/mysql8/mysql.sock -pwujianwei123 -e "select version();"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
[root@testdb ~]# 
[root@testdb ~]# /usr/local/mysql8/bin/mysql -uwujianwei -S /data1/mysql8/mysql.sock -pwujianwei -e "select version();"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'wujianwei'@'localhost' (using password: YES)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

提示：
有一个需要注意的点：虽然全局参数开启，但是 alter user 命令优先级更高，可以直接覆盖全局参数设置。下面是全局参数开启的环境下，用 alter user 命令来关闭用户 wujianwei 的当前密码验证策略。

登录后复制

[root@testdb ~]#  /usr/local/mysql8/bin/mysql -uroot -S /data1/mysql8/mysql.sock -e "alter user wujianwei password require current optional;"
[root@testdb ~]# 
[root@testdb ~]# /usr/local/mysql8/bin/mysql -uwujianwei -S /data1/mysql8/mysql.sock -pwujianwei123 -e "alter user wujianwei identified by 'wujianwei456'"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@testdb ~]# 
[root@testdb ~]# /usr/local/mysql8/bin/mysql -uwujianwei -S /data1/mysql8/mysql.sock -pwujianwei456 -e "select version();"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

还有另外一个子句：password require current default ，具体行为由全局参数password_require_current 的设置决定，全局参数关闭，这个子句恢复 MySQL 旧版本安全行为；全局参数开启，这个子句使用 MySQL 新版本安全行为

登录后复制

[root@testdb ~]#  /usr/local/mysql8/bin/mysql -uroot -S /data1/mysql8/mysql.sock -e "alter user wujianwei password require current default;"
1.

参考文章：
 https://mp.weixin.qq.com/s/GK56N5SaafCIKuP6N3lMUg