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

##</code><code>##

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     **            **
 . . .

##</code><code>##

#!/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 permissions
# 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 access to
# (the USAGE permission) even when they have no database-specific entries.
# 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 blank
# 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 servers 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 symbols 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 @defaults;
        }
    }
}

# 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;
}