1

I write sanity check script for an application. One of the tests is supposed to verify that a given user@host with 'password' exists in MySQL. I'm using simple native hash passwords. In MySQL5.7- there is the PASSWORD() function that allows me to query mysql.user and verify that the user exists and the passwords match. Is there a way to accomplish this in MySQL8.0+? Documentation page for PASSWORD() in MySQL 5.7 does not provide any alternatives and the function is removed in MySQL 8.0.

I could simply try to add the user again, but I feel my script should know and register an event of missing user with expected credentials.

Please note that I do not necessarily need to generate password hash, I just want to verify that given user/password exists. This needs to be automated so solutions like 'try to log in with user/password from host machine' do not count as I may not have access to the host machine

  • Look in mysql.user. – Rick James Nov 2 '18 at 19:06
2

Actually, there is an alternative that MySQL (really Oracle) does not mention.

  • Back on Apr 22, 2015, I posted the Algorithm for the Password Function
  • MySQL 8.0 will still store the PASSWORD function output in the authentication_string column if you create the user using the mysql_native_password plugin.

Here is the algorithm

SET @plaintextpassword = 'whatever password you want';
SELECT CONCAT('*',UPPER(SHA1(UNHEX(SHA1(@plaintextpassword)))));

GIVING CREDIT

I first learned this from a blog by a company called PalominoDB. They changed their name to blackbird. Pythian purchased blackbird. Pythian now has it as a blog post with this algorithm : Hashing Algorithm in MySQL PASSWORD()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.