#1686 (ok 4.5) Warn about "Any user" potential problem
I realized today why I have spent hours of my life debugging the same error:
mysql -u cohtaxion -p test
ERROR 1045 (28000): Accès refus' pour l'utilisateur: 'cohtaxion'@'@localhost' (m
ot de passe: OUI)
In English : Access refused for user: [...] (password: YES)
https://dev.mysql.com/doc/refman/5.6/en/problems-connecting.html explains:
If you cannot figure out why you get Access denied, remove from the user table all rows that have Host values containing wildcards (rows that contain '%' or '_' characters). A very common error is to insert a new row with Host='%' and User='some_user', thinking that this enables you to specify localhost to connect from the same machine. The reason that this does not work is that the default privileges include a row with Host='localhost' and User=''. Because that row has a Host value 'localhost' that is more specific than '%', it is used in preference to the new row when connecting from localhost! The correct procedure is to insert a second row with Host='localhost' and User='some_user', or to delete the row with Host='localhost' and User=''. After deleting the row, remember to issue a FLUSH PRIVILEGES statement to reload the grant tables. See also Section 6.2.4, “Access Control, Stage 1: Connection Verification”.
In other words, for MySQL "users" are different from identities. Since many (most?) MySQL installs will come by default with low-privileged (USAGE) identities on Host %, trying to create user foo for Host % will make foo unable to connect. In other words, if you choose Host so that you get the least connection problems, you trigger a connection problem.
This is obviously a major usability bug in MySQL, but it doesn't mean phpMyAdmin can't help. In fact, looking at the Users tab, the identities listed are presented as users. The page is titled "Users overview", and at the bottom, the user is offered to "Add user", or to "Remove selected users". This is standard terminology, but prevents users from realizing that a user is not what they think it is for MySQL.
It may not be needed to change terminology everywhere, but I would recommend:
To change "Users overview" to "Identities overview".
s/Add user/Add identity/
s/Remove selected users/Remove selected identities/
Possibly rename the tab from "Users" to "Identities", but that is slightly longer.
This issue was previously reported in http://sourceforge.net/p/phpmyadmin/bugs/4273/
Marc Delisle indicated there that phpMyAdmin warns about this problem since version 4.1. I did notice this warning in my phpMyAdmin 4.1.14. I am not saying there was none though - it's a fresh install, and there were other warnings which I was ignoring. I am sure, however, that I was never asked to confirm when I added the user. Ideally, phpMyAdmin would detect if there is a possible overlap between identities and require a confirmation in such cases.
Just to clarify - the reason why this bug has cost me hours is that I am not a system administrator. Months go between every time I hit this issue, and I have forgotten the next time. I suspect this is not a unique case for phpMyAdmin users.
I tried version 4.1.0, which indeed gives this warning as soon as the username is typed: "An account already exists with the same username but possibly a different hostname. Are you sure you wish to proceed?".
Version 4.4.10 does the same, and 4.4.6 (which is outdated) too. Are you saying that 4.4.6 does not emit this warning for you?
In the MySQL manual, I read this: "As a user, when you connect to a MySQL server, your identity is determined by the host from which you connect and the user name you specify.". So I believe that our terminology "Add user" is correct. A user is identified by a user name + host. Replacing this by "identity" would only be confusing.
The only improvement I see would be to replace the header "User" by "User name" in the Users overview.
Last edit: Marc Delisle 2015-06-29
Philippe,
I eventually understood that you had a problem logging in because there was an anonymous (blank) user created with host 'localhost'. In the Users overview, we paint this user as "Any" in red but we are thinking of adding a warning, because it looks like painting it in red is not sufficient.
IMPORTANT CORRECTION FOLLOWS
Marc,
I am sorry even you had trouble understanding my report. That must be mostly due to a big error I made. When I wrote:
I indeed meant:
IMPORTANT CORRECTION ENDS
Thank you Marc. I would say I had subconsciously noticed that Any appeared in red, but that never attracted my attention, and I do not think I would have understood it as a warning anyway.
After I understood the problem, I removed the anonymous entries from my install. So when I tried to verify I got the warning you were talking of, I started by re-creating an anonymous entry... and that's where it ended, since doing so broke the Host % identity I was using to connect to phpMyAdmin. I was directly locked out, and had to remove the new identity to use that phpMyAdmin again. Clearly, there are 2 scenarios, one of which 4.4.6 does not cover at all:
I have re-read your comment and re-tested user creation, and I finally got your warning ("An account already exists with the same username but possibly a different hostname. Are you sure you wish to proceed?") from 4.4.6, but without the question. I see a few problems:
I suggest the following formulation to address the first 2 issues:
Ideally, the warning would refer to https://dev.mysql.com/doc/refman/5.7/en/connection-access.html to let users determine whether overlap will actually occur.
Terminology
The first thing to say about terminology is that MySQL itself may use it inconsistently.
Marc wrote:
In this specific case, I think this is rather due to the ambiguity of "user" in IT. "Users" are first people who use software foo. But they are often also accounts of a foo install. To illustrate these 2 senses, just consider that as someone who uses Windows, I am a Windows user. But I have a test user on my install... so, as a user, I have 2 users (my regular user and my test user).
While I don't think this part of the MySQL manual is wrong, this shows that the term "user" should be avoided.
After looking at the manual, I see that "identity" is rarely used. The manual appears to use it to mean something a little different than "account". "account" (or "user account") is the most used term. Since that term is more official, shorter and clearer than "identity", I hereby retract my original suggestions. I think references to "users" (in the jargon sense) should be replaced with references to "accounts".
As for renaming the first column to "User name", I do not know if that is necessary, but I almost suggested the same.
Importance
I wrote that many or most installs will come with a low-privileged localhost account. In reality, the MySQL 5.7 manual explains that vanilla MySQL ships this account by default.
Ticket scope
I realize this ticket is vague. My original intention was only to request a terminological change, but the end goal is to keep users from experiencing the scenario I described. If someone changes the terminology as requested, or adds confirmations which they think greatly reduce the risk of users unintentionally creating overlapping identities, feel free to close.
Thanks Philippe; I'll read carefully your answer later. For now, let me clarify one thing: the message "An account already exists with the same username but possibly a different hostname." only intended to warn about creating a second user with username foo and a different hostname. There was no message to warn about the issues related to an anonymous user.
Terminology will be discussed at our IRC team meeting next week:
http://wiki.phpmyadmin.net/pma/2015-07_Meeting#Terminology:_Users_vs_Accounts
To sum up this bug ticket, there are two issues involved:
- terminology clarification (now "users" versus "accounts")
- warning on the impact of an anonymous user
Thank you Marc. IRC is a good idea; I will try to attend.
Account names
Also, the manual page you linked ( http://dev.mysql.com/doc/refman/5.7/en/account-names.html ) makes me realize MySQL uses the term "account name" to designate an "account identifier". I had never heard of this term, so I do not think users would understand it. However, I would still suggest using it, since the point of the terminological changes I recommend is of course to use the best terminology, but more importantly to ensure that users realize MySQL uses unorthodox access control.
I think it would be ideal to put a new "Account name" cell in the table header, over both "User" and "Host" cells, so users understand that Host is basically as important as User. Unfortunately, HTML tables have limitations, and as far as I know it will not be possible to make such an advanced header cleanly (i.e. without giving up thead), so I do not think this suggestion should be implemented at this point.
See http://wiki.phpmyadmin.net/pma/Category:Meetings for IRC meeting's time and place. You're welcome to attend.
Thanks Marc (I had found it, but I admit that would probably not have been the case if it wasn't for my MediaWiki experience).
"User accounts" was accepted for the tab name, and we should also update some of the other terminology on that page to match.
Ticket moved from /p/phpmyadmin/bugs/4973/