users

MySQL Administration (Remote Access)

So, once you’re all set up and running, you may want to give users access from the outside world. This is clearly an entry point for hackers and script kiddies, so be sure that you want to open up your database to the net.

I believe that, out of the box, MySQL is bound to the localhost IP address (127.0.0.1) so that will need changing first of all.

Edit my.cnf and find the line ‘bind-address’:

bind-address=127.0.0.1

Now change this to allow access from other IP addresses, if you want *all* access then change it to 0.0.0.0 or comment it out completely. You can only set it to one IP address, or all. (as shown)

Ensure your users can connect from the IP address you set it to:

GRANT <privileges> TO 'user'@'<ipaddress> IDENTIFIED BY '<password>';

for example

GRANT SELECT,INSERT,DELETE,UPDATE TO 'user1'@'203.122.211.90' IDENTIFIED BY 'letm3in';

Or, to all this user access from anywhere:

GRANT SELECT,INSERT,DELETE,UPDATE TO 'user1'@'%' IDENTIFIED BY 'letm3in';

Finally, restart the server and you’re done.

Let me just re-iterate, ensure you will be able to connect to the database from anywhere before restarting the server. Don’t blame me if you lock down a remote server with no access to it!

How to add a new user to MySQL

Don’t leave the root user as ‘the’ user for your web application. That’s just not a good idea. For instance, if you are running a whole bunch of sites from one database, and you security reasons you need to change the root password, you’ll have to update all the configs for the sites. Also, if one site gets hacked, they have the password for /all/ your databases and they can pretty much do whatever they like. Instead, have an extremely tough password for the root account and ONLY use that for root based activity. Have separate (and equally tough) passwords for each application. Keep the data separate as much as the access is concerned. Limit the databases that the users can see, to the ACTUAL databases they need access to. The more you tighten your defences, the safer you become, this is also known as multiple level security.

Anyway, to add a user, log in as root:

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Replace username with the username and password with the desired password. The localhost part is where you specify ‘from what domain/ip address’ the user can connect to this database server. You can really nail down security so that unless they are connecting from the specified address, then they cannot connect at all. Useful if you know the web server ip/domain (in this case they are on the same machine). You can allow connections form anywhere by substituting the localhost with % (which is a wildcard)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON databasename.* TO 'username'@'localhost';

The above gives the standard access to the user (SELECT INSERT UPDATE and DELETE) – this user will not be able to ALTER tables, DROP columns or tables, etc. This is important really.

Finally, flush the priveleges, although after many years of working with MySQL I am still unsure what this actually does. Something to do with re-caching the users and releasing table locks, etc.

mysql> FLUSH PRIVELEGES;

Of course you can use your favourite MySQL GUI to make these changes, but I think its better if you know the syntax yourself. One day you may have to make these changes remotely using a terminal of some kind. And then what are you going to do?