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
ip address of admin pc.
-- Replace unlimited access rule to only allow host rule
mysql>update user set host='' where host = '%' and user in ('dmitry');
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.