Rem Rem $Header: esm_default_notification_rules.sql 06-may-2005.06:27:49 dkjain Exp $ Rem Rem esm_default_notification_rules.sql Rem Rem Copyright (c) 2005, Oracle. All rights reserved. Rem Rem NAME Rem esm_default_notification_rules.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem dkjain 05/06/05 - Changing the desc Rem dkjain 04/18/05 - dkjain_enhancement-4296265 Rem dkjain 03/21/05 - Created Rem SET ECHO ON SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 DEFINE EM_REPOS_USER = "&1" Rem Rem Create default notification rules for types - database & listener Rem BEGIN DECLARE current_user VARCHAR2(128); current_em_user VARCHAR2(128); -- variables for default notification rules rule_name VARCHAR2(64); rule_owner VARCHAR2(128); rule_desc VARCHAR2(256); rule_public NUMBER(1); want_policy_violation_in SMP_EMD_INTEGER_ARRAY; want_policy_clear_in SMP_EMD_INTEGER_ARRAY; want_policy_job_succeed_in SMP_EMD_INTEGER_ARRAY; want_policy_job_problem_in SMP_EMD_INTEGER_ARRAY; rule_policies SMP_EMD_STRING_ARRAY; rule_job_names SMP_EMD_STRING_ARRAY; rule_job_owners SMP_EMD_STRING_ARRAY; rule_job_types SMP_EMD_STRING_ARRAY; want_tgt_up NUMBER; want_tgt_down NUMBER; want_tgt_unreachable_start NUMBER; want_tgt_unreachable_end NUMBER; want_tgt_metric_err_start NUMBER; want_tgt_metric_err_end NUMBER; want_tgt_blkout_started NUMBER; want_tgt_blkout_ended NUMBER; target_type VARCHAR2(64); rule_targets_array SMP_EMD_STRING_ARRAY; rule_mnames_array SMP_EMD_STRING_ARRAY; rule_mcolumns_array SMP_EMD_STRING_ARRAY; rule_mindexes_array SMP_EMD_NVPAIR_ARRAY; user_email VARCHAR2(128); send_email NUMBER(1); ignore_rca NUMBER(1); rule_device_names SMP_EMD_STRING_ARRAY; rule_device_types SMP_EMD_INTEGER_ARRAY; device_not_avail_list SMP_EMD_STRING_ARRAY; email_gateway_setup NUMBER; BEGIN -- Save the currently logged in user current_user := mgmt_user.get_current_em_user(); -- Set em user context to EM_REPOS_USER. The default notification -- rules will be owned by this user. setemusercontext('&EM_REPOS_USER', MGMT_USER.OP_SET_IDENTIFIER); current_em_user := mgmt_user.get_current_em_user(); rule_owner := current_em_user; dbms_output.put_line( 'current em user: ' || current_em_user ); -- no email to be assigned with the rules at this stage ... send_email := 0; ignore_rca := 0; -- no devices to be assigned with the rules ... rule_device_names := SMP_EMD_STRING_ARRAY(); rule_device_types := SMP_EMD_INTEGER_ARRAY(); BEGIN -- initialize the values rule_name := 'Violation Notification for Database Security Policies'; rule_desc := 'System-generated notification rule for monitoring the secureness of the database configuration'; rule_public := 1; target_type := MGMT_GLOBAL.G_DATABASE_TARGET_TYPE; rule_targets_array := SMP_EMD_STRING_ARRAY(); rule_targets_array.extend( 1 ); rule_targets_array( 1 ) := '%'; rule_mnames_array := SMP_EMD_STRING_ARRAY(); rule_policies := SMP_EMD_STRING_ARRAY(); rule_policies.extend( 11 ); rule_policies( 1 ) := 'Remote_OS_Authentication' ; rule_policies( 2 ) := 'Remote_OS_Role' ; rule_policies( 3 ) := 'System_Privileges_To_Public' ; rule_policies( 4 ) := 'PUBLIC_Trace_Files' ; rule_policies( 5 ) := 'Audit_File_Destination' ; rule_policies( 6 ) := 'Core_Dump_Destination' ; rule_policies( 7 ) := 'DB_control_files_Permission' ; rule_policies( 8 ) := 'Log_Archive_Destination_Owner' ; rule_policies( 9 ) := 'Log_Archive_Destination_Permission' ; rule_policies( 10 ) :='Background_Dump_Destination' ; rule_policies( 11 ) := 'Log_Archive_Start' ; --rule_policies( 11 ) := 'LOG_ARCHIVE_START_NAME' ; want_policy_violation_in := SMP_EMD_INTEGER_ARRAY(); want_policy_clear_in := SMP_EMD_INTEGER_ARRAY() ; want_policy_job_succeed_in := SMP_EMD_INTEGER_ARRAY(); want_policy_job_problem_in := SMP_EMD_INTEGER_ARRAY(); want_policy_violation_in.extend(11); want_policy_clear_in.extend(11); want_policy_job_succeed_in.extend(11); want_policy_job_problem_in.extend(11); FOR i in 1..rule_policies.COUNT LOOP want_policy_violation_in(i) := 1; want_policy_clear_in(i) := 0; want_policy_job_succeed_in(i) := 0; want_policy_job_problem_in(i) := 0; END LOOP ; rule_job_names := SMP_EMD_STRING_ARRAY(); rule_job_owners := SMP_EMD_STRING_ARRAY(); rule_job_types := SMP_EMD_STRING_ARRAY(); want_tgt_up := 0; want_tgt_down := 0; want_tgt_unreachable_start := 0; want_tgt_unreachable_end := 0; want_tgt_metric_err_start := 0; want_tgt_metric_err_end := 0; want_tgt_blkout_started := 0; want_tgt_blkout_ended := 0; dbms_output.put_line( 'Creating rule: ' || rule_name || ' for user: ' || current_em_user ); mgmt_preferences.create_notification_rule( rule_name, rule_owner, rule_desc, rule_public, target_type, rule_targets_array, null, want_tgt_up, want_tgt_down, want_tgt_unreachable_start, want_tgt_unreachable_end, want_tgt_metric_err_start, want_tgt_metric_err_end, want_tgt_blkout_started, want_tgt_blkout_ended, ignore_rca, rule_mnames_array, null, null, null, null, null, null, null, null, null, rule_policies, want_policy_violation_in , want_policy_clear_in , want_policy_job_succeed_in , want_policy_job_problem_in , rule_job_names, rule_job_owners, rule_job_types, null, null, null, null, null, send_email, rule_device_names, rule_device_types, device_not_avail_list, email_gateway_setup ); dbms_output.put_line( 'email gateway setup: ' || email_gateway_setup ); COMMIT; EXCEPTION WHEN OTHERS THEN dbms_output.put_line( 'Exception occurred while creating rule: ' || rule_name || ' for user: ' || current_em_user || ', SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM ); END; EXCEPTION WHEN OTHERS THEN setemusercontext(current_user, MGMT_USER.OP_SET_IDENTIFIER); END; END; /