How MySQL 5.6 handles passwords more securely

There are many thing changed in MySQL 5.6 which are related to passwords:

• There is a new password hash algorithm (SHA-256)
• There is obfuscation for passwords with the .mylogin.cnf file.
• The option to store slave passwords in a database table.
• It's now possible to supply a password to START SLAVE.

But that's not what this blog post is about.

This blog post is a great new feature: Hiding passwords from your log files, automatically.

MySQL 5.6 will by default hide passwords from the general log. This is not just obfuscation as only the one-way hash will be put in the log files. By setting log-raw=OFF you can disable password hiding for the general log. The log-raw setting will only influence the general log, so the passwords in the slow query log and the binary logs will still be hidden.

With MySQL 5.5 this could be done manually by first storing the hash in a variable, but like many other work-arounds this is not needed anymore.

But it doesn't stop there. According to the manual the mysql client now won't log statements which match "*IDENTIFIED*:*PASSWORD*".

But don't forget that MySQL Server logfiles and MySQL Client logfiles should still be protected with the correct file permissions and the same is true for all other files like master.info. And if you store slave credentials inside the database these should be protected by using the MySQL privileges system, but that's even true if master.info is used as someone with too many database privileges might just load the contents with LOAD DATA INFILE.

Of course there there is still room for improvement:
It would be nice if we could easily enable connection security. MySQL and many other applications use TLS, but that has some performance overhead and isn't really easy to setup. And MySQL is still not secure-by-default, but easy-by-default...

So MySQL 5.6 improves security without needing any special configuration.

Avoid clear text passwords in MySQL logging.

What happens when you use the PASSWORD() function to insert a password hash into a table?

• The hash will be written to the table
• The password might be written in clear text to the binlog
• The password might be written in clear text to the general log
• The password might be written in clear text to the slow query log

The query

mysql [localhost] {msandbox} (test) > INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'));
Query OK, 1 row affected (0.00 sec)

The General log

130128 16:04:41     1 Query     INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'))

The Slow query log

# Time: 130128 16:04:41
# User@Host: msandbox[msandbox] @ localhost []
# Query_time: 0.004887  Lock_time: 0.001043 Rows_sent: 0  Rows_examined: 0
SET timestamp=1359385481;
INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'));

The binlog:

# at 219
#130128 16:04:41 server id 1  end_log_pos 287  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1359385481/*!*/;
BEGIN
/*!*/;
# at 287
#130128 16:04:41 server id 1  end_log_pos 315  Intvar
SET INSERT_ID=1/*!*/;
# at 315
#130128 16:04:41 server id 1  end_log_pos 438  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1359385481/*!*/;
INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'))
/*!*/;
# at 438
#130128 16:04:41 server id 1  end_log_pos 465  Xid = 8
COMMIT/*!*/;

It's possible to do it in a more secure way by using variables.

The query

mysql [localhost] {msandbox} (test) > SET @pwd := PASSWORD('another_secret_password');
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO testpwd(pwd) VALUES(@pwd);
Query OK, 1 row affected (0.01 sec)

The General log

130128 16:05:18     1 Query     SET @pwd := PASSWORD('another_secret_password')
130128 16:05:30     1 Query     INSERT INTO testpwd(pwd) VALUES(@pwd)

The Slow query log

# Time: 130128 16:05:18
# User@Host: msandbox[msandbox] @ localhost []
# Query_time: 0.000251  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1359385518;
SET @pwd := PASSWORD('another_secret_password');
# Time: 130128 16:05:30
# User@Host: msandbox[msandbox] @ localhost []
# Query_time: 0.003031  Lock_time: 0.000288 Rows_sent: 0  Rows_examined: 0
SET timestamp=1359385530;
INSERT INTO testpwd(pwd) VALUES(@pwd);

The binlog

# at 465
#130128 16:05:30 server id 1  end_log_pos 533  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1359385530/*!*/;
BEGIN
/*!*/;
# at 533
#130128 16:05:30 server id 1  end_log_pos 561  Intvar
SET INSERT_ID=2/*!*/;
# at 561
#130128 16:05:30 server id 1  end_log_pos 638  User_var
SET @`pwd`:=_utf8 0x2A41424645453834453346463233423442454338323635383832433244383141414536363744363235 COLLATE `utf8_general_ci`/*!*/;
# at 638
#130128 16:05:30 server id 1  end_log_pos 738  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1359385530/*!*/;
INSERT INTO testpwd(pwd) VALUES(@pwd)
/*!*/;
# at 738
#130128 16:05:30 server id 1  end_log_pos 765  Xid = 10
COMMIT/*!*/;

An other trick is to set binlog_format to ROW:

# at 555
#130128 16:30:47 server id 1  end_log_pos 623   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1359387047/*!*/;
BEGIN
/*!*/;
# at 623
# at 672
#130128 16:30:47 server id 1  end_log_pos 672   Table_map: `test`.`testpwd` mapped to number 33
#130128 16:30:47 server id 1  end_log_pos 752   Write_rows: table id 33 flags: STMT_END_F

BINLOG '
p5kGURMBAAAAMQAAAKACAAAAACEAAAAAAAEABHRlc3QAB3Rlc3Rwd2QAAggPAv8AAg==
p5kGURcBAAAAUAAAAPACAAAAACEAAAAAAAEAAv/8BQAAAAAAAAApKkNFNDdGODRBOThDOTI0ODdC
RjI5MUM1QzIyNTY3ODg0RjAxMjdGM0U=
'/*!*/;
# at 752
#130128 16:30:47 server id 1  end_log_pos 779   Xid = 19
COMMIT/*!*/;

And even with the "-v" option for mysqlbinlog the passwords won't show with row based binlogs:

### INSERT INTO `test`.`testpwd`
### SET
###   @1=5
###   @2='*CE47F84A98C92487BF291C5C22567884F0127F3E'

With 5.6 hiding passwords should get done automatically and for all the log types. Unfortunately I didn't get it to work yet. (Bug #68200)

To prevent logging of passwords you need to

• Disable the slow query log
• Disable the general log
• Use variables or disable the binlog
• Or wait on the 5.6 GA version