Newbie password mistake

I received a panic call from a newbie MySQL DBA. Or should I say the ‘Linux Admin’/’MySQl DBA’/’CSS guru’/’PHP Programmer’/’Network Admin’/’Backup Operator’/’CIO’ of a small business. He had reset his password was was now locked out. Luckily, he had only changed his password and still had root access.

What he did:
mysql>use mysql;
SET PASSWORD for 'mrdoesall'@'10.%' to 'bigsecret';

Long time MySQL DBAs should be groaning at this with a wince remembering when it happened to them. For those of you who did not catch the problem, what happened is that he value in the user.password table is set to the string ‘bigsecret’. When our friend tries to login, the password is encrypted and compared to the value in user.Password. The comparison of the encrypted value does not equate to the unencrypted value and the login fails.

What he meant to do:
mysql>use mysql;
SET PASSWORD for 'mrdoesall'@'10.%' = PASSWORD('bigsecret');

So with the help of the root account, all was resolved. The CLI interface can let you step on your own feet which is one of the reasons I recommend MySQL Workbench to novices and non full-time DBAs.


One Response to Newbie password mistake

  1. Maybe SET PASSWORD should work in a more obvious way (e.g. use the PASSWORD() function on the input if it’s not a hash. Or at least give a warning if the generated or supplied string doesn’t start with ‘*’ (might break for SHA256 passwords).

    It would have been more friendly if storing the hash would not be the default action:

    SET PASSWORD = ‘vewwy_secret’;
    SET PASSWORD HASH = ‘160402238111E4F40672D275E63B3345A6FDAF85’;

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s