Hiding MySQL passwords in the General Query Log
There are situations where the General Query Log has to be kept running. One problem here is that when creating MySQL users and setting a passwords, it will be logged in clear text. There is a bug/feature request handling this. Here is my workaround using MySQL 5.1 (and higher).
mysql> CREATE USER geert; mysql> SELECT 'Password setting hidden'; mysql> SET SESSION sql_log_off = 1; mysql> SET PASSWORD FOR geert = PASSWORD('asdf'); mysql> SET SESSION sql_log_off = 0;
The SELECT is just handy to put in a note in the log file so the reader knows what happens. The above statements produce the following lines in the General Query Log:
090623 15:24:24 3 Query CREATE USER geert090623 15:24:33 3 Query SELECT 'Password setting hidden'090623 15:24:41 3 Query SET SESSION sql_log_off = 1
Facebook
LinkedIn
Twitter
I left my comment on the bug report, but the problem with workaround this is that Geert needs SUPER ;)
I guess that's the closest thing to workable at the moment.
To hide from the general_log, you can do something like:
– Random number or string
SET @salt = RAND();
– Encrypt password using @salt (eg: AES in PHP)
SET PASSWORD FOR foo = PASSWORD(XXX_DECRYPT(crypt_str, @pass));
One could also XOR the string back and forth.. or use some other advanced method of exchange :-)
Another solution:
On your own laptop, do
SELECT PASSWORD('the_new_password');
Say you get '*123456…'
Now:
SET PASSWORD FOR foo = '*123456…';
Ugly – but not only keeps your password from general log, but also from history.