Author: Yang Taotao
Senior database expert, specializing in MySQL for more than ten years. He is good at backup and recovery, SQL tuning, monitoring, operation and maintenance, high availability architecture design related to MySQL, PostgreSQL, MongoDB and other open source databases. At present, he works in aikesheng, providing MySQL related technical support and MySQL related course training for major operators and banking and financial enterprises.
Source: original contribution
*It is produced by aikesheng open source community. The original content cannot be used without authorization. For reprint, please contact Xiaobian and indicate the source.
Today, we will continue to introduce the new password policies of MySQL 8.0, which are double password policy and built-in random password generation.
First, dual password policy:
First, let's explain what is a double password policy? The double password policy is a policy that needs to change the specified user password regularly in daily operation and maintenance, and also needs to keep the old password for a certain period of time. Its function is to delay the connection time between the user's old and new passwords between the application and the database, so as to smooth the operation perception of the application. It can be used in the following scenarios:
In MySQL database, we deploy the most and most mature architecture: one master and many slaves. For example, this architecture separates reading from writing. It is mainly responsible for processing the front-end write traffic, and reading is responsible for processing the front-end read traffic. For security reasons, it is necessary to change the password of the users connecting to the database regularly. With the dual password mechanism, the change of user password can have a certain buffer delay on the application side to avoid the risk of business interruption and the complaints of developers. The application side can still use the old password to complete the retrieval of the database, and wait for the appropriate time to use the new password sent by the administrator to retrieve the database.
The dual password mechanism includes the master password and the standby password. When the standby password is no longer used, the administrator is informed to discard the standby password. At this time, the user's master password is the only password.
How to use it? The usage is as follows:
The administrator creates a new user ytt with the password root_old, it's over. Change his password to root_new . At this point, root_new is the main password, while root_old is the alternate password.
mysql:(none)>create user ytt identified by 'root_old'; Query OK, 0 rows affected, 2 warnings (0.24 sec) mysql:(none)>alter user ytt identified by 'root_new' retain current password; Query OK, 0 rows affected (0.17 sec)
Next, the user ytt uses the standby password and the master password to connect to MySQL and execute a simple SQL statement:
Standby password connection database:
root@ytt-ubuntu:/home/ytt# mysql -h ytt-ubuntu -P 3306 -uytt -proot_old -e "select 'hello world'" mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | hello world | +-------------+ | hello world | +-------------+
Master password connection database:
root@ytt-ubuntu:/home/ytt# mysql -h ytt-ubuntu -P 3306 -uytt -proot_new -e "select 'hello world'" mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | hello world | +-------------+ | hello world | +-------------+
It can be found that both passwords can be used normally before the administrator discards the old password.
After relevant business changes are completed, inform the administrator to discard the standby password:
root@ytt-ubuntu:/home/ytt# mysql -S /opt/mysql/mysqld.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 27 Server version: 8.0.27 MySQL Community Server - GPL ... mysql:(none)>alter user ytt discard old password; Query OK, 0 rows affected (0.02 sec) mysql:(none)>\q Bye
The dual password policy has the following precautions:
If the user already has a dual password policy and does not bring the retain current password clause when changing the new password again, the previous primary password will be replaced with the newly changed password, but the standby password will not be replaced. For example, change the new password to root_new_new. At this time, the backup password is still root_old, not the previous master password root_new . Enter the password root in the following example_ Old can still connect to the database and enter the password root_new is rejected by the database:
mysql:(none)>alter user ytt identified by 'root_new_new'; Query OK, 0 rows affected (0.16 sec) root@ytt-ubuntu:/home/ytt# mysql -h ytt-ubuntu -u ytt -proot_old -e "select 'hello world'" mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | hello world | +-------------+ | hello world | +-------------+ root@ytt-ubuntu:/home/ytt# mysql -h ytt-ubuntu -u ytt -proot_new -e "select 'hello world'" mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'ytt'@'ytt-ubuntu' (using password: YES)
There is another detail to note. If the retain current password clause is not included and the new password is changed to an empty string, the active and standby passwords will be changed to an empty string. In the following example, the database rejects the previous standby password connection:
mysql:(none)>alter user ytt identified by ''; Query OK, 0 rows affected (0.80 sec) root@ytt-ubuntu:/home/ytt# mysql -h ytt-ubuntu -u ytt -proot_old -e "select 'hello world'" mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'ytt'@'ytt-ubuntu' (using password: YES) root@ytt-ubuntu:/home/ytt# mysql -h ytt-ubuntu -u ytt -e "select 'hello world'" +-------------+ | hello world | +-------------+ | hello world | +-------------+
The new password is empty. Alternate passwords are not allowed.
mysql:(none)>alter user ytt identified by '' retain current password; ERROR 3895 (HY000): Current password can not be retained for user 'ytt'@'%' because new password is empty.
When using the double password policy, the user's authentication plug-in cannot be changed.
mysql:(none)>alter user ytt identified with sha256_password by 'root_new' retain current password; ERROR 3894 (HY000): Current password can not be retained for user 'ytt'@'%' because authentication plugin is being changed.
Second, random password generation:
In the past, the old version had the need to generate random passwords, which could not be set directly on the MySQL side, unless the user password setting logic was encapsulated and the random password generation was realized in the code. For example, using stored procedures, scripts, and so on.
MySQL 8.0 can directly set the user's random password
mysql:(none)>create user ytt_new identified by random password; +---------+------+----------------------+-------------+ | user | host | generated password | auth_factor | +---------+------+----------------------+-------------+ | ytt_new | % | >h<m3[bnigz%*f/SnLfp | 1 | +---------+------+----------------------+-------------+ 1 row in set (0.02 sec)
You can also use the set password clause to set a random password
mysql:(none)>set password for ytt_new to random; +---------+------+----------------------+-------------+ | user | host | generated password | auth_factor | +---------+------+----------------------+-------------+ | ytt_new | % | 5wzZ+0[27cd_CW/]<ua, | 1 | +---------+------+----------------------+-------------+ 1 row in set (0.04 sec)
In addition, the length of the random password is determined by the parameter generated_random_password_length adjustment, the default is 20.
summary
The dual password strategy can make the communication between applications and DBA s more coordinated; Random password setting can make the database system more secure.