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 [email protected] | +------------------------------------------------------------------------------+ | 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.
5 Comments. Leave new
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