My Two Cents on MySQL Password Security
Jun 5, 2009 / By Gerry Narvaja
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:
- 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';
- Use
SHOW GRANTSto get the hashed password:SHOW GRANTS FOR 'name'@'host'; +------------------------------------------------------------------------------+ | Grants for name@host | +------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'name'@'host' IDENTIFIED BY PASSWORD '*<hash value>' | +------------------------------------------------------------------------------+
- Log in to the production (or public) server and use
CREATE USERwith 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.
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.
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.
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.
Hi Gerry,
Good tip. However, you need to include the “PASSWORD” keyword in step 3:
CREATE USER ‘name’@\'host’ IDENTIFIED BY PASSWORD ‘*’
/Craig