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.

introduction

introduction

Here we introduce the new password authentication strategy that comes with MySQL version 8.0.

text

We are very familiar with this mode: if users want to change their password, they need to provide the original password or add the mobile phone verification code. This mode has never existed in MySQL database. Before MySQL 8.0, ordinary users can directly change their passwords without old password authentication or informing administrators, such as users ytt_admin needs to change the password. Just click the alter user command under MySQL 5.7:

root@ytt-ubuntu:~# mysql -uytt_admin -proot1234 -P5734 -h ytt-ubuntu -e "alter user ytt_admin identified by 'root'"
mysql: [Warning] Using a password on the command line interface can be insecure.

In fact, such password change behavior is not very secure. Suppose the following scenarios occur:

User YTT_ After logging in to MySQL service, admin did some daily operations and forgot to exit after completion; There happens to be a user with ulterior motives ytt_fake enters YTT_ In the login environment of admin, directly click the command alter user to change the user ytt_admin password, and exit the current login environment, user YTT_ When the admin user logs in to MySQL again, he will be prompted with the wrong password and is not allowed to log in. At this time, the user ytt_admin's brain must be confused.

In order to prevent such unsafe events, MySQL 8.0 has issued a series of password authentication policies. Here is the first item: current password authentication policy settings!

The current password authentication policy has two methods to give to specific users.

First, set the current password authentication policy of a single user from the administrator side.

When creating a user or changing user settings, use the clause: password require current (which means to force the user to meet the current password authentication policy).

mysql:(none)>create user ytt_admin identified by 'root123' password require current;
Query OK, 0 rows affected (0.11 sec)

Followed by user ytt_admin logs in to MySQL and changes the password. You need to provide the old password.

root@ytt-ubuntu:/home/ytt# mysql -h ytt-ubuntu -uytt_admin -proot123
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 33
Server version: 8.0.27 MySQL Community Server - GPL

mysql:(none)>alter user ytt_admin identified by 'root';
ERROR 3892 (HY000): Current password needs to be specified in the REPLACE clause in order to change it.

Next, the alter user clause replace s the user ytt_admin entered the old password and successfully changed the new password.

mysql:(none)>alter user ytt_admin identified by 'root' replace 'root123';
Query OK, 0 rows affected (0.00 sec)

If it is necessary to maintain the password change behavior of the old version of MySQL in some scenarios, the administrator side can turn off the new feature with the clause: password require current option.

-- (optional Keywords available default For substitution, refer to global password authentication parameter settings)
mysql:(none)>alter user ytt_admin password require current optional;
Query OK, 0 rows affected (0.04 sec)

To authenticate the user YTT again_ Admin's behavior of changing password: it is changed to the unsafe old version of MySQL security behavior.

mysql:(none)>alter user ytt_admin identified by 'root';
Query OK, 0 rows affected (0.01 sec)

Second, set global parameters to force all users to use the current password authentication policy.

The built-in parameter password of MySQL 8.0_ require_ Current defines a global password policy, which is off by default. When this option is enabled, the user must provide the old password when changing the password.

Enable global parameters:

mysql:(none)>set persist password_require_current=on;
Query OK, 0 rows affected (0.00 sec)

Create another new user ytt_usage:

mysql:(none)>create user ytt_usage identified by 'root123';
Query OK, 0 rows affected (0.00 sec)

As user ytt_usage log in to MySQL and change your password: directly refuse to change, and you need to provide the old password.

root@ytt-ubuntu:~# mysql -uytt_usage -proot123 -h ytt-ubuntu
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 37
Server version: 8.0.27 MySQL Community Server - GPL

...

mysql:(none)>alter user ytt_usage identified by 'root';
ERROR 3892 (HY000): Current password needs to be specified in the REPLACE clause in order to change it.
mysql:(none)>

replace clause provides the old password and successfully changes the new password again:

mysql:(none)>alter user ytt_usage identified by 'root' replace 'root123';
Query OK, 0 rows affected (0.02 sec)

Here is a point to note: Although the global parameter is enabled, the alter user command has higher priority and can directly override the global parameter settings. The following is the environment where the global parameter is enabled. Use the alter user command to close the user YTT_ Current password authentication policy for usage.

mysql:(none)>alter user ytt_usage password require current optional;
Query OK, 0 rows affected (0.11 sec)

Next, user ytt_usage reverts to the security behavior of the old MySQL version:

mysql:(none)>alter user ytt_usage identified by 'rootnew';
Query OK, 0 rows affected (0.11 sec)

There is another clause: password requires current default. The specific behavior is determined by the global parameter password_ require_ The setting of current determines that the global parameter is closed, and this clause restores the security behavior of the old version of MySQL; Global parameter is enabled. This clause uses the new version of MySQL security behavior.

mysql:(none)>alter user ytt_usage password require current default;
Query OK, 0 rows affected (0.09 sec)

Summary:

The current password authentication strategy introduced in this paper makes MySQL work towards more security.