--- title: "MySQL 5.6: Security through Complacency?" publish_date: 2013-03-18 author: "MariaDB" --- # MySQL 5.6: Security through Complacency? MySQL 5.6 introduces a number of new features designed to improve the security of MySQL. There’s the new `master_info_repository` variable that lets you store replication connection information in a table instead of a lowly text file, new warnings telling users that they should use SSL/TLS, there is a new option to give replication user & password with `START SLAVE` instead of `CHANGE MASTER`, and there’s `mysql_config_editor` to encrypt passwords. The problem with these features is that they are a form of Security through Complacency: these things make you feel more secure, but the realistic benefits disappear behind the curtains of Security Theater as soon as an even marginally-determined intruder comes along. In this post, I’ll look at some of the new security features in MySQL 5.6 and, however well-intentioned they may be, the danger of relying on these features. I fist noticed a couple new warnings issued when running `CHANGE MASTER TO` in the standard way: ``` mysql 5.6.10 (root) [test]> change master to master_host='x', master_port=1, master_user='a', master_password='b'; Query OK, 0 rows affected, 2 warnings (0.12 sec) mysql 5.6.10 (root) [test]> show warningsG *************************** 1. row *************************** Level: Note Code: 1759 Message: Sending passwords in plain text without SSL/TLS is extremely insecure. *************************** 2. row *************************** Level: Note Code: 1760 Message: Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives. 2 rows in set (0.00 sec) ``` The first one, the suggestion to use SSL/TLS, isn’t so bad. This is a reasonable idea, if your replication traffic is going across a public network. But if that’s the case, you ought to be using a VPN, not leaving it up to each service to fend for itself against an onslaught from nefarious agents across the entire Internet. Even on a private network, it might make sense to encrypt replication traffic, but there are enough other options and use cases here that having to suffer this obvious warning every time you do`CHANGE MASTER TO` across a VPN or on a private switch might be a little bit extreme. But really I’m more interested in the second Note. The second Note I am convinced is flawed in its premise, existence, and execution; it conveys a completely different message than it is meant to convey. That is, it appears to suggest that the user use`master_info_repository=TABLE` instead of `master_info_repository=FILE` (which is what cases the master.info file to be used). However, this message appears even when you *are* using `master_info_repository=TABLE`! I think the goal here is to discourage the inclusion of `MASTER_USER` and `MASTER_PASSWORD` in `CHANGE MASTER TO`in the first place, instead encouraging the user of the [new `USER` and `PASSWORD` options to `START SLAVE`](http://dev.mysql.com/doc/refman/5.6/en/start-slave.html). I filed a bug report to this effect: . I wrote a related blog post entitled “[MySQL 5.6 master info repository: documentation, behavior, and reality](internal:blogs/kolbe/mysql-56-master_info_repository-documentation-behavior-and-reality)” where I talk a bit more about why`master_info_repository=TABLE` offers essentially **no** increased security over`master_info_repository=FILE`. I won’t repeat that content here in full, but the gist is that **reading a password from a single-row InnoDB table that uses `innodb-file-per-table` is not any more difficult than reading a password from a plain-text`master.info` file**: ``` mysql 5.6.10-log (root) [test]> change master to master_host='127.0.0.1', master_port=4001, master_user='root', master_password='abf3$**92kjh1lffjmavn', master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.07 sec) ``` ``` kolbe datadir $ strings -a mysql/slave_master_info.ibd | tail -n 1 rootabf3$**92kjh1lffjmavn ``` However even with the alternative interpretation, the suggestion to rely on `USER` and`PASSWORD` options to `START SLAVE`, there are problems. Is the suggestion that an administrator should manually intervene every time a slave needs to be started? Where does the DBA store the username and password? Especially with GTID replication in MySQL 5.6, this is a suspicious recommendation. Automatic slave provisioning/recovery is obviously not going to work if a human has to go type `START SLAVE USER='x' PASSWORD='y'` each time a new slave is provisioned. So, **the username and password will be stored elsewhere in plaintext**. But the new security measures/options in MySQL 5.6 aren’t related only to replication. There’s also[`mysql_config_editor`](http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html), which “enables you to store authentication credentials in an encrypted login file named `.mylogin.cnf`“. ``` kolbe@prosimmon 5.6 $ mysql_config_editor set --login-path=local --host=localhost --user=user1 --password Enter password: kolbe@prosimmon 5.6 $ mysql_config_editor print --login-path=local [local] user = user1 password = ***** host = localhost ``` Encryption can be fun, but I find the details much more enlightening. Sure, the contents of the `.mylogin.cnf` file are encrypted, but what does that mean? Encryption has to be a *two-way street* for the encrypted data to be of any use. The MySQL client has to be able to read the password in plaintext in order to perform the hashing required to send the password to the server for authentication. If the `.mylogin.cnf` file contains all the information you need to log in to a remote MySQL instance, it has to contain all the information needed to decrypt the password. A quick look at[`client/mysql_config_editor.cc`](http://bazaar.launchpad.net/~mysql/mysql-server/5.6/view/4806/client/mysql_config_editor.cc#L865) in MySQL 5.6.10 source shows this function: ``` static void mask_password_and_print(char *buf) { DBUG_ENTER("mask_password_and_print"); const char *password_str= "npassword = ", *mask = "*****"; char *next= NULL; while ((next= strstr(buf, password_str)) != NULL) { while ( *buf != 0 && buf != next) putc( *(buf ++), stdout); printf("%s", password_str); printf("%sn", mask); if (*buf == 'n') /* Move past n' */ buf ++; /* Ignore the password. */ while( *buf && *(buf ++) != 'n') {} if ( !opt_all) break; } /* Now print the rest of the buffer. */ while ( *buf) putc( *(buf ++), stdout); // And a new line.. if required. if (* (buf - 1) != 'n') putc('n', stdout); DBUG_VOID_RETURN; } ``` Alright, so this is printing some asterisks and just skipping past the password in the decrypted message buffer. But obviously if we build a version of this tool that *doesn’t*skip over the buffer, we can happily print out the plain-text password to connect to the MySQL server. All we have to do is take out the first `while` loop, and we end up with this: ``` static void mask_password_and_print(char *buf) { DBUG_ENTER("mask_password_and_print"); const char *password_str= "npassword = ", *mask = "*****"; char *next= NULL; while ( *buf) putc( *(buf ++), stdout); // And a new line.. if required. if (* (buf - 1) != 'n') putc('n', stdout); DBUG_VOID_RETURN; } ``` I do `cd client; make mysql_config_editor` and a few seconds later I have a new`mysql_config_editor` binary: ``` kolbe@prosimmon client $ ./mysql_config_editor print --login-path=local [local] user = user1 password = MyP@ssw0rd host = localhost ``` The point is that **if someone can read this file, they have your MySQL password**. It might take them longer to copy/paste it than it would if they saw it in true plaintext in`~/.my.cnf`, but don’t be fooled: **`~/.mylogin.cnf` is not a more secure place to store your password than `~/.my.cnf`.** It takes an intruder absolutely no time to get a copy of your `~/.mylogin.cnf` file and decrypt it, if they have any interest at all in trying to do so. I do appreciate that Oracle is taking steps to make MySQL more secure. My concern is that users will see these new features, use them, and assume that they don’t need to take exactly the same security measures that have been necessary when using MySQL for the last 10 years. These new features, in my analysis, lead to a very dangerous **Security through Complacency**; this, to me, seems an irresponsible alternative to understanding your environment and using filesystem permissions, firewalls, VPNs, and other time-tested security measures.