March 18, 2013

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 doCHANGE 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 usemaster_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 TOin 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 whymaster_info_repository=TABLE offers essentially no increased security overmaster_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-textmaster.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 andPASSWORD 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 alsomysql_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 atclient/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'tskip 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 newmysql_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 Kolbe Kegel

Kolbe Kegel is the MariaDB Enterprise Tech Lead at MariaDB Corporation. 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.

Read all posts by Kolbe Kegel