MySQL Bugs: #71460: ALTER USER fails if mysql.user.plugin is empty

Bug #71460	ALTER USER fails if mysql.user.plugin is empty
Submitted:	23 Jan 2014 16:49	Modified:	28 Jan 2014 7:10
Reporter:	Paul DuBois	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Pluggable Authentication	Severity:	S3 (Non-critical)
Version:	5.7.2, 5.7.3-m13	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I recently noticed that, beginning with 5.7.2, the ALTER USER statement fails if the plugin value is empty.
Example script: (run with mysql -f to ignore errors):

CREATE USER 'x'@'localhost' IDENTIFIED WITH 'mysql_native_password';
ALTER USER 'x'@'localhost' PASSWORD EXPIRE; -- succeeds
UPDATE mysql.user SET plugin = '' WHERE User='x' AND Host='localhost';
FLUSH PRIVILEGES;
ALTER USER 'x'@'localhost' PASSWORD EXPIRE; -- fails
DROP USER 'x'@'localhost';

Before 5.7.2, the first and second ALTER USER succeed.
As of 5.7.2, the first one succeeds and the second one fails.

I suspect this is due to the change in 5.7.2 that for an account to be valid, it must have a nonempty plugin value, and the server disables any account that has an empty plugin value. (Probably the server doesn't have the account loaded in the in-memory grant information and fails, but that's just speculation.)

From a DBA point of view, the ALTER USER statement should work regardless. All it does is flip the password_expired column from 'N' to 'Y', why should the plugin value make any difference?

How to repeat:
See script above.

Hello Paul,

Thank you for the bug report and test case.
Verified as described on 5.7.3

Thanks,
Umesh

[27 Jan 2014 21:05] Vamsikrishna Bhagi

Reasons for imposing a restriction on plugin column
that it cannot be empty, are discussed in worklog page
for WL#6982. Regarding the reproduction of this bug,
there can never be a scenario where plugin is empty,
unless the DBA deliberately does that. If versions lower
that 5.7 have an empty plugin value, they are filled up
during the upgrade process. Any new user created in 5.7
server, will definitely have the plugin column filled.
When a DBA purposely removes the plugin value and flushes
the privileges, a warning is rightly thrown stating that
that particular user will seize to be valid.

Above all, there should be no reason for DBA to make the
plugin column empty for any user. An empty plugin is used
in earlier versions to switch between a 16 digit hash and
41 digit hash for passwords according to the value of old_passwords variable. Now that support for 16 digit hash
is stopped, IMHO it is good to discourage DBAs from using it.
Making the plugin non empty is a step towards it.

Hence, marking this as not a bug.