An example of double password experiment in MySQL8.0.16 account
Starting with MySQL 8.0.14, user accounts are allowed to have double passwords, designated as primary and secondary passwords.
The dual password feature can seamlessly perform credential changes in the following scenarios:
The system has a large number of MySQL servers, which may involve master-slave replication
Multiple applications connect to different MySQL servers
Periodic password changes must be made to the account that the application uses to connect to the server
The experiments are as follows:
Mysql version:
Mysql > select version ()
+-+
| | version () |
+-+
| | 8.0.16 |
+-+
1 row in set (0.00 sec)
Mysql > create user root@'%' identified by '123456'
Query OK, 0 rows affected (0.25 sec)
Mysql > grant all privileges on *. * to root@'%'
Query OK, 0 rows affected (0.10 sec)
Mysql > alter user root@'%' identified by 'root' RETAIN CURRENT PASSWORD
Query OK, 0 rows affected (0.28 sec)
Open another session:
You can log in with both passwords:
# mysql-uroot-p123456-h 192.168.140.52
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 21
Server version: 8.0.16 MySQL Community Server-GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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.
Mysql >\ Q
Bye
[root@test2] # mysql-uroot-proot-h 192.168.140.52
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 22
Server version: 8.0.16 MySQL Community Server-GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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.
Mysql >\ Q
Bye
Discard the old password:
Mysql > alter user root@'%' DISCARD OLD PASSWORD
Query OK, 0 rows affected (0.12 sec)
Open another session and log in with the old password to report an error:
# mysql-uroot-p123456-h 192.168.140.52
Mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'192.168.140.52' (using password: YES)
The new password can be logged in:
# mysql-uroot-p123456-h 192.168.140.52
Mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'192.168.140.52' (using password: YES)
[root@test2 ~] #
[root@test2 ~] #
[root@test2] # mysql-uroot-proot-h 192.168.140.52
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 27
Server version: 8.0.16 MySQL Community Server-GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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.
Mysql >\ Q
Bye
Remarks:
RETAIN CURRENT PASSWORD retains the account's current password as its secondary password, replacing any existing secondary password. The new password will be the master password
However, the client can use this account to connect to the server using a primary or secondary password.
For ALTER USER, DISCARD OLD PASSWORD discards the secondary password, if present. The account retains only its master password, and the client can use the
The account uses only the master password to connect to the server.
