![\"MySQL](\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/MySQL_DB_Lock2.png\")
<\/figure><\/div>\n<\/div><\/div>\n\n\n\n\n\n\n\n
TL;DR<\/h3>\n\n\n\n
Dual-password capability makes it possible to seamlessly perform credential changes without downtime.<\/p>\n\n\n\n
\n\n\n\n\n\n
MySQL implements dual-password<\/strong> capability with syntax that saves and discards secondary passwords :<\/p>\n\n\n\n The purpose is to avoid downtime<\/span> while changing passwords in a replicated environment.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n\n\n The workflow is :<\/p>\n\n\n\n Let\u2019s take a quick look using MySQL 8.0<\/p>\n\n\n\n Create a user account myApp@localhost<\/em> with password pwd1<\/em> :<\/p>\n\n\n\n Now we can connect with the name and the password :<\/p>\n\n\n\n Note<\/span>: Now the DBA (super user) use ALTER USER<\/em><\/strong> statement with the RETAIN CURRENT PASSWORD<\/em><\/strong> clause to perform credential changes using the dual password mechanism by adding as primary<\/span> password pwd2<\/em>. We can use the user name and the new password (pwd2<\/em>) to connect :<\/p>\n\n\n\n But the old password (pwd1<\/em>) is still valid :<\/p>\n\n\n\n Now it is the time to discard the secondary password (pwd1<\/em>) :<\/p>\n\n\n\n As you can see, only the new password (pwd2<\/em>) is valid.<\/p>\n\n\n\n\n\n\n\n Olivier’s MySQL Channel<\/span><\/a><\/strong><\/p>\n\n\n\n Reference Manual<\/span><\/strong><\/p>\n\n\n\n MySQL Security<\/span><\/strong><\/p>\n\n\n\n MySQL Security Serie – Password Management<\/span><\/strong><\/p>\n\n\n\n MySQL Security Serie<\/span> (1st edition)<\/strong><\/p>\n\n\n\n
e.g.
ALTER USER<\/strong> ‘myApp’@’host’ IDENTIFIED BY ‘NEW_password’ RETAIN CURRENT PASSWORD<\/strong>;
<\/li>
<\/li>
<\/li>
e.g.
ALTER USER<\/strong> ‘myApp’@’host’ DISCARD OLD PASSWORD<\/strong>;<\/li><\/ol>\n\n\n\n\n\nMySQL SQL> SELECT VERSION();\n+-----------+\n| VERSION() |\n+-----------+\n| 8.0.19 |\n+-----------+<\/code><\/pre>\n\n\n\n\n\nMySQL root SQL> \nCREATE USER myApp@localhost IDENTIFIED BY 'pwd1';<\/code><\/pre>\n\n\n\n$ mysql -u myApp -ppwd1 -e\"SELECT USER()\"\nmysql: [Warning] Using a password on the command line interface can be insecure.\n+-----------------+\n| USER() |\n+-----------------+\n| myApp@localhost |\n+-----------------+<\/code><\/pre>\n\n\n\n
As indicated in the output, it is a very bad practice<\/span><\/strong> to put the password on the command line interface.<\/p><\/blockquote>\n\n\n\n\n\n
Thus pwd1<\/em> is now the secondary<\/span> password :<\/p>\n\n\n\nMySQL root SQL> \nALTER USER myApp@localhost IDENTIFIED BY 'pwd2' RETAIN CURRENT PASSWORD;<\/code><\/pre>\n\n\n\n$ mysql -u myApp -ppwd2 -e\"SELECT USER()\"\nmysql: [Warning] Using a password on the command line interface can be insecure.\n+-----------------+\n| USER() |\n+-----------------+\n| myApp@localhost |\n+-----------------+<\/code><\/pre>\n\n\n\n$ mysql -u myApp -ppwd1 -e\"SELECT USER()\"\nmysql: [Warning] Using a password on the command line interface can be insecure.\n+-----------------+\n| USER() |\n+-----------------+\n| myApp@localhost |\n+-----------------+<\/code><\/pre>\n\n\n\n\n\nMySQL root SQL> \nALTER USER myApp@localhost DISCARD OLD PASSWORD;<\/code><\/pre>\n\n\n\n$ mysql -u myApp -ppwd2 -e\"SELECT USER()\"\nmysql: [Warning] Using a password on the command line interface can be insecure.\n+-----------------+\n| USER() |\n+-----------------+\n| myApp@localhost |\n+-----------------+\n\n\n$ mysql -u myApp -ppwd1 -e\"SELECT USER()\"\nmysql: [Warning] Using a password on the command line interface can be insecure.\nERROR 1045 (28000): Access denied for user 'myApp'@'localhost' (using password: YES)<\/code><\/pre>\n\n\n\nTo Go Further<\/h2>\n\n\n\n