Monitor Audit MySQL Users Logon Script

I was searching all over to find a script to effectively monitor or audit users logging in to MySQl databases. There may be more scripts out there or tools that you can buy to audit users logging in to your MYSQL databases, but I can tell you, the below script that I decided to write, effectively monitors 30+ MySQL databases within 5 seconds.

So let’s begin monitoring our MySQL databases of unauthorized user logons

Create a file called:

servers.txt
and add all of your server names or DNS hostnames. One IP or hostname per line.

run_mysql_audit
Create a file with the below in it and name it run_mysql_audit. You will need to edit the values for your environment. I would suggest to create a folder and place all the MySQL audit scripts in it that are created below and make this folder the “FP” folder path in the script below.

#!/bin/bash
FP="/folder/to/scripts"
SUBJECT="Unauthorized User MySQL Access $servername"
PEOPLE="mysql@MyDomain.com"

# Lets use our audit account to show processlist
for servername in `cat $FP/servers.txt`; do
mysql -uaudit -paudit -h$servername -e "show processlist" >> $FP/unauthorized_user.txt; echo $servername >> $FP/unauthorized_user.txt; date >> $FP/unauthorized_user.txt; echo Unauthorized access!!! >> $FP/unauthorized_user.txt;

# Create Rules of not to alert on.
sed -e /localhost/d -e /10.73.3.*/d -e /MyPCname/d $FP/unauthorized_user.txt > $FP/unauthorized_user2.txt

SUBJECT="Unauthorized User MySQL Access $servername"

#Create Rules to alert on and mail!
if more $FP/unauthorized_user2.txt | grep -E 'root|pcname|anotheruser|etc' $FP/unauthorized_user2.txt; then
mutt -s "$SUBJECT" $PEOPLE < $FP/unauthorized_user.txt touch $FP/pause.pid else # Added html to build into a web portal. echo ""; date; echo "
No Alert for $servername
"

fi

rm -f $FP/unauthorized_user.txt
done

# Nothing found, lets audit again in 10 seconds
echo "** 10 seconds before next audit. **"
sleep 10

# We found someone breaking a rule, allow 15 minutes before we audit again.
if [ -f $FP/pause.pid ]; then
echo "*** Stopping for 15 minutes to allow unauthorized user to log out. ***"
sleep 900

rm -f $FP/pause.pid
. $FP/run_mysql_audit.sh

else

. $FP/run_mysql_audit.sh

Close and save your file. After you have done all of the above steps. To start, you can run the following

nohup ./run_mysql_audit >> /path/to/store/audit.log

Once started, you can “tail -f audit.log” to see exactly what is going on.

Jason

, , , , , ,

Comments are closed.