#!/usr/local/bin/perl
# 
# $Header: esaPrivReports.pl 03-aug-2005.21:35:08 dsukhwal Exp $
#
# esaPrivReports.pl
# 
# Copyright (c) 2005, Oracle. All rights reserved.  
#
#    NAME
#      esaPrivReports.pl - <one-line expansion of the name>
#
#    DESCRIPTION
#      implement SYS_CONNECT_BY_PATH like output in 8i databases
#
#    NOTES
#      <other useful comments, qualifications, etc.>
#
#    MODIFIED   (MM/DD/YY)
#    dsukhwal    08/03/05 - dsukhwal_8i_reports
#    dsukhwal    08/03/05 - Creation
# 
  
  require "emd_common.pl";
  require "semd_common.pl";
  require "$ENV{EMDROOT}/sysman/admin/scripts/db/esaDbUtils.pl";
  require "$ENV{EMDROOT}/sysman/admin/scripts/db/esaUtils.pl";
  my %stdinArgs = get_stdinvars();
  my $username = $stdinArgs{"EM_TARGET_USERNAME"};
  my $password = $stdinArgs{"EM_TARGET_PASSWORD"};
  my $oracleHome = $ENV{EM_TARGET_ORACLE_HOME};
  my $oracleOwner;
  my $dsn = "dbi:Oracle:" ;
  my $address = $ENV{EM_TARGET_ADDRESS};
  my $role = $ENV{EM_TARGET_ROLE};
  my $mode = 0;
  my $maxRows = 200;
  my $numRows = 0;

  if($role =~ /SYSDBA/i)
  {
    $mode = 2;
  }
  elsif($role =~ /SYSOPER/i)
  {
    $mode = 4;
  }

 my $table = "v\$database";  
 my $value = "log_mode";
 my $dbh = open_db_connection("dbi:Oracle:", "$username@".$address,$password,$mode); 
 my $sth; #to be used as a statement handle
 
if($ARGV[0] eq 'EXEMPT_ACCESS_POLICY'){
     $sth = $dbh->prepare_cached('select p, c from (
        select null   p, name   c from system_privilege_map where name = \'EXEMPT ACCESS POLICY\'  union
        select granted_role p,grantee c from dba_role_privs  union
        select privilege p,grantee c from dba_sys_privs
      )
                where ((c = \'PUBLIC\') or 
                         (exists (select \'w\' from dba_users where username=c))or 
                         (exists (select \'w\' from dba_roles where role=c))) and 
                     rownum < 1000
    start with p is null connect by p = prior c')
    or die print "em_error=Couldn't prepare statement: $dbh->errstr"; 
    $sth->execute() 
    or die print "em_error=Couldn't execute statement: $sth->errstr";
}
elsif($ARGV[0] eq 'DBA_ROLE'){
    $sth = $dbh->prepare_cached('select p,c from (
        select null   p, role c from dba_roles where role in  (\'DBA\')  union
        select granted_role  p, grantee c from dba_role_privs  union
        select privilege p, grantee c from dba_sys_privs
      )
      where rownum < 1000 AND p is not null
    start with p is null connect by p = prior c')
    or die print "em_error=Couldn't prepare statement: $dbh->errstr"; 
    $sth->execute() 
    or die print "em_error=Couldn't execute statement: $sth->errstr";
}
elsif($ARGV[0] eq 'CREATE_PRIVILEGE'){
    $sth = $dbh->prepare_cached('select p, c from (
           select null p, name   c from system_privilege_map  where name like \'%CREATE%\'  union
           select granted_role  p, grantee c from dba_role_privs  union
           select privilege p, grantee c from dba_sys_privs
      )
      where rownum < 1000 AND p is not null
    start with p is null connect by p = prior c')
    or die print "em_error=Couldn't prepare statement: $dbh->errstr"; 
    $sth->execute() 
    or die print "em_error=Couldn't execute statement: $sth->errstr";
}
elsif($ARGV[0] eq 'CATALOG_ROLE'){
    $sth = $dbh->prepare_cached('select p,c from  (
           select null p, role c from dba_roles where role like \'%_CATALOG_%\'   union
           select granted_role p, grantee c from dba_role_privs   union
           select privilege p, grantee c from dba_sys_privs
      )
      where rownum < 1000 AND p IS NOT NULL
    start with p is null connect by p = prior c')
    or die print "em_error=Couldn't prepare statement: $dbh->errstr"; 
    $sth->execute() 
    or die print "em_error=Couldn't execute statement: $sth->errstr";
}
elsif($ARGV[0] eq 'BECOME_USER'){
    $sth = $dbh->prepare_cached('select distinct p,c from  (
           select null p, name c from system_privilege_map where name = \'BECOME USER\'  union
           select granted_role p, grantee c from dba_role_privs  union
           select privilege p, grantee c from dba_sys_privs
      )
      where rownum < 1000 AND p is not null
    start with p is null connect by p = prior c')
    or die print "em_error=Couldn't prepare statement: $dbh->errstr"; 
    $sth->execute() 
    or die print "em_error=Couldn't execute statement: $sth->errstr";
}
elsif($ARGV[0] eq 'AUDIT_SYSTEM'){
    $sth = $dbh->prepare_cached('select distinct p, c from (
            select null p, name c from system_privilege_map where name = \'AUDIT SYSTEM\'  union
            select granted_role p,grantee  c from dba_role_privs  union
            select privilege p, grantee c from dba_sys_privs
      ) 
      where rownum < 1000 and p is not null
    start with p is null connect by p = prior c')
    or die print "em_error=Couldn't prepare statement: $dbh->errstr"; 
    $sth->execute() 
    or die print "em_error=Couldn't execute statement: $sth->errstr";
}
elsif($ARGV[0] eq 'ANY_DICTIONARY'){
    $sth = $dbh->prepare_cached('select p, c from (
            select null p, name c from system_privilege_map where name like \'%ANY DICTIONARY%\'  union
            select granted_role p, grantee c from dba_role_privs  union
            select privilege p, grantee c from dba_sys_privs
      )
      where rownum < 1000 and p is not null
    start with p is null connect by p = prior c')
    or die print "em_error=Couldn't prepare statement: $dbh->errstr"; 
    $sth->execute() 
    or die print "em_error=Couldn't execute statement: $sth->errstr";
}
elsif($ARGV[0] eq 'CONNECT_PRIVILEGE'){
    $sth = $dbh->prepare_cached('select distinct p, c from (
            select null p, role c from dba_roles where role in (\'RESOURCE\', \'CONNECT\')  union
            select granted_role p, grantee  c from dba_role_privs  union
            select privilege p, grantee c from dba_sys_privs
      )
      where rownum < 1000 and p is not null
    start with p is null connect by p = prior c')
    or die print "em_error=Couldn't prepare statement: $dbh->errstr"; 
    $sth->execute() 
    or die print "em_error=Couldn't execute statement: $sth->errstr";
}
elsif($ARGV[0] eq 'ALL_PRIVILEGES'){
    $sth = $dbh->prepare_cached('select distinct p, c from (
            select null p, name c from system_privilege_map where name = \'GRANT ANY PRIVILEGE\'  union
            select granted_role p, grantee c from dba_role_privs  union
            select privilege p, grantee c from dba_sys_privs
  ) 
  where rownum < 1000 and p is not null
start with p is null connect by p = prior c')
    or die print "em_error=Couldn't prepare statement: $dbh->errstr"; 
    $sth->execute() 
    or die print "em_error=Couldn't execute statement: $sth->errstr";
}
elsif($ARGV[0] eq 'ANY_PRIVILEGE'){
    $sth = $dbh->prepare_cached('select distinct p, c from (
            select null p, name c from system_privilege_map where name like \'%ANY%\'  union
            select granted_role p, grantee c from dba_role_privs  union
            select privilege p, grantee  c from dba_sys_privs
  )
  where rownum < 1000 and p is not null
start with p is null connect by p = prior c')
    or die print "em_error=Couldn't prepare statement: $dbh->errstr"; 
    $sth->execute() 
    or die print "em_error=Couldn't execute statement: $sth->errstr";
}
elsif($ARGV[0] eq 'POWER_PRIVILEGE'){
    $sth = $dbh->prepare_cached('select distinct p, c from ( 
            select null p, name c from system_privilege_map where name in (\'ALTER SESSION\',\'ALTER SYSTEM\',\'CREATE PROCEDURE\',\'CREATE LIBRARY\')  union
            select granted_role p, grantee c from dba_role_privs  union 
            select privilege p, grantee c from dba_sys_privs
  )
  where rownum < 1000 and p is not null
start with p is null connect by p = prior c')
    or die print "em_error=Couldn't prepare statement: $dbh->errstr"; 
    $sth->execute() 
    or die print "em_error=Couldn't execute statement: $sth->errstr";
}

my @grantList;
my %grantTree;#grantTree{priv}=%privHash such that 
                        #%privHash{grantee} = 1 if priv has been granted to grantee, 
                                                    #undef otherwise
while (@grantList = $sth->fetchrow_array()){
    if( !defined($grantTree{$grantList[0]}) ){
        my %tempHash;
        $tempHash{$grantList[1]} = 1;
        $grantTree{$grantList[0]} = \%tempHash;
    }
    else{
        my %tempHash = %{$grantTree{$grantList[0]}};
        $tempHash{$grantList[1]} = 1;
        $grantTree{$grantList[0]} = \%tempHash;
    }
}
close_db_connection($dbh) ;
foreach $privs (keys %grantTree){
    my %tempHash = %{$grantTree{$privs}};
}
    
if(($ARGV[0] eq 'EXEMPT_ACCESS_POLICY') && defined($grantTree{"EXEMPT ACCESS POLICY"})){
        $numRows = $maxRows;
        printRows("EXEMPT ACCESS POLICY", "EXEMPT ACCESS POLICY", "EXEMPT_ACCESS_POLICY");
}
elsif(($ARGV[0] eq 'DBA_ROLE') && defined($grantTree{"DBA"})){
        $numRows = $maxRows;
        printRows("DBA", "DBA", "DBA_ROLE");
}
elsif($ARGV[0] eq 'CREATE_PRIVILEGE'){
    $numRows = $maxRows;
    my $create_priv;
    foreach $create_priv (keys %grantTree){
        if(($create_priv =~ /CREATE/) && defined($grantTree{$create_priv})){
            $numRows = $numRows - printRows($create_priv, $create_priv, 'CREATE_PRIVILEGE');
        }
    }
}
elsif($ARGV[0] eq 'CATALOG_ROLE'){
    $numRows = $maxRows;
    my $cata_role;
    foreach $cata_role (keys %grantTree){
        if(($cata_role =~ /_CATALOG_/) && defined($grantTree{$cata_role})){
            $numRows = $numRows - printRows($cata_role, $cata_role, 'CATALOG_ROLE');
        }
    }
}
elsif(($ARGV[0] eq 'BECOME_USER') && defined($grantTree{"BECOME USER"})){
        $numRows = $maxRows;
        printRows("BECOME USER", "BECOME USER", "BECOME_USER");
}
elsif(($ARGV[0] eq 'AUDIT_SYSTEM') && defined($grantTree{"AUDIT SYSTEM"})){
        $numRows = $maxRows;
        printRows("AUDIT SYSTEM", "AUDIT SYSTEM", "AUDIT_SYSTEM");
}
elsif($ARGV[0] eq 'ANY_DICTIONARY'){
    $numRows = $maxRows;
    my $dict_priv;
    foreach $dict_priv (keys %grantTree){
        if(($dict_priv=~ /ANY DICTIONARY/) && defined($grantTree{$dict_priv})){
            $numRows = $numRows - printRows($dict_priv, $dict_priv, 'ANY_DICTIONARY');
        }
    }
}
elsif($ARGV[0] eq 'CONNECT_PRIVILEGE'){
    $numRows = $maxRows;
    my $conn_priv;
    foreach $conn_priv (keys %grantTree){
        if((($conn_priv eq 'CONNECT') || ($conn_priv eq 'RESOURCE')) && 
          defined($grantTree{$conn_priv})){
            $numRows = $numRows - printRows($conn_priv, $conn_priv, 'CONNECT_PRIVILEGE');
        }
    }
}
elsif(($ARGV[0] eq 'ALL_PRIVILEGES') && defined($grantTree{"GRANT ANY PRIVILEGE"})){
        $numRows = $maxRows;
        printRows("GRANT ANY PRIVILEGE", "GRANT ANY PRIVILEGE", "ALL_PRIVILEGES");
}
elsif($ARGV[0] eq 'ANY_PRIVILEGE'){
    $numRows = $maxRows;
    my $any_priv;
    foreach $any_priv (keys %grantTree){
        if(($any_priv=~ /ANY/) && defined($grantTree{$any_priv})){
            $numRows = $numRows - printRows($any_priv, $any_priv, 'ANY_PRIVILEGE');
        }
    }
}
elsif($ARGV[0] eq 'POWER_PRIVILEGE'){
    $numRows = $maxRows;
    my $conn_priv;
    foreach $conn_priv (keys %grantTree){
        if((($conn_priv eq 'ALTER SESSION') || ($conn_priv eq 'ALTER SYSTEM') || ($conn_priv eq 'CREATE PROCEDURE') || ($conn_priv eq 'CREATE LIBRARY')) && 
          defined($grantTree{$conn_priv})){
            $numRows = $numRows - printRows($conn_priv, $conn_priv, 'POWER_PRIVILEGE');
        }
    }
}

sub printRows{
#The first argument is the privilege 
#starting from which we print the grant tree. Second argument is the prefix each line must 
#contain(which is the family lineage). Third argument is the report name to be printed along with the data.
#Fourth argument(only for recursive calls) is the number of rows already printed before the recursive call
#returns the number of rows printed
    my $startPriv = shift;
    my $prefix = shift;
    my $prop = shift;
    my $rowsPrinted = shift;
    if(!defined($rowsPrinted)){
        $rowsPrinted = 0;
    }
    if($numRows == 0){
        return;
    }
    #print "em_result=debug arg3|".rand()."|$prop\n";
    if(!defined($grantTree{$startPriv})){
        print "em_result=$prefix|$startPriv|$prop\n";
        $numRows--;
    }
    else{
        my %granteeHash = %{$grantTree{$startPriv}};
        foreach $grantee (keys %granteeHash){
            $rowsPrinted = $rowsPrinted + printRows($grantee, "$prefix->$grantee", $prop, $rowsPrinted);
        }
    }
    return $rowsPrinted;
}