Thursday, October 27, 2016

mysql old password, updating the password hash

Today I was trying to dump and restore mysql grants to a new Percona Server 5.6 system and I got an error about the password hash being in an unexpected format.

ERROR 1827 (HY000) at line 56: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.

 I logged in checked the password hash:
mysql> select user, host, password from mysql.user;

There was an application user which had a shorter password hash kind of like this:
6f8c114b58f2ce9e

I wanted to upgrade the password to the newer hash so I had to first get a hold of the application password. After retrieving the application password I did a compare of the two different hashing algorithms like this:

SELECT OLD_PASSWORD('mypass');
6f8c114b58f2ce9e

SELECT PASSWORD('mypass');
*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4

I was able to confirm that the old password hash matched what I saw when I ran the OLD_PASSWORD('xxx') function.

Then I changed the application password like this:

SET PASSWORD FOR 'my_app'@'%' = PASSWORD('mypass');

You could also drop and re-create the user with the new password hash.

No comments:

Post a Comment