Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\esm\esm_pkgbodys.sql
Rem drv: <create type="pkgbodys" pos="ecm/ecm_pkgbodys.sql+"/> Rem Rem $Header: esm_pkgbodys.sql 07-dec-2005.04:08:06 dkjain Exp $ Rem Rem esm_pkgbodys.sql Rem Rem Copyright (c) 2003, 2005, Oracle. All rights reserved. Rem Rem NAME Rem esm_pkgbodys.sql - <one-line expansion of the name> Rem Rem DESCRIPTION Rem <short description of component this file declares/defines> Rem Rem NOTES Rem <other useful comments, qualifications, etc.> Rem Rem MODIFIED (MM/DD/YY) Rem dkjain 12/07/05 - Backport dkjain_bug-4741787 from main Rem dkjain 11/16/05 - Rem dsukhwal 09/07/05 - proc for console home page Rem dkjain 07/28/05 - Adding modify_policy Rem dkjain 08/15/05 - Adding policy_eval for unlimited login policy Rem dsukhwal 08/05/05 - add get_repository_databases Rem gsbhatia 07/01/05 - New repmgr header impl Rem gsbhatia 06/26/05 - New repmgr header impl Rem dkjain 04/17/05 - Add call to auto_enable_existing_targets Rem procedure Rem gsbhatia 02/13/05 - updating repmgr header Rem gsbhatia 02/09/05 - updating repmgr header Rem gsbhatia 02/07/05 - updating repmgr header Rem dkjain 01/31/05 - Added clear param Rem dkjain 01/19/05 - Fixed Bug-4128312(added support to old agent Rem version) Rem dkjain 01/05/05 - Adding author constant Rem ktlaw 01/11/05 - add repmgr header Rem dkjain 11/08/04 - Added p_impact_nlsid Rem dkjain 11/07/04 - p_auto_enable param added to the create_policy of esm Rem dkjain 10/08/04 - ESA 10gR2 Impl Rem eujang 09/27/03 - Changed ECM snapshot type to be oracle_security Rem eujang 09/04/03 - eujang_esm_init_no_intgr Rem anajmi 08/26/03 - Created Rem set echo on; CREATE OR REPLACE PACKAGE BODY ESM AS FUNCTION GET_REPOSITORY_TARGETS RETURN MGMT_USER_GUID_ARRAY IS rep_guid RAW(16); rep_connect VARCHAR2(1024); l_host MGMT_TARGETS.target_name%TYPE := ''; l_guids MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY(); guids MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY(); l_this_guid RAW(16); l_database_array SMP_EMD_STRING_ARRAY; l_found INTEGER :=0; l_domain_index INTEGER :=0; counter INTEGER := 0; counter1 INTEGER := 0; BEGIN -- Get the target guid of the repository target. There should only be -- one repository target but just make sure by using rownum SELECT target_guid INTO rep_guid FROM mgmt_targets WHERE target_type = MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE AND rownum = 1; BEGIN -- Get the ConnectDescriptor of the repository target SELECT LOWER(property_value) INTO rep_connect FROM mgmt_target_properties WHERE property_name = 'ConnectDescriptor' AND target_guid = rep_guid; EXCEPTION WHEN NO_DATA_FOUND THEN rep_connect := ''; END; -- get targets associated with repository database IF rep_connect IS NOT NULL AND LENGTH(rep_connect) > 0 THEN FOR crec IN (SELECT /*+ ORDERED INDEX(a) INDEX(b) */ b.target_guid, b.host_name FROM mgmt_target_properties a, mgmt_targets b WHERE b.target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE AND a.property_name = 'ConnectDescriptor' AND LOWER(a.property_value) = rep_connect AND a.target_guid = b.target_guid) LOOP guids.extend(1); guids(guids.LAST) := crec.target_guid; END LOOP; END IF; -- Now locate targets monitoring instances and add them to our arrays SELECT LOWER(instance_name) BULK COLLECT INTO l_database_array FROM gv$instance; -- Find instance database targets and associated agents FOR i IN l_database_array.FIRST..l_database_array.LAST LOOP FOR crec IN (SELECT a.target_guid FROM mgmt_targets a, mgmt_target_properties b, mgmt_target_properties c WHERE a.target_type IN (MGMT_GLOBAL.G_DATABASE_TARGET_TYPE, MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE) AND a.target_guid = b.target_guid AND b.property_name = 'MachineName' AND LOWER(b.property_value) LIKE (l_host||'%') AND a.target_guid = c.target_guid AND c.property_name = 'SID' AND LOWER(c.property_value) = l_database_array(i) AND a.target_guid = b.target_guid) LOOP guids.extend(1); guids(guids.LAST) := crec.target_guid; END LOOP; END LOOP; RETURN guids; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN MGMT_USER_GUID_ARRAY(); END GET_REPOSITORY_TARGETS; PROCEDURE CREATE_POLICY( --Policies parameters p_target_type IN VARCHAR2, p_policy_name IN VARCHAR2, p_metric_name IN VARCHAR2, p_policy_label_nlsid IN VARCHAR2 DEFAULT NULL, p_description IN VARCHAR2 DEFAULT ' ', p_description_nlsid IN VARCHAR2 DEFAULT NULL, p_impact IN VARCHAR2 DEFAULT ' ', p_impact_nlsid IN VARCHAR2 DEFAULT NULL, -- p_clear_message IN VARCHAR2 DEFAULT NULL, -- p_clear_message_nlsid IN VARCHAR2 DEFAULT NULL, p_recommendation IN VARCHAR2 DEFAULT ' ', p_recommendation_nlsid IN VARCHAR2 DEFAULT NULL, p_violation_level IN NUMBER DEFAULT NULL, p_condition_type IN NUMBER DEFAULT 1, p_condition IN VARCHAR2 DEFAULT NULL, p_condition_operator IN NUMBER DEFAULT 0, p_message IN VARCHAR2 DEFAULT NULL, p_message_nlsid IN VARCHAR2 DEFAULT NULL, p_clear_message IN VARCHAR2 DEFAULT NULL, p_clear_message_nlsid IN VARCHAR2 DEFAULT NULL, p_auto_enable IN NUMBER := 1, p_cs_consider_percentage IN NUMBER DEFAULT 0, p_start_type_meta_ver IN VARCHAR2 DEFAULT '1.0', p_end_type_meta_ver IN VARCHAR2 DEFAULT NULL, p_policy_param_list IN MGMT_POLICY_PARAM_DEF_ARRAY DEFAULT NULL, p_dflt_param_val_list IN MGMT_POLICY_KEY_VAL_ARRAY DEFAULT NULL, p_viol_ctxt_list IN MGMT_VIOL_CTXT_DEF_ARRAY DEFAULT NULL ) AS l_category_list MGMT_CATEGORY_ARRAY; l_author VARCHAR2(256) ; BEGIN l_category_list := MGMT_CATEGORY_ARRAY( MGMT_CATEGORY_OBJ.NEW( p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL, p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_SECURITY)); l_author := 'ORACLE' ; MGMT_POLICY.CREATE_POLICY( p_target_type => p_target_type, p_policy_name => p_policy_name, p_metric_name => p_metric_name, p_author =>l_author, p_policy_label_nlsid => p_policy_label_nlsid, p_description => p_description, p_description_nlsid => p_description_nlsid, p_impact => p_impact, p_impact_nlsid => p_impact_nlsid, p_recommendation => p_recommendation, p_recommendation_nlsid => p_recommendation_nlsid, p_violation_level => p_violation_level, p_condition_type => p_condition_type, p_condition => p_condition, p_condition_operator => p_condition_operator, p_message => p_message, p_message_nlsid => p_message_nlsid, p_clear_message => p_clear_message, p_clear_message_nlsid => p_clear_message_nlsid, p_auto_enable => p_auto_enable, p_cs_consider_percentage => p_cs_consider_percentage, p_start_type_meta_ver => p_start_type_meta_ver, p_end_type_meta_ver => p_end_type_meta_ver, p_category_list => l_category_list, p_policy_param_list => p_policy_param_list, p_dflt_param_val_list => p_dflt_param_val_list, p_viol_ctxt_list => p_viol_ctxt_list ); COMMIT ; IF p_auto_enable = 1 THEN MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS( p_target_type => p_target_type, p_policy_name => p_policy_name ); COMMIT ; END IF ; END; --ESM Modify Policy PROCEDURE MODIFY_POLICY( --Policies parameters p_target_type IN VARCHAR2, p_policy_name IN VARCHAR2, p_metric_name IN VARCHAR2, p_policy_label_nlsid IN VARCHAR2 DEFAULT NULL, p_description IN VARCHAR2 DEFAULT ' ', p_description_nlsid IN VARCHAR2 DEFAULT NULL, p_impact IN VARCHAR2 DEFAULT ' ', p_impact_nlsid IN VARCHAR2 DEFAULT NULL, -- p_clear_message IN VARCHAR2 DEFAULT NULL, -- p_clear_message_nlsid IN VARCHAR2 DEFAULT NULL, p_recommendation IN VARCHAR2 DEFAULT ' ', p_recommendation_nlsid IN VARCHAR2 DEFAULT NULL, p_violation_level IN NUMBER DEFAULT NULL, p_condition_type IN NUMBER DEFAULT 1, p_condition IN VARCHAR2 DEFAULT NULL, p_condition_operator IN NUMBER DEFAULT 0, p_message IN VARCHAR2 DEFAULT NULL, p_message_nlsid IN VARCHAR2 DEFAULT NULL, p_clear_message IN VARCHAR2 DEFAULT NULL, p_clear_message_nlsid IN VARCHAR2 DEFAULT NULL, p_auto_enable IN NUMBER := 1, p_cs_consider_percentage IN NUMBER DEFAULT 0, p_start_type_meta_ver IN VARCHAR2 DEFAULT '1.0', p_end_type_meta_ver IN VARCHAR2 DEFAULT NULL, p_policy_param_list IN MGMT_POLICY_PARAM_DEF_ARRAY DEFAULT NULL, p_dflt_param_val_list IN MGMT_POLICY_KEY_VAL_ARRAY DEFAULT NULL, p_viol_ctxt_list IN MGMT_VIOL_CTXT_DEF_ARRAY DEFAULT NULL ) AS l_category_list MGMT_CATEGORY_ARRAY; l_author VARCHAR2(256) ; BEGIN l_category_list := MGMT_CATEGORY_ARRAY( MGMT_CATEGORY_OBJ.NEW( p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL, p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_SECURITY)); l_author := 'ORACLE' ; MGMT_POLICY.MODIFY_POLICY( p_target_type => p_target_type, p_policy_name => p_policy_name, p_metric_name => p_metric_name, p_author =>l_author, p_policy_label_nlsid => p_policy_label_nlsid, p_description => p_description, p_description_nlsid => p_description_nlsid, p_impact => p_impact, p_impact_nlsid => p_impact_nlsid, p_recommendation => p_recommendation, p_recommendation_nlsid => p_recommendation_nlsid, p_violation_level => p_violation_level, p_condition_type => p_condition_type, p_condition => p_condition, p_condition_operator => p_condition_operator, p_message => p_message, p_message_nlsid => p_message_nlsid, p_clear_message => p_clear_message, p_clear_message_nlsid => p_clear_message_nlsid, p_auto_enable => p_auto_enable, p_cs_consider_percentage => p_cs_consider_percentage, p_start_type_meta_ver => p_start_type_meta_ver, p_end_type_meta_ver => p_end_type_meta_ver, p_category_list => l_category_list, p_policy_param_list => p_policy_param_list, p_dflt_param_val_list => p_dflt_param_val_list, p_viol_ctxt_list => p_viol_ctxt_list ); COMMIT ; END; PROCEDURE DELETE_POLICY( p_target_type VARCHAR2, p_policy_name VARCHAR2 ) AS BEGIN MGMT_POLICY.DELETE_POLICY( p_target_type => p_target_type, p_policy_name => p_policy_name ); COMMIT ; END ; PROCEDURE DELETE_POLICY_METADATA( p_target_type VARCHAR2, p_policy_name VARCHAR2 ) AS BEGIN MGMT_POLICY.DELETE_POLICY_METADATA( p_target_type => p_target_type, p_policy_name => p_policy_name ); COMMIT ; END ; PROCEDURE EVAL_POLICY10GR2( p_target_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2, p_metric_values OUT mgmt_metric_Value_array ) AS BEGIN p_metric_values := mgmt_metric_Value_array() ; SELECT mgmt_metric_value_obj.new( target_guid,0 , 'status_message' , mgmt_namevalue_array( mgmt_namevalue_obj.new('profile', value), mgmt_namevalue_obj.new('limit', value2) ) ) BULK COLLECT INTO p_metric_values FROM esm_collection_latest e WHERE property='Unlimited_login_attempts' AND ( (value not in (select profile from dba_users where username=user)) OR (p_target_guid not in (SELECT DISTINCT * FROM TABLE(CAST(GET_REPOSITORY_TARGETS AS MGMT_USER_GUID_ARRAY)))) ) AND e.target_guid = p_target_guid order by target_guid ; END EVAL_POLICY10GR2; PROCEDURE EVAL_POLICY10GR1( p_target_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2, p_metric_values OUT mgmt_metric_Value_array ) AS BEGIN p_metric_values := mgmt_metric_Value_array() ; SELECT mgmt_metric_value_obj.new( target_guid,0 , 'status_message' , mgmt_namevalue_array( mgmt_namevalue_obj.new('dbuser', value), mgmt_namevalue_obj.new('dummy', value2) ) ) BULK COLLECT INTO p_metric_values FROM esm_collection_latest e WHERE property='Unlimited login attempts' AND ( (value != user) OR (p_target_guid not in (SELECT DISTINCT * FROM TABLE(CAST(GET_REPOSITORY_TARGETS AS MGMT_USER_GUID_ARRAY)))) ) AND e.target_guid = p_target_guid order by target_guid ; END EVAL_POLICY10GR1; PROCEDURE get_console_homepage_info( p_time_period IN VARCHAR2, p_ext_viol_cur_out OUT cursorType, --existing violations by severity p_new_viol_cur_out OUT cursorType --new violations in last p_time_period days ) IS cnt NUMBER; sec_policies GUID_ARRAY; CURSOR c IS SELECT p.policy_guid as policy_guid FROM mgmt_policies p, mgmt_category_map c WHERE p.policy_guid = c.object_guid AND c.category_name = 'Security' AND c.class_name = 'Functional' AND c.object_type = 2; BEGIN OPEN c; FETCH c BULK COLLECT INTO sec_policies; --current violations by severity OPEN p_ext_viol_cur_out FOR SELECT NVL(SUM(DECODE(e.max_violation_level,18,e.non_exempt_violations_logged,0)),0) information_count, NVL(SUM(DECODE(e.max_violation_level,20, e.non_exempt_violations_logged,0)),0) warning_count, NVL(SUM(DECODE(e.max_violation_level,25,e.non_exempt_violations_logged,0)),0) critical_count FROM mgmt_policy_assoc_eval_summ e WHERE EXISTS (SELECT 1 FROM TABLE(CAST (sec_policies AS GUID_ARRAY)) g WHERE g.column_value = e.policy_guid) AND EXISTS (SELECT 1 FROM mgmt_targets tgt WHERE tgt.target_guid = e.target_guid); --New violations in the last 24 hrs OPEN p_new_viol_cur_out FOR SELECT NVL(SUM(DECODE(s.violation_level,18,1,20,1,25,1,0)),0) new FROM TABLE(CAST (sec_policies AS GUID_ARRAY)) g, mgmt_violations s WHERE s.policy_guid = g.column_value AND s.violation_type = 3 AND s.collection_timestamp >= sysdate - p_time_period AND EXISTS (SELECT 1 FROM mgmt_targets t WHERE t.target_guid = s.target_guid); END get_console_homepage_info; END ESM; / COMMIT ; show errors; /* */ /* create or replace package body ESM as CONS_SECURITY_POLICY_AUTHOR constant MGMT_POLICY_GROUP.AUTHOR%TYPE:= 'Oracle'; CONS_SECURITY_POLICY_NAME constant MGMT_POLICY_GROUP.POLICY_NAME%TYPE := 'Security Best Practices'; CONS_SECURITY_POLICY_VERSION constant MGMT_POLICY_GROUP.VERSION%TYPE:= '1.0'; CONS_SECURITY_POLICY_CATEGORY constant MGMT_POLICY_RULE.CATEGORY%TYPE := 'Security'; CONS_SECURITY_SNAPSHOT_TYPE constant MGMT_ECM_SNAPSHOT_METADATA.SNAPSHOT_TYPE%TYPE := 'oracle_security'; -- This procedure creates a single policy for security called CONS_SECURITY_POLICY_NAME. -- This policy covers all rules from CONS_SECURITY_POLICY_AUTHOR of category CONS_SECURITY_POLICY_CATEGORY. -- It further registers this policy to execute automatically for a target of any type. -- This procedure must be invoked AFTER adding a new target type, if the user -- wishes to define Security policies on this new target. -- NOTE: Commit is not called automatically. procedure CREATE_POLICY as policyId MGMT_POLICY_GROUP.POLICY_ID%TYPE; begin policyId := MGMT_ECM_POLICY.CREATE_POLICY_GROUP( CONS_SECURITY_POLICY_AUTHOR, -- p_author CONS_SECURITY_POLICY_NAME, -- p_policyName CONS_SECURITY_POLICY_VERSION, -- p_policyVersion 'Common security best practices from Oracle', -- p_description -- p_ruleCriteria ECM_POLICY_RULE_CRITERIA( ECM_POLICY_RULE_CRITERION( CONS_SECURITY_POLICY_AUTHOR, '%', CONS_SECURITY_POLICY_CATEGORY, '%' ) ), -- p_targetCriteria ECM_POLICY_TARGET_CRITERIA( ECM_POLICY_TARGET_CRITERION( 'N', '%', '%', 'LIKE' ) ), NULL, -- p_paramList NULL -- p_displayId ); -- Insert this policy id for all snapshots of type CONS_SECURITY_SNAPSHOT_TYPE -- for any existing target type associated with this snapshot type insert into mgmt_policy_snapshot_criteria (target_type, policy_id, snapshot_type) (select distinct(target_type), policyId, snapshot_type from mgmt_ecm_snapshot_metadata where snapshot_type = CONS_SECURITY_SNAPSHOT_TYPE); end; procedure CREATE_RULE( test_name VARCHAR2, -- Name of test test_description VARCHAR2, -- Text description of test severity VARCHAR2, -- Severity: 'C' | 'W' | 'I' impact VARCHAR2, -- Text description of impact of violation recommendation VARCHAR2, -- Text description of recommendation target_type VARCHAR2, -- EM Target Type from MGMT_TARGETS esm_metric_name VARCHAR2, -- Name of metric which collects property below (for future use) property_name VARCHAR2, -- Name of property used by the rule value_expression VARCHAR2 := 'VALUE', -- Expression for the column value shown in UI value_expression_type VARCHAR2 := 'T', -- Type: 'T' for text, 'N' for numeric, 'D' for date property_display_name VARCHAR2 := null, -- Default display name for the property (used if no i18n) value_selection_expression VARCHAR2 := null,-- Further filter using VALUE URL VARCHAR2 := null, -- Not sure what to do with this yet rule_display_Id VARCHAR2 := null, -- for i18n support property_display_Id VARCHAR2 := null -- for i18n support ) as ruleText MGMT_POLICY_RULE.RULE_OBJECT%TYPE; priority number; format number; column_name VARCHAR2(31); --MGMT_ECM_POLICY.ECM_RULE_COLUMN.COLUMN_NAME%TYPE; ecm_rule ECM_POLICY_RULE; begin if (property_display_Id is not null) then column_name := property_display_Id; else column_name := 'EXPRESSION'; end if; ruleText := 'select TARGET_GUID, '||value_expression|| ' as '||column_name|| ' from esm_collection_latest'|| ' where PROPERTY = '''||property_name||''''; if (value_selection_expression is not null) then ruleText := ruleText||' and '||value_selection_expression; end if; -- Set the severity -- if (severity = 'C') then priority := MGMT_ECM_POLICY.CONS_HIGH; elsif (severity = 'W') then priority := MGMT_ECM_POLICY.CONS_MEDIUM; else priority := MGMT_ECM_POLICY.CONS_INFORMATIONAL; end if; -- Set the format -- if (value_expression_type = 'N') then format := MGMT_ECM_POLICY.CONS_NUMBER_FORMAT; elsif (value_expression_type = 'D') then format := MGMT_ECM_POLICY.CONS_DATE_FORMAT; else format := MGMT_ECM_POLICY.CONS_TEXT_FORMAT; end if; ecm_rule := MGMT_ECM_POLICY.CREATE_POLICY_RULE( CONS_SECURITY_POLICY_AUTHOR, -- p_author target_type, -- p_targetType CONS_SECURITY_POLICY_CATEGORY, -- p_category test_name, -- p_ruleName CONS_SECURITY_POLICY_VERSION, -- p_ruleVersion test_description, -- p_description 'QUERY', -- p_ruleType ruleText, -- p_ruleObject 'rownum > 0', -- p_test priority, -- p_priority impact, -- p_impactOfProblem recommendation, -- p_recommendation -- p_columnList ECM_RULE_COLUMN_LIST( ECM_RULE_COLUMN(column_name, 'Y', 'Y', URL, NVL(property_display_name, 'Expression'), format ) ), NULL, -- p_paramList rule_display_Id, -- p_displayId NULL, -- p_updateComment true); -- p_replaceIfExists -- For debugging purposes, print out the exact text of the rule -- dbms_output.put_line('Rule [' || ruleText || ']'); end; end ESM; / show errors; */
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de