Bug #99066 Document about temporary account locking vs persistent account locking
Submitted: 25 Mar 2020 10:25 Modified: 26 Mar 2020 13:15
Reporter: NGUYEN TRUNG HIEU Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0.19 OS:Any
Assigned to: CPU Architecture:Any

[25 Mar 2020 10:25] NGUYEN TRUNG HIEU
Description:
Hi all

My testing

-- START --

1. Temporary account locking

1.1 Account locked because of incorrect password

ALTER USER hieu@localhost FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME UNBOUNDED;

mysql -u hieu -p1 (incorrect password 4 times)

5th:

ERROR 3955 (HY000): Access denied for user 'hieu'@'localhost'.
Account is blocked for unlimited day(s) (unlimited day(s) remaining) due to 5 consecutive failed logins.

SELECT user, host, account_locked FROM mysql.user WHERE user = 'hieu';
+------+-----------+----------------+
| user | host      | account_locked |
+------+-----------+----------------+
| hieu | localhost | N              |  --> Don't change to Y
+------+-----------+----------------+

1.2 Solution for account unlock

systemctl restart mysqld

or

FLUSH PRIVILEGES;

or

ALTER USER hieu@localhost ACCOUNT UNLOCK;

2. Persistent account locking

SELECT user, host, account_locked FROM mysql.user WHERE user = 'hieu';
+------+-----------+----------------+
| user | host      | account_locked |
+------+-----------+----------------+
| hieu | localhost | N              | --> is N
+------+-----------+----------------+

ALTER USER hieu@localhost ACCOUNT LOCK;

SELECT user, host, account_locked FROM mysql.user WHERE user = 'hieu';
+------+-----------+----------------+
| user | host      | account_locked |
+------+-----------+----------------+
| hieu | localhost | Y              | --> change to Y
+------+-----------+----------------+

systemctl restart mysqld

mysql -u hieu -pCorrect_password

ERROR 3118 (HY000): Access denied for user 'hieu'@'localhost'. Account is locked.

-- END --

Just curious!

1. Are there metadata, data about temporary account locking info in mysql?

2. ALTER USER ... ACCOUNT UNLOCK is not only change column account_locked: Y --> N in mysql.user but also other tasks!

Could you please explain at least for me about more details of other tasks?

Is it need to mention in mysql document?

Document link:

https://dev.mysql.com/doc/refman/8.0/en/flush.html#flush-privileges

https://dev.mysql.com/doc/refman/8.0/en/password-management.html#failed-login-tracking

Thank you very much!

How to repeat:
Solution for account unlock

systemctl restart mysqld

or

FLUSH PRIVILEGES;

or

ALTER USER hieu@localhost ACCOUNT UNLOCK;
[25 Mar 2020 11:08] NGUYEN TRUNG HIEU
Summary:

Where is failed-login tracking, account locking status for temporary account locking?

What ALTER USER ... ACCOUNT UNLOCK does for temporary account unlock?
--> because temporary account locking don't change account_locked:N-->Y in mysql.user

Thank you for your explain!
[26 Mar 2020 13:15] MySQL Verification Team
HI Mr. HIEU,

Thank you for your report.

I am not sure about your question. But. if you ask where you can track locked / unlocked account, there are two ways.

One is to set verbosity level at maximum and follow the error log.

The other way is to use Auditing plugins as described in the chapter 6.4.