code学习

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 |
+------------+
           

采用下面的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;"