My Two Cents on MySQL Password Security

Posted in: MySQL, Technical Track

Lenz Grimmer recently wrote two blogs about password security on MySQL. Both are worth reading in detail. You’ll find them in Basic MySQL Security: Providing passwords on the command line and More on MySQL password security.

Although I wrote a comment on the latter one, there is one point I thought was worth its own blog.


You can work around having to specify the password in the open following these steps:

  1. Use a local or non-public instance (for example using MySQL Sandbox) to define the user / password combination you need:
    CREATE USER 'name'@'host' IDENTIFIED BY 'secret';
  2. Use SHOW GRANTS to get the hashed password:
    SHOW GRANTS FOR 'name'@'host';
    | Grants for [email protected]                                                         |
    | GRANT USAGE ON *.* TO 'name'@'host' IDENTIFIED BY PASSWORD '*<hash value>'   |
  3. Log in to the production (or public) server and use CREATE USER with the hash value obtained above:
    CREATE USER 'name'@'host' IDENTIFIED BY '*<hash value>'

This way, even if the CREATE USER ... (or GRANT) shows in plain text in the MySQL history, binary logs or anywhere else, the password remains secret.



Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

I have been working with databases for the last 20 years. Prior to Pythian worked for MySQL for 6 years.

5 Comments. Leave new

Tom Krouper
June 5, 2009 5:55 pm

Another option would be to just


The result will give you the hashed password. If you wanted to go crazy you could do something like…

SELECT CONCAT(“CREATE USER ‘name’@’host’ IDENTIFIED BY ‘”,password(‘xxxxx’),”‘;”);

And copy the whole line.


I think I usually use Tom’s method (ie PASSWORD() function), but it’s always good to know alternatives. Thanks for the info.


Ronald Bradford
June 8, 2009 12:00 pm

That’s a good tip.


PASSWORD() works, as long as you keep using it on the private server before re-using the hash value. I like SHOW GRANTS because it already shows you the whole GRANT command line which I can copy & paste … call my lazy.

Craig Sylvester
June 10, 2009 11:50 am

Hi Gerry,

Good tip. However, you need to include the “PASSWORD” keyword in step 3:




Leave a Reply

Your email address will not be published. Required fields are marked *