Subscribe via feed.

Restricting mysql access to a user based on his source ip

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

mysqlOne 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.

1. Review grants for the user which I want to lock down:

[root@workvm ~]# mysql -u root -p -h localhost
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user,host from mysql.user;
+-------+--------------------+
| user  | host               |
+-------+--------------------+
| kwame | %                  |
| root  | 127.0.0.1          |
|       | localhost          |
| kwame | localhost          |
| root  | localhost          |
|       | workvm.pythian.com |
| root  | workvm.pythian.com |
+-------+--------------------+
7 rows in set (0.00 sec)

mysql> show grants for 'kwame'@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for kwame@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kwame'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'kwame'@'%'                                                       |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>

In this case we want to restrict access to the user ‘kwame’ to have access only from 192.168.100.x

2. Remove access to this user:


mysql> delete from user where user='kwame' and host='%';
Query OK, 1 row affected (0.00 sec)

mysql> delete from user where user='kwame' and host='localhost';
Query OK, 1 row affected (0.00 sec)

mysql>

3. Grant access to the user only from 192.168.100.x


mysql> grant all privileges on `db1`.* TO 'kwame'@'192.168.100.%' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'kwame'@'192.168.100.%';
+------------------------------------------------------------------------------------------------------------------+
| Grants for kwame@192.168.100.%                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kwame'@'192.168.100.%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'kwame'@'192.168.100.%'                                                       |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select user,host from mysql.user;
+-------+--------------------+
| user  | host               |
+-------+--------------------+
| root  | 127.0.0.1          |
| kwame | 192.168.100.%      |
|       | localhost          |
| root  | localhost          |
|       | workvm.pythian.com |
| root  | workvm.pythian.com |
+-------+--------------------+
6 rows in set (0.00 sec)

mysql> 


4. Confirm the access is working only from the expected source:

[kwame@workvm ~]$ ifconfig | grep 'inet addr' | grep 100
          inet addr:192.168.100.194  Bcast:192.168.100.255  Mask:255.255.255.0
[kwame@workvm ~]$ mysql -u kwame -p -h 192.168.100.194
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+------------------------------------------------------------------------------------------------------------------+
| Grants for kwame@192.168.100.%                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'kwame'@'192.168.100.%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'kwame'@'192.168.100.%'                                                       |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>



This post is under “geek stuff, linux, MySQL, sysadmin” and has 1 respond so far.
If you enjoy this article, make sure you subscribe to my RSS Feed.