MySQL-JDBC remote permissions
Back again with a new discovery.
We all use MySQL, its easy, fast and most importantly free!!!!! but there are certain important issues one of which is allowing remote access to your MySQL server right? it could be providing remote access from client, your workbench or another application server. So whats the big deal, its simple and easy right?
Just rebind your MySQL server to the host IP address will do most of the part (to do this, you will need to edit MySQL's configuration file named my.cnf, located in the /etc/mysql directory in linux and similarly in the install directory in Windows)
You can permit a user or account inside MySQL to access a database or all databases and restrict privileges using the GRANT command.
The reason for this post: MySQL not able to resolve hostname to IP address causing access denied!
All that I have described in the post before this point is trivial. What is not trivial to do is the fact that MySQL by default has IP to hostname resolution, so it will try to find a hostname for your accessing client using the dhcp server on your network.
Since in a typical business scenario or office you will likely have a dhcp server and you would prefer to use hostnames instead of IP address to make sure that consistency is maintained and no glitches occur because of changing IP addresses due to dhcp lease expiration right?
The problem: as we know usually servers have multiple IPs if not multiple NICs, a feature usually common in virtualized environments and more importantly servers that are hosting a web application.
If your server has multiple NICs or multiple IPs using virtualization that a single OS is in control of or a single OS is using what will end up happening is: the OS is free to select either IP address to send its packets or to establish a TCP connection therefore your MySQL server not only has to have similar permissions for a use account for all IPs that the client server has but also has to disable IP to hostname resolution because what this will generally result in is IP to hostname resolution causing a hostname to be picked from the various that a server might have.
Disabling IP to hostname resolution will force MySQL to query its permissions table using IP only thereby restricting the randomness of which IP or NIC that your client might be using depending upon availability.
I know this post is a little confusing and if you have any questions I would be eager to answer them!
We all use MySQL, its easy, fast and most importantly free!!!!! but there are certain important issues one of which is allowing remote access to your MySQL server right? it could be providing remote access from client, your workbench or another application server. So whats the big deal, its simple and easy right?
Just rebind your MySQL server to the host IP address will do most of the part (to do this, you will need to edit MySQL's configuration file named my.cnf, located in the /etc/mysql directory in linux and similarly in the install directory in Windows)
You can permit a user or account inside MySQL to access a database or all databases and restrict privileges using the GRANT command.
The reason for this post: MySQL not able to resolve hostname to IP address causing access denied!
All that I have described in the post before this point is trivial. What is not trivial to do is the fact that MySQL by default has IP to hostname resolution, so it will try to find a hostname for your accessing client using the dhcp server on your network.
Since in a typical business scenario or office you will likely have a dhcp server and you would prefer to use hostnames instead of IP address to make sure that consistency is maintained and no glitches occur because of changing IP addresses due to dhcp lease expiration right?
The problem: as we know usually servers have multiple IPs if not multiple NICs, a feature usually common in virtualized environments and more importantly servers that are hosting a web application.
If your server has multiple NICs or multiple IPs using virtualization that a single OS is in control of or a single OS is using what will end up happening is: the OS is free to select either IP address to send its packets or to establish a TCP connection therefore your MySQL server not only has to have similar permissions for a use account for all IPs that the client server has but also has to disable IP to hostname resolution because what this will generally result in is IP to hostname resolution causing a hostname to be picked from the various that a server might have.
Disabling IP to hostname resolution will force MySQL to query its permissions table using IP only thereby restricting the randomness of which IP or NIC that your client might be using depending upon availability.
I know this post is a little confusing and if you have any questions I would be eager to answer them!
Comments
Post a Comment