概念描述

 从8.0.14版本开始，MySQL实现了双密码功能：允许用户同时有两个密码存在，这个新功能确实可以解决很多实际问题，典型使用场景为：
  1. 密码修改：修改密码前后服务访问不中断，不影响业务。
  2. 角色区分：两类不同的用户使用不同的密码，随时可以中断一类用户的使用。
 从8.0.18版本开始，MySQL又提供了一个产生随机密码的功能，在创建用户时候可以指定随机密码，创建完用户后，会显示出一个20位长度的随机密码，再也不用去为想一个符合安全级别的复杂密码而发愁了。

测试验证

1. 双密码功能测试场景：

-- 1. 创建业务用户app_user 和第一个密码 “old_password”：
root@localhost:(none) 09:07:54 >create user app_user@'%' identified by 'old_password';
Query OK, 0 rows affected (0.08 sec)
root@localhost:(none) 09:09:00 >grant all on testdb.* to app_user@'%';
Query OK, 0 rows affected (0.01 sec)
root@localhost:(none) 09:09:16 >flush privileges;
Query OK, 0 rows affected (0.01 sec)

-- 2. 为用户app_user创建第二个密码（新密码为主密码，旧密码为辅助密码）
root@localhost:(none) 09:10:47 >alter user app_user@'%' identified by 'new_password' RETAIN CURRENT PASSWORD;
Query OK, 0 rows affected (0.01 sec)
说明：这里请记住关键字：“RETAIN CURRENT PASSWORD” 必须指定，这条语句是必须要带上的。

-- 3. 验证是否可以两个密码都同时能登录
## 使用旧密码old_password登录
[root@c1 ~]# /data/mysql/mysql8032/bin/mysql -uapp_user -pold_password -h 192.168.139.128 -P 3832
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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.

app_user@192.168.139.128:(none) 09:17:04 >show grants;
+------------------------------------------------------+
| Grants for app_user@%                                |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`%`                 |
| GRANT ALL PRIVILEGES ON `testdb`.* TO `app_user`@`%` |
+------------------------------------------------------+
2 rows in set (0.01 sec)

app_user@192.168.139.128:(none) 09:17:09 >exit
Bye

## 使用新密码new_password登录
[root@c1 ~]# /data/mysql/mysql8032/bin/mysql -uapp_user -pnew_password -h 192.168.139.128 -P 3832
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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.

app_user@192.168.139.128:(none) 09:17:30 >show grants;
+------------------------------------------------------+
| Grants for app_user@%                                |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`%`                 |
| GRANT ALL PRIVILEGES ON `testdb`.* TO `app_user`@`%` |
+------------------------------------------------------+
2 rows in set (0.00 sec)
说明：经验证，同时使用新密码和旧密码都可以无缝登录MySQL数据库，权限都一样，可以同时使用。

附加试验：将新密码从 new_password修改为newnew_password
root@localhost:(none) 09:31:29 >alter user app_user@'%' identified by 'newnew_password';
Query OK, 0 rows affected (0.01 sec)

使用修改后的新密码 和之前的旧密码 依然可以正常登录（新密码的修改不影响旧密码的使用）
[root@c1 ~]# /data/mysql/mysql8032/bin/mysql -uapp_user -pnewnew_password -h 192.168.139.128 -P 3832
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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.

app_user@192.168.139.128:(none) 09:32:19 >exit
Bye
[root@c1 ~]# /data/mysql/mysql8032/bin/mysql -uapp_user -pold_password -h 192.168.139.128 -P 3832
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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.

app_user@192.168.139.128:(none) 09:32:23 >



-- 4. 删除旧密码（辅助密码）：
root@localhost:(none) 09:22:47 >alter user app_user@'%' DISCARD OLD PASSWORD;
Query OK, 0 rows affected (0.01 sec)

root@localhost:(none) 09:23:06 >exit
Bye
## 删除旧密码后，再使用旧密码登录被拒绝
[root@c1 ~]# /data/mysql/mysql8032/bin/mysql -uapp_user -pold_password -h 192.168.139.128 -P 3832
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'app_user'@'c1' (using password: YES)
[root@c1 ~]# 

复制

2. 随机密码功能测试场景：

## 创建用户时指定关键字RANDOM PASSWORD 
root@localhost:(none) 09:40:12 >create user user1@'%' IDENTIFIED BY RANDOM PASSWORD,
    -> user2@'10.202.173.%' IDENTIFIED BY RANDOM PASSWORD,
    -> user3@'%.enmotech.com' IDENTIFIED BY RANDOM PASSWORD;
+-------+----------------+----------------------+-------------+
| user  | host           | generated password   | auth_factor |
+-------+----------------+----------------------+-------------+
| user1 | %              | hF*T(GV)68n;569U5mYk |           1 |
| user2 | 10.202.173.%   | Vx6TQefn9/-SYeIu1<[R |           1 |
| user3 | %.enmotech.com | OaGedK_@3@UVGe1a<:Zz |           1 |
+-------+----------------+----------------------+-------------+
3 rows in set (0.01 sec)

root@localhost:(none) 09:42:26 >set password for user1@'%' to random;
+-------+------+----------------------+-------------+
| user  | host | generated password   | auth_factor |
+-------+------+----------------------+-------------+
| user1 | %    | VWn*Anx;J__RFPknv{L] |           1 |
+-------+------+----------------------+-------------+
1 row in set (0.00 sec)

root@localhost:(none) 09:42:44 >

## 控制随机密码长度的变量generated_random_password_length，默认为20个字符
root@localhost:(none) 09:42:44 >show variables like 'generated_random_password_length';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| generated_random_password_length | 20    |
+----------------------------------+-------+
1 row in set (0.01 sec)

## 可以在线修改随机密码的长度
root@localhost:(none) 09:45:32 >set generated_random_password_length=50;
Query OK, 0 rows affected (0.00 sec)

root@localhost:(none) 09:45:45 >set password for user3@'%.enmotech.com' to random;
+-------+----------------+----------------------------------------------------+-------------+
| user  | host           | generated password                                 | auth_factor |
+-------+----------------+----------------------------------------------------+-------------+
| user3 | %.enmotech.com | 2Q&tfmV)XB74LDJS7eO;RaG1+uelUo0W635qh3pI.HRv}wsQ7, |           1 |
+-------+----------------+----------------------------------------------------+-------------+
1 row in set (0.00 sec)


复制

知识总结

1. MySQL8.0.14版本以后的双密码可以有效解决修改密码产生的影响，同时也可以让两个密码承担起不同的作用。
2. 如果用户已经存老密码/辅助密码，只修改新密码/主密码（不使用RETAIN CURRENT PASSWORD），老密码/辅助密码保持不变。
3. 如果指定新密码为空，那么老密码也会被设置为空，不建议这样做。
4. 对自身账号设置辅助密码，自身用户需要具有 APPLICATION_PASSWORD_ADMIN权限。
5. 对其他账号设置辅助密码，自身用户需要有CREATE USER权限。
6. MySQL8.0.18版本以后随机密码可以让创建复杂密码更加简单高效，并可以设置随机密码的长度。

参考文档

https://dev.mysql.com/doc/refman/8.0/en/password-management.html