dasini.net – Diary of a MySQL expert

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…

---

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

MySQL SQL> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.19    |
+-----------+

Create a user account myApp@localhost with password pwd1 :

MySQL root SQL> 
CREATE USER myApp@localhost IDENTIFIED BY 'pwd1';

Now we can connect with the name and the password :

$ mysql -u myApp -ppwd1 -e"SELECT USER()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| USER()          |
+-----------------+
| myApp@localhost |
+-----------------+

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 :

MySQL root SQL> 
ALTER USER myApp@localhost IDENTIFIED BY 'pwd2' RETAIN CURRENT PASSWORD;

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

$ mysql -u myApp -ppwd2 -e"SELECT USER()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| USER()          |
+-----------------+
| myApp@localhost |
+-----------------+

But the old password (pwd1) is still valid :

$ mysql -u myApp -ppwd1 -e"SELECT USER()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| USER()          |
+-----------------+
| myApp@localhost |
+-----------------+

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

MySQL root SQL> 
ALTER USER myApp@localhost DISCARD OLD PASSWORD;

$ mysql -u myApp -ppwd2 -e"SELECT USER()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+
| USER()          |
+-----------------+
| myApp@localhost |
+-----------------+


$ mysql -u myApp -ppwd1 -e"SELECT USER()"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'myApp'@'localhost' (using password: YES)

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

To Go Further

Reference Manual

• Dual Password Support

MySQL Security Serie (1st edition)

• Password Validation Plugin
• Password Management
• User Account Locking
• The Connection-Control Plugins
• Enterprise Audit
• Enterprise Transparent Data Encryption (TDE)
• Enterprise Firewall
• Enterprise Data Masking and De-Identification

Thanks for using MySQL!

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

Filed under:
MySQL, Security, Tuto by Olivier DASINI