MySQL 8.0 Password Rotation

MySQL 8.0 supports password rotation using dual password support.

Dual password support

As of MySQL 8.0.14, user accounts are permitted to have dual passwords, designated as primary and secondary passwords. Dual-password capability makes it possible to seamlessly perform credential changes in scenarios like this:

• A system has a large number of MySQL servers, possibly involving replication.
• Multiple applications connect to different MySQL servers.
• Periodic credential changes must be made to the account or accounts used by the applications to connect to the servers.

Consider how a credential change must be performed in the preceding type of scenario when an account is permitted only a single password. In this case, there must be close cooperation in the timing of when the account password change is made and propagated throughout all servers, and when all applications that use the account are updated to use the new password. This process may involve downtime during which servers or applications are unavailable.

With dual passwords, credential changes can be made more easily, in phases, without requiring close cooperation, and without downtime:

• For each affected account, establish a new primary password on the servers, retaining the current password as the secondary password. This enables servers to recognize either the primary or secondary password for each account, while applications can continue to connect to the servers using the same password as previously (which is now the secondary password).
• After the password change has propagated to all servers, modify applications that use any affected account to connect using the account primary password.
• After all applications have been migrated from the secondary passwords to the primary passwords, the secondary passwords are no longer needed and can be discarded. After this change has propagated to all servers, only the primary password for each account can be used to connect. The credential change is now complete.

Random password generation

Another useful feature of MySQL 8.0 is the random password generation.

As of MySQL 8.0.18, the CREATE USER, ALTER USER, and SET PASSWORD statements have the capability of generating random passwords for user accounts, as an alternative to requiring explicit administrator-specified literal passwords.

Here’s how to create a user using a random password

mysql> create user sysbench@'%' identified by random password;
+----------+------+----------------------+
| user     | host | generated password   |
+----------+------+----------------------+
| sysbench | %    | p%iJnUDNjqH<SB%v}ZF2 |
+----------+------+----------------------+
1 row in set (0.09 sec)
 
mysql> grant all privileges on sysbench.* to sysbench@'%';
Query OK, 0 rows affected (0.10 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

Rotating password using dual password support

Let’s now try changing the password

Change the password using –

mysql> alter user sysbench@'%' identified by random password RETAIN CURRENT PASSWORD;
+----------+------+----------------------+
| user     | host | generated password   |
+----------+------+----------------------+
| sysbench | %    | 8YQuk5pZyMEf{4f}em-C |
+----------+------+----------------------+
1 row in set (0.12 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

RETAIN CURRENT PASSWORD is the clause that enables us to have two active passwords.

Let’s try connecting with old password

mysql -u sysbench -p'p%iJnUDNjqH<SB%v}ZF2' sysbench
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.18 Homebrew
 
mysql>

Let’s try connecting with new password

mysql -u sysbench -p'8YQuk5pZyMEf{4f}em-C' sysbench
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.18 Homebrew
 
mysql>

Both passwords remain active till all applications can finish changing the passwords. Once all applications are finished changing the password we can disable the old password using

mysql> alter user sysbench@'%' DISCARD OLD PASSWORD;
Query OK, 0 rows affected (0.21 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

Now if we try connecting with the old password

mysql -u sysbench -p'p%iJnUDNjqH<SB%v}ZF2' sysbench
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'sysbench'@'localhost' (using password: YES)

Connecting with the new password still works

mysql -u sysbench -p'8YQuk5pZyMEf{4f}em-C' sysbench
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.18 Homebrew
 
mysql>

Observations

Dual password and random password generation makes password rotation much simpler. However it would be nice to add more observability in. For example, what was the last time the old password was used or number of hosts connecting using old passwords. This observability would give a database level assurance that clients have switched over to the new password and it is reasonably safe to discard / delete the old password.