In this post we show you how to restrict MySQL DB remote access i.e, to allow database connections from only specific hosts or IPs. This may be required if you are defining a security policy for your database and want to restrict to few known hosts only. Example uses MySQL database installed on a RedHat Linux server.Method 1 - Using MySQL inbuilt Host based access restriction
In MySQL , the permissions you grant while creating a database user determines the DB access permissions. For example - when a user is created with below
[root@shareolite ~]# mysql -u root -p
mysql> use mysql;
mysql> CREATE USER 'shareolite'@'localhost' IDENTIFIED BY 'shareo123';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'shareolite'@'localhost' identified by 'shareo123' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'shareolite'@'%' identified by 'shareo123' WITH GRANT OPTION;
mysql> select Host,User,Password from user;
+-----------+------------+-------------------------------------------+
| Host | User | Password |
+-----------+------------+-------------------------------------------+
| localhost | root | *2632CF75CFB8987B429348FA90905C86DF24D0A7 |
| 127.0.0.1 | root | *2632CF75CFB8987B429348FA90905C86DF24D0A7 |
| % | shareolite | *7BC3D850E05DE3C76AA3028CD5D41C5D145CC03F |
| localhost | shareolite | *7BC3D850E05DE3C76AA3028CD5D41C5D145CC03F |
+-----------+------------+-------------------------------------------+
This user "shareolite" is allowed to access from any remote host as the permission is granted for '%' host. Now to disable remote access and to allow only localhost and few known IPs / hostnames say for example - 192.167.12.5 , shareolite.com , the entry with % should be deleted and permissions should be granted to required hosts as mentioned below.
mysql> delete from user where Host='%' and User='shareolite';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'shareolite'@'localhost' identified by 'shareo123' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'shareolite'@'192.167.12.5' identified by 'shareo123' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'shareolite'@'shareolite.com' identified by 'shareo123' WITH GRANT OPTION;
mysql> flush privileges;
mysql> flush privileges;
+----------------+------------+-------------------------------------------+
| Host | User | Password |
+----------------+------------+-------------------------------------------+
| localhost | root | *2632CF75CFB8987B429348FA90905C86DF24D0A7 |
| 127.0.0.1 | root | *2632CF75CFB8987B429348FA90905C86DF24D0A7 |
| 192.167.12.5 | shareolite | *7BC3D850E05DE3C76AA3028CD5D41C5D145CC03F |
| shareolite.com | shareolite | *7BC3D850E05DE3C76AA3028CD5D41C5D145CC03F |
| localhost | shareolite | *7BC3D850E05DE3C76AA3028CD5D41C5D145CC03F |
+----------------+------------+-------------------------------------------+
Now , only connections from localhost , 192.167.12.5 and shareolite.com domains should be allowed. Rest of hosts will get an error "Access Denied".
Method 2 : Using MySQL bind address method
While starting MySQL database process , we can mention the IP socket on which MySQL should listen for DB connections using parameter bind-address= , This can be edited in my.cnf file also. By default this line will be commented and MySQL accepts connections on all IPs in a system.
[root@shareolite ~]# netstat -anpt |grep mysql
tcp 0 0 :::3306 :::* LISTEN 25182/mysqld
If bind-address=127.0.0.1, then note the change below.
[root@shareolite ~]# netstat -anpt |grep mysql
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 29681/mysqld
This method is useful if you want to restrict DB connections to a single IP in a system having multiple IPs assigned to different Ethernet ports.
Method 3 : Using IPTables and Firewalls.
Using IPtables and firewalls , restrict access to specific hosts to specific ports.
Sample IP tables rules -
-A INPUT -s 192.167.12.5 -p tcp -m tcp --dport 3306 -j ACCEPT
-A INPUT -s 192.167.12.4 -p tcp -m tcp --dport 3306 -j ACCEPT