In this blog, I will provide answers to the Q & A for the Troubleshooting MySQL Access Privileges Issues webinar.
First, I want to thank everybody for attending the February 23 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:
Q: Should the root@localhost user be given ALL privileges or Super privileges? Does All include Super privileges also?
A: Yes, you should have a user with all privileges. Better if this user has access from localhost only. ALL includes SUPER.
Q: We have users who connect via a laptop that get dynamic IP addresses, so granting access with a server name is an easier way to manage these users. Can I grant access to a MySQL database with a hostname as opposed to an ipaddress? For example “myname@mymachine.mydomain.com” as opposed to “myname@10.10.10.10”? Is the host cache/performance_schema required for this?
A: Yes, you can.
But it looks like I was not clear about host cache. Host cache is an internal structure that is always available and contains answers from DNS server. You cannot enable or disable it. Until version 5.6, you also could not control it. For example, if the cache got corrupted the only thing you could do is to restart the server. Version 5.6 the table HOST_CACHE was introduced to Performance Schema. With this table you can examine the content of the host cache and truncate it if needed.
Q: If there are multiple entries in the user table that match the connecting user (e.g., with wildcards, hostname, and IP), what rules does MySQL use to select which is used for authentication? Does it try multiple ones until it gets a password match?
A: Not, mysqld does not try to hack your passwords. Instead it sorts the user table by name and host in descending order as I showed on slide #37 (page 110). Then it takes the first matching row. So if you created users foo@somehost, foo@some% and foo@1.2.3.4, and you connect as foo from somehost, mysqld first checks the user name and then chooses the first matching row foo@somehost. If you instead connect as foo from someotherhost, mysqld chooses foo@some%. An IP-based host is chosen if either mysqld started with option skip-networking or if 1.2.3.4 points to a host whose name does not start with “some”.
Mixing IP-based and name-based hosts is dangerous in situations when the same host can be resolved as somehost or 1.2.3.4. In this case, if something goes wrong with the host cache or DNS server, the wrong entry from the user table can be chosen. For example, if you initially had three hosts: uniquehost (which resolves as 1.2.3.4), somehost (which resolves as 4.3.2.1) and someothershost (which resolves as 4.3.2.2). Now you decided to re-locate uniquehost to a machine with IP 1.2.3.5 and use IP 1.2.3.4 for the host with name someyetanotherhost. In this case, the clients from the machine with IP 1.2.3.4 will be treated as foo@some%, which isn’t what you want.
To demonstrate this issue, I created two users and granted two different privileges to them:
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> create user sveta@Thinkie; Query OK, 0 rows affected (0,01 sec) mysql> create user sveta@'192.168.0.4'; Query OK, 0 rows affected (0,00 sec) mysql> grant all on *.* to 'sveta'@'Thinkie'; Query OK, 0 rows affected (0,00 sec) mysql> grant all on db1.* to 'sveta'@'192.168.0.4'; Query OK, 0 rows affected (0,00 sec) |
Now I modified my /etc/hosts file and pointed address 192.168.0.4 to name Thinkie:
|
1 2 3 |
127.0.0.1 localhost # 127.0.1.1 Thinkie 192.168.0.4 Thinkie |
Now, if I connect as sveta both Thinkie and 192.168.0.4 are resolved to the same host:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
sveta@Thinkie:$ mysql -hThinkie -usveta ... mysql> select user(), current_user(); +---------------+----------------+ | user() | current_user() | +---------------+----------------+ | sveta@Thinkie | sveta@thinkie | +---------------+----------------+ 1 row in set (0,00 sec) mysql> show grants; +--------------------------------------------------+ | Grants for sveta@thinkie | +--------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'sveta'@'thinkie' | +--------------------------------------------------+ 1 row in set (0,00 sec) mysql> q Bye sveta@Thinkie:$ mysql -h192.168.0.4 -usveta ... mysql> show grants; +--------------------------------------------------+ | Grants for sveta@thinkie | +--------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'sveta'@'thinkie' | +--------------------------------------------------+ 1 row in set (0,00 sec) mysql> select user(), current_user(); +---------------+----------------+ | user() | current_user() | +---------------+----------------+ | sveta@Thinkie | sveta@thinkie | +---------------+----------------+ 1 row in set (0,00 sec) mysql> q Bye |
Now I modified the /etc/hosts file and pointed Thinkie back to 127.0.0.1 ( localhost):
|
1 2 3 |
127.0.0.1 localhost 127.0.1.1 Thinkie # 192.168.0.4 Thinkie |
But host 192.168.0.4 still resolves to Thinkie:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
sveta@Thinkie:$ mysql -h192.168.0.4 -usveta ... mysql> select user(), current_user(); +---------------+----------------+ | user() | current_user() | +---------------+----------------+ | sveta@Thinkie | sveta@thinkie | +---------------+----------------+ 1 row in set (0,00 sec) mysql> show grants; +--------------------------------------------------+ | Grants for sveta@thinkie | +--------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'sveta'@'thinkie' | +--------------------------------------------------+ 1 row in set (0,00 sec) mysql> q Bye |
The reason for this is a stalled host cache, which can be easily observable with Performance Schema:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
sveta@Thinkie:$ mysql -uroot ... mysql> select * from performance_schema.host_cacheG *************************** 1. row *************************** IP: 192.168.0.4 HOST: Thinkie HOST_VALIDATED: YES SUM_CONNECT_ERRORS: 0 COUNT_HOST_BLOCKED_ERRORS: 0 COUNT_NAMEINFO_TRANSIENT_ERRORS: 0 COUNT_NAMEINFO_PERMANENT_ERRORS: 0 COUNT_FORMAT_ERRORS: 0 COUNT_ADDRINFO_TRANSIENT_ERRORS: 0 COUNT_ADDRINFO_PERMANENT_ERRORS: 0 COUNT_FCRDNS_ERRORS: 0 &nbs |