job.answiz.com
  • 4
Votes
name
name Punditsdkoslkdosdkoskdo

How does mysql decide the hostname of its customers?

I'm trying to create a MySQL user which will only be allowed to connect to the MySQL database from a specific hostname.

grant all on db_name.* to 'user_name'@'appserver-lan.mydomain.com' identified by 'some_passwd'

By checking the user table on the mysql db, I can see that the user was created successfully:

use mysql; select * from user where User='user_name' and Host='appserver-lan.mydomain.com'

or

show grants for 'username'@'appserver-lan.mydomain.com'

The hostname I specified is an alias to an amazon-ec2 name, which when resolved by the AWS DNS servers results in a LAN address:

[[email protected]_server ~] # host appserver-lan.mydomain.com

appserver-lan.mydomain.com is an alias for ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com has address 10.xxx.xxx.xxx

The problem is that when I try to connect to the database LAN IP from this appserver-lan, I get an access denied error, although the password is correct. The weird thing here is that the hostname showed in the error is not the hostname I had specified when the user was created:

ERROR 1045 (28000): Access denied for user 'user_name'@'appserver.mydomain.com' (using password: YES)

So, my question is: how does mysql determines the client hostname? I believe it doesn't do so by a reverse DNS lookup, since I checked and it does not point to "appserver.mydomain.com" neither to "appserver-lan.mydomain.com". Additionally, the db server has no entries related to the appserver on /etc/hosts.

Summarizing, I'm pretty sure it's a hostname resolution issue, since granting privileges for host "%" or to the LAN IP works just fine.

Any ideas of what I'm missing?

I have also seen problems where IPV4 and IPV6 hostnames and IP addresses do not match, and where a host name only is used for the user permission. For example, where there is a IPV6 reverse DNS but no forward IPV6 DNS.

  • 0
Reply Report

MySQL will be doing a reverse DNS look up on the IP address to get the host name. If you are running in AWS EC2 then you can assign an elastic IP to your server (this does not cost any extra) and then ask amazon to set up reverse DNS for the elastic IP to go to your hostname.

Also is your DB server also in EC2? Because if so it will be using the private IP address of the instance, otherwise it will be using the public ip address. It looks from you post as thou appserver-lan is the 10.XXX.XXX.XXX private ip assigned to your server, not the putlic one.

I'm not sure which IP address would be used if communicating with a different region though as I have only had servers within the same region.

  • 0
Reply Report

It uses a reverse DNS lookup. It takes the IP address of the client and uses whatever PTR record is returned for that name.

In my opinion doing authentication based on the name is not very useful at all, I suggest you consider using IP addresses instead.

  • 0
Reply Report