hiyall:

Sorry in advance--it's long, pedantic and preachy. But I'm tired of working on this, so rather than discarding it, I'll just post what I have, and move on...

Yes, chopping down the program to the minimum is always best. First, it helps you clarify the problem in your head better. Often, it clarifies things so much that you figure out the answer and need not post a question! Secondly, it simplifies things for possible respondents--the easier you make it for someone to help you, the more likely someone is likely to help out. Each unnecessary bit of code you have raises the hurdle for someone willing to help.

I typically pass over questions with too much unrelated code, because I don't want to have to do too much analysis to understand the question. Today, I'm home sick and bored, so here I am.. ;^)

Since you already have your data in an array that's easy to work with, you could've posted something more like:

#!/usr/bin/perl -w
use warnings;
use strict;

my @data = (
    # Host         User      DB      Permissions
    [ "localhost", "jma_dbo", "*",   "USAGE, SELECT, INSERT, SHOW VIEW
+" ],

    [ "localhost", "jma_ro", "*",    "USAGE, SELECT" ],
    [ "localhost", "jma_ro", "jma",  "SHOW VIEW" ],
    [ "localhost", "jma_ro", "uat",  "SELECT, INSERT" ],

    [ "localhost", "jma_rw", "jma",  "USAGE, SELECT, INSERT, SHOW VIEW
+" ],

    [ "prod",      "robo",   "*",    "SELECT, SHOW VIEW" ],
    [ "prod",      "robo",   "jma",  "USAGE" ],
);


. . . your attempt to solve the problem . . .
[download]

Since you want to keep it short, you don't need to show *all* the data you have. Just enough to illustrate the problem. Frequently, people leave out data showing any of the special cases you may have. So be sure to review the data and make sure you have at least *one* example of all the special cases you can think of. (I also like to chop out some data (and/or code) to keep the lines from wrapping.)

Then be sure to specify clearly what you're trying to get from the data. Drawing a sample of the output is very helpful, as it can clarify many of your special cases. Be sure to describe your special cases, as well.

Another important point: You're probably very familiar with your data. But don't assume everyone else is as familiar with it as you are. Be sure to describe your problem as if we don't really know what the data is, or what it's for. In this case, you don't really have a database question, you're asking (I think) how to consolidate your data into a report. You don't need to describe what the data *is* so much, but rather what items are what, so when you describe the problem, we can tell what you're saying.

Having said that, I'll state the question that I'm imagining:

I'm trying to create a report of all the permissions in my databases. I've got all the data in an array, but need to figure out how to create a report with it. I want one line of output for each host/user/database combination, and each permission should be in a separate column, all lined up.

In my data, the first three columns are the host, user and database, respectively. The final column is a string (comma separated) representing all the permissions the user has.

The tricky bit is this: Database '*' isn't really a database, it's the default permissions the user has for all databases on the server. I don't want to show a line for the '*' database. Instead, I'd like to make each column show whether the permission is specified for the specific database ('Y'), whether it's a default permission ('**') or whether it's a default permission *and* specified for the database ('Y*').

So how to I merge all this data together into something like this:

host      user    DB  usage select insert show view  ...
--------- ------- --- ----- ------ ------ ---------  ...
localhost jma_dbo jma **    **     **     **
localhost jma_ro  jma **    **            Y
localhost jma_ro  uat **    Y*     Y      
localhost jma_rw  jma Y     Y      Y      Y
prod      robo    jma Y     **            **
 . . .
[download]

So I created part of the desired output, and made sure to include each special case I could think of. Then I edited the data to ensure that the data would support the rows shown. Don't leave much other data in there, or it will make comparison/debugging complex. (I left in a *little* other data in there, but not much.) If your data doesn't generate the part of the report you show, then people will often guess which "hidden rules" you forgot to mention would generate the row you wanted. Don't make us guess.

Finally, here's my answer to the question I imagine you're asking:

Normally, when I need to create a report, and there are interactions between various parts of the data, I'll either transform the data structure I have into a form that makes the report easy to write, or I'll create a new data structure chunk by chunk to represent the report, and then print it out. I've chosen the latter for this case.

As I see it, the difficulties are: (1) make sure you can put a flag in each different permission column, (2) marking whether it's a default, specified or both, and (3) ensuring that all databases the user can access are listed.

I'm suggesting a new data structure as a multilevel hash (keys being HOST, USER and DATABASE), where each entry is an array of the permissions that are set. So I'd first create the hash. Then I'd go over the data and apply any special cases. Finally, when the data is in a suitable shape, I'd print the report, like so:

#!/usr/bin/perl -w
use warnings;
use strict;

my @data = ( 
    ... same as above ...
);

# First scan the data to find out what rows are needed, and the permis
+sions
# columns needed

my %report;   # {HOST}{USER}{DB}
my %DBs;      # {HOST}{DB}        # (see tricky bit below)
my %perms;
my $next_column=0;
for my $row (@data) {
    my ($host, $uid, $db, $tmp) = @$row;

    # Convert permissions string to array of permissions
    $tmp = [ split /,\s*/, $tmp ];

    # Ensure we have a column for each permission
    for my $p (@$tmp) {
        next if exists $perms{$p};
        $perms{$p} = $next_column++;
    }

    $report{$host}{$uid}{$db} = $tmp;

    # Track each database we see
    $DBs{$host}{$db}=0;
}
my $max_perm = scalar(keys %perms) - 1;

# Tricky bit: We want to include rows for databases the user has acces
+s to
# (the USAGE permission) even when they have no database-specific entr
+ies.
# So we'll find all host/user combinations with a '*' database having 
+a
# 'USAGE' permission, and make sure that we have a hash entry with a b
+lank
# list of permissions.  To simplify this, we built the DBs hash in the
+ previous
# step to hold a list of all databases we have on each server
for my $host (keys %report) {
    my @dbs_to_add;
    for my $user (keys %{$report{$host}}) {
        next unless exists $report{$host}{$user}{'*'};
        next unless grep { $_ eq 'USAGE' } values $report{$host}{$user
+}{'*'};

        # OK, we have HOST/USER entry where the user has access to all
+ databases
        # on the server, so add a blank permissions list for all serve
+rs not
        # already on the list
        for my $DB (keys %{$DBs{$host}}) {
            next if exists $report{$host}{$user}{$DB};
            $report{$host}{$user}{$DB} = [ ];
        }
    }
}

# Now we can transform the permissions lists for each row into the sym
+bols we
# want to show in the report
for my $host (keys %report) {
    for my $user (keys %{$report{$host}}) {

        # First set symbol to '' or 'Y'
        for my $DB (keys %{$report{$host}{$user}}) {
            next if $DB eq '*';

            my @symbol = ('') x $max_perm; # default values
            $symbol[$perms{$_}] = 'Y' for @{$report{$host}{$user}{$DB}
+};

            $report{$host}{$user}{$DB} = [ @symbol ];
        }

        # Now add '*' for all default permissions
        next unless exists $report{$host}{$user}{'*'};
        my @defaults = @{$report{$host}{$user}{'*'}};
        for my $DB (keys %{$report{$host}{$user}}) {
            next if $DB eq '*';
            $report{$host}{$user}{$DB}[$perms{$_}] .= '**' for @defaul
+ts;
        }
    }
}

# Print the report

# Header
print "server     user ID  DB   ";
printf "% 2u ", $_+1 for 0 .. $max_perm;
print "\n";

print "---------- -------- ---  ";
print "-- " for 0 .. $max_perm;
print "\n";

# Data
for my $host (sort keys %report) {
    for my $user (sort keys %{$report{$host}}) {
        for my $DB (sort keys %{$report{$host}{$user}}) {
            next if $DB eq '*';

            printf "%-10.10s %-8.8s %-3.3s: ", $host, $user, $DB;

            printf "%-2.2s ", $_ for @{$report{$host}{$user}{$DB}};
            print "\n";
        }
    }
}

print "\nColumns:\n";
for my $perm (sort { $perms{$a} <=> $perms{$b} } keys %perms) {
    printf "% 2u: %s\n", $perms{$perm} + 1, $perm;
}
[download]

Finally, I didn't generate *exactly* the report asked for. I demonstrated the concepts I thought I should, and am leaving the adjustments to you, the reader.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

Hello hiyall,

Feedback and critique is welcome.

roboticus has already provided an excellent answer, so I’ll just comment on one aspect of your code:

my($grant,$db,$schema,$user,$scope) = $results[0] =~ m/.*GRANT\s(.+)\s
+ON\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;
  }
  ...
}
[download]

First, there is no point in testing the length of each string, because the capture groups are all quantified with a +, meaning “one or more” — so if a match succeeds at all (defined is true), the length must be at least 1.

Second, there is no point in separately testing whether each string is defined, as the regex — any regex — either succeeds or it fails, so either all the match strings are defined, or none of them are.¹

Third, note that the trailing /x does nothing here, as you don’t have any whitespace in the regex.

So the above code can be better written like this:

if (my ($grant, $db, $schema, $user, $scope) = $results[0] =~ /.*GRANT
+\s(.+)\sON\s(.+)\.(.+)\sTO\s(\S+)\@(\S+)/i)
{
    $db     =~ tr/'`//d;
    $schema =~ tr/'`//d;
    $user   =~ tr/'`//d;
    $scope  =~ tr/'`//d;
    ...
}
[download]

¹Update: Except in the cases where the captures are optional, either because zero matches are allowed: X?, X*, X{0,5}; or because the captures are part of an alternation: (?:(X)|(Y)). Thanks to AnomalousMonk for explaining this, below.

Hope that helps,

... any regex ... either succeeds or it fails, so either all the match strings are defined, or none of them are.

A minor cavil: capture groups that are not required to match for an overall match will return undef in their respective $n capture variables and in list context.

c:\@Work\Perl\monks>perl -wMstrict -MData::Dump -le
"my $s = 'abc';
 ;;
 my @captures = $s =~ m{ (?: (a)(b)(c) | (w)(x)(y)) (z)? }xms;
 dd \@captures;
"
["a", "b", "c", undef, undef, undef, undef]
[download]

The  (?|pattern) construct available with Perl version 5.10 (see Extended Patterns in perlre) modifies this behavior in alternations, but you may still be left with undefs from other sources.

c:\@Work\Perl\monks>perl -wMstrict -MData::Dump -le
"use 5.010;
 ;;
 my $s = 'abc';
 ;;
 my @captures = $s =~ m{ (?| (a)(b)(c) | (w)(x)(y)) (z)? }xms;
 dd \@captures;
"
["a", "b", "c", undef]
[download]

---

Give a man a fish:  <%-(-(-(-<

A basic example for you to adapt/enhance as required

#!perl
use DBI; 
use strict; 
use warnings; 
use Data::Dump 'pp';

my $dbh = get_dbh(); # connect

# use hash to group data
my %logins=();
my $sql = 'SHOW GRANTS';
my $sth = $dbh->prepare($sql);
$sth->execute();
while (my ($result) = $sth->fetchrow_array()) {
  $result =~ s/['`]//g;
  if ($result =~ m/GRANT (.+?) ON (.+?) TO (\S+\@\S+)/i){
    push @{$logins{$3}{$2}},$1;
  }; 
}
pp \%logins;

# build array from hash
my @logins=();
for my $userhost (sort keys %logins){
  for my $db (sort keys %{$logins{$userhost}}){
    # join into one line here
    my $grant = join ',',@{$logins{$userhost}{$db}};
    my ($user,$host) = split '@',$userhost;
    push @logins,[$host,$user,$db,$grant];
  }
}
pp \@logins;
[download]

See I know what I mean. Why don't you? for an answer to "Is this code too complex for the forum, or would it be preferred that I post a much simpler example". Note in particular the bit about database examples - you can use DBD::CSV in place of DBD::mysql with only slight changes to the sample code.