| Bug #112873 | Locked account is not shown in mysql.user | ||
|---|---|---|---|
| Submitted: | 27 Oct 2023 22:49 | Modified: | 30 Oct 2023 7:11 |
| Reporter: | Juan Arruti | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Security: Privileges | Severity: | S3 (Non-critical) |
| Version: | 8.0.34, 8.0.35 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[30 Oct 2023 7:11]
MySQL Verification Team
Hello Juan Arruti, Thank you for the report and test case. Verified as described. regards, Umesh
[2 Jul 17:25]
Sveta Smirnova
This metric exists in the privilege cache only, so should not be stored in the system table: sveta@s76:~/src/percona/percona-toolkit$ ptmaster -utest mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES) sveta@s76:~/src/percona/percona-toolkit$ ptmaster -utest mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES) sveta@s76:~/src/percona/percona-toolkit$ ptmaster -utest mysql: [Warning] Using a password on the command line interface can be insecure. 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. sveta@s76:~/src/percona/percona-toolkit$ ptmaster -uroot -e "FLUSH PRIVILEGES" mysql: [Warning] Using a password on the command line interface can be insecure. sveta@s76:~/src/percona/percona-toolkit$ ptmaster -utest mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES) sveta@s76:~/src/percona/percona-toolkit$ ptmaster -utest -ppassword -e "SELECT USER(), CURRENT_USER()" mysql: [Warning] Using a password on the command line interface can be insecure. +----------------+----------------+ | USER() | CURRENT_USER() | +----------------+----------------+ | test@localhost | test@% | +----------------+----------------+ But having a way to see content of actual privilege cache, say, in Performance or Information schemas, would be nice.
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!