MySQL Bugs: #97693: ALTER USER user IDENTIFIED BY 'password' broken by invalid authentication

Bug #97693	ALTER USER user IDENTIFIED BY 'password' broken by invalid authentication_string
Submitted:	19 Nov 2019 15:44	Modified:	21 Nov 2019 5:00
Reporter:	Nikolai Ikhalainen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	8.0.18, 5.7.28	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
ALTER USER user IDENTIFIED BY 'password' returns
ERROR 1396 (HY000): Operation ALTER USER failed for 'user'@'%'

If mysql.user.authentication_string contains invalid string.

Workaround:
UPDATE mysql.user SET authentication_string='$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED' where user='user';
flush privileges;
ALTER USER user IDENTIFIED BY 'password';

How to repeat:
docker run -d --name m80 -e MYSQL_ROOT_PASSWORD=secret -p 3306:3306 mysql:8.0
mysql --protocol=tcp --user=root --password=secret

mysql> create user user@'%' identified by 'password';UPDATE mysql.user SET authentication_string='password' where user='user';flush privileges;ALTER USER user IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.13 sec)

Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 0 rows affected (0.08 sec)

ERROR 1396 (HY000): Operation ALTER USER failed for 'user'@'%'

Suggested fix:
Update password by ALTER USER even if the length of authentication_string is invalid.

Hello Nikolai Ikhalainen,

Thank you for the report and feedback.

Thanks,
Umesh

Question: do you get any messages in the error log ? 
Generally MySQL will try to read and validate all users from mysql.user at startup and at flush privileges. 

If it fails to verify a user for some reason it will not add it to the ACL in-memory cache and will signal that with a message into the server's error log. 

For these user accounts what works and what doesn't is not defined, but it's safe to assume that nothing will work. 
Manually fixing the issue with the row (or deleting it) and issuing FLUSH PRIVILEGES will fix it. 

Another question: how did you achieve this state ? Was it a result of "normal" server activity or was it you updating the ACL table manually ?

mysqld prints same warning twice (one for flush privileges one for alter user):
2019-11-20T12:22:48.560392Z 8 [Warning] [MY-010319] [Server] Found invalid password for user: 'user@%'; Ignoring user
2019-11-20T12:22:48.652408Z 8 [Warning] [MY-010319] [Server] Found invalid password for user: 'user@%'; Ignoring user

> Another question: how did you achieve this state ? Was it a result of "normal" server activity or was it you updating the ACL table manually ?
It was attempt to change root@localhost password with skip-grant-tables and sql query instead of using https://dev.mysql.com/doc/refman/en/resetting-permissions.html .

The fix is easy (e.g. use workaround I've provided in the opening message), but the whole problem investigation really confuses.