MySQL Failed Logins Locking Account

MySQL 8.0.19 introduced the ability to lock an account on a MySQL instance after too many failed logins.  This is not turned on by default, the locking period is measured in days (more below), and I would urge caution as not to 'denial of service' yourself out of your server.

Syntax

CREATE USER 'foobar'@'localhost'
IDENTIFIED by 'password'
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 2;

Data

And if you look at the mysql.user entry you will see

User_attributes: {"Password_locking": {"failed_login_attempts": 3, "password_lock_time_days": 2}}

Example

$mysql -u foobar -p
Enter password:
ERROR 1045 (28000): Access denied for user 'foobar'@'localhost' (using password: YES)
$ mysql -u foobar -p
Enter password:
ERROR 1045 (28000): Access denied for user 'foobar'@'localhost' (using password: YES)
$ mysql -u foobar -p
Enter password:
ERROR 3955 (HY000): Access denied for user 'foobar'@'localhost'. Account is blocked for 2 day(s) (2 day(s) remaining) due to 3 consecutive failed logins

Other Options

You can also use PASSWORD_LOCK_TIME UNBOUNDED; to keep the account locked.

The range for the lock time can range from 0 (which disables locking) to 32767 (32767 days is 89.7 years!).

ALTER USER can be used to modify existing accounts to enable locking. This locking feature supports DUAL passwords (see My MySQL Account Has TWO Passwords).

How to Reset

A reboot will reset the counter as will a FLUSH PRIVILEGE.

If you restart the instance with skip grant tables, the first FLUSH PRIVILEGE will enable this locking feature.

Or you can use ALTER TABLE to any value (including the current option value), or execution of an ALTER USER ... UNLOCK statement for the account.

Conclusion

This is an interesting security option that you may find useful. Once again I urge caution to avoid locking yourself out of an account -- so do not set it on root or other similar account and it would take a lot of debate to get me to enable this on a password used on an application's account. 

Last week I presented at the Midwest PHP Conference virtually and one of the participants was looking at the granularity of the locking period.  I would like to hear some opinions from those who want something shorter than a day.  And I would love to know if you need somethings longer then a day as 89.7 years seems extreme enough to me.