MySQL hardening script
The MySQL database has become the world's most popular open source database because of its
consistent fast performance, high reliability and ease of use.This script gives some of the
best pracise steps to secure your database from attacks and vulnurabilities for improved
performance .
******************************************************************************
#!/bin/bash
#########################Mysqlhardeningscript################################################################Author:ratheeshvasudevan########################################
read -p "Enter mysql root password" mysqlpass
# Setting password for users
a=`mysql -uroot -p$mysqlpass -Dmysql --execute "select User from mysql.user where length(password) = 0
or password is null;"`
if [ -z "$a" ]; then
echo "Already password is configured for all users" >> /tmp/mysqlhardening.txt
else
for i in $a
do
`mysql -uroot -p$mysqlpass -Dmysql --execute "UPDATE mysql.user SET password=PASSWORD('onmobile') WHERE user='${i}'and length(password) = 0;"`
`mysql -uroot -p$mysqlpass -Dmysql --execute "flush privileges;"`
echo "password is set to 'onmobile'" >> /tmp/mysqlhardening.txt
done
fi
#Remove Anonymous account
`mysql -uroot -p$mysqlpass -Dmysql --execute "delete from mysql.user where user = '';"`
if [ $? -eq 0 ]; then
echo "Anonymous account is removed" >> /tmp/mysqlhardening.txt
fi
#Remove shutdown privilege for non root users
b=`mysql -uroot -p$mysqlpass -Dmysql --execute "select user from mysql.user where Shutdown_priv = 'Y';" |grep -v "user" |grep -v "host" |grep -v "root"`
if [ -z "$b" ]; then
echo "No non admin users with shutdown privileges" >> /tmp/mysqlhardening.txt
else
for i in $b
do
mysql -uroot -p$mysqlpass -Dmysql --execute "update mysql.user set Shutdown_priv = 'N' where user='${i}';"
done
echo "removed the shutdown privileges for non admin users" >> /tmp/mysqlhardening.txt
fi
#Remove Create user privilege for non root users
c=`mysql -uroot -p$mysqlpass -Dmysql --execute "select user from mysql.user where Create_user_priv= 'Y';" |grep -v "user" |grep -v "host" |grep -v "root"`
if [ -z "$c" ]; then
echo "No non admin users with create user privileges" >> /tmp/mysqlhardening.txt
else
for i in $c
do
mysql -uroot -p$mysqlpass -Dmysql --execute "update mysql.user set Create_user_priv='N' where user='${i}';"
done
echo "Removed create user priv for non admin users" >> /tmp/mysqlhardening.txt
fi
#Remove Reload privilege for non admin users
d=`mysql -uroot -p$mysqlpass -Dmysql --execute "select user from mysql.user where Reload_priv = 'Y';" |grep -v "user" |grep -v "root"`
if [ -z "$d" ]; then
echo "No non admin users with reload privileges" >> /tmp/mysqlhardening.txt
else
for i in $d
do
mysql -uroot -p$mysqlpass -Dmysql --execute "update mysql.user set Reload_priv='N' where user='${i}';"
done
echo "Removed Reload privileges for non admin users" >> /tmp/mysqlhardening.txt
fi
#Remove GRANT Privileges for non admin users
e=`mysql -uroot -p$mysqlpass -Dmysql --execute "select user from mysql.user where Grant_priv = 'Y';" |grep -v "user" |grep -v "root"`
if [ -z "$e" ]; then
echo "No non admin users with GRANT privileges" >> /tmp/mysqlhardening.txt
else
for i in $e
do
mysql -uroot -p$mysqlpass -Dmysql --execute "update mysql.user set Grant_priv = 'N' where user='${i}';"
done
echo "Removed non admin users with GRANT privileges" >> /tmp/mysqlhardening.txt
fi
#Old password hashing
f=`mysql -uroot -p$mysqlpass -Dmysql --execute "show variables like 'old_passwords';" |grep -v "Variable_name" |grep -v "Value" |awk -F " " '{print $2}'`
if [ `echo "$f" |grep -c "OFF"` -eq 1 ]; then
echo "Old password hashing is disabled already" >> /tmp/mysqlhardening.txt
else
mysql -uroot -p$mysqlpass -Dmysql --execute "set old_passwords = 'OFF';"
echo "old_password is set to OFF" >> /tmp/mysqlhardening.txt
fi
#Remove show database privilege for all non admin users
g=`mysql -uroot -p$mysqlpass -Dmysql --execute "select user from mysql.user where Show_db_priv='Y';" |grep -v "user" |grep -v "root"`
if [ -z "$g" ]; then
echo "No non admin users with Show_db_priv" >> /tmp/mysqlhardening.txt
else
for i in $g
do
mysql -uroot -p$mysqlpass -Dmysql --execute "update mysql.user set Show_db_priv='N' where user='${i}';"
done
fi
#Adding Secure auth in myql config
h=`mysql -uroot -p$mysqlpass -Dmysql --execute "show variables like 'secure_auth';" | grep -v "Variable_name" |grep -v "Value" |awk -F " " '{print $2}'`
if [ `echo "$h" |grep -c "OFF"` -eq 1 ]; then
sed -i "/^\[mysqld\]/ a\\secure_auth" /etc/my.cnf
else
echo "secure_auth is already configured in this DB" >> /tmp/mysqlhardening.txt
fi
#Removing grant tables option in mysql config
j=`mysql -uroot -p$mysqlpass -Dmysql --execute "show variables like 'skip_grant_tables'" |grep -v "Variable_name" |grep "Value" |awk -F " " '{print $2}'`
if [ `echo "$j" |grep -c "OFF"` -eq 1 ]; then
echo "Grant tables option is already disabled in this DB" >> /tmp/mysqlhardening.txt
elif [ `echo "$j" |grep -c "ON"` -eq 1 ]; then
sed -i 's/skip_grant_tables/#skip_grant_tables/g' /etc/my.cnf
else
echo "Grant tables option is not avilable in this DB" >> /tmp/mysqlhardening.txt
fi
#Removing skip merge option from mysql
k=`mysql -uroot -p$mysqlpass -Dmysql --execute "show variables like 'have_merge_engine'" |grep -v "Variable_name" |grep "Value" |awk -F " " '{print $2}'`
if [ `echo "$k" |grep -c "DISABLED"` -eq 1 ]; then
echo "merge option is already disabled in this DB" >> /tmp/mysqlhardening.txt
elif [ `echo "$k" |grep -c "ENABLED"` -eq 1 ]; then
sed -i 's/skip_merge/#skip_merge/g' /etc/my.cnf
else
echo "merge option is not avilable in this DB" >> /tmp/mysqlhardening.txt
fi
#Removing client password from mysql config
r=`cat /etc/my.cnf |grep -A 4 "\[client\]"|grep "#password"`
cl_en=1
if [ `echo "$r" |grep -c "#password"` -eq 1 ]; then
echo "Client password is not enabled in this DB" >> /tmp/mysqlhardening.txt
else
sed -i 's/password/#password/' /etc/my.cnf
fi
#Disable interactive login
l=`cat /etc/passwd |grep mysql | awk -F ":" '{print $7}'`
if [ `echo "$l" |grep -c "/bin/bash"` -eq 1 ]; then
usermod -s /sbin/nologin mysql
echo "interactive login is disabled for mysql user" >> /tmp/mysqlhardening.txt
else
echo "interactive login is already disabled" >> /tmp/mysqlhardening.txt
fi
#Check file system permissions
m=`mysql -uroot -p$mysqlpass -Dmysql --execute "show variables like 'datadir';" |grep -v "variable_name" |grep -v "Value" |awk -F " " '{print $2}'`
n=`stat --format '%a' $m`
if [ `echo "$n" |grep -c "755"` -eq 1 ]; then
echo "permission is already set for datadir" >> /tmp/mysqlhardening.txt
else
chmod 755 $m
echo "permission is reseted for datadir" >> /tmp/mysqlhardening.txt
fi
#Configuration file permission
o=`stat --format '%a' /etc/my.cnf`
if [ `echo "$o" |grep -c "644"` -eq 1 ]; then
echo "configuration file permission is already set for this DB" >> /tmp/mysqlhardening.txt
else
chmod 644 /etc/my.cnf
echo "Configuration file permisison is set to 644" >> /tmp/mysqlhardening.txt
fi
#Removing test database
p=`mysql -uroot -p$mysqlpass --execute "show databases;" |grep "test"`
if [ -z "$p" ]; then
echo "test database is not available in this DB" >> /tmp/mysqlhardening.txt
else
mysql -uroot -p$mysqlpass --execute "drop database test;"
echo "test database is succesfully removed" >> /tmp/mysqlhardening.txt
fi
echo "Hardening of MysqlDB is completed for this server. Please check /tmp/mysqlhardening.txt for details"
