Mysql – How to verify if given user@host with ‘password’ exists in MySQL 8.0+

authenticationMySQLmysql-8.0password

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

Best Answer

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()

Related Question