#!/usr/bin/perl -w
use strict;
use DBI;
use Data::Dumper;

$Data::Dumper::Indent = 1;
$Data::Dumper::Sortkeys = 1;
$Data::Dumper::Useqq = 1;


#USAGE BLOCK:
# (1) quit unless we have the correct number of command-line args
#my $num_args = $#ARGV + 1;
#if ($num_args != 1) {
#  print "\nUsage: jmagetUsers mysql_server\n";
#  exit;
#}

#CHANGES BLOCK:
#20140520       jma     Created DBDsample script

#DECLARATIONS
my $SERVER = "localhost";
#my $BASEDIR = "/data/timeinc/servers/mysql";
#my $COMMONDIR = "$BASEDIR/common";
#my $SERVERDIR = "$BASEDIR/$SERVER";
#my $CONTROLDIR = "$COMMONDIR/control";
#my $PWF = "$CONTROLDIR/\.passwdfile";
#my $IFF = "$COMMONDIR/mysql_interfaces";
my $user = "user";
my $database = "mysql";

my %login;
my $lc = 0;

my @logins = ();

my $pw = "pw";
my $host = "localhost";
my $port = 3301;

#Uncomment to validate sub are working correctly
#print "PW is $pw, Host is $host, Port is $port, Server is $SERVER\n";

#Open connection to server
my $dsn = "DBI:mysql:database=$database:host=$host:port=$port";
my $dbh = DBI->connect($dsn, $user, $pw);
my $server = "localhost";

#Prepare query
#  Get applicable databases that contain the cache_form table
my $sth = $dbh->prepare(qq(select distinct user,host from mysql.user where user like "jma%";));
$sth->execute();

## Retrieve the results of a row of data and print
#print "\tQuery results:\n================================================\n";

while (my @rows = $sth->fetchrow_array()) {
#    print "@rows\n";
    $login{$lc}{user} = $rows[0];
    $login{$lc}{scope} = $rows[1];
    $lc = $lc + 1;
}
warn "Problem in retrieving results", $sth->errstr( ), "\n"
        if $sth->err( );
$sth->finish();

#print Dumper \%login;

#print "DUMPER ENDS\n";

foreach my $line (keys %login) {
# print qq('$login{$line}->{user}'\@'$login{$line}->{scope}'\n);
my $sql = qq( SHOW GRANTS for '$login{$line}->{user}';);
my $sth = $dbh->prepare($sql);
$sth->execute();
while (my @results = $sth->fetchrow_array()) {
#   print "$SERVER: @results\n";
#  print "$results[0]\n";
  my($grant,$db,$schema,$user,$scope) = $results[0] =~ m/.*GRANT\s(.+)\sON\s(.+)\.(.+)\sTO\s(\S+)\@(\S+)/ix;
  if (defined $user && length $user > 0) {
    if (defined $db && length $db > 0) {
      $db =~ tr/'`//d;
    }
    if (defined $schema && length $schema > 0) {
      $schema =~ tr/'`//d;
    }
    if (defined $user && length $user > 0) {
      $user =~ tr/'`//d;
    }
    if (defined $scope && length $scope > 0) {
      $scope =~ tr/'`//d;
    }
    push @logins, [$server, $user, $db, $grant];
    #print "SERVER = $server: GRANT = $grant: DB = $db: SCHEMA = $schema: USER = $user: SCOPE: $scope\n";
    #if ($grant =~ m/SELECT/i) {
    #  push @logins, [$server, $user, $db, $grant];
    #}
    #if ($grant =~ m/USAGE/i) {
    #  push @logins, [$server, $user, $db, $grant];
    #}
    #if ($grant =~ m/ALL/i) {
    #  push @logins, [$server, $user, $db, $grant];
    #}

  }
}
print Dumper @logins;
}
exit;