To only slightly misquote one of the greatest movies of all times:
You keep using that option. I do not think it means what you think it means.
Perhaps like many users, I had certain assumptions about what max_connect_errors really does – but in looking closely as part of investigating the new PERFORMANCE_SCHEMA.HOST_CACHE table in MySQL 5.6, I learned that some very fundamental elements had escaped my notice. I’m writing this blog post to help others who hold similar misconceptions of what this option does.
Many, if not most, MySQL DBAs are familiar with “host blocked” errors:
C:\mysql-5.5.27-winx64>bin\mysql -utest_mce -P3307 -h192.168.2.8 ERROR 1129 (HY000): Host 'Crowder' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
The solution to this problem is readily apparent from the error message – some DBAs might not even bother to glance at the documentation regarding this. Even those who do might miss the nuanced explanation of the root cause:
The value of the max_connect_errors system variable determines how many successive interrupted connection requests are permitted.
The use of “interrupted” is surely intentional here, and it’s key to understanding the first point I’ll make:
1. It provides no meaningful protection against brute force access attacks
Truly. You can set max_connect_errors to any value you please, and it will have exactly zero impact on somebody trying to brute force their way into your system by guessing user names and passwords. It will lock out a host if somebody does a dumb port scan 100 times successively without trying to log in, but who scans a port 100 times? The useful information from a port scan is divulged in the initial scan:
- MySQL is running on the specified port.
- The version of MySQL is included in the handshake.
- There are (or aren’t) accounts configured to allow access from the client machine, based on error code.
- The default authentication mechanism preferred by the server.
What’s the use of scanning it an additional 99 times when you already have all the information you are going to get?
2. Authentication failures reset the counter
Strange, but true. Not only do authentication failures not increment the host counter, they actually reset it to zero – along with all other errors other than handshake interruptions. The only thing that matters is whether the handshake was interrupted or not. If it wasn’t interrupted, it counts as “success” and reset the host counter – regardless of whether the end result was a successful connection or not. So, if you want to run a dumb port scanner more than 100 times, just make sure you intersperse an actual connection attempt every 99 cycles or so to rest the counter. Here’s my testing of MySQL 5.5 behavior:
mysql> select @@global.max_connect_errors; +-----------------------------+ | @@global.max_connect_errors | +-----------------------------+ | 1 | +-----------------------------+ 1 row in set (0.00 sec) mysql> exit Bye D:\mysql-5.5.28-win32>bin\mysql -uhct -P3308 -h10.159.156.50 -ptest ERROR 1129 (HY000): Host 'TFARMER-MYSQL.wh.oracle.com' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' D:\mysql-5.5.28-win32>bin\mysqladmin -uroot -P3308 flush-hosts D:\mysql-5.5.28-win32>start telnet 10.159.156.50 3308 D:\mysql-5.5.28-win32>bin\mysql -uhct -P3308 -h10.159.156.50 -ptest-bad ERROR 1045 (28000): Access denied for user 'hct'@'TFARMER-MYSQL.wh.oracle.com' (using password: YES) D:\mysql-5.5.28-win32>start telnet 10.159.156.50 3308 D:\mysql-5.5.28-win32>bin\mysql -uhct -P3308 -h10.159.156.50 -ptest Welcome to the MySQL monitor. Commands end with ; or \g. ... mysql> exit Bye D:\mysql-5.5.28-win32>bin\mysqladmin -uroot -P3308 flush-hosts D:\mysql-5.5.28-win32>start telnet 10.159.156.50 3308 D:\mysql-5.5.28-win32>start telnet 10.159.156.50 3308 D:\mysql-5.5.28-win32>bin\mysql -uhct -P3308 -h10.159.156.50 -ptest ERROR 1129 (HY000): Host 'TFARMER-MYSQL.wh.oracle.com' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
3. All bets are off if you use –skip-name-resolve
Because this is all managed in the host cache, if you turn off reverse DNS lookups using –skip-name-resolve – and many people will to avoid potential DNS overhead in creation of new connections – max_connect_errors has zero effect.
4. Localhost and IP loopbacks are excluded
For the same reason as #3, you’ll never see host blocked errors when connecting to localhost or via IP loopback interface. These don’t go through the DNS reverse lookup and thus the host cache, and are therefore not tracked at all. Whether that’s good (nobody can lock up local access) or not, I’ll let you decide.
5. The host cache is a fixed size
Marc Alff pointed out to me that the fixed size of the host cache – along with the LRU purge algorithm used – makes it quite possible that blocked hosts can fall out of the cache and cease to be blocked. That has pretty obvious implications for how it can be bypassed by any third party needing to do so.
Conclusion
If you are looking for a mechanism to limit exposure to brute-force attempts to access MySQL, max_connect_errors won’t help you. If you’re worried about a SYN flood attack, max_connect_errors might help you in very specific situations. PERFORMANCE_SCHEMA improvements in MySQL 5.6 expose meaningful information about potential brute-force attacks, but again – only in situations where the host cache is involved. Beyond that, the contents of MySQL Enterprise Audit log or general query log can be mined to identify such attacks. I filed several feature requests to give even more visibility through PERFORMANCE_SCHEMA and to provide a mechanism to restrict access from hosts based on number of failed authorization attempts.
I just recommend setting max_connect_errors to 2^64-1 (the max value) and pretend it doesn’t exist.
Except in very limited circumstances, I also always recommend turning on –skip-name-resolve which bypasses the problem anyway, but I figure setting max_connect_errors to a large value still won’t hurt anything in that case.
Agree with Justin — my approach as well.
“Authentication failures reset the counter” — not on my machine the don’t.
On my 5.5 server, a failed login (wrong user+password) increment the “Aborted_connects” coutner:
mysql: show global status like '%abort%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Aborted_clients | 0 |
| Aborted_connects | 3 |
+------------------+-------+
bash$ mysql -ufoo -pbar
ERROR 1045 (28000): Access denied for user 'foo'@'localhost' (using password: YES)
mysql: show global status like '%abort%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Aborted_clients | 0 |
| Aborted_connects | 4 |
+------------------+-------+
Shlomi
Hi Shlomi,
When I said the counter is reset, I was talking about the host cache counter which tracks successive handshake errors (those which count towards max_connect_errors). The global Aborted_connects counter is something else entirely and doesn’t directly influence whether a given host is blocked or not.
Todd
Thanks for the clarification; indeed the internal counters are a mystery.
Hi, your points #1 and #2 suggest that mysql login failures are not counted as connection errors.. however this is not true (at least on mysql 5.6)
a bad user+pass does show up as COUNT_AUTHENTICATION_ERRORS in select * from performance_schema.host_cache
Hi Mark,
PERFORMANCE_SCHEMA.HOST_CACHE exposes counters – including COUNT_AUTHENTICATION_ERRORS – which do not impact the behavior of host blocking. While this post is 4 years old, a quick review of current GitHub code indicates this hasn’t changed (the comments are misaligned in the code – m_handshake_errors has nothing to do with proxy users). You can validate this by setting max_connect_errors =1, then trying to connect (from a machine other than localhost) using bad credentials, and seeing that you don’t get the “host blocked” error after a few – or many – attempts. COUNT_AUTHENTICATION_ERRORS will increase, but SUM_CONNECT_ERRORS – the counter that truly matters for host blocking behavior – will not. If you try to telnet to the MySQL Server port from a remote host, you will see SUM_CONNECT_ERRORS – and COUNT_HANDSHAKE_ERRORS – increase. When SUM_CONNECT_ERRORS reaches max_connect_errors, the host is blocked. Subsequent connection attempts will see COUNT_HOST_BLOCKED_ERRORS increase.
In short, while PERFORMANCE_SCHEMA improvements in 5.6 provide additional visibility to host cache counters, only handshake errors trigger the host to be blocked. As you noted, this drives my first two points and assertion that max_connect_errors is virtually useless for security purposes.