Rem Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/11.2.0.0/esm/upgrade_create_db_metrics.sql /st_emdbsa_11.2/1 2009/03/28 20:00:56 sudvarma Exp $ Rem Rem upgrade_create_db_metrics.sql Rem Rem Copyright (c) 2008, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem upgrade_create_db_metrics.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem sudvarma 03/14/09 - Created Rem SET DEFINE OFF DECLARE -- Metric Variable l_metric_column_list MGMT_METRIC_COLUMN_ARRAY; l_metric_type NUMBER ; l_snapshot_list MGMT_SNAPSHOT_ARRAY; l_snapshot_list_rac MGMT_SNAPSHOT_ARRAY; l_snapshot_list_inst_10gR1 MGMT_SNAPSHOT_ARRAY; l_snapshot_list_inst_10gR2 MGMT_SNAPSHOT_ARRAY; l_10gR1_db_end_meta_ver VARCHAR2(4) ; l_valid_if_list MGMT_VALIDIF_ARRAY ; l_rac_database CONSTANT VARCHAR2(12) := 'rac_database'; -- New snapshot name for the instance specific oracle_security metrics is oracle_security_inst l_oracle_security_inst CONSTANT VARCHAR2(20) := 'oracle_security_inst'; pol_eval_func_10gR1 CONSTANT VARCHAR2(50) := 'ESM.EVAL_POLICY10GR1' ; BEGIN l_metric_type := MGMT_GLOBAL.G_METRIC_TYPE_TABLE ; l_snapshot_list := MGMT_SNAPSHOT_ARRAY( MGMT_SNAPSHOT_OBJ.NEW( p_target_type => ESM.DB_TARGET_TYPE, p_snapshot_name => ESM.SECURITY_SNAPSHOT_TYPE)); l_snapshot_list_rac := MGMT_SNAPSHOT_ARRAY( MGMT_SNAPSHOT_OBJ.NEW( p_target_type => l_rac_database, p_snapshot_name => ESM.SECURITY_SNAPSHOT_TYPE)); -- This snapshot list is prepared for all the db instance -- security metrics which are applicable For both 10gR1 + 10gR2. -- In 10gR2 release oracle_security snapshot name has split up into two -- oracle_security + oracle_security_int(all db instance security metric used this) -- oracle_security_inst snapshot name is included in the snapshot list -- bcoz 10gR2 agent will upload the instance data with this snapshot name -- Snapshot name used by 10gR1 agent remains same so included to support the -- backward compatibility as 10gR1 agent uploads the data for -- those metric with oracle_security snapshot name. l_snapshot_list_inst_10gR1 := MGMT_SNAPSHOT_ARRAY( MGMT_SNAPSHOT_OBJ.NEW( p_target_type => ESM.DB_TARGET_TYPE, p_snapshot_name => l_oracle_security_inst), MGMT_SNAPSHOT_OBJ.NEW( p_target_type => ESM.DB_TARGET_TYPE, p_snapshot_name => ESM.SECURITY_SNAPSHOT_TYPE)); -- for 10gR2 db instance specific metrics => this is oracle_security_inst l_snapshot_list_inst_10gR2 := MGMT_SNAPSHOT_ARRAY( MGMT_SNAPSHOT_OBJ.NEW( p_target_type => ESM.DB_TARGET_TYPE, p_snapshot_name => l_oracle_security_inst)); ----------->> Execute_And_Other_Privleges_On_Dir_Obj <<<-------- l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY('DB'))); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'directory', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Directory Object', p_column_label_nlsid =>'DBDIRECTORY_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'directorypath', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Directory Object Path', p_column_label_nlsid =>'DBDIRECTORY_PATH_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'esm_dirObjUsersWithExeAndOtherPrivsRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, -- Applicable for 10gR2 p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type, p_usage_type => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT, p_metric_label => 'Execute and other privileges on Directory Objects', p_metric_label_nlsid => 'EXECUTE_OTHER_PRIV_DIROBJ_METRIC_LABEL', p_description => 'Collect the information of execute and other privileges on Directory Objects', p_description_nlsid => 'EXECUTE_OTHER_PRIV_DIROBJ_METRIC_DESC', p_is_repository => MGMT_GLOBAL.G_TRUE , p_author => ESM.METRIC_AUTHOR, p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY, p_source =>'SELECT target_guid,value as directory,value2 as directorypath ' ||'FROM esm_collection_latest ' ||'WHERE property=''esm_dirObjUsersWithExeAndOtherPrivs''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ); COMMIT; MGMT_METRIC.CREATE_METRIC( p_target_type => 'rac_database', p_metric_name => 'esm_dirObjUsersWithExeAndOtherPrivsRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, -- Applicable for 10gR2 -- p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type, p_usage_type => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT, p_metric_label => 'Execute and other privileges on Directory Objects', p_metric_label_nlsid => 'EXECUTE_OTHER_PRIV_DIROBJ_METRIC_LABEL', p_description => 'Collect the information of execute and other privileges on Directory Objects', p_description_nlsid => 'EXECUTE_OTHER_PRIV_DIROBJ_METRIC_DESC', p_is_repository => MGMT_GLOBAL.G_TRUE , p_author => ESM.METRIC_AUTHOR, p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY, p_source =>'SELECT target_guid,value as directory,value2 as directorypath ' ||'FROM esm_collection_latest ' ||'WHERE property=''esm_dirObjUsersWithExeAndOtherPrivs''', p_snapshot_name_list => l_snapshot_list_rac, p_metric_column_list => l_metric_column_list ); COMMIT; ----------->> Public_Having_Execute_Privleges_On_Dir_Obj <<<-------- l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY('DB'))); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'directory', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Directory Object', p_column_label_nlsid =>'DBDIRECTORY_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'directorypath', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Directory Object Path', p_column_label_nlsid =>'DBDIRECTORY_PATH_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'esm_publicHavingExePrivOnDirObjRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, -- Applicable for 10gR2 p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type, p_usage_type => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT, p_metric_label => 'PUBLIC Having EXECUTE privilege on Directory Objects', p_metric_label_nlsid => 'PUBLIC_EXECUTE_PRIV_DIROBJ_METRIC_LABEL', p_description => 'Collect the information of Directory Objects with PUBLIC having EXECUTE privilege.', p_description_nlsid => 'PUBLIC_EXECUTE_PRIV_DIROBJ_METRIC_DESC', p_is_repository => MGMT_GLOBAL.G_TRUE , p_author => ESM.METRIC_AUTHOR, p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY, p_source =>'SELECT target_guid,value as directory,value2 as directorypath ' ||'FROM esm_collection_latest ' ||'WHERE property=''esm_publicHavingExePrivOnDirObj''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ); COMMIT; MGMT_METRIC.CREATE_METRIC( p_target_type => 'rac_database', p_metric_name => 'esm_publicHavingExePrivOnDirObjRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, -- Applicable for 10gR2 -- p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type, p_usage_type => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT, p_metric_label => 'PUBLIC Having EXECUTE privilege on Directory Objects', p_metric_label_nlsid => 'PUBLIC_EXECUTE_PRIV_DIROBJ_METRIC_LABEL', p_description => 'Collect the information of Directory Objects with PUBLIC having EXECUTE privilege.', p_description_nlsid => 'PUBLIC_EXECUTE_PRIV_DIROBJ_METRIC_DESC', p_is_repository => MGMT_GLOBAL.G_TRUE , p_author => ESM.METRIC_AUTHOR, p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY, p_source =>'SELECT target_guid,value as directory,value2 as directorypath ' ||'FROM esm_collection_latest ' ||'WHERE property=''esm_publicHavingExePrivOnDirObj''', p_snapshot_name_list => l_snapshot_list_rac, p_metric_column_list => l_metric_column_list ); COMMIT; END; / SET DEFINE ON