tail -f /dev/me

Restricting mysql access to a user based on his source ip

Posted by kwame on July 16, 2014 – 12:36 am

One of the most challenging aspects of working as a sysadmin is the broad scope of the tasks you have to work on. In a single day’s work you could be asked to look into a security report and take the appropriate steps to address it and fix it. You can also be brought into an alert reported by a monitoring system and do various things, such as, modify the threshold of the alert since it was a false positive and / or look into the alert itself and fix it. You can be asked to modify some application software to be able to handle the load it’s receiving by changing its settings or just deploy a second or more instances of this application and place all of them behind a load balancer so the load is spread between all of the app servers, all of this, without any downtime or interrupting the sessions of users already logged into your application. You can also be tasked to lock down the access to an application on a specific layer and you need to be able to do it in a very short amount of time since the application might be vulnerable or under attack.

All of these aspects make a sysadmin’s work day a very interesting one. I was recently asked to restrict access to MySQL and allow users to be able to connect from only a specific network segment. If I had just been asked to restrict access to MySQL based on network segment (this is a high traffic MySQL server running on a Linux server) I would have used iptables right out of the bat and be done with the task, but the request was to restrict access in the database itself.

So I went to the MySQL documentation site and followed some pointers. Fired up a VM to do some tests and these are the steps I would follow to achieve such task.