No, we cannot have a similar information in the mysql.global_priv table, because blocking is a transient information, it is only present in memory and we do not and do not want to modify a persistent table every time such a transient event happens.

We can provide an INFORMATION_SCHEMA table that will show blocked accounts.

Technically you can use fail2ban for this with an actionban to execute the sql lock the user.

This will make brute force virtually impossible, but DoS will be trivial...

f_razzoli, this MDEV is only about showing the blocking information. The blocking itself was already implemented in MDEV-7598. For DoS one needs to know the user name. root is easy to guess, of course. FLUSH resets blocking information. And superuser on localhost ignores the limit, so one can always connect from localhost and FLUSH.

I see.

There are cases where the username is known or guessable:

• A CMS default username
• A former/malicious employee knows the pattern for human users (eg: name_surname)

Even if the DBA unblocks the users, the attacker can re-block them.

I'd think that if we had an option to block a client instead, that would be safer.

serg I ran through the list of the IS table we have: USER_STATISTICS table looks like a good fit.

But note that it requires userstat variable enabled, which enables statistics gathering for USER_STATISTICS, CLIENT_STATISTICS, INDEX_STATISTICS and TABLE_STATISTICS tables.

nikitamalyavin, may be PERFORMANCE_SCHEMA.ACCOUNTS table?

You can add m_password_errors to PFS_account, and a line to handle_password_errors(), like

    PSI_CALL_set_account_password_errors(u->user, u->host, u->password_errors);

As an optimization you can only do it on the wrong password and reset it to zero in PSI_CALL_set_thread_account, this way there will be no extra PSI_CALL's if the authentication is successful.

serg putting it into a PFS database doesn't seem logically correct – there's nothing about performance in the requested field, be it max_password_exceeded, or password_errors has nothing to do with performance. On the contrary, USER_STATISTICS already has some fields related to security, and a similar field ACCESS_DENIED.

Though it also has performance-related field TOTAL_SSL_CONNECTIONS which would fit PERFORMANCE_SCHEMA.ACCOUNTS best.

Is there any special benefit from using a PFS ACCOUNTS table, apart from no need of enabling userstat variable?

Note about MDEV-29209: following my logic, a number of failed login attempts would not be suitable for ACCOUNTS, while connection delay seems perfect to be placed there.

serg if we don't want the need to enable userstat for max-passwords violations, maybe really create a new table, like INFORMATION_SCHEMA.SECURITY, where we'd put this field and a number a failed logins overall? Also ACCESS_DENIED can be duplicated there (probably with deprecating it in the USER_STATISTICS table)?

PERFORMANCE_SCHEMA is not limited to performance as such, see THREADS, STATUS_VARIABLES, etc. It's simply a schema for all kind of run-time stats and data. While INFORMATION_SCHEMA is a standard schema for metadata. It's not supposed to be used for anything else, STATUS_VARIABLES, GLOBAL_VARIABLES, etc, etc — those tables don't logically belong to INFORMATION_SCHEMA, but they were added before we had PERFORMANCE_SCHEMA, and there's no easy way to remove them.

On the other hand, if we'll do MDEV-32218, then the same table can be used here too, even though it won't be strictly correct.