Description:
If an account is locked due to failed login attempts, it's not shown in the mysql.user table.
How to repeat:
mysql [localhost:36595] {msandbox} ((none)) > select @@version, @@version_comment ;
+-----------+------------------------------+
| @@version | @@version_comment |
+-----------+------------------------------+
| 8.0.34 | MySQL Community Server - GPL |
+-----------+------------------------------+
1 row in set (0.00 sec)
mysql [localhost:36595] {msandbox} ((none)) > CREATE USER 'test'@'%' IDENTIFIED BY 'password' FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME UNBOUNDED;
Query OK, 0 rows affected (1.42 sec)
$ ./use -utest
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
$ ./use -utest
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
$ ./use -utest
ERROR 3955 (HY000): Access denied for user 'test'@'localhost'. Account is blocked for unlimited day(s) (unlimited day(s) remaining) due to 3 consecutive failed logins.
mysql [localhost:36595] {root} ((none)) > select account_locked from mysql.user where user = 'test' \G
*************************** 1. row ***************************
account_locked: N
1 row in set (0.00 sec)
I know this information is seen in the MySQL error log in case log_error_verbosity is 3, which is not the default value.
Still, I think this should be exposed in a table for monitoring, admin, or other purposes.
Thanks!
Description: If an account is locked due to failed login attempts, it's not shown in the mysql.user table. How to repeat: mysql [localhost:36595] {msandbox} ((none)) > select @@version, @@version_comment ; +-----------+------------------------------+ | @@version | @@version_comment | +-----------+------------------------------+ | 8.0.34 | MySQL Community Server - GPL | +-----------+------------------------------+ 1 row in set (0.00 sec) mysql [localhost:36595] {msandbox} ((none)) > CREATE USER 'test'@'%' IDENTIFIED BY 'password' FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME UNBOUNDED; Query OK, 0 rows affected (1.42 sec) $ ./use -utest ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES) $ ./use -utest ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES) $ ./use -utest ERROR 3955 (HY000): Access denied for user 'test'@'localhost'. Account is blocked for unlimited day(s) (unlimited day(s) remaining) due to 3 consecutive failed logins. mysql [localhost:36595] {root} ((none)) > select account_locked from mysql.user where user = 'test' \G *************************** 1. row *************************** account_locked: N 1 row in set (0.00 sec) I know this information is seen in the MySQL error log in case log_error_verbosity is 3, which is not the default value. Still, I think this should be exposed in a table for monitoring, admin, or other purposes. Thanks!