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 warnings\G
*************************** 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. I filed a bug report to this effect: http://bugs.mysql.com/bug.php?id=68602.

I wrote a related blog post entitled "MySQL 5.6 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, 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 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("%s\n", 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.

About the Author

Kolbe Kegel is a Principal Support Engineer at SkySQL, where he has worked since 2011. Kolbe has worked with MySQL since 2005, first at MySQL, later at Sun Microsystems after its acquisition of MySQL Inc., then at Oracle after its acquisition of Sun.
adonnison

Security?

Just looking at the code for the config editor, I'm not sure I can see that there can be any serious claim to secure storage.

Beyond what you've already identified, there is the fact that the encryption key is stored raw in the header of the config file, followed by the encrypted data. It would be trivial to pull that out and run the decryption outside of the program.

Another thing that concerns me is that the program decrypts the data, stores it in a buffer and then operates on that buffer until completion, finally encrypting and writing it back into the file. In other words, while the program is running the data is in clear text within its allocated memory. This does provide an opportunity for finding that data in a memory dump. Albeit not a great danger, but if you are touting security, shouldn't you - I don't know - write secure code?

tw (not verified)

mariadb

how does mariadb improve password handling?

Sergei Golubchik (not verified)

There's not much one can do

There's not much one can do to improve password handling. The mysql client needs to store some bit of information somewhere that allows to identify a user to the server. So if a bad guy would have access to this bit of information - no matter how obfuscated or encrypted it is - he will be able to impersonate the user just as well by repeating whatever mysql client does.

But MariaDB fixes real security bugs related to password handling, even when MySQL does not. For example, CVE-2012-5627 - see http://blog.mariadb.org/unbreakable-mysql/ for details.

Henrik Ingo (not verified)

Postgres Ident authentication

Sergei

Actually, it is possible to have authentication that is not dependent on moving a password around at all. Postgres by default uses "Ident" authentication, which on localhost (unix sockets) uses the SO_PEERCRED authentication.

As convenient as it is, I personally don't actually like that mode so much. For example, I now need to be mindful of which unix user I'm logged in as, which can be a headache if you use different accounts for dev vs test vs production, or just have more than one developer. Also it is good to point out this isn't inherently more secure, if anything it can even be less secure: the attacker now needs to just get access to my unix account, and he has instant PostgreSQL access too.

But still, the benefit from this is that you don't need a password and therefore don't need to store a password anywhere either. For example for some job that needs to run from a cron job or puppet this is a huge win.

kolbe

MariaDB socket_peercred authentication plugin

Henrik,

Isn't that the same functionality included in the socket_peercred authentication plugin, which has been included in MariaDB since 5.2.0? https://kb.askmonty.org/en/socket_peercred-authentication-plugin/

MariaDB also includes the PAM authentication plugin: https://kb.askmonty.org/en/pam-authentication-plugin/

Some of this functionality is also included in some form in Oracle MySQL. Oracle MySQL's auth_socket plugin is distributed in source form, but it does not seem to be included as an available plugin in MySQL binary distributions (http://dev.mysql.com/doc/refman/5.6/en/socket-authentication-plugin.html), and Oracle MySQL's PAM plugin is distributed in the form of a "commercial extension" only available to paying customers (http://dev.mysql.com/doc/refman/5.6/en/pam-authentication-plugin.html).

Kolbe

kolbe

oops

Oops, it looks like auth_socket.so is there in Linux distributions of Oracle MySQL, but not Mac OS X.

ccharles (not verified)

because so_peercred doesn't

because so_peercred doesn't work on macosx ;) Not all socket implementations provide credential support.

kolbe

Todd Farmer discusses mysql_config_editor

In August of 2012, Todd Farmer gave a much better treatment of mysql_config_editor than I have here. I strongly suggest a look at his blog post for a better discussion of what mysql_config_editor might be used for: http://mysqlblog.fivefarmers.com/2012/08/16/understanding-mysql_config_editors-security-aspects/