My Two Cents on MySQL Password Security

Jun 5, 2009 / By Gerry Narvaja

Tags:

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.

GRANT … IDENTIFIED BY PASSWORD…

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 name@host                                                         |
    +------------------------------------------------------------------------------+
    | 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.

5 Responses to “My Two Cents on MySQL Password Security”

  • Tom Krouper says:

    Another option would be to just

    SELECT PASSWORD(‘xxxxxx’);

    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.

  • Mac says:

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

    -Mac

  • That’s a good tip.

  • Gerry says:

    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 says:

    Hi Gerry,

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

    CREATE USER ‘name’@'host’ IDENTIFIED BY PASSWORD ‘*’

    /Craig

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>