Detecting malicious database activity

I was talking to a customer yesterday and the customer was surprised that database backups can be used to detect malicious database activity from legitimate users and hackers.

ZRM for MySQL stores MySQL binary logs as part of the database backups. The binary logs provide a good audit trail of all database activity. ZRM for MySQL binary log parsing capability, usually used for selective point in time recovery, can also be used to detect malicious database activity using SQL inspection.

ZRM for MySQL plugin interface allows DBAs to write log parser plugin scripts to track the database activity that they are interested in. For example: the following script can be used to detect deletion of data from the PRODUCTS table in the database. This script prints all instances of deletion from PRODUCTS table.

@fields = split ( / \| /, $ARGV[0] );

my $SQL_VERB=”DELETE”;
my $TABLE_NAME=”PRODUCTS”;

if (($fields[3] == “Query”) && \
( $fields[4] =~ /$SQL_VERB *FROM.*`$TABLE_NAME`/ )) {
print “$fields[2] $fields[4]\n”;
}

The mysql-zrm command can be used to run the customized plugin script for all backups or a specific backup image. The following command looks for deletion from PRODUCTS table on a backup image dated Dec 5, 2006.

# mysql-zrm –action parse-binlogs \
–source-directory /var/lib/mysql-zrm/pricebook/20061205142103 \
–parse-binlogs-plugin /usr/share/mysql-zrm/plugins/detect_deletion.pl

The output of the command will contain valid deletions as well as malicious database deletions.

Yet another use for ZRM of MySQL.

Comments are closed.