#!/usr/local/bin/perl
# 
# $Header: tableAccess.pl 04-jul-2005.11:45:01 dsukhwal Exp $
#
# tableAccess.pl
# 
# Copyright (c) 2004, 2005, Oracle. All rights reserved.  
#
#    NAME
#      tableAccess.pl - <one-line expansion of the name>
#
#    DESCRIPTION
#      <short description of component this file declares/defines>
#
#    NOTES
#      <other useful comments, qualifications, etc.>
#
#    MODIFIED   (MM/DD/YY)
#    dsukhwal    07/04/05 - support flood control for all metrics 
#    dkjain      07/03/05 - Fixed the em_error with print 
#    dsukhwal    05/14/05 - performance improvement 
#    dkjain      01/07/05 - Modified propertytext for role_role_privs 
#    dkjain      12/07/04 - Changed text to DBA_ROLE_PRIVS
#    dkjain      11/26/04 - Fixed Bug-4027306 
#    dkjain      10/31/04 - Fixed unable to connect
#    dkjain      10/08/04 - dkjain_esa_impl_init
#    dkjain      10/08/04 - 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 $address = $ENV{EM_TARGET_ADDRESS};
  my $role = $ENV{EM_TARGET_ROLE};
  my $mode = 0;
  my $public = "PUBLIC" ;
  my $privilege ;
  my $i = 0;

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

  my @role ;
  my @users ;
  my $index = 0 ;
  my @list1 ;

  my $dbh = open_db_connection("dbi:Oracle:", "$username@".$address,$password,$mode); 
  my $sth1 = $dbh->prepare('select username from dba_users where username = ? ') or die print "em_error=Couldn't prepare statement: $dbh->errstr"; 

  my $sth2 = $dbh->prepare('select distinct granted_role,grantee from dba_role_privs order by granted_role') or die print "em_error=Couldn't prepare statement: $dbh->errstr"; 
  $sth2->execute() 
  or die print "em_error=Couldn't execute statement: $sth->errstr";
  while (@list1 = $sth2->fetchrow_array()) {
   $role[$index] = qq($list1[0]:$list1[1]); 
   $index++;
   if($index >= 200){
   last ;
   }
  }

  my @user_priv ;
  my $j = 0 ;
  my $table_name = q('AUD$');
  my $property="access_aud_table" ; 
  table_access2($ENV{'AUDMAX'});

 
  @user_priv = ();
  $j = 0 ;
  $table_name = q('USER_HISTORY$');
  $property="access_user_history" ; 
  table_access2($ENV{'UHISTMAX'});

  @user_priv = ();
  $j = 0 ;
  $table_name = q('SOURCE$');
  $property="access_source_table" ; 
  table_access2($ENV{'SRCTABMAX'});
 
  @user_priv = ();
  $j = 0 ;
  $table_name = q('LINK$');
  $property="access_link_table" ; 
  table_access2($ENV{'LNKTABMAX'});
 
  @user_priv = ();
  $j = 0 ;
  $table_name = q('USER$');
  $property="access_user_table" ; 
  table_access2($ENV{'USRTABMAX'});

  @user_priv = ();
  $j = 0 ;
  $table_name = q('STATS$SQLTEXT');
  $property="access_sql_text" ; 
  table_access2($ENV{'SQLTXTMAX'});

  @user_priv = ();
  $j = 0 ;
  $table_name = q('STATS$SQL_SUMMARY');
  $property="access_sql_summary" ; 
  table_access2($ENV{'SQLSUMMAX'});

  @user_priv = ();
  $j = 0 ;
  $table_name = q('ALL_SOURCE');
  $property="access_all_source" ; 
  table_access2($ENV{'ALLSRCMAX'});


  $j = 0 ;
  $table_name = q('DBA_ROLES');
  $property="access_dba_roles" ; 
  table_access2($ENV{'DBAROLMAX'});


  @user_priv = ();
  $j = 0 ;
  $table_name = q('DBA_SYS_PRIVS');
  $property="access_dba_sysprivs" ; 
  table_access2($ENV{'SYSPRIVMAX'});


  $j = 0 ;
  $table_name = q('DBA_ROLE_PRIVS');
  $property="access_dba_roleprivs" ; 
  table_access2($ENV{'ROLPRIVMAX'});

  @user_priv = ();
  $j = 0 ;
  $table_name = q('DBA_TAB_PRIVS');
  $property="access_dba_tabprivs" ; 
  table_access2($ENV{'TABPRIVMAX'});

  @user_priv = ();
  $j = 0 ;
  $table_name = q('DBA_USERS');
  $property="access_dba_users" ; 
  table_access2($ENV{'DBAUSRMAX'});


  @user_priv = ();
  $j = 0 ;
  $table_name = q('ROLE_ROLE_PRIVS');
  $property="access_role_roleprivs" ; 
  table_access2($ENV{'ROLROLMAX'});

  @user_priv = ();
  $j = 0 ;
  $table_name = q('USER_TAB_PRIVS');
  $property="access_user_tabprivs" ; 
  table_access2($ENV{'USRTABPRMAX'});

  @user_priv = ();
  $j = 0 ;
  $table_name = q('USER_ROLE_PRIVS');
  $property="access_user_roleprivs" ; 
  table_access2($ENV{'USRROLPRMAX'});

  close_db_connection($dbh);



 # This function returns list of users have been given object privs on tables
 # directly or thru roles
 sub table_access2{
   my $sth ;
   my @list2 = ();
   my $numRows=0;
   my $maxRows = shift;
   my $query = qq(select distinct grantee,privilege from dba_tab_privs where table_name = $table_name);
  my $sth3 = $dbh->prepare_cached($query ) or die print "em_error=Couldn't prepare statement:$dbh->errstr"; 
   $sth3->execute() 
   or die print "em_error=Couldn't execute statement: $sth->errstr";
   while (@list2 = $sth3->fetchrow_array()) {
     if(($list2[0] ne "SYS")&&(($public =~ /$list2[0]/i) || (is_user($list2[0])))){
       if(search_n_store($property,$list2[0],$list2[1])  && compareNegInf($numRows, $maxRows) ){
         print "em_result=$property|$list2[0]|$list2[1]\n" ; 
         $numRows++;
       }
     }  
     else {
        $privilege = $list2[1] ;
	is_role($list2[0], $maxRows, $numRows);
     } 
   }
}

 sub is_role{
  my $is_role = shift ;
  my $maxRows = shift;
  my $numRows = shift;
  my $role ;
  my @tmp ;
  foreach $role (@role){
    @tmp = split(/:/,$role); 
    if($tmp[0] eq $is_role){
      if(($tmp[1] ne "SYS")&&(($public =~ /$tmp[1]/i) || (is_user($tmp[1])))){
        if(search_n_store($property,$tmp[1],$privilege) && compareNegInf($numRows, $maxRows) ){
    	 print "em_result=$property|$tmp[1]|$privilege\n" ; 
         $numRows++;
        }
	last ;
      }  
      else{
       is_role($tmp[1], $maxRows, $numRows);
      }
    }
  }
 }  

  sub search_n_store {
   my  $flag = 0 ;
   my  $elem;
   my  ($property,$user,$privilege) = @_ ;
   my @tmp ;	
   if($j > 20){
    return 0;
   } 
   foreach $elem (@user_priv){
     @tmp = split(/:/,$elem); 
     if(($tmp[0] eq $property) && ($tmp[1] eq $user) && ($tmp[2] eq $privilege)){
      $flag = 1 ;
      last ;
     }
   }
   if($flag){
     return 0 ;   
   }  
   $user_priv[$j] = qq($property:$user:$privilege);
   $j++;  		
   return 1;
 } 

 sub is_user{
   my $dbuser = shift ;
   $sth1->execute($dbuser) 
   or die print "em_error=Couldn't execute statement: $sth->errstr";
   my @list0 ; 
   if(@list0 = $sth1->fetchrow_array()){
    return 1;
   }else{
    return 0 ; 
   }
}