Thursday, August 8, 2013

Manipulate Access Control List (ACL) on mysql

I had to limit of access to mysql server due to security improvement task.
We like to allow only few host to the mysql. It was very easy

mysql>use mysql
mysql> select host, user from user;
----------------------------+
host  user
----------------------------+
%  dmitry
host : % means all (It is security breach)
host side should be ip address of web server
or
ip address of admin pc.
example)
-- Replace unlimited access rule to only allow 192.168.0.5 host rule
mysql>update user set host='192.168.0.5' where host = '%' and user in ('dmitry');
mysql>commit;
mysql>flush privileges;
Query OK, 0 rows affected (0.01 sec)


-- Remove unlimited access rule

mysql> delete from user where host = '%';
mysql> commit;
mysql> flush privileges;


you can insert more hosts if you like to.