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.

nikitamalyavin Our idea is to add user locked due to max_password_errors in the INFORMATION_SCHEMA.USER_STATISTICS table?
Thanks

pandi.gurusamy The idea is to show the current password_errors value for the user.

Also, after some thinking and prototyping, I tend to the solution with a separate table, like INFORMATION_SCHEMA.USERS as Sergei suggests in MDEV-32218 (or we should think better on the name, since USERS looks too vague, especially for the statement like FLUSH USERS – it's unclear what will happen).

So we won't be dependent on whether the user statistics are enabled (and won't need the userstat variable).

nikitamalyavin, if we add a separate table it could make sense to address MDEV-27205 at the same time. And it could make sense to use a JSON field for the details.

Finding a good name is always tricky. Ideas are USER_DETAILS, USER_USAGE, USER_CONNECT_INFO, USER_STATUS

Thanks for another reference, ralf.gebhardt! Yes, MDEV-27205 absolutely makes sense to be put in the same table. As I can see by the code, we currently don't store last login time internally, so an extra effort will be required.

Since all these fields bend around user login details, my ideas are:
AUTH, USER_ACCESS, USER_LOGIN, LOGON

INFORMATION_SCHEMA.LOGON sounds the most decent to me, but we won't be able to add more general information about users there.

Finally I came across with the following implementation:

• The view will iterate through the acl_users array under a single critical section. Given the fact that nearly the same is done during other operations (like login, etc), it should be fine, and will not disrupt already logged in users' performance.
• The unprivileged user will see their own info. The access is optimized with binary search.
• The table name is LOGON.
• At the same time, password expiration timestamp has been added, so MDEV-32218 can be closed at once.

serg please review 9e5d4dfc, branch bb-11.5-password-errors

bb-11.5-password-errors d1ec5274161db821de7ae9ce9be2dd583ef23c2d is ok to push into 11.5.

Minor problem- if user has SELECT privileges to mysql.global_priv, then he should be able to see also all users after "select * from information_schema.users;" (similar to the situation when the user has SELECT privileges to the whole mysql.* database )

create user a@localhost;

connect(con1, localhost, a);

select * from information_schema.users ;

--connection default

grant select on mysql.global_priv to a@localhost;

--connection con1

select * from information_schema.users ;

select * from mysql.global_priv;

--connection default

grant select on mysql.* to a@localhost;

--connection con1

select * from information_schema.users ;

--connection default

drop user a@localhost;

serg, it seems that an access to mysql.global_priv is not done as we thought – not through show_global_privileges, which was a common sense, but rather as to a usual table...

Anyway, I don't see a problem. It's not consistent across implementations (SHOW GRANTS requires select on mysql.*), and we can require a privilege different from mysql.global_priv.