#!/usr/local/bin/perl
# 
# $Header: emdb/sysman/admin/scripts/dv/commandruleconfissue.pl /st_emdbsa_11.2/1 2009/02/12 23:34:24 hpalitan Exp $
#
# commandruleconfissue.pl
# 
# Copyright (c) 2008, 2009, Oracle and/or its affiliates.All rights reserved. 
#
#    NAME
#      commandruleconfissue.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/16/09 - Bug #7716543
#    hpalitan    10/31/08 - Database Vault Command Rule Configuration Metric
#    hpalitan    10/31/08 - Creation
# 


use strict;
use Oraperl;
use DBI;

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


if (EMAGENT_isPerlDebugEnabled())
{
  EMD_PERL_DEBUG ("DatabaseVaultCommandRuleConfigurationIssues:Debug --> ++++  Welcome to Database Vault Command Rule Configuration Issues 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 $action_object_name;
my $count;
my $mode = 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};



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

if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultCommandRuleConfigurationIssues:Debug username : $username");
    EMD_PERL_DEBUG ("DatabaseVaultCommandRuleConfigurationIssues:Debug address  : $address");
    EMD_PERL_DEBUG ("DatabaseVaultCommandRuleConfigurationIssues:Debug role     : $role");
}

# --------------------------------------------------------------------
# +++ 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'"; 

$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 ("DatabaseVaultCommandRuleConfigurationIssues:Debug  is_dv_enabled  : $is_dv_enabled");
}

if ($is_dv_enabled == "1")
{

}
else
{

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

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




# --------------------------------------------------------------------
# +++ 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 action between 10003 and 10004 order by id# asc) where rownum < 10";


$audit_trail_sql =	"select command, sum(cnt) ".
			"from ".
			"( ".
			"	select m.command, count(*) cnt ".
			"	from dvsys.dv\$command_rule m ".
			"	where m.rule_set_id# is not null ".
			"	and exists( ".
			"	    select 'x' ".
			"	    from dvsys.dv\$rule_set d ".
			"	    where d.id# = m.rule_set_id# ".
			"	       and NVL(d.enabled,'N') = 'N' ".
			"	) ".
			"	group by m.command ".
			"	union ".
			"	select m.command, count(*) cnt ".
			"	from dvsys.dv\$command_rule m ".
			"	where ".
			"	 m.rule_set_id# is not null ".
			"	and not exists( ".
			"	    select 'x' ".
			"	    from dvsys.dv\$rule_set_rule d ".
			"	    where d.id# = m.rule_set_id# ".
			"	        and nvl(d.enabled,'N') = 'Y' ".
			"	) ".
			"	group by m.command ".
			"	union ".
			"	select m.command,  count(*) cn ".
			"	from  dvsys.dv\$command_rule m ".
			"	where m.object_owner not like '%' and not exists( ".
			"	    select 'x' ".
			"	    from dvsys.dv\$sys_grantee d ".
			"	    where d.grantee_name = m.object_owner ".
			"	) ".
			"	group by m.command ".
			") ". 
			"group by command "; 


$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 $fmtline="";

while (my ($action_object_name1, $count1) = 
    $audit_trail_cur->fetchrow_array())  # keep fetching until 
                             # there's nothing left
{

	$action_object_name = trim($action_object_name1);
	$count = trim($count1);

	$fmtline = "em_result=$action_object_name|$count\n";

    	$violationsList .= $fmtline;

}	


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