offsite mysql access

Question

I'm moving my mysql database off-site and for the most part web-apps (to be used privately) running on a separate server on the same network are all that need to access it. So from what I read it's best that the mysql database is only accessible over the network and not exposed to the outside world. However I'm currently accessing the database (on a read only basis) using an excel addin I wrote and I'd like to continue to do so. What's the best way to do so?

I like the idea of MySQL proxy to sit on a local server that securly accesses the database via ssh tunnel to the webapp server, but "MySQL proxy" is still alpha so it doesn't seem ideal.

I could have each client have to open up a SSH tunnel manually, but this is too inconvenient for other users of the excel add-in.

I could set up a VPN but this seems like overkill... maybe it's not?

Any ideas?

Answer 1

There are three things you want to look at:

1. In MySQL make sure you set up your GRANTs correctly, so each user has the correct access, password protected, only from limited IP addresses: GRANT SELECT, INSERT, UPDATE, DELETE ON database.* TO 'user1'@'ip.add.re.ss' IDENTIFIED BY 'PASSWORD';
2. Set up a firewall to restrict access to port 3306 to the IP addresses you trust.
3. Look at SSL encrypted sessions: http://dev.mysql.com/doc/refman/5.0/en/secure-connections.html You can set your GRANTs up to require SSL connections.