-1

I had the same problem as the OP in the question below, and itsols' answer there solved it for me. Now I'd like to understand why, yet I am not allowed to post my follow-up question as a comment on his answer.

What I'd like to know specifically is, why it's necessary to have two separate entries for users coming from "any host" and localhost. Should "any host" not include localhost?

(See New users can't login on PHPmyadmin)

cygnus
  • 195
  • 1
  • 12

1 Answers1

0

After reading the mysql manual section on connection access my guess would be because mysql matches host before user and % is the least relevant.

So when looking up users mysql looks for the host first and only if no matching user for that host is found it looks for user@%.

The relevant passage is:

+----------------+----------+-
| Host           | User     | ...
+----------------+----------+-
| thomas.loc.gov |          | ...
| %              | jeffrey  | ...
+----------------+----------+-

A connection by jeffrey from thomas.loc.gov is matched by the first row, whereas a connection by jeffrey from any host is matched by the second.

Note:

It is a common misconception to think that, for a given user name, all rows that explicitly name that user are used first when the server attempts to find a match for the connection. This is not true. The preceding example illustrates this, where a connection from thomas.loc.gov by jeffrey is first matched not by the row containing 'jeffrey' as the User column value, but by the row with no user name. As a result, jeffrey is authenticated as an anonymous user, even though he specified a user name when connecting.

So the user @localhost (i.e. anonymous) would take precedence over user@%.

If no anonymous user @localhost is specified user@% should also include user@localhost but explicitly specifying user@localhost in addition to user@% seems to be the more reliable option.

Trudbert
  • 3,178
  • 14
  • 14