#!/usr/local/bin/perl
# 
# $Header: esaDbUtils.pl 15-dec-2004.05:25:03 dkjain Exp $
#
# esaDbUtils.pl
# 
# Copyright (c) 2004, Oracle. All rights reserved.  
#
#    NAME
#      esaDbUtils.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)
#    dkjain      12/15/04 - add error handling 
#    dkjain      10/31/04 - Fixed unable to connect
#    dkjain      10/13/04 - More utility functions added 
#    dkjain      10/08/04 - dkjain_esa_impl_init
#    dkjain      10/08/04 - Creation
# 

  use strict ;
  use DBI ;
   
##############################################################################
################Functions Required By Report Collection#######################
 
  my @role ;
  my $userGlobal ;
  my $i = 0;
  my $totalRows = 0;
  my  $flag1 = "FALSE" ;
  my  $flag2 = "FALSE" ;
  my  $flag3 = "FALSE" ;
  my  $flag4 = "FALSE" ;

  sub getUserWithPrivileges{
   my $sth0 ;
   my $sth1 ;
   my $index = 0 ;
   my @user ;
   my @list ;
   my ($dbh,@priv) = @_;

   $sth1 = $dbh->prepare_cached("select grantee,granted_role from dba_role_privs union select grantee,privilege from  dba_sys_privs") or die "Couldn't prepare statement: " . $dbh->errstr; 
   $sth1->execute() or die "Couldn't execute statement: " . $sth1->errstr;

   $sth0 = $dbh->prepare_cached("select username from dba_users") or die "Couldn't prepare statement: " . $dbh->errstr; 
   $sth0->execute() or die "Couldn't execute statement: " . $sth0->errstr;
         
   while (@list = $sth1->fetchrow_array()) {
     $role[$index] = qq($list[0]:$list[1]); 
     $index++;
   }
	
   while (@user = $sth0->fetchrow_array()) 
   {
     $i = 0 ;
     $userGlobal = $user[0] ;  
     checkInRole($user[0],@priv);     
     $totalRows = $totalRows + $i ;
     if($totalRows > 200){
       exit(0);  
     }
     $flag1 = "FALSE" ;
     $flag2 = "FALSE" ;
     $flag3 = "FALSE" ;
     $flag4 = "FALSE" ;
   }
  } #End of function            


 sub checkInRole {
  my @userPriv = @_ ;
  my $r ;
  my @tmp ;
  my $user = $userPriv[0] ;
   
  foreach $r (@role)
  {
    @tmp = split(/:/,$r); 
    if($tmp[0] eq $user)
    {
       if(($tmp[1] eq $userPriv[2])&&($flag1 eq "FALSE")){
          print "em_result=$userGlobal|$tmp[1]|$userPriv[1]\n";
          $i++;
          $flag1="TRUE"; 
       }
       elsif (($tmp[1] eq $userPriv[3])&&($flag2 eq "FALSE")){
          print "em_result=$userGlobal|$tmp[1]|$userPriv[1]\n";
          $i++;
          $flag2="TRUE"; 
       }
       elsif(($tmp[1] eq $userPriv[4])&&($flag3 eq "FALSE")){
          print "em_result=$userGlobal|$tmp[1]|$userPriv[1]\n";
          $i++;
          $flag3="TRUE"; 
       }
       elsif(($tmp[1] eq $userPriv[5])&&($flag4 eq "FALSE")){
          print "em_result=$userGlobal|$tmp[1]|$userPriv[1]\n";
          $i++;
          $flag4="TRUE"; 
       }
       else
       {
         $userPriv[0]=$tmp[1];  
	 checkInRole(@userPriv);
         if($i > 3){ 
          last ; 
         }
       } 	 
       if($i > 3){ 
        last ; 
       }
     } 
   }
 }
################################################################
  my @role ;
  my $userGlobal ;
  my $i = 0;
  my $totalRows = 0 ;
  my @user_priv ;
  my $j = 0 ; 
  my $globalPrivs ;

  sub getUserWithPrivs{
   my $sth0 ;
   my $sth1 ; 
   my $index = 0 ;
   my @user ;
   my @list ;
   my ($dbh,$priv,$localPrivs) = @_;
   $globalPrivs = $localPrivs ; 
   $sth1 = $dbh->prepare_cached("select grantee,granted_role from dba_role_privs union select grantee,privilege from  dba_sys_privs") or die "Couldn't prepare statement: " . $dbh->errstr; 
   $sth1->execute() or die "Couldn't execute statement: " . $sth1->errstr;
   $sth0 = $dbh->prepare_cached("select username from dba_users") or die "Couldn't prepare statement: " . $dbh->errstr; 
   $sth0->execute() or die "Couldn't execute statement: " . $sth0->errstr;
         
   while (@list = $sth1->fetchrow_array()) {
    $role[$index] = qq($list[0]:$list[1]); 
    $index++;
   }
   while (@user = $sth0->fetchrow_array()) {
    $i = 0 ;
    $j = 0 ;
    $userGlobal = $user[0] ;  
    checkInRole2($user[0],$priv);     
    $totalRows = $totalRows + $i ;
    if($totalRows > 200){
      exit(0);  
    }
   }
 } #End of function            

  sub getUserWithCataRole{
   my $sth0 ;
   my $sth1 ; 
   my $index = 0 ;
   my @user ;
   my @list ;
   my ($dbh,$priv,$localPrivs) = @_;
   $globalPrivs = $localPrivs ; 

   $sth1 = $dbh->prepare_cached("select grantee,granted_role from dba_role_privs") or die "Couldn't prepare statement: " . $dbh->errstr; 
   $sth1->execute() or die "Couldn't execute statement: " . $sth1->errstr;
   $sth0 = $dbh->prepare_cached("select username from dba_users") or die "Couldn't prepare statement: " . $dbh->errstr; 
   $sth0->execute() or die "Couldn't execute statement: " . $sth0->errstr;
         
   while (@list = $sth1->fetchrow_array()) {
    $role[$index] = qq($list[0]:$list[1]); 
    $index++;
   }
   while (@user = $sth0->fetchrow_array()) {
    $i = 0 ;
    $j = 0 ;
    $userGlobal = $user[0] ;  
    checkInRole2($user[0],$priv);     
    $totalRows = $totalRows + $i ;
    if($totalRows > 200){
      exit(0);  
    }
   }
 } #End of function            

 sub getUserWithDbaRole{
   my $sth0 ;
   my $sth1 ; 
   my $index = 0 ;
   my @user ;
   my @list ;
   my ($dbh,$priv,$localPrivs) = @_;
   $globalPrivs = $localPrivs ; 

   $sth1 = $dbh->prepare_cached("select grantee,granted_role from dba_role_privs") or die "Couldn't prepare statement: " . $dbh->errstr; 
   $sth1->execute() or die "Couldn't execute statement: " . $sth1->errstr;

   $sth0 = $dbh->prepare_cached("select username from dba_users where password='EXTERNAL' ") or die "Couldn't prepare statement: " . $dbh->errstr; 
   $sth0->execute() or die "Couldn't execute statement: " . $sth0->errstr;
         
   while (@list = $sth1->fetchrow_array()) {
    $role[$index] = qq($list[0]:$list[1]); 
    $index++;
   }
	
   while (@user = $sth0->fetchrow_array()) {
    $i = 0 ;
    $j = 0 ;
    $userGlobal = $user[0] ;  
    checkInRole2($user[0],$priv);     
    $totalRows = $totalRows + $i ;
    if($totalRows > 200){
      exit(0);  
    }
   }
 } #End of function            

 sub checkInRole2 {
  my @userPriv = @_ ;
  my $r ;
  my @tmp ;
  my $flag = 0 ;
  my $user = $userPriv[0] ;
  foreach $r (@role)
  {
    $flag = 0 ;
    @tmp = split(/:/,$r); 
    if($tmp[0] eq $user)
    {
       if(($tmp[1] =~ $globalPrivs)){
         $flag = search($userGlobal,$tmp[1]);
         if($flag){ 
           print "em_result=$userGlobal|$tmp[1]|$userPriv[1]\n";
           $i++;
         }
       }
       else{
         $userPriv[0]=$tmp[1];  
	 checkInRole2(@userPriv);
         if($i > 3){ 
           last ; 
         }
       }	 
       if($i > 3){ 
         last ; 
       }
     } 
   }
 }

 sub search {
   my  $flag = 0 ;
   my  $elem;
   my  ($user,$privilege) = @_ ;
   my @tmp ;	
   my $i = 0 ;

   foreach $elem (@user_priv){
     if($i >= $j){
       last ;
     }
     @tmp = split(/:/,$elem); 
     if(($tmp[0] eq $user) && ($tmp[1] eq $privilege)){
       $flag = 1 ;
       last ;
     }
     $i++;    
   }
   if($flag){
     return 0 ;   
   }  
   $user_priv[$j] = qq($user:$privilege);
   $j++;  		
   return 1;
 } 
################Functions Required By Report Collection#######################



##############################################################################

   sub open_db_connection {  
      my ($dsn, $user, $passwd,$mode) = @_;
      my $dbh = DBI->connect($dsn, $user, $passwd, 
      {ora_session_mode => $mode, PrintError => 0, RaiseError => 0, AutoCommit => 0})
      or die "em_error=Could not connect to $user: $DBI::errstr\n";
      return $dbh ;
  }


  sub getValue{

   	my $sth ;
	my $index = 0 ;
	my @row ;
	my @list ;

	my ($dbh, $value, $param_col_name, $param_col_value, $table) = @_ ;
        if($param_col_name eq "")
	{
           $sth = $dbh->prepare_cached("SELECT $value FROM $table") 
	    or die "Couldn't prepare statement: " . $dbh->errstr; 
	}
	else
	{
           $sth = $dbh->prepare_cached("SELECT $value FROM $table where $param_col_name = '$param_col_value'")
	    or die "Couldn't prepare statement: " . $dbh->errstr; 
	}
        $sth->execute()
        or die "Couldn't execute statement: " . $sth->errstr;
     
	while (@row = $sth->fetchrow_array()) {
                $list[$index] = $row[0];
		$index ++;
	}
	return @list;

  } #End of getParamValue            

  sub close_db_connection {
        my $dbh = shift ;
        $dbh->disconnect()  or die "couldnt close connection \n $DBI::errstr" ; 
  }
  
  1 ;