Bug #60432 Modifying mysql.user table can deny users from logging in
Submitted: 11 Mar 2011 14:56 Modified: 12 Mar 2011 13:58
Reporter: Jacek Osiecki Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Pluggable Authentication Severity:S3 (Non-critical)
Version:5.5.9 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysql user table plugin

[11 Mar 2011 14:56] Jacek Osiecki
Description:
If database manager accidentally (or deliberately) modifies mysql.user table by adding any column in position lower (or equal) than "max_user_connections", then after reloading privileges no one is allowed to log in.

Errors displayed by mysql client:

[root@localhost ~]# mysql -ujoshua -pjoshua
ERROR 1524 (HY000): Plugin '0' is not loaded
[root@localhost ~]#

Errors shown in mysql logfile when privileges are reloaded:

110311 15:33:54 [Warning] 'user' entry 'mysql@%' has both a password and an authentication plugin specified. The password will be ignored.

If new field is added after "plugin" column, there is no problem with authentication (although, I'm not sure whether plugins work properly if column is inserted between "plugin" and "authentication_string" columns).

How to repeat:
Just open mysql database, and run the command:

mysql> alter table user add column xxx varchar(40) after max_connections;
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

No user can log in now.
To repair it, one has to delete the additional column:

mysql> alter table user drop column xxx;
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
Probably mysql during verification of authentication plugins somehow does not care about column names and assumes that position of "plugin" is always the same.
[12 Mar 2011 13:58] Valeriy Kravchuk
Verified just as described on Windows XP. 

It would be nice for server NOT to reply on column orders and always refer columns by name (and to prevent alteration of tables in MySQL database in cases like this).
[11 Apr 2012 12:23] S R
How can this be resolved if no one, even root, can log on to mysql? I have stopped and restarted the service; rebooted the server to no avail...

**********
No user can log in now.
To repair it, one has to delete the additional column:
**********
[11 Apr 2012 12:31] Peter Laursen
Did you try to start the server with --skip-grant-tables option? ie:

1) navigate ("cd ...") to the server/bin folder/directory
2) start the server by executing "mysqld --skip-grant-tables" (if server is running as a service then first stop the service)
3) now connect from a client and 'normalize' the user table 

Refer: http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_skip-grant-tables

Peter
(not a MySQL person)