Securing MySQL on Linux

Never run mysql as user root.

Running MySQL as root, means everything the server process does is also done as root. MySQL user with super grant  can have a root shell access using User defined functions and setuid binaries normal user can have root shell access on server. Click on link below for details.

Get a root shell access using mysql UDF function

Change root password immediately after mysql installation.

update mysql.user
set password=PASSWORD('newpassword')
where user='root';
flush privileges;

Removing anonymous MySQL accounts. 

These account comes by default on installation. So these account need to be removed immediately.

select user,host from mysql.user;
delete from mysql.user where user='';
flush privileges;

Changing the Root User

It is wise to change the Administrative username to something other than root. In my case I have changed it to Admin. 

Generate required SQL command with the command below. Replace Admin with the desired username from Admin role in MySQL.
select concat(' rename user ''',user,'''','@','''',host,''' to ''Admin''','@','''',host,''';') from mysql.user;
rename user 'root'@'127.0.0.1' to 'Admin'@'127.0.0.1'; 
       rename user 'root'@'localhost' to 'Admin'@'localhost'; 
       rename user 'root'@'zabbix.localdomain' to 'Admin'@'zabbix.localdomain';
       flush privileges;
    In case above we have replaced root with Admin. Later we have to connect mysql with user Admin and not root as below
    mysql -u Admin -p
 

Use Application-Specific Users
Never use % It means every host.
Use different password for different host if possible.
Never grant the PROCESS or SUPER or FILE privilege to normal users.

Rather than creating single user with full access on all database as below. Create user per database, per host and per privilege level. 
        Bad Practice
grant all on *.* to 'user_dilli'@'%' identified by 'password';

        Good Practice
grant select on db_dilli.* to 'user_dilli_r'@'192.168.1.10' identified by 'password1';
grant select on db_dilli.* to 'user_dilli_r'@'192.168.1.11' identified by 'password2;

grant all on db_dilli.* to 'user_dilli_rw'@'192.168.1.10' identified by 'p@ssword3';
grant all on db_dilli.* to 'user_dilli_rw'@'192.168.1.11' identified by 'passw0rd4';

Drop test database that comes while installation.

Database test comes default with the installation. This database is of no use and can be removed immediately after installation.

show databases;
drop database test;

Disabling MySQL Server history file

MySQL client writes all executed commands to the Linux system’s user home directory in file .mysql_history. Though the file is hidden the file is easily accessible. The file contains commands as well as passwords which is dangerous in the case host machine is compromised.
So better way is to disable this feature. Literally, begin DBA I have never read this file till now and assume it is not required in coming days too. Execute following command on Linux shell.

rm -rf ~/.mysql_history
ln -sf /dev/null ~/.mysql_history

Disable remote login if DB is used locally with skip-networking or bind-address. Execute following command on shell.

netstat -ant | grep 3306
/etc/init.d/mysqld stop
#Add Following lines to my.cnf file 
bind-address = 127.0.0.1
/etc/init.d/mysqld Start
netstat -ant | grep 3306

Now database can be connected locally only. Remove host login is not allowed.

Disable access to the underlying filesystem

The LOAD DATA statement can load a file that is located on the server host, or it can load a file that is located on the client host when the LOCAL keyword is specified. It may be security loop hole sometimes so it is wise to disable it. Any user can load plugins and you database may be hacked using this option. So we should OFF this setting and should not ON unless it is required.

load data local infile '/Users/dilli/Desktop/infile.data' 
into table `tbl_infile` 
fields terminated by ',' 
lines terminated by '\n';

 

Disable loading local file with 

set global local_infile=OFF;
or
Add following configuration on my.cnf
local-infile=0 
 

Start mysql database on non default port.

Starting mysql to non default port is another way to protect the mysql database. In such case we have to specify the port while connecting to mysql database.
To start mysql database on non default port add following configuration on my.cnf

# Find the PID of mysql from command below. Execute command below on Linux shell
ps aux | grep mysql

# Replace the PID from pid generated above
ss -l -p -n | grep ",PID,"

# Stop mysql service
/etc/init.d/mysqld stop

# Add following setting with desired port on my.cnf FILE

port = 6603 

Verify new port with command below

ps aux | grep mysql
ss -l -p -n | grep ",PID,"

Iptables rules to secure mysql

Use Linux iptables firewall rule to allow mysql request from required hosts. Make sure to deny rest.
iptables -A INPUT -p tcp -s 192.168.0.100 --dport 3306 -j ACCEPT
iptables -A OUTPUT -p tcp -d 192.168.0.100 --sport 3306 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP
iptables -A OUTPUT -p tcp --sport 3306 -j DROP

Chrooting the server

A chroot on Unix operating systems is an operation that changes the apparent root directory for the current running process and its children. A program that is run in chroot environment cannot access outside the modified root. If there is high threat of accessing system via mysql then it is advised to chroot mysql to protect from such a threat. To be frank chroot is little tricky but is very easy to deploy.