Bug #102378 ALTER USER IF EXISTS ... ACCOUNT LOCK throws error with validate_password
Submitted: 26 Jan 2021 14:24 Modified: 27 Jan 2021 10:21
Reporter: Holger Thiel Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:8.0.22 Enterprise, 5.7.33, 8.0.23 OS:Any
Assigned to: CPU Architecture:x86
Tags: user account

[26 Jan 2021 14:24] Holger Thiel
Description:
"ALTER USER IF EXISTS ... ACCOUNT LOCK" causes an error message if the user does not exist and the validate_password component is in use.

mysql> ALTER USER IF EXISTS nonexistentuser ACCOUNT LOCK;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> SHOW GLOBAL VARIABLES LIKE 'val%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

How to repeat:
1. Activate validate_password component
2. ALTER USER IF EXISTS nonexistentuser ACCOUNT LOCK;

Suggested fix:
Throwing a warning instead of an error
[27 Jan 2021 10:21] MySQL Verification Team
Hello Holger Thiel,

Thank you for the report and feedback.

regards,
Umesh