My DBA told me that an account I use to talk to my MySQL database instance has TWO passwords! How does that happen? Do I have to provide both passwords every time?
Sincerely,
A Confused User Who Does Not Want to Type Two Passwords All The Time
Dear Confused User,
Dual Password Support was added in MySQL 8.0.14 and it is a very handy thing. User accounts can now have a primary password and a secondary password. Image a scenario where you are rotating passwords as directed by your company policy but it takes a while to change the credentials in all your code despite your best EMACS magic. The ability to keep the servers up and active as your do your security due diligence is very helpful.
And you do not need to type both password! Just one and hopefully the newer one.
How Do You Establish A Secondary Password?
Very simple.
ALTER USER 'dave'@'deardave.xyz' IDENTIFIED BY 'deardave2' RETAIN CURRENT PASSWORD;
In the mysql.user table there is now a JSON column named User_attributes that now has the secondary password:
{"additional_password": "$A$005$;H7\u001b\u001b\u0006<`qFRU\tNRxT Z\u0003Ya/iej8Az8LoXGTv.dtf9K3RdJuaLFtXZHBs3/DntG2"}
And How Do I Get Rid Of A Secondary Password
So you are done with updating the password and want to remove the old password? Simply use another ALTER TABLE.
ALTER USER 'dave'@'deardave'xyz' DISCARD OLD PASSWORD
Things To Note
RETAIN CURRENT PASSWORD keeps the old password in the additional_password key/value pair. The new password becomes the primary. And if the new password is empty both the primary and secondary passwords will be empty.
If you try to add a secondary password to an account without a password it will fail.
If the account has a secondary password by you change the primary password
