Perl script for checking user security in MySQL

This script is used to check the user security in MySQL server.
Prerequisites: Perl modules : DBI, Getopt::Long and Text::Table

Script will check following details from mysql grant tables.

# Looks for anonymous user accounts
# Looks for accounts accessible from any host
# Looks for accounts with empty password
# Looks for accounts with identical (non empty) passwords
# Looks for accounts with admin privileges
# Looks for accounts with data definition privileges
# Looks for accounts with schema data definition privileges
# Looks for accounts with global data manipulation privileges
# Looks for accounts with write privileges on the mysql schema

==============================================================

#!/usr/bin/perl

# Created Date : 17-07-2011

# Created By : Ramesh

# User Security for MySQL

use DBI;

use Getopt::Long;

use Text::Table;

sub usage {

print <rule(qw/- +/);

my @arr = $tb->body;

print $rule, $tb->title, $rule;

for (@arr) {

print $_;

}

print $rule;

}

my $dbh = DBI->connect(“DBI:mysql:database=information_schema:host=$opts{host}”, $opts{usr},$opts{password})

or die “Use –help for more optionsn”;

#Looking for anonymous user accounts

my $ano_user = $dbh->prepare(“SELECT user,host FROM mysql.user WHERE user=””);

$ano_user->execute();

if ($ano_user->rows()){

print “nList of anonymous users in the databasesnn”;

$tb = Text::Table->new(‘| ‘,”User “,’ |’,’| ‘,”Host “,’ |’);

while(($user,$host)=$ano_user->fetchrow_array){

$tb->load(

[$user,$host]

);

}

call_tb();

}

#Looking for accounts accessible from any host

my $com_host = $dbh->prepare(“SELECT user,host FROM mysql.user WHERE host=’%'”);

$com_host->execute();

if ($com_host->rows()){

print “nList of accounts accessible from any hostnn”;

$tb = Text::Table->new(‘| ‘,”User “,’ |’,’| ‘,”Host “,’ |’);

while(($user,$host)=$com_host->fetchrow_array){

$tb->load(

[$user,$host]

);

}

call_tb();

}

#Looking for accounts with empty password

my $empty_pass = $dbh->prepare(“SELECT user,host FROM mysql.user WHERE password=””);

$empty_pass->execute();

if ($empty_pass->rows()){

print “nList of accounts with empty passwordnn”;

$tb = Text::Table->new(‘| ‘,”User “,’ |’,’| ‘,”Host “,’ |’);

while(($user,$host)=$empty_pass->fetchrow_array){

$tb->load(

[$user,$host]

);

}

call_tb();

}

#Looking for accounts with identical (non empty) passwords

my $identical_pass = $dbh->prepare(‘SELECT CONCAT(user,”@”, host) AS account, pass FROM (SELECT user1.user, user1.host, user2.user AS u2, user2.host AS h2, left(user1.password,5) as pass FROM mysql.user AS user1 INNER JOIN mysql.user AS user2 ON (user1.password = user2.password) WHERE user1.user != user2.user AND user1.password != “”) users GROUP BY CONCAT(user,”@”,host) ORDER BY pass’);

$identical_pass->execute();

if ($identical_pass->rows()){

print “nList of accounts with identical (non empty) passwordsnn”;

$tb = Text::Table->new(‘| ‘,”Account “,’ |’,’| ‘,”Password “,’ |’);

while(($account,$pass)=$identical_pass->fetchrow_array){

$tb->load(

[$account,$pass]

);

}

call_tb();

}

#Looking for accounts with admin privileges

my $admin_priv = $dbh->prepare(‘SELECT GRANTEE, GROUP_CONCAT(PRIVILEGE_TYPE) AS privileges FROM information_schema.USER_PRIVILEGES WHERE PRIVILEGE_TYPE IN (“SUPER”, “SHUTDOWN”, “RELOAD”, “PROCESS”, “CREATE USER”, “REPLICATION CLIENT”, “REPLICATION SLAVE”) GROUP BY GRANTEE’);

$admin_priv->execute();

if ($admin_priv->rows()){

print “nList of (non-root) accounts with admin privilegesnn”;

$tb = Text::Table->new(‘| ‘,”GRANTEE “,’ |’,’| ‘,”PRIVILEGES “,’ |’);

while(($grantee,$privileges)=$admin_priv->fetchrow_array){

$tb->load(

[$grantee,$privileges]

);

}

call_tb();

}

#Looking for accounts with data definition privileges

my $ddl_priv = $dbh->prepare(‘SELECT GRANTEE, GROUP_CONCAT(PRIVILEGE_TYPE) AS privileges FROM information_schema.USER_PRIVILEGES WHERE PRIVILEGE_TYPE IN (“CREATE”, “DROP”, “EVENT”, “ALTER”, “INDEX”, “TRIGGER”, “CREATE VIEW”, “ALTER ROUTINE”, “CREATE ROUTINE”) GROUP BY GRANTEE’);

$ddl_priv->execute();

if ($ddl_priv->rows()){

print “nList of (non-root) accounts with data definition privilegesnn”;

$tb = Text::Table->new(‘| ‘,”GRANTEE “,’ |’,’| ‘,”PRIVILEGES “,’ |’);

while(($grantee,$privileges)=$ddl_priv->fetchrow_array){

$tb->load(

[$grantee,$privileges]

);

}

call_tb();

}

#Looking for accounts with schema data definition privileges

my $schema_ddl_priv = $dbh->prepare(‘SELECT GRANTEE, GROUP_CONCAT(PRIVILEGE_TYPE) AS privileges, TABLE_SCHEMA FROM information_schema.SCHEMA_PRIVILEGES WHERE PRIVILEGE_TYPE IN (“CREATE”, “DROP”, “EVENT”, “ALTER”, “INDEX”, “TRIGGER”, “CREATE VIEW”, “ALTER ROUTINE”, “CREATE ROUTINE”) GROUP BY GRANTEE’);

$schema_ddl_priv->execute();

if ($schema_ddl_priv->rows()){

print “nList of (non-root) accounts with schema data definition privilegesnn”;

$tb = Text::Table->new(‘| ‘,”GRANTEE “,’ |’,’| ‘,”PRIVILEGES “,’ |’,’| ‘,”TABLE_SCHEMA”,’ |’);

while(($grantee,$privileges,$table_schema)=$schema_ddl_priv->fetchrow_array){

$tb->load(

[$grantee,$privileges,$table_schema]

);

}

call_tb();

}

# Looking for accounts with global data manipulation privileges

my $global_dml_priv = $dbh->prepare(‘SELECT GRANTEE, GROUP_CONCAT(PRIVILEGE_TYPE) AS privileges FROM information_schema.USER_PRIVILEGES WHERE PRIVILEGE_TYPE IN (“DELETE”, “INSERT”, “UPDATE”, “CREATE TEMPORARY TABLES”) GROUP BY GRANTEE’);

$global_dml_priv->execute();

if ($global_dml_priv->rows()){

print “nList of (non-root) accounts with global data manipulation privilegesnn”;

$tb = Text::Table->new(‘| ‘,”GRANTEE “,’ |’,’| ‘,”PRIVILEGES “,’ |’);

while(($grantee,$privileges)=$global_dml_priv->fetchrow_array){

$tb->load(

[$grantee,$privileges]

);

}

call_tb();

}

# Looking for accounts with write privileges on the mysql schema

my $mysql_write_priv = $dbh->prepare(‘SELECT GRANTEE, GROUP_CONCAT(PRIVILEGE_TYPE) AS privileges, TABLE_SCHEMA FROM information_schema.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA=”mysql” AND PRIVILEGE_TYPE IN (“CREATE”, “DROP”, “EVENT”, “ALTER”, “INDEX”, “TRIGGER”, “CREATE VIEW”, “ALTER ROUTINE”, “CREATE ROUTINE”,”DELETE”, “INSERT”, “UPDATE”, “CREATE TEMPORARY TABLES”) GROUP BY GRANTEE’);

$mysql_write_priv->execute();

if ($mysql_write_priv->rows()){

print “nList of (non-root) accounts with write privileges on the mysql schemann”;

$tb = Text::Table->new(‘| ‘,”GRANTEE “,’ |’,’| ‘,”PRIVILEGES “,’ |’);

while(($grantee,$privileges)=$mysql_write_priv->fetchrow_array){

$tb->load(

[$grantee,$privileges]

);

}

call_tb();

}

5 thoughts on “Perl script for checking user security in MySQL”

  1. Thanks for the script. I would like to use it in MySQL server, but i am not familiar with Perl scripts. I will appreciate if someone could help me on: (1) which format should i save the script to? (2) where do i get the Perl modules : DBI, Getopt::Long and Text::Table? where and how should i run it?
    Thanks for your time and consideration.
    Peter

    1. Hi Peter,

      My answers are inline.

      (1) which format should i save the script to?
      you have to save that script with .pl extension.

      (2) where do i get the Perl modules : DBI, Getopt::Long and Text::Table?
      you should install perl modules using CPAN. Check out this link.
      http://www.thegeekstuff.com/2008/09/how-to-install-perl-modules-manually-and-using-cpan-command/

      where and how should i run it?
      you can run in from your dir where you have created the script with “perl” command.

      i.e shell> perl script.pl

  2. Thanks nilnandan. I work as an IT auditor and I was planning to use the Perl Script to check users security on a MySQL server from a client. Therefore, from what I understood on your reply:
    1- I will have to install the Perl modules in Linux into the MySQL server (let´s see how easy it is)
    2- Run the Perl Script from the shell and I am assuming it ill create a file with the output, right?

    My question is, since the MySQL server belongs to my client, should i be aware of any potential problem that installing the Perl modules and running the Perl Script may cause? I don’t want to make any changes on the server configuration or anything that may cause a problem in the future for MySQL server.

    Thanks a lot for your time and consideration,
    peter

  3. Hi Nilnandan,

    When I run your script its giving me
    Unterminated operator at line 17

    There is anything wrong with the line print <rule(qw/- +/); ?

    1. add “>” like this:
      print ;

      and now run well until this:
      Unrecognized character xE2; marked by connect(<– HERE near column 24 at 33

Leave a Reply

Your email address will not be published. Required fields are marked *