MySQL Bugs: #35738: Logging Access Denied Connections

Bug #35738	Logging Access Denied Connections
Submitted:	1 Apr 2008 11:58	Modified:	3 Apr 2008 10:34
Reporter:	Surinder Singh	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Enterprise Monitor: Server	Severity:	S1 (Critical)
Version:	All Versions	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Our MySQL server is currently being attacked by brute force. I discovered this by looking at the Server Connections in MySQL Administrator. I can always see 3 connections from a particular IP address with user "unauthenticated". I enabled the general log to see more details, and it shows that the hacker/worm is attempting to use the "root" user name. I've now disabled remote access for the "root" user.

What I would like to do is to write a little app that periodically examines a log file. The app would look for the existence of hosts that are constantly failing to log into the server because access is denied. It would then add the IP to the routing table using the "ROUTE" command to block it.

My question is - Is it possible to separately log the "Access Denied" connections? I don't want to use the general log because that logs everything and can slow down the server and also result in a huge log file. I've enabled the error log and set log_warnings to 2. This appears to log aborted connections but doesn't log access denied failed connections. Currently the "Aborted_connects" status variable shows "6622", most of these are access denied failures. The error log only shows 2 genuine "Aborted connections", it doesn't show any access denied failures/aborted connects. This indicates that "Access denied" failed connections aren't being logged to the error log. Please could you add such a feature? Or let me know how I can achieve this.

Thanks.

How to repeat:
Add following to the my.ini file:

log_warnings=2
log_error=d:/mysql_logs/error.log

Restart the MySQL service.

Then attempt to make a connection to the MySQL server with incorrect user name or password.

Examine the d:/mysql_logs/error.log file for access denied failed/aborted connections.

Hi,

Thanks for the bug report, I actually put forward a patch for this already and it has been added to the 6.0 version - unfortunately, as it is a new feature (and changes error logging), it can not go to earlier versions. 

See http://bugs.mysql.com/bug.php?id=25822

Best regards

Mark

It seems like a very simple fix. Why can't you add this to version 5.0? Version 6.0 is in alpha, even version 5.1 is not suitable for production systems. The vast majority of MySQL production servers out there are probably running version 5.0. I highly recommend that you add this feature to version 5.0.

Thanks, Surinder

Whilst we recognize that the fix is small, and not really considered a large change, it does change functionality and therefore has to follow our strict guidelines for new features or behavior changes within GA versions. 5.0 is a very late GA version, and 5.1 is almost ready to become GA (being a very late RC currently). 

We are striving for stability within our product, and unfortunately that means that any behavior changes such as this have to go in to a version that is not within 'Feature Freeze' (which both 5.0 and 5.1 are currently). 

Hopefully 6.0 should be a much shorter release than 5.1 was however.

We really think you should take this more seriously. This isn't a minor feature request but a seriously big security hole that you should fix for all MySQL Server versions.

The only reason why we could see the 3 connections by a hacker/worm is because the MySQL server couldn't resolve the name for the IP address. This delays a connection by up to 15 seconds. Due to this name resolving delay and because this IP doesn’t have a reserve DNS lookup hostname, we were able to see the connections in SHOW PROCESSLIST. After disabling name resolving in MySQL we found that in the space of less than 9 hours the hacker/worm had attempted to connect over 414,000 times (more than 12 times per second!). It obviously ran out of passwords to try. Most users of MySQL server will probably be totally unaware that their server is being attacked in this way.

We seriously think that MySQL Server should have a built in feature to stop brute force attacks and this should be enabled by default. For example, if an IP address attempts to connect to the server more than 100 times in succession and fails with invalid user/password then block this IP for a few hours, or until the server is next restarted. Or even log it to a table and never allow access for this rogue IP address ever again. 

Thanks.