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

#!/usr/local/bin/perl
# 
# $Header: emdb/sysman/admin/scripts/dv/policychanges.pl /st_emdbsa_11.2/1 2009/02/12 23:34:24 hpalitan Exp $
#
# policychanges.pl
# 
# Copyright (c) 2008, 2009, Oracle and/or its affiliates.All rights reserved. 
#
#    NAME
#      policychanges.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    12/05/08 - Store policy changes scanned time in state file
#    hpalitan    10/31/08 - Database Vault Policy Changes
#    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 ("DatabaseVaultPolicyChanges:Debug --> ++++  Welcome to Database Vault Policy Changes Debugging ++++ ");
}

# Declare the subroutines
sub trim($);

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

# GENERAL
my @fetch_row;
my $lda;
my $audit_trail_sql;
my $audit_trail_cur;
my $audit_trail_value;
my $action_object_name;
my $timestamp;
my $count;
my $mode = 0;

my $date_last_run = "";

# 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 ("DatabaseVaultPolicyChanges:Debug username : $username");
    EMD_PERL_DEBUG ("DatabaseVaultPolicyChanges:Debug address  : $address");
    EMD_PERL_DEBUG ("DatabaseVaultPolicyChanges:Debug role     : $role");
    EMD_PERL_DEBUG ("DatabaseVaultPolicyChanges:Debug state_root : $state_root");
    EMD_PERL_DEBUG ("DatabaseVaultPolicyChanges: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 ("DatabaseVaultPolicyChanges:Debug  is_dv_enabled  : $is_dv_enabled");
}

if ($is_dv_enabled == "1")
{

}
else
{

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

if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultPolicyChanges:Debug  separator  : $separator");
    EMD_PERL_DEBUG ("DatabaseVaultPolicyChanges:Debug  scannedFile  : $scannedFile");
}

# ------------------------------------------------------------------
  # Open scanned file for reading (if scanned file does not exist, create it.)
  # ------------------------------------------------------------------
  if (!open(SCANNED, "+>> $scannedFile"))
  {
    EMAGENT_PERL_DEBUG("target: $targetGuid; Cannot open $scannedFile for read/write.");
    print "Cannot open $scannedFile for read/write.";
    exit 1; 
  }

# seek to top of file
  seek(SCANNED, 0, 0);

# file only contains a single line, a timestamp
  while (<SCANNED>)
  {
        
      my @pos =  $_;
      my $pos = \@pos;
    
      $date_last_run = $pos->[0];
      $date_last_run =~ s/\s+$//;
  }

close(SCANNED);

if($date_last_run == ""){
	$date_last_run = "2008-01-01 00:00:00";
}
if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultPolicyChanges:Debug  date_last_run  : $date_last_run");
}

# --------------------------------------------------------------------
# +++ 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 policy_name, ".
"		TO_CHAR(CAST(SYSDATE AS TIMESTAMP) , 'YYYY-MM-DD HH24:MI:SS') timestamp, ".
"		sum(cnt) policy_changes_count  ".
"	from ".
"	( ".
"	select (CASE WHEN (INSTR(lower(obj_name),'realm')!=0)  THEN 'Realms' ".
"		    WHEN (INSTR(lower(obj_name),'command')!=0) THEN 'Command Rules' ".
"		    WHEN (INSTR(lower(obj_name),'role') !=0 ) THEN 'Secure Application Roles' ".
"		    WHEN (INSTR(lower(obj_name),'rule') !=0 ) THEN 'Rules' ".
"		    ELSE  'Factors' ".
"		END) policy_name,cnt ".
"		      from ".
"		      (               ".
"			      select obj_name,COUNT(*) cnt from DBA_AUDIT_TRAIL ".
"			      where UPPER(owner) in ('DVSYS','DVF')  ".
"			      and TIMESTAMP >= TO_DATE('$date_last_run','YYYY-MM-DD HH24:MI:SS') ".
"			      and ( UPPER(action_name) LIKE 'INSERT%'  OR ".
"				    UPPER(action_name) LIKE 'UPDATE%'  OR ".
"				    UPPER(action_name) LIKE 'DELETE%'  OR ".
"				    UPPER(action_name) LIKE 'MERGE%'  OR  ".
"				    UPPER(action_name) LIKE 'PURGE%'  OR ".
"				    UPPER(action_name) LIKE 'TRUNCATE%'  OR ".
"				    UPPER(action_name) LIKE 'SESSION REC%')  ".
"				    group by obj_name ".
"		      ) ".
"	) group by policy_name order by policy_changes_count desc";

if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultPolicyChanges:Debug  audit_trail_sql : $audit_trail_sql");
}

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

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

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

#print "3";
my $fmtline="";

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

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

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

$violationsList .= $fmtline;
	$date_last_run = $timestamp;
}

if (EMAGENT_isPerlDebugEnabled())
{
    EMD_PERL_DEBUG ("DatabaseVaultPolicyChanges:Debug date_last_run: $date_last_run");
}
# --------------------------------------------------------------------
# Reopen scanned file and write out new offset(time)
#
# --------------------------------------------------------------------
     open (SCANNED, "+> $scannedFile") or die print "em_error=Cannot open $scannedFile";
     print SCANNED "$date_last_run";
     close(SCANNED);
     
# --------------------------------------------------------------------
# +++ 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;
}

exit 0;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de