日常各种系统账户，包括各大银行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 |
+------------+
采用下面的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.
验证修改情况：修改成功
[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 |
+------------+
这个是非常不安全的，那天用户自己不小心把密码泄露了，被其他的人给修改了，岂不是非常危险的事情。 那这个问题如何解呢？？ 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 |
+-----------+
当前密码验证策略设置
当前密码验证策略有两种方法来给到具体用户。
第一种，从管理员侧来设置单个用户的当前密码验证策略
创建用户或者更改用户设置时使用子句：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)
之后以用户 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 ~]#
直接修改密码报错，要求提供原始的密码：
[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.
下面采用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 |
+-----------+
验证原始密码修改成功:
[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)
如果有的场景下需要保持 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 ~]#
来再次验证下用户 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 |
+-----------+
第二种，设置全局参数，来强制所有用户使用当前密码验证策略:
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.
即使修改的密码还设置的原来的 但是依然要求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.
普通用户采用下面的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 ~]#
验证：
[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)
[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 |
+-----------+
[root@testdb ~]# /usr/local/mysql8/bin/mysql -uroot -S /data1/mysql8/mysql.sock -e "alter user wujianwei password require current default;"