More Than One Connection Match

• It is possible for the client hostname and username of an incoming connection to match more than one row in the user table.
  • The server must determine which of them to use.
  • It resolves this issue as follows:
    • Whenever the server reads the user table into memory, it sorts the rows.
    • When a client attempts to connect, the server looks through the rows in sorted order.
    • The server uses the first row that matches the client hostname and username.

• When the server reads the table into memory, it orders the rows with the most-specific.
  • Host values first.
    • Literal hostnames and IP numbers are the most specific.
    • The pattern "%" means "any host" and is least specific.
  • Rows with the same Host value are ordered with the most-specific User values first.
    • A blank User value means "any user" and is least specific.

• If connection to the server is successful , but privileges are not as expected, one is probably being authenticated as some other account.
  • To find out what account the server used to authenticate, use SELECT CURRENT_USER().
    • It returns a value in user_name@host_name format that indicates the User and Host values from the matching user table row.
  • To further diagnose authentication problems print out the user table and sort it by hand to see where the first match is being made.

How does MySQL process request verification?

---

---

© 2007 John Michael Pierobon

Notes