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:
[root@db_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?
Thank you!
