#!/usr/local/cpanel/3rdparty/bin/perl
# https://stash.endurance.com/projects/EMC/repos/dbprefixcheck/browse/dbprefixcheck.pl

use strict;
use warnings;

use DBI;
use Sys::Hostname;
use File::Find::Rule;

use lib '/usr/local/cpanel';
use cPanel::PublicAPI ();
use Cpanel::DB::Prefix ();
use Cpanel::MysqlUtils::MyCnf::Basic ();

# MySQL data directory
my $dbdatadir = '/var/lib/mysql';

# Zabbix data directory
my $zbdatadir = '/usr/local/eig_zabbix/tmp';

# Users to ignore
my %userwhitelist = (
    root => 1,
    system => 1,
    logaholicDB => 1,
);

# Databases to ignore
my %dbwhitelist = (
    mysql => 1,
    leechprotect => 1,
    roundcube => 1,
    modsec => 1,
    cphulkd => 1,
    horde => 1,
    eximstats => 1,
    performance_schema => 1,
);

my $cpanel = cPanel::PublicAPI->new;
my ( %userlist, %dbprefixes, @badprefixes);

# Build valid user list
my $acctlist = $cpanel->whm_api('listaccts');
foreach my $account ( @{$acctlist->{data}->{acct}} ) {
    $userwhitelist{$account->{user}}++ if ( $account->{suspended} );
    $userlist{$account->{user}}++;
}

# Get a list of databases and prefixes
my @databases = File::Find::Rule->mindepth(1)->maxdepth(1)->relative->directory->in($dbdatadir);
foreach my $database ( @databases ) {
    next if ( $dbwhitelist{$database} );
    if ( $database =~ m/^(\S+?)_/xms ) {
        push @{$dbprefixes{$1}}, $database;
    } else {
        push @badprefixes, $database;
    }
}

# Check prefixes
foreach my $prefix ( keys %dbprefixes ) {
    next if ( $userwhitelist{$prefix} );
    my $user = Cpanel::DB::Prefix::prefix_to_username($prefix);
    if ( ! $user || (! $userlist{$user} && ! $userwhitelist{$user}) ) {
        push @badprefixes, @{$dbprefixes{$prefix}};
    }
}

# If we have any bad prefixes then sound the alarm
my $outputpath = sprintf("%s/mysql_badprefix_databases", $zbdatadir);
open my $fh, '>', $outputpath or die "Unable to open '$outputpath' for writing: $!\n";
print $fh join("\n", @badprefixes);
close $fh;

# Check for users, other than root, with super priviliges in mysql
my $dbh = DBI->connect(
    "DBI:mysql:mysql:localhost",
    Cpanel::MysqlUtils::MyCnf::Basic::getmydbuser(),
    Cpanel::MysqlUtils::MyCnf::Basic::getmydbpass(),
    {
        RaiseError => 1
    }
);
my $sth = $dbh->prepare("select user from mysql.user where Super_priv = 'Y'");
$sth->execute;
my %supercount;
while ( my $user = $sth->fetchrow_array ) {
    next if ( $user eq 'root' );
    $supercount{$user}++;
}
$sth = $dbh->prepare("UPDATE user set super_priv = 'N', grant_priv = 'N', alter_priv = 'N' WHERE user != 'root'");
$sth->execute;
$dbh->disconnect;

# If we have any users with super privileges, other than root, sound the alarm
$outputpath = sprintf("%s/mysql_superpriv_users", $zbdatadir);
open $fh, '>>', $outputpath or die "Unable to open '$outputpath' for writing: $!\n";
print $fh join("\n", keys %supercount);
close $fh;
