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

[8 Apr 2009 8:08] Oli Sennhauser
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'
[8 Apr 2009 8:34] Geert Vanderkelen
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?
[8 Apr 2009 8:47] Geert Vanderkelen
Bah, we got it already: sql_log_off
[5 May 2009 19:00] Tobias Asplund
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.
[20 May 2009 14:20] Geert Vanderkelen
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.
[23 Jun 2009 15:40] Morgan Tocker
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 :)
[9 Jun 2013 15:56] MySQL Verification Team
Oli, can we close this now ?

http://dev.mysql.com/doc/refman/5.6/en/password-logging.html
[11 Jun 2013 13:11] MySQL Verification Team
This FR / bug is fixed, so I close this record.
[18 Jun 2013 6:26] Tatjana Nuernberg
fixed in 5.6.3+