How to set up MySQL remote hosts IP address for users with dynamic IP's

Question

Background

I need remote access from my Delphi PC application to a MySQL database hosted on a shared server at my ISP. This DB is not associated with any web site. All there is on my ISP account is the database.

My application runs on half a dozen user's machines in their own homes. To give my users access I have then to set up remote hosts in cpanel. Some of the users have dynamic IP addresses. Because their IP address might change, I've written code in my application using IdHTTP that takes the user's IP, logs onto my ISP, navigates to the remote hosts page, checks if that IP is there, adds it if not and then deletes that last IP address that user used. Works, but rather complicated.

I know I could put the wildcard %.%.%.% in the remote hosts and make life simpler

Question

Some posts say this is insecure and I can appreciate that but is it that insecure?

After all, anyone trying to hack my database has to

• know my user name and password for the ISP account

• know there is a database there

• know the name of the database

• know the user name and password of one of the allowed database users

If they know all that lot then the limiting IP address seems rather work for nothing.

Further info

I have already read these posts here, here, here and here and would point out that this is a shared hosting site so I can't get to system files or restart MySql or issue most admin commands such as GRANT ALL ON etc

The reason for my question is that the ISP recently changed their cpanel software from Paper_Lantern to Jupiter, which of course changed the URL of the remote hosts page, broke my application and required me to fix the code to point to the new URL. So I am looking for a way to do this without needing to navigate to the remote hosts page.

Answer 1

Answer based on the discussion above in comments:

I would recommend to use wildcards, not '%' for the whole internet, but at least narrow it down to each user's ISP. Even if their IP address is dynamic, it should be within the subnet owned by their ISP, so you could grant access from a pattern like '123.45.%' (that's just an example assuming the ISP owns a class B network), and that would block 99.998% of the internet, but allow any client from that user's ISP.

On the other hand, I think it is generally unsafe to give direct access to your MySQL instance to remote IP addresses. If it were me, I'd develop code for an API service, and design the client app to request only prescribed actions via http requests. The API service would connect to MySQL and do it for them. So it would be your API service, not your users, connecting to MySQL and the grant would only need to allow the IP address where your service runs. This is the way most web services are designed, but I understand from your comments that it would be a major redesign for your app, and you favor allowing direct MySQL connections and implementing SQL in the client app.

I hope you at least are using SSL connections from the client app to your MySQL instance, or else anyone (not just your users) can run packet-sniffing tools to learn all sorts of things about your queries and tables.

Anyway, by using wildcards for the IP ranges, you would only need to change the grants if your users come or go, or if they change ISP's. Also some ISP's may own multiple subnets. It's possible a given user might get assigned a different IP address that doesn't match the pattern to which you granted access.

The bottom line is that there's a tradeoff between simplicity, flexibility, and security. No solution gives all three.