Auditing Login Attempts in MySQL and MariaDB

My colleague Miguel wrote about ways to audit login attempts in MySQL over 13 years ago, and this is still a relevant subject. I decided to refresh this topic to include some important changes since then.

Very often, it is important to track login attempts to our databases due to security reasons as well as to catch application misconfigurations. I’ll focus here on the most convenient ways to log authentication attempts. While auditing all client connections is usually pointless on busy production systems, when even thousands of new client sessions may be authenticating per second, let’s concentrate especially on the failed ones.

The Error Log

All MySQL and MariaDB variants have an easy way of logging failed authentication attempts in the standard error log via elevated log verbosity. To enable it, in older MySQL versions up to 5.7, as well as in all MariaDB versions, set log_warnings = 2 (or higher).

In MySQL 5.7+, the role of setting the error log talkativeness was moved to the new variable: log_error_verbosity. MySQL 8.0+ no longer recognizes log_warnings. To log failed logins, set log_error_verbosity = 3 instead.

An example log entry when login credentials were incorrect may look like this:

Another error occurs when user credentials are fine, but the user does not have privileges to access a given database:

Now, the problem with this is that the above note will only be printed when there is a system user allowing the source hostname, IP address, or subnet, and the actual username authentication phase is engaged. When the host is rejected early, we will not find anything in the error log! Only the client side will receive a rejection message, like this one:

Therefore, the error log cannot provide the complete information about failed login attempts if some are incoming from undefined hosts. Btw, the general log does not log these either.

The Audit Log (old type)

The traditional Audit Log Plugin feature, available in all recent MySQL variants, allows us to limit logging for connection activities, while we can ignore other queries. Unfortunately, we cannot log only failed logins, so the log may grow very fast when applications actively open new connections, and further filtering for failed attempts has to be done externally.

In Percona Audit Log Plugin, it can be done via the policy setting: audit_log_policy = LOGINS.

An example of a netcat TCP probe will result in the following audit log entry:

The entry provides a status error code:

Interestingly, the corresponding error log entry has the same message with a different code:

Another example of an unknown user login attempt:

Here, we can determine that the user part is wrong, as the priv_user field is empty.

A typical legit client session will have two entries, with status “0”, i.e:

In the MariaDB Audit Plugin, connection events can be logged via the server_audit_events = CONNECT setting. An example logging attempt with the wrong password would be seen as:

The Audit Log Filter (new type)

The new audit log functionality comes as a plugin in versions 8.0.x or a component in versions 8.4+, and provides way higher flexibility and finer-grained control on what and when is logged.

With Audit Log Filter, to minimize noise and overhead, it is possible to log only failed login attempts, so legitimate application sessions won’t flood the log.

Here is an example of how to enable such a filter rule:

The above rules set logging for the connection class, for all events that resulted in an error (status is not success).

An example entry for a client trying to authenticate with the wrong user credentials may look like this (if JSON format is used):

And a simple TCP probe (netcat) may look like this:

Unfortunately, again, connections from unknown hosts are not logged. Logging attempts aborted early on the host validation phase, do not reach the audit log or any other log! This is because the validation occurs at the very beginning of the handshake workflow, and if it returns failure, the connection is terminated before reaching the logging capabilities.

To workaround this limitation and make all unsuccessful login attempts be logged, we need to create a catch-all user account. However, we don’t want to extend the possible attack surface at the same time, so let’s disable authentication entirely for such an account. We will need the no-login plugin first:

Now, the following user entry will allow auditing connection attempts from any host/IP:

Now, specific port knocking may still not be logged, like with nmap, as the client sends a RST packet even before the initial MySQL “hello” packet. So, we will not see this session anywhere in the MySQL logs:

The only way to capture the above would be to watch the wire protocol, i.e., with tcpdump.

Additional Instrumentation

The error log entries can be viewed in a more structured manner, with the P_S table, so the entry:

Will have equivalent in:

And it allows SQL queries against the log, like aggregations:

Also, MySQL keeps track of connection attempts from external hosts in the host cache structure, which can be observed via performance_schema.host_cache view. The view provides statistics on the number of failed connections grouped by reason categories, together with success and failure timestamps. This view provides information only about source hosts’ connection events, but not users. An example output may look like this:

Depending on what was wrong with the connection or authentication attempt, a different counter will increment. For instance, when a user tries to log in with the default database, it does not have the privilege to, the COUNT_DEFAULT_DATABASE_ERRORS will increase. When an unknown user tries to log in without a password, the COUNT_AUTH_PLUGIN_ERRORS is used, but when any user (existing or not) tries a wrong password, the COUNT_AUTHENTICATION_ERRORS gets used instead.

Some cases of port probing, for example, using telnet or netcat, will increment COUNT_HANDSHAKE_ERRORS. Interestingly, though, the nmap probe does not increment any of them.

Also, when there is no catch-all user and no user entry matching the source IP/host/network, the host_cache table will allow us to at least observe statistics about failed logins per source IP, as each connection refused on the host validation phase will increment the COUNT_HOST_ACL_ERRORS counter.

Some limited visibility into failed login attempts can be available via the Connection Control Plugin. However, this plugin’s main purpose is not auditing, but rather slowing down brute force attacks against MySQL user accounts. Still, when installed, failed attempt counts are visible via the following view:

Finally, another per-user view was added in recent Percona Server versions, which shows how many failed login attempts are left until the account gets locked. This feature is active (tracking active) only for the accounts that have the FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME limitations:

Summary

There are multiple ways to monitor MySQL or MariaDB login attempts, but only the Audit Log Filter allows you to specify exactly what you want to log, like only failed logins, for instance. Due to MySQL connection handling behavior, in order to log authentication attempts from undefined hosts, a secure catch-all user account may be needed, though.

As for the TCP scanners, MySQL may not be able to log all such connection attempts, depending on how fast the connection is terminated.

The article was created by a human.