#!/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;

##</code><code>##


$VAR1 = [
  "localhost",
  "jma_rw",
  "*",
  "USAGE"
];
$VAR2 = [
  "localhost",
  "jma_rw",
  "jma",
  "SELECT, INSERT, UPDATE, DELETE, SHOW VIEW"
];
$VAR3 = [
  "localhost",
  "jma_ro",
  "*",
  "USAGE"
];
$VAR4 = [
  "localhost",
  "jma_ro",
  "jma",
  "SELECT, SHOW VIEW"
];
$VAR1 = [
  "localhost",
  "jma_rw",
  "*",
  "USAGE"
];
$VAR2 = [
  "localhost",
  "jma_rw",
  "jma",
  "SELECT, INSERT, UPDATE, DELETE, SHOW VIEW"
];
$VAR3 = [
  "localhost",
  "jma_ro",
  "*",
  "USAGE"
];
$VAR4 = [
  "localhost",
  "jma_ro",
  "jma",
  "SELECT, SHOW VIEW"
];
$VAR5 = [
  "localhost",
  "jma_dbo",
  "*",
  "USAGE"
];
$VAR6 = [
  "localhost",
  "jma_dbo",
  "jma",
  "SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER"
];


##</code><code>##

CREATE DATABASE "drupal"
	DEFAULT CHARACTER SET latin1
	DEFAULT COLLATE latin1_swedish_ci;
CREATE DATABASE "jma"
	DEFAULT CHARACTER SET latin1
	DEFAULT COLLATE latin1_swedish_ci;
GRANT USAGE ON *.* TO "drupal_dbo"@'%' IDENTIFIED BY '<password>' ;
GRANT SELECT ON *.* TO "drupal_dbo"@'%' ;
GRANT INSERT ON *.* TO "drupal_dbo"@'%' ;
GRANT UPDATE ON *.* TO "drupal_dbo"@'%' ;
GRANT DELETE ON *.* TO "drupal_dbo"@'%' ;
GRANT ALTER ON *.* TO "drupal_dbo"@'%' ;
GRANT USAGE ON *.* TO "drupal_ro"@'%' IDENTIFIED BY '<password>' ;
GRANT SELECT ON *.* TO "drupal_ro"@'%' ;
GRANT USAGE ON *.* TO "drupal_rw"@'%' IDENTIFIED BY '<password>' ;
GRANT SELECT ON *.* TO "drupal_rw"@'%' ;
GRANT INSERT ON *.* TO "drupal_rw"@'%' ;
GRANT UPDATE ON *.* TO "drupal_rw"@'%' ;
GRANT DELETE ON *.* TO "drupal_rw"@'%' ;
GRANT USAGE ON *.* TO "jma_dbo"@'%' IDENTIFIED BY '<password>' ;
GRANT USAGE ON *.* TO "jma_ro"@'%' IDENTIFIED BY '<password>' ;
GRANT USAGE ON *.* TO "jma_rw"@'%' IDENTIFIED BY '<password>' ;
GRANT USAGE ON *.* TO "monitor"@'localhost' IDENTIFIED BY '<password>' ;
GRANT USAGE ON *.* TO "monitor"@'%' IDENTIFIED BY '<password>' ;
GRANT USAGE ON *.* TO "root"@'localhost' IDENTIFIED BY '<password>'  WITH GRANT OPTION;
GRANT SELECT ON *.* TO "root"@'localhost' ;
GRANT INSERT ON *.* TO "root"@'localhost' ;
GRANT UPDATE ON *.* TO "root"@'localhost' ;
GRANT DELETE ON *.* TO "root"@'localhost' ;
GRANT CREATE ON *.* TO "root"@'localhost' ;
GRANT DROP ON *.* TO "root"@'localhost' ;
GRANT GRANT ON *.* TO "root"@'localhost' ;
GRANT REFERENCES ON *.* TO "root"@'localhost' ;
GRANT INDEX ON *.* TO "root"@'localhost' ;
GRANT ALTER ON *.* TO "root"@'localhost' ;
GRANT RELOAD ON *.* TO "root"@'localhost' ;
GRANT SHUTDOWN ON *.* TO "root"@'localhost' ;
GRANT PROCESS ON *.* TO "root"@'localhost' ;
GRANT FILE ON *.* TO "root"@'localhost' ;
GRANT SHOW DATABASES ON *.* TO "root"@'localhost' ;
GRANT SUPER ON *.* TO "root"@'localhost' ;
GRANT CREATE TEMPORARY TABLES ON *.* TO "root"@'localhost' ;
GRANT LOCK TABLES ON *.* TO "root"@'localhost' ;
GRANT EXECUTE ON *.* TO "root"@'localhost' ;
GRANT REPLICATION SLAVE ON *.* TO "root"@'localhost' ;
GRANT REPLICATION CLIENT ON *.* TO "root"@'localhost' ;
GRANT CREATE VIEW ON *.* TO "root"@'localhost' ;
GRANT SHOW VIEW ON *.* TO "root"@'localhost' ;
GRANT CREATE ROUTINE ON *.* TO "root"@'localhost' ;
GRANT ALTER ROUTINE ON *.* TO "root"@'localhost' ;
GRANT CREATE USER ON *.* TO "root"@'localhost' ;
GRANT TRIGGER ON *.* TO "root"@'localhost' ;
GRANT EVENT ON *.* TO "root"@'localhost' ;
GRANT CREATE TABLESPACE ON *.* TO "root"@'localhost' ;