MySQL Bugs: #48439: Password gets reset for user 'root'; Other connectivity issues

Bug #48439	Password gets reset for user 'root'; Other connectivity issues
Submitted:	30 Oct 2009 15:34	Modified:	12 Feb 2010 1:07
Reporter:	Alex Stepanov	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.51a	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This bug is related to #30104, which was hastily marked as "no repro".

Synopsis: after adding a host with name that contains '%' to the list of allowed hosts for the user 'root' (e.g. 'root'@'192.168.200.%' or similar), password for the user 'root' gets reset.

Software: MySQL 5.0.51a-3ubuntu5.4, MySQL Admin 5.1.11

Notes: adding hosts with '%' seems to severely mess up the grant tables on MySQL 5.0.45 and higher. Aside from the above problem, a plethora of other connectivity issues has been observed, all related to the above issue.

How to repeat:
Steps to reproduce:

a) Obtain a fresh instance of the MySQL server
b) Connect as 'root' using MySQL Admin
c) Switch to the User Management tab and delete 'anonymous' user; apply changes
d) Turn 'show hosts' option on for MySQL Admin and then add a host to the user 'root'. Use any host that has '%' in the name
e) Save changes. MySQL Admin should prompt to re-login again. Login using the 'root' credentials
f) Close MySQL Admin
g) Attempt to connect again using regular password. You should get Error 1061
h) Now try to logon without password (using blank password). You should be able to connect

After additional experiments, we discovered that ANY manipulation with the list of allowed hosts for user 'root' that involves hosts with '%' in the name,  results in the password being reset to blank

Thank you for the problem report. Please, send the results of:

select user, host, length(password) from mysql.user;

from the problematic server.

Valery,

Thank you for understanding that we are unable to keep our servers in limbo just for the purpose of reproducing the bug. The affected servers were rolled back to the normal operational state, which included resetting passwords on the affected users. If you follow the above steps, you should be able to reproduce the issue without much trouble.

Regards
Alex Stepanov

If you think this was a server bug, please, check if it is repeatable with a newer version of server, 5.0.86. 

If you think it was a bug in MySQL Administrator, note that it is no longer supported on Linux. There is no chance for a fix. Check MySQL Workbench 5.2.8 that should replace MySQL Administrator eventually.

-- Changed category back to MySQL Server. 

Sorry for misunderstanding, the MySQL Admin version I am using is Win32. I believe that while the bug may be originated by MySQL Admin sending incorrect requests, MySQL server should disallow any requests that may potentially corrupt the privilege data. Or, if the tables were not damaged, then the host/password verification mechanisms have some serious issues.

As far as re-testing with different builds, we are in the production environment and unfortunately, are unable to accommodate for additional testing for MySQL team. When we submit a bug ticket, we do that as a professional courtesy to the other MySQL users who may experience the same issue but were unable to report it. We believe that the bug description for this particular one is fairly straightforward and the testing team should have no trouble recreating this particular scenario with the build/version in question.

Many thanks for writing a bug report.

Unfortunately, this is not a bug.

In MySQL every combination of user@host has its own password.

When you want that root@% has same password then root@192.168.%.% then you need to set the password for both combination.

Please take a moment to notice the nature of the problem (as described in the very first comment), which is the fact that ALL root account passwords get reset to "blank" regardless of the 'host' entry. For example, if I had a 'root@localhost' user and then went on and added a 'root@192.168.%.%' - that would result in password being reset for both accounts. Additionally, with a high degree of probability, it might corrupt the schema ownership information for the schema(s) owned by the root@localhost.

Thank you for the feedback.

Please provide output of SELECT user, host FROM mysql.user

Please see our response to the similar request by Valery Kravchuk

Thank you for the feedback.

I could not repeat described behavior using steps provided.

> g) Attempt to connect again using regular password. You should get Error 1061
> h) Now try to logon without password (using blank password). You should be able to
connect

Please indicate connection options (host, port) you used when connect first time (with regular password). I assume if you connect from Windows box to Linux these are different machines and you don't connect via socket. Please confirm.

The server was Linux (Ubuntu, as indicated in the original submission) and the client side was a Windows box. The connection was made on the standard port 3306.

Thank you for the feedback.

I partially could repeat described behavior: got "Access denied" error when tried to edit users second time, but this behavior is correct and explained at http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html So in my opinion this is either "not a bug" or "can't repeat".

If you still insist this is MySQL bug we need output of SELECT user, host FROM mysql.user and information about host IPs/names you use.

Perhaps I'm not following the drift but im my opinion, the article describes the security/privilege system the way it is supposed to work - which is precisely the way you mentioned before (every user/host combination has it's own password and access level). I don't think this article ever alludes to this particular situation, when adding a 'root'@'%' wipes out passwords on every other 'root'@'<host>' user. It is entirely possible that it could be a version-specific bug that got fixed in subsequent releases but it does not necessarily mean that it is "no repro", it just means that it should be tested with the exact software configuration that was indicated in the original submission

On the unrelated note, the overall experience dealing with MySQL folks has been the single most frustrating one in many years, beating even the profound Microsoft arrogance and indifference. Way to go guys!