In my linux.conf.au 2013 talk, I had a big slide telling the audience how to do a simple Denial of Service attack against a MySQL server (post login). This was only one example of many others I could give, but I think it’s the simplest, and only requires the mysql command line tool and a single command. FYI, this also applies to PostgreSQL but I’ll leave the specifics up to somebody else to write.
There is a fundamental flaw in just about all MVCC databases that leaves a giant Denial of Service attack hole. It is the following: START TRANSACTION WITH CONSISTENT SNAPSHOT followed by a bunch of waiting. Sine the database server has to maintain this read view, InnoDB will continue to grow UNDO until it has to extend the ibdata1 file (system table space).
It’s important to remember that you cannot shrink the system table space (unlike with file-per-table where you can just do ALTER TABLE for any individual table suddenly finding itself a lot smaller).
As UNDO grows, InnoDB will faithfully expand the system table space until ENOSPC and then everything will fall in a heap.
In theory, you could have a system table space that doesn’t auto-extend, but then you’re relying on code paths to error out gracefully that I can pretty much bet you are completely untested.
The only real way to avoid this is doing both of the following:
- Use kill-idle-transactions feature from Percona Server
- have a script that checks for long running transactions and just kills them.
Similar things affect just about any MVCC database system. You’ll also see similar things with file system and volume manager snapshots.
So is it highly irresponsible pointing this out? Of course it isn’t, this should be pretty well known to most DBAs already and so should a whole bunch of other things. Remember all the things you saw in production and then went to hit your developers over the head for? Well, they’re all in this same category.
Go run giant UPDATEs, DELETEs or ALTER TABLE on a giant table in a replication setup, you’ll pretty much DoS your app as everything can’t get up to date read-only information from slaves.
Considering that this is merely scratching the top of the iceberg of ways to DoS a database server, keeping post authentication crashing bugs secret just seems… well… futile, even if you do accept security through obscurity as valid.
Further reading:
Andrew Hutchings liked this on Facebook.
Antony T Curtis liked this on Facebook.
In Oracle RDBMS UNDO tablespace is set explicitly and can be limited in size (not at the OS level, just with SQL). This solves the problem once and forever. Unfortunately even in MySQL 5.6 I see no way to limit growth of UNDO even if it is stored outside of ibdata*.
What happens when you hit UNDO limit in Oracle RDBMS? Does it kill oldest txn or just not allow new ones to start? If it doesn’t allow new ones then it’s as good as DoS
UNDO is used in a circular way and eventually the one that want to see that ages old snapshot will get error “ORA-01555: snapshot too old” and will be rolled back. In Oracle writers have preference over readers who need ages old data.
To summarize, yes, oldest is killed.
Valerii Kravchuk liked this on Facebook.
On top of that smart Oracle DBA can limit resources for user using resource profile. While UNDO space “used” is not among them, one can just limit connection time, idle time or even reads per session. See http://docs.oracle.com/cd/B10500_01/server.920/a96540/statements_611a.htm#2065932. So, DoS with properly managed Oracle RDBMS is not that easy to do using just SQL.
My MySQL Security talk is full of ways to DoS a server.
Pingback: MySQL 5.6 innodb_undo_tablespaces very usefull only if … « Serge Frezefond 's blog
On a somewhat related node ApacheMQ has (or at least had a while ago) a ‘clever’ trick to ensure that it was running only once by doing something like:
BEGIN;
SELECT * FROM t_test WHERE id = ‘1’ FOR UPDATE ;
repeat
UPDATE t_test SET name=’3′ WHERE id=’1′;
sleep 1
until forever
We stumbled across this as a customer was using ApacheMQ with MySQL Cluster where this lead to the discovery of “ndbd can’t start while old transactions are kept open” / http://bugs.mysql.com/65037 ) but this would have ‘funny’ effects on other storage engines (or even other DBMS products, too, then?