#!/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 - # # DESCRIPTION # # # NOTES # # # 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/. # 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 () { 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;