A problem that I seem to have dealt with regularly for the last month is clients wanting to access their database servers remotely – so I thought a blog post on what you need to do and the potential pitfalls might be useful for other people doing the same.
One worry is that opening the port on the firewall will make your server insecure, and although this can happen it doesn’t have to be. Because when most people open the port 3306 on the server they think thats the job done, when in fact you are only half way there!
Making Sure Other Users Are Safe
MySQL users have access rights as to where they can be connected from. When creating users through a control panel it is normally localhost by default, meaning that even with the port open in the firewall you still can’t connect with that user. So to check how your users are set up run through the following steps:
- Log into mysql (from the command line mysql -u root -p)
- use mysql
- select * from user\G
This will list all users in a readable format and what you are looking for is users that have the host *
These are users that are open to any host. This means that these are the ones you want to close down on. The simplest way of doing that is just modify the table while you are there.
Update user set host=’localhost’ where host=’*’ and User=’[USERNAME]‘ Limit 1
Once you have set all the users to be localhost we can create some users for you to use remotely.
Enabling A User For Remote Access
MySQL allows you to have multiple users with the same username, with different hosts. If you have a static IP address for where you will be connecting from replace the * with that IP address.
- Create user ‘root’@'*’ identified by ‘[PASSWORD];
- Grant ALL on *.* to ‘root’@'*’
- Flush Privileges
And your user should now be ready to use. To make it even more secure you can choose a more oblique username.
