To display the number of attempts by the locked accounts to connect we have the command -
SHOW GLOBAL STATUS LIKE "Locked_connects";
For example, I create two locked accounts - user1 and user2.
CREATE USER user1 IDENTIFIED BY "********" ACCOUNT LOCK;
CREATE USER user2 IDENTIFIED BY "********" ACCOUNT LOCK;
Initially -
SHOW GLOBAL STATUS LIKE 'Locked_connects';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Locked_connects | 0 |
+-----------------+-------+
First user1 attempts to connect -
mysql -u user1 -p
Enter password:
ERROR 3118 (HY000): Access denied for user 'user1'@'localhost'. Account is locked.
Now we have -
SHOW GLOBAL STATUS LIKE "locked_connects";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Locked_connects | 1 |
+-----------------+-------+
Next user2 attempts to connect -
mysql -u user2 -p
Enter password:
ERROR 3118 (HY000): Access denied for user 'user2'@'localhost'. Account is locked.
Now we have -
SHOW GLOBAL STATUS LIKE "Locked_connects";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Locked_connects | 2 |
+-----------------+-------+
Thus, here we have number of net attempts by all locked accounts to connect.
But, suppose we want to display the number of attempts by each locked account to connect.
We have -
SELECT user FROM mysql.user
-> WHERE account_locked = "Y";
+---------------------+
| user |
+---------------------+
| user1 |
| user2 |
+---------------------+
We want to display the number of attempts done by user1 or user2 to connect. Is it possible to do so?
