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"