Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\scripts\dv\commandruleviolations.pl

#!/usr/local/bin/perl
# 
# $Header: emdb/sysman/admin/scripts/dv/commandruleviolations.pl /st_emdbsa_11.2/1 2009/02/12 23:34:24 hpalitan Exp $
#
# commandruleviolations.pl
# 
# Copyright (c) 2008, 2009, Oracle and/or its affiliates.All rights reserved. 
#
#    NAME
#      commandruleviolations.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/30/08 - Check if the DB is DV enable before running metric
#                           sql
#    amahalin    09/12/08 - 
#    hpalitan    08/31/08 - Collect Database Vault Command Rule 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 ("DatabaseVaultCommandRuleViolation:Debug --> ++++  Welcome to Database Vault CommandRule 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 ("DatabaseVaultCommandRuleViolation:Debug username : $username");
    EMD_PERL_DEBUG ("DatabaseVaultCommandRuleViolation:Debug address  : $address");
    EMD_PERL_DEBUG ("DatabaseVaultCommandRuleViolation:Debug role     : $role");
    EMD_PERL_DEBUG ("DatabaseVaultCommandRuleViolation:Debug state_root : $state_root");
    EMD_PERL_DEBUG ("DatabaseVaultCommandRuleViolation: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'";

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

if ($is_dv_enabled == "1")
{

}
else
{

if (EMAGENT_isPerlDebugEnabled())
        {
                EMD_PERL_DEBUG ("DatabaseVaultCommandRuleViolation: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_commandrule_violations.log";

if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultCommandRuleViolation:Debug  separator  : $separator");
    EMD_PERL_DEBUG ("DatabaseVaultCommandRuleViolation: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("DatabaseVaultCommandRuleViolation: $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 ("DatabaseVaultCommandRuleViolation:Debug  Row $cnt : $_");
}
    my @pos = split('~', $_);
    my $pos = \@pos;

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

}

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

close(SCANNED);
if (EMAGENT_isPerlDebugEnabled())
{
#    EMD_PERL_DEBUG ("DatabaseVaultCommandRuleViolation:Debug  realm_scanned_till_id  : $realm_scanned_till_id");
    EMD_PERL_DEBUG ("DatabaseVaultCommandRuleViolation: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 CommandRule 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# > ". $command_rule_scanned_till_id ." and action in (10005, 10008) and returncode <> 0 order by id# asc) ";

$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 CommandRule violations from resultset
# ------------------------------------------

#print "3";
my $maxid = $command_rule_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 ("DatabaseVaultCommandRuleViolation:Debug >>> $action_object_name|$action_command|$timestamp");
    EMD_PERL_DEBUG ("DatabaseVaultCommandRuleViolation: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 ne $action_command_1)	
	{
		$fmtline = "em_result=$action_object_name|$action_command|$timestamp.$cnt\n";
    		$cnt += 1;
	}

}else{
	$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 ("DatabaseVaultCommandRuleViolation:Debug fmtline $fmtline");
}

if($fmtline ne "")
{
        $violationsList .= $fmtline;
}

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

$fmtline="";

}

if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultCommandRuleViolation: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~$maxid"."\n";
close(SCANNED);

if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultCommandRuleViolation: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;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de