MySQL 8.0 New Password Policies: Dual‑Password Strategy and Random Password Generation

Today we continue to introduce MySQL 8.0's new password policies, namely the dual‑password strategy and built‑in random password generation.

First, Dual‑Password Strategy:

The dual‑password strategy requires periodically changing a designated user's password while temporarily retaining the old password, allowing a buffer period for applications to transition without interruption. It is useful in a typical master‑slave (one‑master‑multiple‑slaves) architecture with read‑write separation, where DBAs must rotate passwords for application accounts safely.

The mechanism consists of a primary password and a backup password; when the backup password is discarded, the primary password becomes the sole credential.

Usage example:

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)

After creation, the user ytt can connect with either password:

# Using backup password
mysql -h ytt-ubuntu -P 3306 -uytt -proot_old -e "select 'hello world'"
+-------------+
| hello world |
+-------------+

# Using primary password
mysql -h ytt-ubuntu -P 3306 -uytt -proot_new -e "select 'hello world'"
+-------------+
| hello world |
+-------------+

Both passwords work until the old password is explicitly discarded:

mysql -S /opt/mysql/mysqld.sock
...
mysql:(none)> alter user ytt discard old password;
Query OK, 0 rows affected (0.02 sec)

Important notes for dual‑password strategy:

If a user already has a dual password and you change the password without the retain current password clause, the previous primary password is replaced while the backup password remains unchanged, which may lead to unexpected authentication results.

Changing the password to an empty string without retaining the current password causes both primary and backup passwords to become empty, disabling the backup password.

Attempting to retain the current password while setting a new password to an empty string results in an error: Current password can not be retained for user ... because new password is empty.

The authentication plugin cannot be changed while using the dual‑password mechanism; attempts will raise an error.

Second, Random Password Generation:

Older MySQL versions required external scripts or stored procedures to generate random passwords. MySQL 8.0 adds native support for creating users with a random password directly.

mysql:(none)> create user ytt_new identified by random password;
+---------+------+----------------------+-------------+
| user    | host | generated password   | auth_factor |
+---------+------+----------------------+-------------+
| ytt_new | %    | >h
+---------+------+----------------------+-------------+

You can also use the SET PASSWORD statement:

mysql:(none)> set password for ytt_new to random;
+---------+------+----------------------+-------------+
| user    | host | generated password   | auth_factor |
+---------+------+----------------------+-------------+
| ytt_new | %    | 5wzZ+0[27cd_CW/
+---------+------+----------------------+-------------

The length of the generated password is controlled by the generated_random_password_length system variable, which defaults to 20 characters.

Summary

The dual‑password strategy helps coordinate password rotation between applications and DBAs, while MySQL 8.0's random password feature enhances database security by simplifying the creation of strong, unpredictable passwords.