comment on

I am attempting to produce a report of users and their permissions from any mysql server (defined by user,host,port) where the report has one line per user.

Extracting the raw information, is done by the code here. I am now trying to rollup the permissions into an array for each user. I am confused and stuck as to how to do this at this time.

Is this code too complex for the forum, or would it be preferred that I post a much simpler example (without the need to connect to mysql for example)

Feedback and critique is welcome. That and my practice are the path to building my skill base

for example, desired results would have one line output for "localhost" server, jma_rw" user with grants array of qq(SELECT, INSERT, UPDATE, DELETE, SHOW VIEW, USAGE) and not 2 lines.

#!/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 w
+here 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 = $sche
+ma: 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;
[download]

UnSummarized Results


$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, AL
+TER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CR
+EATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER"
];
[download]

How to recreate permissions, users, dbs used in code exammple

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>'  W
+ITH 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' ;
[download]

In reply to Hurdle with summarizing results from complex data structure related to MySQL result handling by hiyall

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.