Dec. 16th, 2009

cdybedahl: (Default)

Today I had to figure out why we could log in to the database using the same credentials from all machines except the one the database server was running on. To possibly ease the pain of anyone reading this who may ever have MySQL's login system inflicted on them, I am now going to describe how MySQL logins work. Hopefully, it will be clearer than their documentation.

  1. The most important factor in deciding if you're let in (and if you are, as which user) is not your username or password. It's the host you're connecting from.
  2. The right to connect to a MySQL server depends on three things: the host you're connecting from, a username and a password. The host can be specified explicitly or with wildcards. A more specific host trumps a less specific one.
  3. When making the decision to let you in or not, MySQL uses the most specific entry (or entries) available for the host you're connecting from that also has a matching username.
  4. An empty username works as a wildcard.
  5. This means that if there is a wildcard name with a narrower matching host specification than for any entry with the username you gave, MySQL will try to log you in as the anonymous (wildcard) user instead of the one you wanted.
  6. If that wildcard user has a different password from the one you gave, you will not be allowed in. If it does have the same password (the empty one, for example) you will be logged in as another user than the one you expected.
  7. The MySQL documentation suggests doing SELECT CURRENT_USER(); to see which user you really got logged in as if you're experiencing privilege problems.

It was the second to last one that bit us, of course. The database server machine had a wildcard user entry with no password, so when we tried to connect from there with a user and a password we were not let in. Once you know how it works, it's kind of obvious.

For the question of why in Cthulhu's name it works this way in the first place, I have not been able to find an answer in the documentation.

Profile

cdybedahl: (Default)cdybedahl

July 2021

S M T W T F S
    123
45678910
11121314151617
1819 2021222324
25262728293031

Page Summary

Style Credit

Expand Cut Tags

No cut tags
Page generated May. 29th, 2025 05:26 pm
Powered by Dreamwidth Studios