MySQL Bugs: #44143: Passwords are exposed in clear text in log files

Bug #44143	Passwords are exposed in clear text in log files
Submitted:	8 Apr 2009 8:08	Modified:	11 Jun 2013 13:11
Reporter:	Oli Sennhauser	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:	any	OS:	Any
Assigned to:	Tatjana Nuernberg	CPU Architecture:	Any

Description:
Customer complains that passwords are logged in clear text to the binary log and to the general query log (and to the slow query log and the relay log as well I assume).

How to repeat:
CREATE USER oli IDENTIFIED BY 'secret';
CHANGE MASTER to master_password='secret';

Suggested fix:
1. Log passwords as master_password =********** , identified by '**************'
2. transform the statement into something like:

IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7'

Maybe it would be good to have the general_log variable in the SESSION scope, so SUPER users can disable it for the session only. Just like we can disable logging to binary logs?

Bah, we got it already: sql_log_off

The general log may be tough since it's logged pre-parsing, but the binary log should be doable.

See the difference in the binary log between GRANT and SET PASSWORD:
(removing non-important stuff...)

mysql> GRANT USAGE ON *.* TO bugs@localhost IDENTIFIED BY 'asdf';
Query OK, 0 rows affected (0.23 sec)

mysql> SET PASSWORD FOR bugs@localhost = PASSWORD('asdf');
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT PASSWORD('asdf');
+-------------------------------------------+
| PASSWORD('asdf')                          |
+-------------------------------------------+
| *7F0C90A004C46C64A0EB9DDDCE5DE0DC437A635C | 
+-------------------------------------------+
1 row in set (0.08 sec)

mysql> GRANT USAGE ON *.* TO bugs@localhost IDENTIFIED BY '*7F0C90A004C46C64A0EB9DDDCE5DE0DC437A635C';
Query OK, 0 rows affected (0.00 sec)

Yields the following binary log commands.

GRANT USAGE ON *.* TO bugs@localhost IDENTIFIED BY 'asdf'
SET PASSWORD FOR 'bugs'@'localhost'='*7F0C90A004C46C64A0EB9DDDCE5DE0DC437A635C'
GRANT USAGE ON *.* TO bugs@localhost IDENTIFIED BY '*7F0C90A004C46C64A0EB9DDDCE5DE0DC437A635C'

GRANT works with the encrypted version of the password, so we could log it in this form.

The problem is the General Log, and as Tobias mentions, that's tricky because it's logged before parsing.

A solution would be:

mysql> SET SESSION sql_log_off = 1;
mysql> SET @newpass = PASSWORD('asdf');
mysql> SET SESSION sql_log_off  = 0;
mysql> CREATE USER geert IDENTIFIED BY @newpass;

.. but of course, the CREATE USER will fail because it only accept constant strings..

So no good workaround yet, except:

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 string 'Password setting hidden' will be logged, so the reader is notified something is done but not logged.

The only limitations with the workaround, is that you need the SUPER privilege to turn logging off for your session:

mysql> set sql_log_off=1;
ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation

So you can't use this trick to change your own password if you are an unprivileged user :)

Oli, can we close this now ?

http://dev.mysql.com/doc/refman/5.6/en/password-logging.html

This FR / bug is fixed, so I close this record.

fixed in 5.6.3+