#!/usr/local/bin/perl
# 
# $Header: emdb/sysman/admin/scripts/dv/realmviolations.pl /st_emdbsa_11.2/1 2009/02/12 23:34:24 hpalitan Exp $
#
# realmviolations.pl
# 
# Copyright (c) 2008, 2009, Oracle and/or its affiliates.All rights reserved. 
#
#    NAME
#      realmviolations.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)
#    hpalitan    01/15/09 - Bug #7716543
#    hpalitan    10/29/08 - Check if the DB is DV enable before running metric
#                           sql
#    amahalin    09/12/08 - 
#    hpalitan    08/31/08 - Collect Database Vault Realm Violations
#    hpalitan    08/31/08 - Creation
# 

use strict;
use Oraperl;
use DBI;

require "emd_common.pl";
require "semd_common.pl";


if (EMAGENT_isPerlDebugEnabled())
{
  EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug --> ++++  Welcome to Database Vault Realm Violation Debugging ++++ ");
}

# Declare the subroutines
sub trim($);
sub checkForPatch($$);

# --------------------------------------------------------------------
# +++ VARIABLES
# --------------------------------------------------------------------

# GENERAL
my @fetch_row;
my $lda;
my $audit_trail_sql;
my $audit_trail_cur;
my $audit_trail_value;
my $timestamp_last_run="";
my $mode = 0;

my $realm_key = "";
my $realm_scanned_till_id = "0";
my $command_rule_key = "";
my $command_rule_scanned_till_id = "0";

# Used to check if DB is DV enabled
my @is_dv_enabled_fetch_row;
my $is_dv_enabled_lda;
my $is_dv_enabled_cur;
my $is_dv_enabled = "-1";
my $is_dv_enabled_sql;

# OUTPUT
my $violationsList="";

# INPUT
# The DB Connection info is passed in by the metric definition

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 $state_root = $ENV{EM_AGENT_STATE_DIR};
my $targetGuid = $ENV{EM_TARGET_GUID};



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

if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug username : $username");
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug address  : $address");
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug role     : $role");
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug state_root : $state_root");
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug targetGuid : $targetGuid");
}

# --------------------------------------------------------------------
# +++ Code snippet to check if Target DB is DV enabled
# --------------------------------------------------------------------

$is_dv_enabled_lda = DBI->connect('dbi:Oracle:', "$username@".$address, "$password",
    {ora_session_mode => $mode, PrintError => 0, RaiseError => 0})
    or die (filterOraError("em_error=Could not connect to $username/$address: $DBI::errstr\n", $DBI::err)); 
register_metric_call($is_dv_enabled_lda);

#$is_dv_enabled_sql = "select case when (((select count(*) from dba_users where username =\'DVSYS\' and user_id = 1279990)> 0)) then decode(value,\'TRUE\',1,\'FALSE\',0) else -1 end into dv_status from v\$option where parameter = \'Oracle Database Vault\'";

#$is_dv_enabled_sql = "select decode(value,'TRUE',1,'FALSE',0) as dv_status from v\$option where parameter = 'Oracle Database Vault'"; 

#sql shoulds run independend of 
$is_dv_enabled_sql = "select count(*) from dba_users where username ='DVSYS' and user_id = 1279990"; 

$is_dv_enabled_cur = $is_dv_enabled_lda->prepare($is_dv_enabled_sql)
   or die (filterOraError("em_error=prepare($is_dv_enabled_sql): $DBI::errstr\n", $DBI::err)); 
$is_dv_enabled_cur->execute()
   or die (filterOraError("em_error=is_dv_enabled_cur->execute(): $DBI::errstr\n", $DBI::err)); 


while (my ($is_dv_enabled1) = $is_dv_enabled_cur->fetchrow_array())  # keep fetching until there's nothing left
{
	$is_dv_enabled = trim($is_dv_enabled1);
}

$is_dv_enabled_lda->disconnect
    or warn "disconnect $DBI::errstr\n";


if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug  is_dv_enabled  : $is_dv_enabled");
}

if ($is_dv_enabled == "1")
{

}
else
{

        if (EMAGENT_isPerlDebugEnabled())
        {
                EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug  Database Vault is not enabled on $address");
        }
        exit 0;
}

# --------------------------------------------------------------------




my $separator = $^O =~ m/MSWin32/ ? "\\" : "\/";

#
# Location of the offsets state file is
#  $EMDROOT/sysman/emd/state/<TARGET_GUID>.<filename>
#
my $scannedFile = $state_root.$separator."sysman".$separator."emd".$separator."state".$separator."$targetGuid"."_dv_realm_violations.log";

if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug  separator  : $separator");
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug  scannedFile  : $scannedFile");
}
#print "\n State file: $scannedFile \n";
# ------------------------------------------------------------------
# Open scanned file for reading (if scanned file does not exist, create it.)
# ------------------------------------------------------------------
if (!open(SCANNED, "+>> $scannedFile"))
{
    EMAGENT_PERL_ERROR("DatabaseVaultRealmViolation: $targetGuid; Cannot open $scannedFile for read/write.");
    exit 1;
}

# seek to top of file

seek(SCANNED, 0, 0);

# file only contains a single line with two numbers:  a timestamp and a count
# get these and close the scanned file

my $cnt=0;

while (<SCANNED>)
{
if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug  Row $cnt : $_");
}
    my @pos = split('~', $_);
    my $pos = \@pos;

	if ($cnt == 0)
	{
	    $realm_key = $pos->[0];
	    $realm_scanned_till_id = $pos->[1];
	}    

#	if ($cnt != 0)
#	{
#	    $command_rule_key = $pos->[0];
#	    $command_rule_scanned_till_id = $pos->[1];
#	}    
	
	$cnt += 1;	
}

close(SCANNED);
if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug  realm_scanned_till_id  : $realm_scanned_till_id");
#    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug  command_rule_scanned_till_id  : $command_rule_scanned_till_id");
}



# --------------------------------------------------------------------
# +++ Establish Target DB Connection
# --------------------------------------------------------------------
#print "1";
$lda = DBI->connect('dbi:Oracle:', "$username@".$address, "$password",
    {ora_session_mode => $mode, PrintError => 0, RaiseError => 0})
    or die (filterOraError("em_error=Could not connect to $username/$address: $DBI::errstr\n", $DBI::err)); 
register_metric_call($lda);

# ---------------------------------
#  +++ Get realm violations
# ---------------------------------

#print "2";

$audit_trail_sql = "select * from (select id#, action_object_name, action_command, TO_CHAR(CAST(timestamp AS TIMESTAMP) , 'YYYY-MM-DD HH24:MI:SS TZD') timestamp from dvsys.audit_trail\$ where id# > ". $realm_scanned_till_id ."  and returncode <> 0 and action between 10003 and 10004 order by id# asc) ";

#$audit_trail_sql = "select * from (select id#, action_object_name, action_command, TO_CHAR(CAST(timestamp AS TIMESTAMP) , 'YYYY-MM-DD HH24:MI:SS TZD') timestamp from dvsys.audit_trail\$ where id#>0 and action between 10003 and 10004 order by id# asc) where rownum<10";

#$audit_trail_sql = "select null, null, null, null from dvsys.audit_trail\$ where  1=2 and id# > ". $realm_scanned_till_id ." and action between 10003 and 10004 order by id# asc";

#$audit_trail_sql = "select * from dvsys.audit_trail\$";

$audit_trail_cur = $lda->prepare($audit_trail_sql)
   or die (checkForPatch("em_error=prepare($audit_trail_sql): $DBI::errstr\n", $DBI::err)); 
if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug Passed prepare of audit_trail_sql");
}
$audit_trail_cur->execute()
   or die (checkForPatch("em_error=audit_trail_cur->execute(): $DBI::errstr\n", $DBI::err)); 

if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug Passed execute of audit_trail_sql");
}

# ------------------------------------------
#  +++ Fetch realm violations from resultset
# ------------------------------------------

#print "3";
my $maxid = $realm_scanned_till_id ;
my $fmtline="";
$cnt=1;

my $action_object_name_1 = "";
my $action_command_1 = "";
my $timestamp_1 = ""; 

while (my ($id, $action_object_name, $action_command, $timestamp) = 
    $audit_trail_cur->fetchrow_array())  # keep fetching until 
                             # there's nothing left
{

$action_object_name = trim($action_object_name);
$action_command = trim($action_command);
$timestamp = trim($timestamp);

if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug >>> $action_object_name|$action_command|$timestamp");
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug <<< $action_object_name_1|$action_command_1|$timestamp_1");
}

if(($action_object_name  eq $action_object_name_1) && ($timestamp eq $timestamp_1))
{

#	if($action_command eq $action_command_1)
#	{
#if (EMAGENT_isPerlDebugEnabled())
#{
#    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug Duplicate attempted violation, no need to report");
#}
		#Duplicate attempted violation, no need to report.
#	}else{
#if (EMAGENT_isPerlDebugEnabled())
#{
#    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug Realm name and time is the same, however error message is different");
#}
	if($action_command ne $action_command_1)	
	{

		$fmtline = "em_result=$action_object_name|$action_command|$timestamp.$cnt\n";
    		$cnt += 1;
	}

}else{
#if (EMAGENT_isPerlDebugEnabled())
#{
#    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug different violation");
#}
	$fmtline = "em_result=$action_object_name|$action_command|$timestamp\n";

	$cnt = 1;
}

	$action_object_name_1 = $action_object_name;
	$action_command_1 = $action_command;
	$timestamp_1 = $timestamp;

if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug fmtline $fmtline");
}

if($fmtline ne "")
{
#if (EMAGENT_isPerlDebugEnabled())
#{
#    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug  fmtline is not empty");
#}
	
	$violationsList .= $fmtline;
	
if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug violationsList $violationsList");
}

}

if($id > $maxid)
{
	$maxid = $id;
}	

$fmtline="";

}



    #$violationsList .= "\n";

if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug maxid : $maxid");
}

# --------------------------------------------------------------------
# Reopen scanned file and write out new offset (log size), line number,
# and time
#
# --------------------------------------------------------------------
open (SCANNED, "+> $scannedFile") or die "em_error=Cannot open $scannedFile";
print SCANNED "realm_key~$maxid"."\n";
#print SCANNED "command_rule_key~$command_rule_scanned_till_id"."\n";
close(SCANNED);



if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug violationsList:\n$violationsList");
}

# --------------------------------------------------------------------
# +++ Print Results
#
# This returns the results to the agent, which will look for a
# standard output line that starts with em_result. The '|' character
# is the delimiter between values.
# --------------------------------------------------------------------

print $violationsList;

# --------------------------------------------------------------------
# +++ Disconnect from the Target DB
# --------------------------------------------------------------------

$lda->disconnect
    or warn "disconnect $DBI::errstr\n";

# Perl trim function to remove whitespace from the start and end of the string
sub trim($)
{
	my $string = shift;
	$string =~ s/^\s+//;
	$string =~ s/\s+$//;
	return $string;
}

sub checkForPatch($$) 
{
  my $argsListSize = @_;
  my $userErrMsg = shift(@_);
  my $inOraError = shift(@_);

if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug userErrMsg : $userErrMsg");
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug inOraError :$inOraError");
}

  my $isErrorSkiped; 

  $isErrorSkiped = filterOraError($userErrMsg, $inOraError);
if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug isErrorSkiped: $isErrorSkiped");
}
  if($isErrorSkiped =~ m/^<<<METRIC SKIP>>>/){
	if (EMAGENT_isPerlDebugEnabled())
	{
	    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug isErrorSkiped: $isErrorSkiped");
	}
	return $isErrorSkiped;
  }
  
  if($inOraError =~ m/1031/){
	if (EMAGENT_isPerlDebugEnabled())
	{
	    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug inOraError: $inOraError");
	    EMD_PERL_DEBUG ("\"Database Vault Manageability patch for Enterprise Manager\" has not been applied to this database. Refer to support note 760748.1 for more details");	
#	    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug em_error=Oracle Enterprise Manager requires patch #7319691 on this target. The patch can be downloaded from https://metalink.oracle.com");
	}
#	print "Oracle Enterprise Manager requires patch #7319691 on this target. The patch can be downloaded from https://metalink.oracle.com";
	print "\"Database Vault Manageability patch for Enterprise Manager\" has not been applied to this database. Refer to support note 760748.1 for more details";
		
	exit 1;
  }

	if (EMAGENT_isPerlDebugEnabled())
	{
	    EMD_PERL_DEBUG ("DatabaseVaultRealmViolation:Debug inOraError: $inOraError");
	}
  return $inOraError;
}

exit 0;