MySQL Dual password how to manage them programmatically
What is dual password in MYSQL and how it works was already covered by my colleague Brian Sumpter here (https://www.percona.com/blog/using-mysql-8-dual-passwords/). 
However let me do a brief recap here about it.
Dual password is the MySQL mechanism that allows you to keep two passwords active at the same time. This feature is part of a more extended set of Password management features implemented in MySQL 8 to enforce better security and secrets management, like:
- Internal Versus External Credentials Storage
- Password Expiration Policy
- Password Reuse Policy
- Password Verification-Required Policy
- Dual Password Support
- Random Password Generation
- Failed-Login Tracking and Temporary Account Locking
The most important and requested features are the password expiration and verification policy. The problem in implementing them is the complexity of replacing passwords for accounts on very large platforms, like with thousands of applications and hundreds of MySQL servers.
In fact, while for a single user it is not so complex to change his own password when requested at login, for an application using thousands of sub-services it may require some time. The problem in performing the password change is that while executing the modification some services will have the updated password while others will still use the old one. Without Dual Password a segment of nodes will receive error messages in connecting creating service disruption.
Now let us cover this blog topic.
With Dual Password it is instead possible to declare a new password keeping the old still active until the whole upgrade has been completed.
This highlight two very important aspects:
- When automating the password update, it is better to not use a password expiration policy, but base the expiration on the completion of the new password deployment.
- We need to be sure the account we are changing the password to keeps the password active until we need it, and that is correctly removed when done.
As you see I am focusing on the cases when we have automation and not the single interactive user update.
How dual password works
Let us assume we have create a user like:
create user dualtest@'192.168.4.%' identified by 'password1'; grant all on test.* to dualtest@'192.168.4.%';
This will generate an entry in MySQL mysql.user table as:
(root@localhost) [(none)]>select user,host, plugin, authentication_string, password_last_changed,User_attributes from mysql.user where user = 'dualtest'\G
*************************** 1. row ***************************
user: dualtest
host: 192.168.4.%
plugin: mysql_native_password
authentication_string: *668425423DB5193AF921380129F465A6425216D0
password_last_changed: 2022-11-17 08:31:37
User_attributes: NULL
1 row in set (0.00 sec)
At this point our user will be able to connect from any application located in the correct network and act on the test schema.
After some time, you as application owner,will be notified by your DBA team that the user dualtest is required to change the password in order to respect the security constraints.
At this point there are two options:
- You have privileges to use Dual Password (the required dynamic privilege to use dual Password is APPLICATION PASSWORD ADMIN).
- You do not have the right privileges.
In case 2 your DBA team must perform the change for you, and then they will let you know the new password.
In case 1 you can do the operation yourself.
In the last case what you will do is:
ALTER USER 'dualtest'@'192.168.4.%' IDENTIFIED BY 'password2' RETAIN CURRENT PASSWORD;
Then check it is done properly:
select user,host, plugin, authentication_string, password_last_changed,User_attributes from mysql.user where user ='dualtest' order by 1,2\G
*************************** 1. row ***************************
user: dualtest
host: 192.168.4.%
plugin: mysql_native_password
authentication_string: *DC52755F3C09F5923046BD42AFA76BD1D80DF2E9
password_last_changed: 2022-11-17 08:46:28
User_attributes: {"additional_password": "*668425423DB5193AF921380129F465A6425216D0"}
1 row in set (0.00 sec)
As you can see here the OLD password has been moved to the User_attributes JSON field that is used in MYSQL8 to store several values.
At this point you can rollout safely the password change and that change can take an hour or a week, no production impact given the applications will be able to use either of them.
Once the process is complete, you can ask your DBA team to remove OLD password, or do:
ALTER USER 'dualtest'@'192.168.4.%' DISCARD OLD PASSWORD;
Then check if the password has being removed properly:
(root@localhost) [(none)]>select user,host, plugin, authentication_string, password_last_changed,User_attributes from mysql.user where user ='dualtest' order by 1,2\G
*************************** 1. row ***************************
user: dualtest
host: 192.168.4.%
plugin: mysql_native_password
authentication_string: *DC52755F3C09F5923046BD42AFA76BD1D80DF2E9
password_last_changed: 2022-11-17 08:46:28
User_attributes: NULL
1 row in set (0.00 sec)
If all is clean the process can be considered complete.
Of course all this should be automated and executed by code not by hand high level it should be more or less like this:
{}Input new password
- Check for additional_password in User_attributes in mysql.user
<> If no value you can proceed otherwise exit (another change is in place)
- Read and store authentication_string for the user you need to change password
- Change current password with: Alter user ... RETAIN CURRENT PASSWORD
- Check for additional_password in User_attributes in mysql.user
<> If value is present and match the password stored then you can proceed otherwise exit given there is an error in Dual Password or the passwords are different
- Run update on all application nodes, and verify new password on each application node
<> At regular interval check the number of completed changes and check the additional_password in User_attributes in mysql.user to be sure it is still there
[] When all application nodes are up to date
<> If verification is successful 100%
- Remove OLD password with: ALTER USER ... DISCARD OLD PASSWORD;
- Check for additional_password in User_attributes in mysql.user
<> If no value is present close with OK otherwise report Error for password not removed
() complete
Conclusion
As also Brian mentioned, those are the small things that could make the difference when in large deployments and enterprise environments. Security is a topic that very often is underestimated in small companies or start-ups, but that is wrong, security operations like password rotation are crucial for your safety.
It is nice to see that MySQL is finally adopting simple but effective steps to help DBAs to implement proper procedures without causing production impact and without the need to become too creative.
References
https://www.percona.com/blog/using-mysql-8-dual-passwords/
https://dev.mysql.com/doc/refman/8.0/en/password-management.html#dual-passwords
