Using MySQL 8 Dual Passwords

MySQL 8 brought many highly anticipated features, with support for user roles, a new shell, a more robust data dictionary, and better SQL support, just to name a few. There are lesser-known new features, however, that aim to reduce overall DBA workload and streamline management processes – and one of these is support for dual passwords, first implemented in MySQL 8.0.14. User accounts are now permitted to have dual passwords, with a designated primary and secondary. This makes it possible to seamlessly perform user credential changes even with a large number of servers, or with multiple applications connecting to different MySQL servers.

Historically, a MySQL credential change had to be timed so that when the password change was made and propagated throughout the database nodes, all applications that use that account for connections had to be updated at the same time. This is problematic for many reasons, but with database and application server counts as high as they are today, it becomes especially burdensome at the modern enterprise scale.

Overview

With dual passwords, credential changes can be made easily without requiring any coordination or downtime. The process would work something like this:

• For each account to be updated, establish a new primary password on the server(s) while retaining the current password as secondary.
  • At this point, the servers will recognize both passwords (primary and secondary) and all applications can continue connecting with the old password the same as before.
• Once the password change has been done on the database side, the applications can be updated to connect using the new primary password.
  • Note that this can be done over a period of time, utilizing existing downtime maintenance windows if necessary.
• After all the applications have been migrated to the new primary password, the secondary password is no longer needed on the database side and can be discarded.
  • Once discarded, only the new primary password can be used, and the credential change is now complete.

Usage & Example

To accomplish this dual password capability, the following new syntax will save and discard secondary passwords:

• RETAIN CURRENT PASSWORD clause for the ALTER USER and SET PASSWORD statements saves the current password as a secondary password when a new primary is assigned.
• DISCARD OLD PASSWORD clause for ALTER USER will discard a secondary password, leaving only the primary password in play.

As an example, let’s use the dual password feature to update the password for a theoretical user (‘appuser’@’percona.com’). For this example, assume that application(s) will connect to the database with this user and that we will be changing the password from ‘oldpass’ to ‘newpass’.

1. On each server (that isn’t a replica), we’ll first set ‘newpass’ as the new primary password for ‘appuser’@’percona.com’ while retaining the current password as secondary:
   Shell

   mysql> ALTER USER ‘appuser’@’percona.com’ IDENTIFIED BY ‘newpass’ RETAIN CURRENT PASSWORD;
2. Once the password change has propagated through all the replica nodes, we can now begin changing the application(s) that are using the ‘appuser’@’percona.com’ account to begin connecting with the new password (newpass) rather than the original password (oldpass). This can be done over a period of time, during existing downtime maintenance windows if necessary to minimize impact. Remember, at this point both passwords are valid.
3. With the application changes made, the secondary (old) password is no longer needed. On each server (that isn’t a replica), discard the secondary password:
   Shell

   mysql> ALTER USER ‘appuser’@’percona.com’ DISCARD OLD PASSWORD;
4. Once this has propagated through all replicas, the credential change is now complete.

There are a few caveats you may want to be aware of in using the dual password feature:

• If you use RETAIN CURRENT PASSWORD for an account that has an empty primary password, the statement will fail.
• If an account has a secondary password, and you change the primary password without specifying RETAIN CURRENT PASSWORD, the secondary password remains unchanged.
• If you use ALTER USER and change the authentication plugin assigned to the account, the secondary password is discarded.
  • If you change the authentication plugin and also specify RETAIN CURRENT PASSWORD, the statement fails.

Required Privileges

For modifying your own user account(s), the APPLICATION PASSWORD ADMIN privilege is required in order to use RETAIN CURRENT PASSWORD or DISCARD OLD PASSWORD clause for both the ALTER USER and SET PASSWORD statements.

For modifying the secondary password for any (or all) accounts at an administrative level, the CREATE USER privilege is needed rather than the APPLICATION PASSWORD ADMIN privilege as above.

In Closing

While this is a very simple new feature, it can have a fairly significant impact on how your company manages the security aspects of frequent password changes, minimizing or possibly eliminating downtime from password updates altogether.