Network attacks on MySQL, Part 4: SSL hostnames

In my previous blogs I told you to enable SSL/TLS and configure it to check the CA. So I followed my advice and did all that. Great!

So the --ssl-mode setting was used a few times as a solution. And it has a setting we didn't use yet: VERIFY_IDENTITY. In older MySQL versions you can use --ssl-verify-server-cert. Both turn on hostname verification.

The attack

Get any certificate which is trusted by the configured CA, this can for example be a certificate from a development machine. And use that with a man-in-the-middle proxy.

Then the client:

1. Checks if SSL is uses (--ssl-mode=REQUIRED)
2. Verify if the certificate is signed by a trusted CA (--ssl-mode=VERIFY_CA)

Both checks succeed. But the certificate might be for testhost01.example.com and the database server might be prod-websitedb-123.example.com.

Browsers by default verify hostnames, MySQL does not.

Turning on hostname validation

So use --ssl-mode=VERIFY_IDENTITY and everything should be fine?

Well that might work for simple setups, but would probably fail for more complex setups.

This is because you might have a master-slave setup with loadbalancer in front of it. So your webapp connect to mydb-prod-lb.example.com which might be served by mydb1.example.com (master) or mydb2.example.com (slave). There might or might not be any automatic read/write splitting.

So then just configure the loadbalancer be the endpoint of the SSL connection? Well no, because most loadbalancers don't know how to speak the mysql protocol, which is needed to setup the SSL connection.

Ok, then just configure both servers with the certificate for mydb-prod-lb.example.com and everything should work. And it does!

But then you want to change the replication connection to also use SSL, but now the certificates and hostnames don't match anymore as they connect directly.

The same might be true for mysqldump or mysqlbinlog instances running on a separate backup server.

But there is a X.509 extension available which can be used: 'SubjectAlternativeName' a.k.a. SAN. (Not to be confused with Storage Area Networking). This allows you to have a certificate with multiple hostnames.

So for both hosts put their own hostname and the loadbalancer hostname in there.

But unfortunately that doesn't work yet. MySQL doesn't support this.

See Bug #68052: SSL Certificate Subject ALT Names with IPs not respected with --ssl-verify-serve for more details.

So yes, do enable hostname verification, but probably not everywhere yet.

Network attacks on MySQL, Part 3: What do you trust?

In my previous blogs I told you to enable SSL/TLS and force the connection to be secured. So I followed my advice and did forced SSL. Great!

So now everything is 100% secure isn't it?

No it isn't and I would never claim anything to be 100% secure.

There are important differences in the SSL/TLS implementations of browers and the implementation in MySQL. One of these differences is that your browser has a trust store with a large set of trusted certificate authorities. If the website you visit has SSL enabled then your browser will check if the certificate it presents is signed by a trusted CA. MySQL doesn't use a list of trusted CA's, and this makes sense for many setups.

The key difference is that a website has clients (browsers) which are not managed by the same organization. And for MySQL connections the set of clients is often much smaller are more or less managed by one organization. Adding a CA for a set of MySQL connections if ok, adding a CA for groups of websites is not.

The result is that a self signed certificate or a certificate which is signed by an internal CA is ok. An public CA also won't issue a certificate for internal hostnames, so if your server has an internal hostname this isn't even an option. Note that the organization running public CA's sometimes offer a service where they manage your internal CA, but then your CA is not signed by the public CA.

But if you don't tell your MySQL client or application which CA's it should trust it will trust all certifictes. This allows an attacker to use a man-in-the-middle proxy which terminates the SSL connection between your client and the proxy and setup another connection to the server, which may or may not be useing SSL.

To protect against this attack:

1. Use the --ssl-ca option for the client to specify the CA certificate.
2. Use the --ssl-mode=VERIFY_CA option for the client.

You could use a CA for each server or a CA you use for all MySQL servers in your organization. If you use multiple CA's then you should bundle them in one file or use --ssl-capath instead.

Network attacks on MySQL, Part 2: SSL stripping with MySQL

Intro

In my previous blog post I told you to use SSL/TLS to secure your MySQL network connections. So I followed my advice and did enable SSL. Great!

So first let's quickly verify that everything is working.

So you enabled SSL with mysql_ssl_rsa_setup, used a OpenSSL based build or put ssl-cert, ssl-key and ssl-ca in the mysqld section of your /etc/my.cnf and now show global variables like 'have_SSL'; returns 'YES'.

And you have configured the client with --ssl-mode=PREFERRED. Now show global status like 'Ssl_cipher'; indicates the session is indeed secured.

You could also dump traffic and it looks 'encrypted' (i.e. not readable)...

With SSL enabled everything should be safe isn't it?

The handshake which MySQL uses always starts unsecured and is upgraded to secured if both the client and server have the SSL flag set. This is very similar to STARTTLS as used in the SMTP protocol.

To attach this we need an active attack; we need to actually sit in between the client and the server and modify packets.

Then we modify the flags sent from the server to the client to have the SSL flag disabled. This is called SSL stripping.

Because the client thinks the server doesn't support SSL the connection is not upgraded and continues in clear text.

An example can be found in the dolfijn_stripssl.py script.

Once the SSL layer is stripped from the connection an attacker can see your queries and resultsets again as described before.

To protect against this attack:

1. Set REQUIRE SSL on accounts which should never use unencrypted connections.
2. On the client use --ssl-mode=REQUIRED to force the use of SSL. This is available since 5.6.30 / 5.7 11.
3. For older clients: Check the Ssl_cipher status variable and exit if it is empty.

Network attacks on MySQL, Part 1: Unencrypted connections

Intro

In a set of blog posts I will explain to you how different attacks on the network traffic of MySQL look like and what you can do to secure your systems againt these kinds of attacks.

How to gain access

To gain access to MySQL network traffic you can use tcpdump, dumpcap, snoop or whatever the tool to capture network packets on your OS is. This can be on any device which is part of the connnection: the server, the client, routers, switches, etc.

Besides application-to-database traffic this attack can also be done on replication traffic.

Results

This allows you to extract queries and result sets.

The default password hash type mysql_new_password uses a nonce to protect against password sniffing. But when you change a password this will be sent accross the wire by default. Note that MySQL 5.6 and newer has some protection which ensures passwords are not sent to the logfiles, but this feature won't secure your network traffic.

In the replication stream however there are not as many places where passwords are exposed. This is true especially for row based replication, but even for statement based replication this can be true.

Some examples:

SET PASSWORD FOR 'myuser'@'%' = PASSWORD('foo'); -- deprecated syntax
UPDATE secrets SET secret_value = AES_ENCRYPT('foo', 'secret') WHERE id=5;

For both the password and the encryption key this can be seen in plain text for application-to-server traffic, but not for RBR replication traffic.

There is a trick to make this somewhat more secure, especially on 5.5 and older:

SELECT PASSWORD('foo') INTO @pwd;
SET PASSWORD FOR 'myuser'@'%' = @a;

If your application stores passwords in MySQL: You're doing it wrong. If your application stores hashed passwords (w/ salt, etc): If the hashing is done in your application: this is ok. But note that a man-in-the-middle might send a slightly altered resultset to your application and with this gain access to your application, but that requires an active attack.

This attacks for this level are mostly passive, which makes it hard to detect. An attacker might snif password hashes for your appliation and brute force them and then login to your application. The only thing you will see in your logs is a successful login...

To protect against this attack:

1. Use SSL/TLS
2. Encrypt/Decrypt values in the application before inserting it in the database.
3. Use a SSH tunnel (Workbench has built-in support for this)
4. Use a local TCP or UNIX domain socket when changing passwords.[1]
5. Don't use the MySQL protocol over the internet w/o encryption. Use a VPN or SSH.

For sensitive data you preferably should combine 1. and 2. Item 3. and 4. are mostly for ad-hoc DBA access.

Keep in mind that there might be some cron jobs, backups etc. which also need to use a secure connection. Ofcourse you should also protect your data files and backup files, but that's not what this post is about.

[1] It is possible to snoop on UNIX domain socket traffic, but an attacker who has that access probably has full system access and might more easily use an active attack.

Improving MySQL out of disk space behaviour

Running out of disk space is something which, of course, should never happen as we all setup monitoring and alerting and only run well behaved applications. But when it does happen we want things to fail gracefully.

So what happens when mysqld runs out of disk space?
The answer is: It depends

1. It might start to wait until disk space becomes available.
2. It might crash intentionally after a 'long semaphore wait'
3. It might return an error to the client (e.g. 'table full')
4. It might skip writing to the binlog (see binlog_error_action )

What actually happens might depend on the filesystem and OS.

Fixing the disk space issue can be done by adding more space or cleaning up some space. The later can often be done without help of the administrator of the system.

So I wanted to change the behaviour so that it MySQL wouldn't crash or stop to respond to read queries. And to also make it possible for a user of the system to cleanup data to get back to a normal state.

So I wrote a audit plugin which does this:

1. The DBA sets the maxdiskusage_minfree variable to a threshold for the minimum amount of MB free.
2. If the amount of free disk space goes under this threshold:
1. Allow everything for users with the SUPER privilege
2. Allow SELECT and DELETE
3. Disallow INSERT
3. If the amount of free space goes back to normal: Allow everything again

This works, but only if you delete data and then run optimize table to actually make the free space available for the OS.

Note that DELETE can actually increase disk usage because of binlogs, undo, etc.

The code is available on github: https://github.com/dveeden/mysql_maxdiskusage