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
• Status: Verified
• Category: MySQL Server: Security: Privileges
• Severity: S3 (Non-critical)
• Version: 8.0.22 Enterprise, 5.7.33, 8.0.23
• OS: Any
• 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