MySQL Security – Dual Password Support

May 19, 2020

When thinking about security within a MySQL installation, you can consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications.

MySQL provides many tools / features / plugins or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE)Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration Policy, Password Reuse Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Connection-Control Plugins, Password Validation Component, etc…

MySQL Security

TL;DR

Dual-password capability makes it possible to seamlessly perform credential changes without downtime.


MySQL implements dual-password capability with syntax that saves and discards secondary passwords :

  • The RETAIN CURRENT PASSWORD clause for the ALTER USER and SET PASSWORD statements saves an account current password as its secondary password when you assign a new primary password.
  • The DISCARD OLD PASSWORD clause for ALTER USER discards an account secondary password, leaving only the primary password.

The purpose is to avoid downtime while changing passwords in a replicated environment.

Clients can use the old password while a new password is being established in a group of servers and retire the old password only when the new password has been established across the whole group.

The workflow is :

  1. On each server that is not a replication slave, establish the new password
    e.g.
    ALTER USER ‘myApp’@’host’ IDENTIFIED BY ‘NEW_password’ RETAIN CURRENT PASSWORD;
  2. Wait for the password change to replicate throughout the system to all slave servers
  3. Modify each application that uses the myApp account so that it connects to the servers using a password of ‘NEW_password’ rather than ‘OLD_password’
  4. On each server that is not a replication slave, discard the secondary password
    e.g.
    ALTER USER ‘myApp’@’host’ DISCARD OLD PASSWORD;

Let’s take a quick look using MySQL 8.0

Create a user account myApp@localhost with password pwd1 :

Now we can connect with the name and the password :

Note:
As indicated in the output, it is a very bad practice to put the password on the command line interface.

Now the DBA (super user) use ALTER USER statement with the RETAIN CURRENT PASSWORD clause to perform credential changes using the dual password mechanism by adding as primary password pwd2.
Thus pwd1 is now the secondary password :

We can use the user name and the new password (pwd2) to connect :

But the old password (pwd1) is still valid :

Now it is the time to discard the secondary password (pwd1) :

As you can see, only the new password (pwd2) is valid.

To Go Further

Reference Manual

MySQL Security Serie (1st edition)

Thanks for using MySQL!

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

2 Responses to “MySQL Security – Dual Password Support”

  1. […] Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control Plugins, Password Validation Component, […]

  2. […] Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control Plugins, Password Validation Component, […]