Rem Rem $Header: db_policies.sql 23-nov-2005.02:04:52 dkjain Exp $ Rem Rem db_policies.sql Rem Rem Copyright (c) 2005, Oracle. All rights reserved. Rem Rem NAME Rem db_policies.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem dkjain 11/23/05 - Reverting changes 4637345 due to new upgrade procedure Rem dkjain 11/14/05 - Fixing bug-4637345 Rem dkjain 07/31/05 - Making identical to Beta Upgr Rem dsukhwal 07/11/05 - change english names of windows policies Rem dkjain 06/27/05 - Added oracle_security_inst object in the Rem snapshot list Rem dkjain 06/17/05 - Hide the rep metrics Rem dsukhwal 06/16/05 - add policies for NT Rem dsukhwal 05/19/05 - move sqlnet policies to listener Rem dkjain 04/26/05 - Changed the message desc string for Rem PWD_REUSE_MAX_DESC Rem rmadampa 04/22/05 - rmadampa_bug-4247709 Rem rmadampa 04/21/05 - Created Rem SET ESCAPE '\' ; SET ESCAPE ON ; DECLARE -- Metric Variable l_metric_column_list MGMT_METRIC_COLUMN_ARRAY; l_metric_type NUMBER ; l_snapshot_list MGMT_SNAPSHOT_ARRAY; -- Policy Variable l_ctxList MGMT_VIOL_CTXT_DEF_ARRAY; l_paramDefList MGMT_POLICY_PARAM_DEF_ARRAY; l_defaultParamValList MGMT_POLICY_PARAM_VAL_ARRAY; l_defaultSettingsList MGMT_POLICY_KEY_VAL_ARRAY; l_auto_enable NUMBER ; l_10gR1_db_meta_ver VARCHAR2(3) ; l_valid_if_list MGMT_VALIDIF_ARRAY ; BEGIN l_10gR1_db_meta_ver := '3.0' ; 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)); -- At many places dummy column has been used as one of the repository metric column. -- The reason being one constraint being imposed by Policy frame that atleast -- one repo column should be defined as non-key column. -- Metric collects more than one row per database target -- All rows are violation row -- Policy may result more than one violation row 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 => 'dbuser', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Database User', p_column_label_nlsid =>'DBUSER_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'tablespace_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Tablespace Name', p_column_label_nlsid =>'TBSPC_NAME_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'tableSpaceQuotaRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type, p_metric_label => 'Database Users With Tablespace Quota', p_metric_label_nlsid => 'TBSPC_NAME_METRIC_LABEL', p_description => 'Collect users tablespace quota information', p_description_nlsid => 'TBSPC_NAME_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 dbuser,value2 as tablespace_name,value2 as dummy ' ||'FROM esm_collection_latest ' ||'WHERE property =''table_space_quotas''', 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 => 'tableSpaceQuotaRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type, p_metric_label => 'Database Users With Tablespace Quota', p_metric_label_nlsid => 'TBSPC_NAME_METRIC_LABEL', p_description => 'Collect users tablespace quota information', p_description_nlsid => 'TBSPC_NAME_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 dbuser,value2 as tablespace_name,value2 as dummy ' ||'FROM esm_collection_latest ' ||'WHERE property =''table_space_quotas''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'dbuser', p_url_link_template =>'database/security/user?oname=^dbuser^\&event=edit\&otype=USER\&target=^TARGET_NAME^\&type=^TARGET_TYPE^\&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'tablespace_name')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Unlimited_Table_Space_Quota', --Policy name p_metric_name =>'tableSpaceQuotaRep', --Metric name p_policy_label_nlsid =>'TABLESPACE_QUOTA_NAME', p_description =>'Ensures database users are allocated a limited tablespace quota', p_description_nlsid =>'TABLESPACE_QUOTA_DESC', p_impact =>'Granting unlimited tablespace quotas can cause the filling up of '|| 'the allocated disk space. This can lead to an unresponsive database.', p_impact_nlsid =>'TABLESPACE_QUOTA_IMPACT', p_recommendation =>'For users with an unlimited tablespace quota, reallocate their '|| 'tablespace quotas to a specific limit.', p_recommendation_nlsid =>'TABLESPACE_QUOTA_RECOMM' , p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database may be in an insecure state as user %dbuser% has an unlimited '|| 'tablespace quota for the tablespace:%tablespace_name%.', p_message_nlsid =>'TABLESPACE_QUOTA_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); ESM.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name =>'Unlimited_Table_Space_Quota', --Policy name p_metric_name =>'tableSpaceQuotaRep', --Metric name p_policy_label_nlsid =>'TABLESPACE_QUOTA_NAME', p_description =>'Ensures database users are allocated a limited tablespace quota', p_description_nlsid =>'TABLESPACE_QUOTA_DESC', p_impact =>'Granting unlimited tablespace quotas can cause the filling up of '|| 'the allocated disk space. This can lead to an unresponsive database.', p_impact_nlsid =>'TABLESPACE_QUOTA_IMPACT', p_recommendation =>'For users with an unlimited tablespace quota, reallocate their '|| 'tablespace quotas to a specific limit.', p_recommendation_nlsid =>'TABLESPACE_QUOTA_RECOMM' , p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database may be in an insecure state as user %dbuser% has an unlimited '|| 'tablespace quota for the tablespace:%tablespace_name%.', p_message_nlsid =>'TABLESPACE_QUOTA_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); -- Metric collects atmost one row per database target -- All rows are violation row -- Policy may result only one violation row -- DB Audit parameter Metrics And Policies l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'permission', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Permission (In Octal)', p_column_label_nlsid =>'PERM_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dir_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Audit File Directory (Absolute Path)', p_column_label_nlsid =>'AUDIT_FILE_DIR_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'auditFileDestRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Audit Files Directory Permission', p_metric_label_nlsid => 'DBAUDIT_FILE_DIR_METRIC_LABEL', p_description => 'Collect permissions of the folder specified by AUDIT_FILE_DEST initialization parameter', p_description_nlsid => 'DBAUDIT_FILE_DIR_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 permission, ' ||'value2 as dir_name,value2 as dummy FROM esm_collection_latest ' ||'WHERE property =''audit_file_dest''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; -- Policies l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'permission'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'dir_name') ); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Audit_File_Destination', --Policy name p_metric_name =>'auditFileDestRep', --Metric name p_policy_label_nlsid =>'AUDIT_FILE_DEST_NAME', p_description =>'Ensures that access to the audit files directory is restricted to '|| 'the owner of the Oracle software set and the DBA group.', p_description_nlsid =>'AUDIT_FILE_DEST_DESC', p_impact => 'The AUDIT_FILE_DEST initialization parameter specifies the directory where '|| 'the Oracle auditing facility creates the audit files. Giving public read '|| 'permission to this directory may reveal important information such as logging '|| 'information of startup, shutdown, and privileged connections.', p_impact_nlsid =>'AUDIT_FILE_DEST_IMPACT', p_recommendation =>'Restrict permissions to the Audit File directory to the owner of the '|| 'Oracle software set and DBA group. Do not give read, write, and execute permissions to public.', p_recommendation_nlsid =>'AUDIT_FILE_DEST_RECOMM' , p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'The audit file directory has insecure permissions. The audit file directory (%dir_name%) '|| 'permission is %permission%.', p_message_nlsid =>'AUDIT_FILE_DEST_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Metric collects atmost one row per database target -- All rows are violation row -- Policy may result only one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'permission', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Permission (In Octal)', p_column_label_nlsid =>'PERM_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dir_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'User Dump Directory (Absolute Path)', p_column_label_nlsid =>'USER_DUMP_DIR_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'userDumpDestRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'User Dump Directory Permission', p_metric_label_nlsid => 'USER_DUMP_DEST_METRIC_LABEL', p_description => 'Collect permissions of the folder specified by USER_DUMP_DEST initialization parameter', p_description_nlsid => 'USER_DUMP_DEST_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 permission, ' ||'value2 as dir_name,value2 as dummy FROM esm_collection_latest ' ||'WHERE property =''user_dump_dest''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'permission'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'dir_name')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'User_Dump_Destination', --Policy name p_metric_name =>'userDumpDestRep', --Metric name p_policy_label_nlsid =>'USER_DUMP_DEST_NAME', p_description =>'Ensures that access to the trace files directory is restricted '|| 'to the owner of the Oracle software set and the DBA group', p_description_nlsid =>'USER_DUMP_DEST_DESC', p_impact => 'The trace files for server processes are stored in the directory '|| 'specified by the USER_DUMP_DEST initialization parameter. Giving '|| 'public read permission to this directory may reveal important and '|| 'sensitive internal details of the database and applications.', p_impact_nlsid =>'USER_DUMP_DEST_IMPACT', p_recommendation =>'Restrict permissions to the user dump directory to the owner '|| 'of the Oracle software set and DBA group. Do not give read, write, and '|| 'execute permissions to public.', p_recommendation_nlsid =>'USER_DUMP_DEST_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'The user dump directory has insecure permissions. The user dump directory (%dir_name%) '|| 'permission is %permission%.', p_message_nlsid =>'USER_DUMP_DEST_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Metric collects atmost one row per database target -- All rows are violation row -- Policy may result only one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'permission', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Permission (In Octal)', p_column_label_nlsid =>'PERM_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dir_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Background Dump Directory (Absolute Path)', p_column_label_nlsid =>'BKGRND_DUMP_DIR_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'backgrdDumpDestRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Background Dump Directory Permission', p_metric_label_nlsid => 'BKGRND_DUMP_DIR_METRIC_LABEL', p_description => 'Collect permissions of the folder specified by '|| 'BACKGROUND_DUMP_DEST initialization parameter', p_description_nlsid => 'BKGRND_DUMP_DIR_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 permission, ' ||'value2 as dir_name,value2 as dummy FROM esm_collection_latest ' ||'WHERE property =''background_dump_dest''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'permission'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'dir_name') ); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Background_Dump_Destination', --Policy name p_metric_name =>'backgrdDumpDestRep', --Metric name p_policy_label_nlsid =>'BKGRND_DUMP_DEST_NAME', p_description =>'Ensures that access to the trace files directory is restricted '|| 'to the owner of the Oracle software set and the DBA group', p_description_nlsid =>'BKGRND_DUMP_DEST_DESC', p_impact => 'Background processes such as the log writer process and the database '|| 'writer process use trace files to record occurrences and exceptions of '|| 'database operations, as well as errors. The trace files are stored in the '|| 'directory specified by the BACKGROUND_DUMP_DEST initialization parameter. '|| 'Giving public read permission to this directory may reveal important and '|| 'sensitive internal details of the database and applications.', p_impact_nlsid =>'BKGRND_DUMP_DEST_IMPACT', p_recommendation =>'Restrict permissions to the background dump directory to the '|| 'owner of the Oracle software set and DBA group. Do not give read, '|| 'write, and execute permissions to public.', p_recommendation_nlsid =>'BKGRND_DUMP_DEST_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'The background dump directory has insecure permissions. The background dump '|| 'directory (%dir_name%) permission is %permission%.', p_message_nlsid =>'BKGRND_DUMP_DEST_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Metric collects atmost one row per database target -- All rows are violation row -- Policy may result only one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'permission', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Permission (In Octal)', p_column_label_nlsid =>'PERM_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dir_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Core Dump Directory (Absolute Path)', p_column_label_nlsid =>'CORE_DUMP_DIR_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name =>'coreDumpDestRep', --Metric name p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Core Dump Directory Permission', p_metric_label_nlsid => 'CORE_DUMP_DIR_METRIC_LABEL', p_description => 'Collect permissions of the folder specified by CORE_DUMP_DEST initialization parameter', p_description_nlsid => 'CORE_DUMP_DIR_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, property, value as permission, ' ||'value2 as dir_name,value2 as dummy FROM esm_collection_latest ' ||'WHERE property =''core_dump_dest''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'permission'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'dir_name') ); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Core_Dump_Destination', --Policy name p_metric_name =>'coreDumpDestRep', --Metric name p_policy_label_nlsid =>'CORE_DUMP_DEST_NAME', p_description =>'Ensures that access to the core dump files directory is restricted '|| 'to the owner of the Oracle software set and the DBA group', p_description_nlsid =>'CORE_DUMP_DEST_DESC', p_impact => 'Core dump files are stored in the directory specified by the CORE_DUMP_DEST '|| 'initialization parameter. A public read privilege on this directory could '|| 'expose sensitive information from the core dump files.', p_impact_nlsid =>'CORE_DUMP_DEST_IMPACT', p_recommendation =>'Restrict permissions to the core dump directory to the owner of '|| 'the Oracle software set and DBA group. Do not give read, write, and execute permissions to public.', p_recommendation_nlsid =>'CORE_DUMP_DEST_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'The core dump directory has insecure permissions. The core dump directory (%dir_name%) '|| 'permission is %permission%.', p_message_nlsid =>'CORE_DUMP_DEST_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Control files Permission Metric -- Metric collects more than one row per database target -- All rows are violation row -- Policy may result more than one violation row 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 => 'permission', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Permission (In Octal)', p_column_label_nlsid =>'PERM_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'file_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Control File (Absolute Path)', p_column_label_nlsid =>'CTRL_FILE_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'dbControlFilesPermRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Control Files Permission', p_metric_label_nlsid => 'CTRL_FILE_METRIC_LABEL', p_description => 'Collect permissions of the files specified by CONTROL_FILE initialization parameter', p_description_nlsid => 'CTRL_FILE_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 permission, ' ||'value2 as file_name,value2 as dummy FROM esm_collection_latest ' ||'WHERE property=''db_control_files_perm''', 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 => 'dbControlFilesPermRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Control Files Permission', p_metric_label_nlsid => 'CTRL_FILE_METRIC_LABEL', p_description => 'Collect permissions of the files specified by CONTROL_FILE initialization parameter', p_description_nlsid => 'CTRL_FILE_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 permission, ' ||'value2 as file_name,value2 as dummy FROM esm_collection_latest ' ||'WHERE property=''db_control_files_perm''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; -- Policy l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'permission'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'file_name')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'DB_control_files_Permission', --Policy name p_metric_name =>'dbControlFilesPermRep', --Metric name p_policy_label_nlsid =>'CONTROL_FILES_NAME', p_description =>'Ensures that access to the control files directory is restricted to '|| 'the owner of the Oracle software set and the DBA group', p_description_nlsid =>'CONTROL_FILES_DESC', p_impact => 'Control files are binary configuration files that control access to data files. '|| 'Control files are stored in the directory specified by the CONTROL_FILES '|| 'initialization parameter. A public write privilege on this directory could '|| 'pose a serious security risk.', p_impact_nlsid =>'CONTROL_FILES_IMPACT', p_recommendation =>'Restrict permissions to the control files directory to the owner '|| 'of the Oracle software set and DBA group. Do not give read and write permissions to public.', p_recommendation_nlsid =>'CONTROL_FILES_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database is in an insecure state. The control file (%file_name%) permission is %permission%.', p_message_nlsid =>'CONTROL_FILES_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; ESM.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name =>'DB_control_files_Permission', --Policy name p_metric_name =>'dbControlFilesPermRep', --Metric name p_policy_label_nlsid =>'CONTROL_FILES_NAME', p_description =>'Ensures that access to the control files directory is restricted to '|| 'the owner of the Oracle software set and the DBA group', p_description_nlsid =>'CONTROL_FILES_DESC', p_impact => 'Control files are binary configuration files that control access to data files. '|| 'Control files are stored in the directory specified by the CONTROL_FILES '|| 'initialization parameter. A public write privilege on this directory could '|| 'pose a serious security risk.', p_impact_nlsid =>'CONTROL_FILES_IMPACT', p_recommendation =>'Restrict permissions to the control files directory to the owner '|| 'of the Oracle software set and DBA group. Do not give read and write permissions to public.', p_recommendation_nlsid =>'CONTROL_FILES_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database is in an insecure state. The control file (%file_name%) permission is %permission%.', p_message_nlsid =>'CONTROL_FILES_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Data Files Permissions Metric -- Metric collects more than one row per database target -- All rows are violation row -- Policy may result more than one violation row 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 => 'permission', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Permission (In Octal)', p_column_label_nlsid =>'PERM_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'file_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Data File (Absolute Path)', p_column_label_nlsid =>'DATA_FILE_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'dbDataFilesPermRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Data Files Permission', p_metric_label_nlsid => 'DATA_FILE_METRIC_LABEL', p_description => 'Collect permissions of data files', p_description_nlsid => 'DATA_FILE_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 permission, ' ||'value2 as file_name, value2 as dummy FROM esm_collection_latest ' ||'WHERE property=''oh_datafiles_perm''', 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 => 'dbDataFilesPermRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Data Files Permission', p_metric_label_nlsid => 'DATA_FILE_METRIC_LABEL', p_description => 'Collect permissions of data files', p_description_nlsid => 'DATA_FILE_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 permission, ' ||'value2 as file_name, value2 as dummy FROM esm_collection_latest ' ||'WHERE property=''oh_datafiles_perm''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; -- Policy l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'permission'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'file_name')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Oracle_Home_Data_Files_Permission', --Policy name p_metric_name =>'dbDataFilesPermRep', --Metric name p_policy_label_nlsid =>'OH_DATAFILES_PERM_NAME', p_description =>'Ensures that access to the datafiles is restricted to '|| 'the owner of the Oracle software set and the DBA group', p_description_nlsid =>'OH_DATAFILES_PERM_DESC', p_impact => 'The datafiles contain all the database data. If datafiles are readable '|| 'to public, they can be read by a user who has no database privileges on the data.', p_impact_nlsid =>'OH_DATAFILES_PERM_IMPACT', p_recommendation =>'Restrict permissions to the datafiles to the owner of the Oracle '|| 'software set and DBA group. Do not give read and write permissions to public.', p_recommendation_nlsid =>'OH_DATAFILES_PERM_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database is in an insecure state. The datafile (%file_name%) permission is %permission%.', p_message_nlsid =>'OH_DATAFILES_PERM_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; ESM.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name =>'Oracle_Home_Data_Files_Permission', --Policy name p_metric_name =>'dbDataFilesPermRep', --Metric name p_policy_label_nlsid =>'OH_DATAFILES_PERM_NAME', p_description =>'Ensures that access to the datafiles is restricted to '|| 'the owner of the Oracle software set and the DBA group', p_description_nlsid =>'OH_DATAFILES_PERM_DESC', p_impact => 'The datafiles contain all the database data. If datafiles are readable '|| 'to public, they can be read by a user who has no database privileges on the data.', p_impact_nlsid =>'OH_DATAFILES_PERM_IMPACT', p_recommendation =>'Restrict permissions to the datafiles to the owner of the Oracle '|| 'software set and DBA group. Do not give read and write permissions to public.', p_recommendation_nlsid =>'OH_DATAFILES_PERM_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database is in an insecure state. The datafile (%file_name%) permission is %permission%.', p_message_nlsid =>'OH_DATAFILES_PERM_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- parameter Files Permission Metric -- Metric collects atmost one row per database target -- All rows are violation row -- Policy may result in exactly one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'permission', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Permission (In Octal)', p_column_label_nlsid =>'PERM_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'file_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Server Parameter File (Absolute Path)', p_column_label_nlsid =>'SPFILE_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'spfilePermRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Server Parameter File(SPFILE) Permission', p_metric_label_nlsid => 'SPFILE_METRIC_LABEL', p_description => 'Collect permissions of the server parameter file(SPFILE)', p_description_nlsid => 'SPFILE_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 permission, ' ||'value2 as file_name,value2 as dummy FROM esm_collection_latest ' ||'WHERE property =''oh_spfileora_perm''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; -- Policies l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'permission'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'file_name')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'spfile_Permission', --Policy name p_metric_name =>'spfilePermRep', --Metric name p_policy_label_nlsid =>'OH_SPFILE_PERM_NAME', p_description =>'Ensures that access to the server paramater file is '|| 'restricted to the owner of the Oracle software set and the DBA group', p_description_nlsid =>'OH_SPFILE_PERM_DESC', p_impact =>'A server parameter file (SPFILE) lets you store and manage your '|| 'initialization parameters persistently in a server-side disk file. '|| 'A publicly accessible SPFILE can be scanned for sensitive initialization '|| 'parameters exposing the security policies of the database. The SPFILE can '|| 'also be searched for the weaknesses of the Oracle database configuration setting.', p_impact_nlsid =>'OH_SPFILE_PERM_IMPACT', p_recommendation =>'Restrict permissions to the server parameter file (SPFILE) to the owner '|| 'of the Oracle software set and DBA group. Do not give read and write permissions to public.', p_recommendation_nlsid =>'OH_SPFILE_PERM_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database is in an insecure state. The server parameter file (%file_name%) permission is %permission%.', p_message_nlsid =>'OH_SPFILE_PERM_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- parameter Files Permission Metric -- Metric collects atmost one row per database target -- All rows are violation row -- Policy may result in exactly one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'permission', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Permission (In Octal)', p_column_label_nlsid =>'PERM_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'file_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Init.Ora (Absolute Path)', p_column_label_nlsid =>'INITORA_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'initoraPermRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Initialization Parameter File(INITORA) Permission', p_metric_label_nlsid => 'INITORA_METRIC_LABEL', p_description => 'Collect permissions of the initialization parameter file(INITORA)', p_description_nlsid => 'INITORA_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 permission, ' ||'value2 as file_name,value2 as dummy FROM esm_collection_latest ' ||'WHERE property =''oh_initora_perm''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'permission'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'file_name') ); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'initora_File_Permission', --Policy name p_metric_name =>'initoraPermRep', --Metric name p_policy_label_nlsid =>'OH_INITORA_PERM_NAME', p_description =>'Ensures that access to the initialization paramater file '|| 'is restricted to the owner of the Oracle software set and the DBA group', p_description_nlsid =>'OH_INITORA_PERM_DESC', p_impact => 'Oracle traditionally stores initialization parameters in a text '|| 'initialization parameter file. A publicly accessible initialization '|| 'parameter file can be scanned for sensitive initialization parameters '|| 'exposing the security policies of the database. The IFILE can also be '|| 'searched for the weaknesses of the Oracle database configuration setting.', p_impact_nlsid =>'OH_INITORA_PERM_IMPACT', p_recommendation =>'Restrict permissions to the initialization parameter file to the '|| 'owner of the Oracle software set and DBA group. Do not give read and write permissions to public.', p_recommendation_nlsid =>'OH_INITORA_PERM_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database is in an insecure state. The text initialization parameter file '|| '(%file_name%) permission is %permission%.', p_message_nlsid =>'OH_INITORA_PERM_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- ifile Referenced Files Permission Metric -- Metric may collect more than one row per database target -- All rows are violation row -- Policy may result in more than one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'permission', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Permission (In Octal)', p_column_label_nlsid =>'PERM_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'file_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'IFILE (Absolute Path)', p_column_label_nlsid =>'IFILE_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'iFileRefFilesPermRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Files Referenced by IFILE Init Parameter Permission', p_metric_label_nlsid => 'IFILE_METRIC_LABEL_', p_description => 'Collect permissions of the initialization parameter files '|| 'referenced by IFILE initialization parameter', p_description_nlsid => 'IFILE_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 permission, ' ||'value2 as file_name,value2 as dummy FROM esm_collection_latest ' ||'WHERE property=''oh_ifile_perm''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; -- Policy l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'permission'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'file_name')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'ifile_File_Permission', --Policy name p_metric_name =>'iFileRefFilesPermRep', --Metric name p_policy_label_nlsid =>'OH_IFILE_PERM_NAME', p_description =>'Ensures that access to the files referenced by the IFILE '|| 'parameter is restricted to the owner of the Oracle software set and the DBA group', p_description_nlsid =>'OH_IFILE_PERM_DESC', p_impact => 'The IFILE initialization parameter can be used to embed the contents of '|| 'another initialization parameter file into the current initialization '|| 'parameter file. A publicly accessible initialization parameter file can '|| 'be scanned for sensitive initialization parameters exposing the security '|| 'policies of the database. Initialization parameter file can also be searched '|| 'for the weaknesses of the Oracle database configuration setting.', p_impact_nlsid =>'OH_IFILE_PERM_IMPACT', p_recommendation =>'Restrict permissions to the files referenced by the IFILE initialization '|| 'parameter file to the owner of the Oracle software set and DBA group. '|| 'Do not give read, write, and execute permissions to public.', p_recommendation_nlsid =>'OH_IFILE_PERM_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message => 'Database is in an insecure state. The IFILE parameter referenced file '|| '(%file_name%) permission is %permission%.', p_message_nlsid =>'OH_IFILE_PERM_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Oracle Home executables Owner Metric -- Metric may collect more than one row per database target -- All rows are violation row -- Policy may result in more than one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'owner', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Owner', p_column_label_nlsid =>'OWNER_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'file_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'File In Bin Folder (Absolute Path)', p_column_label_nlsid =>'BIN_FILE_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'ohBinFilesOwnerRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Owner of Files in Bin Folder', p_metric_label_nlsid => 'BIN_FILE_OWN_METRIC_LABEL', p_description => 'Collect owner information of all files and directories in oralcehome/bin folder', p_description_nlsid => 'BIN_FILE_OWN_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 owner, ' ||'value2 as file_name,value2 as dummy FROM esm_collection_latest ' ||'WHERE property=''oh_bin_files_owner''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; --Policy l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'owner'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'file_name')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Oracle_Home_Executable_Files_Owner', --Policy name p_metric_name =>'ohBinFilesOwnerRep', --Metric name p_policy_label_nlsid =>'OH_BIN_FILE_OWNER_NAME', p_description =>'Ensures that the ownership of all files and directories in the '|| 'ORACLE_HOME/bin folder is the same as the Oracle software installation owner', p_description_nlsid =>'OH_BIN_FILE_OWNER_DESC', p_impact => 'Incorrect file permissions on some of the Oracle files can cause major security issues.', p_impact_nlsid =>'OH_BIN_FILE_OWNER_IMPACT', p_recommendation =>'For files and directories in the ORACLE_HOME/bin folder that do not have '|| 'the same owner as the Oracle software installation, change their owner to the installation owner.', p_recommendation_nlsid =>'OH_BIN_FILE_OWNER_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database is in an insecure state. Owner of (%file_name%) is %owner% who is not '|| 'the owner of the Oracle software installation.', p_message_nlsid =>'OH_BIN_FILE_OWNER_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Oracle Home File Permission Metric -- Metric may collect more than one row per database target -- All rows are violation row -- Policy may result in more than one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'permission', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Permission (In Octal)', p_column_label_nlsid =>'PERM_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'file_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'File In Bin Folder (Absolute Path)', p_column_label_nlsid =>'BIN_FILE_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'ohExeBinFilesPermRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Permission of Files in Bin Folder', p_metric_label_nlsid => 'BIN_FILE_PERM_METRIC_LABEL', p_description => 'Collect permissions of all files and directories in oralcehome/bin folder', p_description_nlsid => 'BIN_FILE_PERM_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 permission, ' ||'value2 as file_name,value2 as dummy FROM esm_collection_latest ' ||'WHERE property=''oh_exebin_files_perm''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; --Policy l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'permission'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'file_name')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Oracle_Home_Executable_Files_Permission', --Policy name p_metric_name =>'ohExeBinFilesPermRep', --Metric name p_policy_label_nlsid =>'EXE_FILE_PERM_NAME', p_description =>'Ensures that all files in the ORACLE_HOME/bin folder do not have public write permission', p_description_nlsid =>'EXE_FILE_PERM_DESC', p_impact => 'Incorrect file permissions on some of the Oracle files can cause major security issues.', p_impact_nlsid =>'EXE_FILE_PERM_IMPACT', p_recommendation =>'Restrict permissions to all files in the ORACLE_HOME/bin to the owner of the Oracle software set and DBA group. Do not give write permission to public.', p_recommendation_nlsid =>'EXE_FILE_PERM_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database is in an insecure state. File (%file_name%) permission is %permission%.', p_message_nlsid =>'EXE_FILE_PERM_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list => l_ctxList ); COMMIT ; -- Oracle Home files Permissions Metric -- Metric may collect more than one row per database target -- All rows are violation row -- Policy may result in more than one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'permission', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Permission (In Octal)', p_column_label_nlsid =>'PERM_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'file_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'OH File (Absolute Path)', p_column_label_nlsid =>'OH_FILE_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'ohFilesPermissionRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Permission of Files and Folders in OH Folder', p_metric_label_nlsid => 'OH_FILE_PERM_METRIC_LABEL', p_description => 'Collect permissions of all files and directories '|| 'except bin directory in oralcehome folder', p_description_nlsid => 'OH_FILE_PERM_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 permission, ' ||'value2 as file_name,value2 as dummy FROM esm_collection_latest ' ||'WHERE property=''oh_files_perm''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; --Policy l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'permission'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'file_name')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Oracle_Home_File_Permission', --Policy name p_metric_name =>'ohFilesPermissionRep', --Metric name p_policy_label_nlsid =>'OH_FILEPERM_NAME', p_description =>'Ensures that all files in the ORACLE_HOME directories (except for ORACLE_HOME/bin) do not have public read, write and execute permissions', p_description_nlsid =>'OH_FILEPERM_DESC', p_impact => 'Incorrect file permissions on some of the Oracle files can cause major security issues.', p_impact_nlsid =>'OH_FILEPERM_IMPACT', p_recommendation =>'Restrict permissions to all files in the ORACLE_HOME directories (except for ORACLE_HOME/bin) to the owner of the Oracle software set and DBA group. Do not give read, write, and execute permissions to public.', p_recommendation_nlsid =>'OH_FILEPERM_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type => MGMT_GLOBAL.G_CONDITION_SQL, p_condition => 'rownum > 0', p_message =>'Database is in an insecure state. File (%file_name%) permission is %permission%.', p_message_nlsid => 'OH_FILEPERM_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Log Archive Destionation Metric -- Metric may collect more than one row per database target -- All rows are violation row -- Policy may result in more than one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'permission', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Permission (In Octal)', p_column_label_nlsid =>'PERM_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dir_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Log Archive Directory (Absolute Path)', p_column_label_nlsid =>'LOG_ARCHVE_DEST_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'logArchiveDestRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Log Archive Directories Permission', p_metric_label_nlsid => 'LOG_ARCHVE_DEST_METRIC_LABEL', p_description => 'Collect permissions of the folders specified by LOG_ARCHIVE_DEST initialization parameter', p_description_nlsid => 'LOG_ARCHVE_DEST_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 permission, ' ||'value2 as dir_name,value2 as dummy FROM esm_collection_latest ' ||'WHERE property =''log_archive_dest_perm''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; -- Policies l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'permission'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'dir_name')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Log_Archive_Destination_Permission', --Policy name p_metric_name =>'logArchiveDestRep', --Metric name p_policy_label_nlsid =>'LOG_ARCH_DEST_NAME', p_description =>'Ensures that the server''s archive logs are not accessible to public', p_description_nlsid =>'LOG_ARCH_DEST_DESC', p_impact => 'LogMiner can be used to extract database information from the archive logs if the directory specified by LOG_ARCHIVE_DEST parameter (in init.ora file) is not owned by the owner of the Oracle software installation or has permissions for others.', p_impact_nlsid =>'LOG_ARCH_DEST_IMPACT', p_recommendation =>'Permissions of the directory specified by LOG_ARCHIVE_DEST parameter should be restricted to the owner of the Oracle software set and DBA group with no permissions to public.', p_recommendation_nlsid =>'LOG_ARCH_DEST_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database may is in an insecure state. The directory(%dir_name%) specified by the LOG_ARCHIVE_DEST parameter has an inappropriate permission: %permission%', p_message_nlsid =>'LOG_ARCH_DEST_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Metric may collect more than one row per database target -- All rows are violation row -- Policy may result in more than one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'owner', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Owner', p_column_label_nlsid =>'OWNER_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dir_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Log Archive Directory (Absolute Path)', p_column_label_nlsid =>'LOG_ARCHVE_DEST_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'logArchiveDestOwnerRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Log Archive Directories Owner', p_metric_label_nlsid => 'LOG_ARCHVE_DEST_OWN_METRIC_LABEL', p_description => 'Collect owner of the folders specified by LOG_ARCHIVE_DEST initialization parameter', p_description_nlsid => 'LOG_ARCHVE_DEST_OWN_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 owner, ' ||'value2 as dir_name,value2 as dummy FROM esm_collection_latest ' ||'WHERE property =''log_archive_dest_owner''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; -- Policies l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'owner'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'dir_name')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Log_Archive_Destination_Owner', --Policy name p_metric_name =>'logArchiveDestOwnerRep', --Metric name p_policy_label_nlsid =>'LOG_ARCH_DEST_OWNER_NAME', p_description =>'Ensures that the server''s archive logs directory is a valid directory owned by Oracle software owner', p_description_nlsid =>'LOG_ARCH_DEST_OWNER_DESC', p_impact => 'LogMiner can be used to extract database information from the archive logs if the directory specified by LOG_ARCHIVE_DEST parameter (in init.ora file) is not owned by the owner of the Oracle software installation or has permissions for others.', p_impact_nlsid =>'LOG_ARCH_DEST_OWNER_IMPACT', p_recommendation =>'Directory specified by LOG_ARCHIVE_DEST parameter should be owned by the Oracle software set.', p_recommendation_nlsid =>'LOG_ARCH_DEST_OWNER_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database is in an insecure state as the directory(%dir_name%) specified by the LOG_ARCHIVE_DEST parameter is owned by %owner%.', p_message_nlsid =>'LOG_ARCH_DEST_OWNER_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Metric may collect more than one row per database target -- All rows are violation row -- Policy may result in more than one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'permission', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Permission (In Octal)', p_column_label_nlsid =>'PERM_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dir_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Log Archive Duplex Directory (Absolute Path)', p_column_label_nlsid =>'LOG_ARCHIVE_DUP_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'logArchiveDupDestRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Log Archive Duplex Directories Permission', p_metric_label_nlsid => 'LOG_ARCHIVE_DUP_METRIC_LABEL', p_description => 'Collect permissions of the folders specified by '|| 'LOG_ARCHIVE_DUPLEX_DEST initialization parameter', p_description_nlsid => 'LOG_ARCHIVE_DUP_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 permission, ' ||'value2 as dir_name , value2 as dummy FROM esm_collection_latest ' ||'WHERE property =''log_archive_duplex_dest_perm''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'permission'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'dir_name')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Log_Archive_Duplex_Destination_Permission', p_metric_name =>'logArchiveDupDestRep', --Metric name p_policy_label_nlsid =>'LOG_ARCHV_DUP_PERM_NAME', p_description =>'Ensures that the server''s archive logs are not accessible to public', p_description_nlsid =>'LOG_ARCHV_DUP_PERM_DESC', p_impact => 'LogMiner can be used to extract database information from the archive logs if the directory specified by LOG_ARCHIVE_DUPLEX_DEST parameter (in init.ora file) is not owned by the owner of the Oracle software installation or has permissions for others.', p_impact_nlsid =>'LOG_ARCHV_DUP_PERM_IMPACT', p_recommendation =>'Permissions of the directory specified by LOG_ARCHIVE_DUPLEX_DEST parameter should be restricted to the owner of the Oracle software set and DBA group with no permissions to public.', p_recommendation_nlsid =>'LOG_ARCHV_DUP_PERM_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database may is in an insecure state. The directory(%dir_name%) specified by the LOG_ARCHIVE_DUPLEX_DEST parameter has an inappropriate permission: %permission%', p_message_nlsid => 'LOG_ARCHV_DUP_PERM_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Metric may collect more than one row per database target -- All rows are violation row -- Policy may result in more than one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'owner', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Owner', p_column_label_nlsid =>'OWNER_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dir_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Log Archive Duplex Directory (Absolute Path)', p_column_label_nlsid =>'LOG_ARCHIVE_DUP_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'logArchiveDupDestOwnerRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Log Archive Duplex Directories Owner', p_metric_label_nlsid => 'LOG_ARCHIVE_DUP_OWN_METRIC_LABEL', p_description => 'Collect owner of the folders specified by LOG_ARCHIVE_DUPLEX_DEST '|| 'initialization parameter', p_description_nlsid => 'LOG_ARCHIVE_DUP_OWN_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 owner, ' ||'value2 as dir_name,value2 as dummy FROM esm_collection_latest ' ||'WHERE property =''log_archive_duplex_dest_owner''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'owner'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'dir_name')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Log_Archive_Duplex_Destination_Owner', --Policy name p_metric_name =>'logArchiveDupDestOwnerRep', --Metric name p_policy_label_nlsid =>'LOG_ARCHV_DUP_OWNER_NAME', p_description =>'Ensures that the server''s archive logs directory is a valid directory owned by Oracle software owner', p_description_nlsid =>'LOG_ARCHV_DUP_OWNER_DESC', p_impact => 'LogMiner can be used to extract database information from the archive logs if the directory specified by LOG_ARCHIVE_DUPLEX_DEST parameter (in init.ora file) is not owned by the owner of the Oracle software installation or has permissions for others.', p_impact_nlsid =>'LOG_ARCHV_DUP_OWNER_IMPACT', p_recommendation =>'Directory specified by LOG_ARCHIVE_DUPLEX_DEST parameter should be owned by the Oracle software set.', p_recommendation_nlsid =>'LOG_ARCHV_DUP_OWNER_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database is in an insecure state as the directory(%dir_name%) specified by the LOG_ARCHIVE_DUPLEX_DEST parameter is owned by %owner%.', p_message_nlsid => 'LOG_ARCHV_DUP_OWNER_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Metric may collect one row per database target -- All rows are violation row -- Policy may result in one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'value', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Log Archive Start', p_column_label_nlsid =>'LOG_ARCHIVE_START_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'logArchiveStartRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Setting of LOG_ARCHIVE_START Parameter', p_metric_label_nlsid => 'LOG_ARCHIVE_START_METRIC_LABEL', p_description => 'Collect the setting of LOG_ARCHIVE_START parameter', p_description_nlsid => 'LOG_ARCHIVE_START_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 ' ||'FROM esm_collection_latest ' ||'WHERE property =''log_archive_start''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; -- This Url will direct to Initialization Parameters page -- From you can edit setting for Initialization Parameters l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'value', -- p_url_link_template =>'database/instance/initParameters?target=^TARGET_NAME^\&type=^TARGET_TYPE^\&cancelURL=^CANCEL_URL^', p_url_link_template =>'database/instance/initParameters?event=findParameter\&target=^TARGET_NAME^\&type=^TARGET_TYPE^\&initParamName=log_archive_start\&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Log_Archive_Start', --Policy name p_metric_name =>'logArchiveStartRep', --Metric name p_policy_label_nlsid =>'LOG_ARCHIVE_START_NAME', p_description =>'Ensures that archiving of redo logs is done automatically and '|| 'prevents suspension of instance operations when redo logs fill. '|| 'Only applicable if database is in archivelog mode', p_description_nlsid =>'LOG_ARCHIVE_START_DESC', p_impact => 'Setting the LOG_ARCHIEVE_START initialization parameter to TRUE ensures '|| 'that the archiving of redo logs is done automatically and prevents '|| 'suspension of instance operations when redo logs fill. This feature is '|| 'only applicable if the database is in archivelog mode.', p_impact_nlsid =>'LOG_ARCHIVE_START_IMPACT', p_recommendation =>'Set LOG_ARCHIEVE_START initialization parameter to TRUE.', p_recommendation_nlsid =>'LOG_ARCHIVE_START_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database is in an insecure state. LOG_ARCHIVE_START initialization parameter is set to %value%.', p_message_nlsid =>'LOG_ARCHIVE_START_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); -- UTL_FILe_DIR Settings Metric -- Metric may collect more than one row per database target -- All rows are violation row -- Policy may result in more than one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dir_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Utl File Directory (Absolute Path)', p_column_label_nlsid =>'UTL_FILE_DIR_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'utlFileDirSettingRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Utl File Directories Permission', p_metric_label_nlsid => 'UTL_FILE_DIR_METRIC_LABEL', p_description => 'Collect permissions of the folders specified by UTL_FILE_DIR initialization parameter', p_description_nlsid => 'UTL_FILE_DIR_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 dir_name, ' ||'value2 as dummy FROM esm_collection_latest ' ||'WHERE property =''utl_file_dir''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; ------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------- --Policies l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'dir_name')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Utl_File_Directory', --Policy name p_metric_name =>'utlFileDirSettingRep', --Metric name p_policy_label_nlsid =>'UTL_FILE_NAME', p_description =>'Ensures that the Utility File Directory (UTL_FILE_DIR) initialization '|| 'parameter is not set to one of ''*'', ''.'', core dump trace file locations', p_description_nlsid =>'UTL_FILE_DESC', p_impact => 'Specifies the directories which the UTL_FILE package can access. '|| 'Having the parameter set to asterisk (*), period (.), or to sensitive '|| 'directories, could expose them to all users having execute privilege on the UTL_FILE package.', p_impact_nlsid =>'UTL_FILE_IMPACT', p_recommendation =>'Change the UTL_FILE_DIR initialization parameter to a value other '|| 'than asterisk (*), or period (.), or to core dump trace locations.', p_recommendation_nlsid =>'UTL_FILE_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database is in an insecure state. UTL_FILE_DIR parameter is set to %dir_name%.' , p_message_nlsid =>'UTL_FILE_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Metric may collect more than one row per database target -- All rows are violation row -- Policy may result in more than one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dir_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Utl File Directory (Absolute Path)', p_column_label_nlsid =>'UTL_FILE_DIR_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name =>'utlSetting9IplusRep', --Metric name p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label =>'Setting of Utl File Directories For 9i-Plus Versions', p_metric_label_nlsid => 'UTL_FILE_DIR9i_METRIC_LABEL', p_description => 'Collect setting of UTL_FILE_DIR initialization parameter for 9i-plus versions', p_description_nlsid => 'UTL_FILE_DIR9i_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 dir_name,value as dummy ' ||'FROM esm_collection_latest ' ||'WHERE property =''utl_file_dir_9i+''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'dir_name')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Utl_File_Dir_for_9iPlus_Versions', --Policy name p_metric_name =>'utlSetting9IplusRep', --Metric name p_policy_label_nlsid =>'UTL_FILE_9IPLUS_NAME', p_description =>'Ensure that the UTL_FILE_DIR initialization parameter is not '|| 'used in Oracle9i Release 1 and later', p_description_nlsid =>'UTL_FILE_9IPLUS_DESC', p_impact => 'Specifies the directories which UTL_FILE package can access. Having the parameter '|| 'set to asterisk (*), period (.), or to sensitive directories could expose '|| 'them to all users having execute privilege on UTL_FILE package.', p_impact_nlsid =>'UTL_FILE_9IPLUS_IMPACT', p_recommendation =>'For Oracle 9i Release 1 and later, remove the UTL_FILE_DIR initialization '|| 'parameter. Instead, use the CREATE DIRECTORY feature.', p_recommendation_nlsid => 'UTL_FILE_9IPLUS_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database is in an insecure state. UTL_FILE_DIR parameter '|| '(set to %dir_name%) is used in a 9i+ server.', p_message_nlsid =>'UTL_FILE_9IPLUS_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- OS Authentication Metrics -- Metric may collect one row per database target -- It may be non-violation or violation row -- Policy may result in one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'os_authent_prefix_domain', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Os Authentication Prefix Domain', p_column_label_nlsid =>'OS_AUTH_PRFX_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'osauthPrefixDomainRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Os Authent Prefix Domain', p_metric_label_nlsid => 'OS_AUTH_PRFX_METRIC_LABEL', p_description => 'Collect the information of OS prefix domain authentication', p_description_nlsid => 'OS_AUTH_PRFX_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 os_authent_prefix_domain ' ||'FROM esm_collection_latest ' ||'WHERE property=''osauth_prefix_domain''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; --Policy l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'os_authent_prefix_domain')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'OS_Authenticate_Prefix_Domain', --Policy name p_metric_name =>'osauthPrefixDomainRep', --Metric name p_policy_label_nlsid =>'OS_AUTH_PRFX_DOM_NAME', p_description =>'Ensures externally identified users specify the domain while connecting', p_description_nlsid =>'OS_AUTH_PRFX_DOM_DESC', p_impact => 'This setting is only applicable to Windows systems. If externally identified '|| 'accounts are required, setting OSAUTH_PREFIX_DOMAIN to TRUE in the registry '|| 'forces the account to specify the domain. This prevents spoofing of user '|| 'access from an alternate domain or local system.', p_impact_nlsid =>'OS_AUTH_PRFX_DOM_IMPACT', p_recommendation =>'For externally identified users from Windows systems, set the '|| 'OSAUTH_PREFIX_DOMAIN initialization parameter to TRUE.', p_recommendation_nlsid =>'OS_AUTH_PRFX_DOM_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'upper(:os_authent_prefix_domain) = ''FALSE''', p_message =>'Database is in an insecure state. OSAUTH_PREFIX_DOMAIN is not set to TRUE.', p_message_nlsid =>'OS_AUTH_PRFX_DOM_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- DB Password setting Metric -- Metric may collect more than one row per database target -- These may be non-violation or violation row -- Policy may result in more than one violation row 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 => 'profile', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Profile', p_column_label_nlsid =>'PROFILE_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'limit', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Password Lock Time', p_column_label_nlsid =>'PWD_LOCK_TIME_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'pwdLockRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Password Lock Time', p_metric_label_nlsid => 'PWD_LOCK_TIME_METRIC_LABEL', p_description => 'Collect setting of PASSWORD_LOCK_TIME', p_description_nlsid => 'PWD_LOCK_TIME_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 limit, value2 as profile ' ||'FROM esm_collection_latest ' ||'WHERE property =''password_lock_time''', 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 => 'pwdLockRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Password Lock Time', p_metric_label_nlsid => 'PWD_LOCK_TIME_METRIC_LABEL', p_description => 'Collect setting of PASSWORD_LOCK_TIME', p_description_nlsid => 'PWD_LOCK_TIME_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 limit, value2 as profile ' ||'FROM esm_collection_latest ' ||'WHERE property =''password_lock_time''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; --Policies l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'profile', p_url_link_template =>'database/security/profile?oname=^profile^\&event=edit\&otype=PROFILE\&target=^TARGET_NAME^\&type=^TARGET_TYPE^\&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'limit')); l_paramDefList := MGMT_POLICY_PARAM_DEF_ARRAY( MGMT_POLICY_PARAM_DEF( param_name => 'MIN_PASSWORD_LOCK_TIME', param_name_nlsid => 'MIN_PWD_LOCK_TIME_PNAME', param_type => MGMT_POLICY.G_PARAM_TYPE_NUMBER)); l_defaultParamValList := MGMT_POLICY_PARAM_VAL_ARRAY( MGMT_POLICY_PARAM_VAL.NEW( p_param_name => 'MIN_PASSWORD_LOCK_TIME', p_warn_threshold => '1')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL, p_param_values => l_defaultParamValList)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Password_Locking_Time', --Policy name p_metric_name =>'pwdLockRep', --Metric name p_policy_label_nlsid =>'PWD_LOCK_TIME_NAME', p_description =>'Ensures PASSWORD_LOCK_TIME is set to a reasonable number of days for all profiles', p_description_nlsid =>'PWD_LOCK_TIME_DESC', p_impact => 'Having a low value increases the likelihood of Denial of Service attacks.', p_impact_nlsid =>'PWD_LOCK_TIME_IMPACT', p_recommendation =>'Set the PASSWORD_LOCK_TIME parameter to no less than 1 for all the profiles.', p_recommendation_nlsid =>'PWD_LOCK_TIME_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition => 'decode(:limit,''UNLIMITED'', 2147483647,to_number(:limit)) < :MIN_PASSWORD_LOCK_TIME', p_message =>'Database is in an insecure state. PASSWORD_LOCK_TIME is set to %limit% days for the profile %profile%.', p_message_nlsid =>'PWD_LOCK_TIME_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_policy_param_list =>l_paramDefList, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; ESM.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name =>'Password_Locking_Time', --Policy name p_metric_name =>'pwdLockRep', --Metric name p_policy_label_nlsid =>'PWD_LOCK_TIME_NAME', p_description =>'Ensures PASSWORD_LOCK_TIME is set to a reasonable number of days for all profiles', p_description_nlsid =>'PWD_LOCK_TIME_DESC', p_impact => 'Having a low value increases the likelihood of Denial of Service attacks.', p_impact_nlsid =>'PWD_LOCK_TIME_IMPACT', p_recommendation =>'Set the PASSWORD_LOCK_TIME parameter to no less than 1 for all the profiles.', p_recommendation_nlsid =>'PWD_LOCK_TIME_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition => 'decode(:limit,''UNLIMITED'', 2147483647,to_number(:limit)) < :MIN_PASSWORD_LOCK_TIME', p_message =>'Database is in an insecure state. PASSWORD_LOCK_TIME is set to %limit% days for the profile %profile%.', p_message_nlsid =>'PWD_LOCK_TIME_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_policy_param_list =>l_paramDefList, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Metric may collect more than one row per database target -- These may be non-violation or violation row -- Policy may result in more than one violation row 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 => 'profile', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Profile', p_column_label_nlsid =>'PROFILE_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'limit', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Password Grace Time', p_column_label_nlsid =>'PWD_GRACE_TIME_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'pwdGraceRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Password Grace Time', p_metric_label_nlsid => 'PWD_GRACE_TIME_METRIC_LABEL', p_description => 'Collect setting of PASSWORD_GRACE_TIME', p_description_nlsid => 'PWD_GRACE_TIME_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 limit, value2 as profile ' ||'FROM esm_collection_latest ' ||'WHERE property = ''password_grace_time''', 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 => 'pwdGraceRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Password Grace Time', p_metric_label_nlsid => 'PWD_GRACE_TIME_METRIC_LABEL', p_description => 'Collect setting of PASSWORD_GRACE_TIME', p_description_nlsid => 'PWD_GRACE_TIME_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 limit, value2 as profile ' ||'FROM esm_collection_latest ' ||'WHERE property = ''password_grace_time''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'profile', p_url_link_template =>'database/security/profile?oname=^profile^\&event=edit\&otype=PROFILE\&target=^TARGET_NAME^\&type=^TARGET_TYPE^\&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'limit')); l_paramDefList := MGMT_POLICY_PARAM_DEF_ARRAY( MGMT_POLICY_PARAM_DEF( param_name => 'MAX_PASSWORD_GRACE_TIME', param_name_nlsid => 'MAX_PWD_GRACE_TIME_PNAME', param_type => MGMT_POLICY.G_PARAM_TYPE_NUMBER)); l_defaultParamValList := MGMT_POLICY_PARAM_VAL_ARRAY( MGMT_POLICY_PARAM_VAL.NEW( p_param_name => 'MAX_PASSWORD_GRACE_TIME', p_crit_threshold => '14')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL, p_param_values => l_defaultParamValList)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Password_Grace_Time', --Policy name p_metric_name =>'pwdGraceRep', --Metric name p_policy_label_nlsid =>'PWD_GRACE_TIME_NAME', p_description =>'Ensures that all profiles have PASSWORD_GRACE_TIME set to a reasonable number of days', p_description_nlsid =>'PWD_GRACE_TIME_DESC', p_impact =>'A high value for the PASSWORD_GRACE_TIME parameter may cause serious '|| 'database security issues by allowing the user to keep the same password for a long time.', p_impact_nlsid =>'PWD_GRACE_TIME_IMPACT', p_recommendation =>'Set the PASSWORD_GRACE_TIME parameter to no more than 14 days for all profiles.', p_recommendation_nlsid =>'PWD_GRACE_TIME_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition => ':MAX_PASSWORD_GRACE_TIME < decode(:limit,''UNLIMITED'', 2147483647,to_number(:limit))', p_message =>'Database is in an insecure state. PASSWORD_GRACE_TIME is set to %limit% days for the profile %profile%.', p_message_nlsid =>'PWD_GRACE_TIME_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_policy_param_list =>l_paramDefList, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; ESM.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name =>'Password_Grace_Time', --Policy name p_metric_name =>'pwdGraceRep', --Metric name p_policy_label_nlsid =>'PWD_GRACE_TIME_NAME', p_description =>'Ensures that all profiles have PASSWORD_GRACE_TIME set to a reasonable number of days', p_description_nlsid =>'PWD_GRACE_TIME_DESC', p_impact =>'A high value for the PASSWORD_GRACE_TIME parameter may cause serious '|| 'database security issues by allowing the user to keep the same password for a long time.', p_impact_nlsid =>'PWD_GRACE_TIME_IMPACT', p_recommendation =>'Set the PASSWORD_GRACE_TIME parameter to no more than 14 days for all profiles.', p_recommendation_nlsid =>'PWD_GRACE_TIME_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition => ':MAX_PASSWORD_GRACE_TIME < decode(:limit,''UNLIMITED'', 2147483647,to_number(:limit))', p_message =>'Database is in an insecure state. PASSWORD_GRACE_TIME is set to %limit% days for the profile %profile%.', p_message_nlsid =>'PWD_GRACE_TIME_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_policy_param_list =>l_paramDefList, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Metric may collect more than one row per database target -- These may be non-violation or violation row -- Policy may result in more than one violation row 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 => 'profile', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Profile', p_column_label_nlsid =>'PROFILE_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'limit', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Password Life Time', p_column_label_nlsid =>'PWD_LIFE_TIME_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'pwdLifeRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Password Life Time', p_metric_label_nlsid => 'PWD_LIFE_TIME_COL', p_description => 'Collect setting of PASSWORD_LIFE_TIME', p_description_nlsid => 'PWD_LIFE_TIME_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 limit, value2 as profile ' ||'FROM esm_collection_latest ' ||'WHERE property =''password_life_time''', 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 => 'pwdLifeRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Password Life Time', p_metric_label_nlsid => 'PWD_LIFE_TIME_COL', p_description => 'Collect setting of PASSWORD_LIFE_TIME', p_description_nlsid => 'PWD_LIFE_TIME_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 limit, value2 as profile ' ||'FROM esm_collection_latest ' ||'WHERE property =''password_life_time''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'profile', p_url_link_template =>'database/security/profile?oname=^profile^\&event=edit\&otype=PROFILE\&target=^TARGET_NAME^\&type=^TARGET_TYPE^\&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'limit')); l_paramDefList := MGMT_POLICY_PARAM_DEF_ARRAY( MGMT_POLICY_PARAM_DEF( param_name => 'MAX_PASSWORD_LIFE_TIME', param_name_nlsid => 'MAX_PWD_LIFE_TIME_PNAME', param_type => MGMT_POLICY.G_PARAM_TYPE_NUMBER)); l_defaultParamValList := MGMT_POLICY_PARAM_VAL_ARRAY( MGMT_POLICY_PARAM_VAL.NEW( p_param_name => 'MAX_PASSWORD_LIFE_TIME', p_warn_threshold => '90')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL, p_param_values => l_defaultParamValList)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Password_Life_Time', --Policy name p_metric_name =>'pwdLifeRep', --Metric name p_policy_label_nlsid =>'PWD_LIFE_TIME_NAME', p_description =>'Ensures that all profiles have PASSWORD_LIFE_TIME set to a reasonable number of days', p_description_nlsid =>'PWD_LIFE_TIME_DESC', p_impact => 'A long password life time gives hackers a long time to try and cook '|| 'the password. May cause serious database security issues.', p_impact_nlsid =>'PWD_LIFE_TIME_IMPACT', p_recommendation =>'Set the PASSWORD_LIFE_TIME parameter to no more than 90 days for all profiles.', p_recommendation_nlsid =>'PWD_LIFE_TIME_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition => 'decode(:limit,''UNLIMITED'',2147483647,to_number(:limit)) > :MAX_PASSWORD_LIFE_TIME', p_message =>'Database is in an insecure state. PASSWORD_LIFE_TIME is set to %limit% days for the profile %profile%.', p_message_nlsid =>'PWD_LIFE_TIME_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_policy_param_list =>l_paramDefList, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; ESM.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name =>'Password_Life_Time', --Policy name p_metric_name =>'pwdLifeRep', --Metric name p_policy_label_nlsid =>'PWD_LIFE_TIME_NAME', p_description =>'Ensures that all profiles have PASSWORD_LIFE_TIME set to a reasonable number of days', p_description_nlsid =>'PWD_LIFE_TIME_DESC', p_impact => 'A long password life time gives hackers a long time to try and cook '|| 'the password. May cause serious database security issues.', p_impact_nlsid =>'PWD_LIFE_TIME_IMPACT', p_recommendation =>'Set the PASSWORD_LIFE_TIME parameter to no more than 90 days for all profiles.', p_recommendation_nlsid =>'PWD_LIFE_TIME_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition => 'decode(:limit,''UNLIMITED'',2147483647,to_number(:limit)) > :MAX_PASSWORD_LIFE_TIME', p_message =>'Database is in an insecure state. PASSWORD_LIFE_TIME is set to %limit% days for the profile %profile%.', p_message_nlsid =>'PWD_LIFE_TIME_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_policy_param_list =>l_paramDefList, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Metric may collect more than one row per database target -- These may be non-violation or violation row -- Policy may result in more than one violation row 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 => 'profile', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Profile', p_column_label_nlsid =>'PROFILE_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'limit', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Password Reuse Max', p_column_label_nlsid =>'PWD_REUSE_MAX_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'reuseMaxRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Password Reuse Max', p_metric_label_nlsid => 'PWD_REUSE_MAX_METRIC_LABEL', p_description => 'Collect setting of PASSWORD_REUSE_MAX', p_description_nlsid => 'PWD_REUSE_MAX_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 limit, value2 as profile ' ||'FROM esm_collection_latest ' ||'WHERE property =''password_reuse_max''', 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 => 'reuseMaxRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Password Reuse Max', p_metric_label_nlsid => 'PWD_REUSE_MAX_METRIC_LABEL', p_description => 'Collect setting of PASSWORD_REUSE_MAX', p_description_nlsid => 'PWD_REUSE_MAX_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 limit, value2 as profile ' ||'FROM esm_collection_latest ' ||'WHERE property =''password_reuse_max''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'profile', p_url_link_template =>'database/security/profile?oname=^profile^\&event=edit\&otype=PROFILE\&target=^TARGET_NAME^\&type=^TARGET_TYPE^\&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'limit')); l_paramDefList := MGMT_POLICY_PARAM_DEF_ARRAY( MGMT_POLICY_PARAM_DEF( param_name => 'MIN_PASSWORD_REUSE_MAX', param_name_nlsid => 'MIN_PWD_REUSE_MAX_PNAME', param_type => MGMT_POLICY.G_PARAM_TYPE_NUMBER)); l_defaultParamValList := MGMT_POLICY_PARAM_VAL_ARRAY( MGMT_POLICY_PARAM_VAL.NEW( p_param_name => 'MIN_PASSWORD_REUSE_MAX', p_warn_threshold => '20')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL, p_param_values => l_defaultParamValList)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Password_Reuse_Max', --Policy name p_metric_name =>'reuseMaxRep', --Metric name p_policy_label_nlsid =>'PWD_REUSE_MAX_NAME', p_description =>'Ensures that all profiles have PASSWORD_REUSE_MAX set to a reasonable number of times', p_description_nlsid =>'PWD_REUSE_MAX_DESC', p_impact => 'Old passwords are usually the best guesses for the current password. '|| 'A low value for the PASSWORD_REUSE_MAX parameter may cause serious '|| 'database security issues by allowing users to reuse their old passwords more often.', p_impact_nlsid =>'PWD_REUSE_MAX_IMPACT', p_recommendation =>'Set the PASSWORD_REUSE_MAX parameter to no less than 20 times for all profiles.', p_recommendation_nlsid =>'PWD_REUSE_MAX_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'decode(:limit,''UNLIMITED'', 2147483647,to_number(:limit)) < :MIN_PASSWORD_REUSE_MAX', p_message =>'Database is in an insecure state. PASSWORD_REUSE_MAX is set to %limit% times for the profile %profile%.', p_message_nlsid =>'PWD_REUSE_MAX_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_policy_param_list =>l_paramDefList, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; ESM.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name =>'Password_Reuse_Max', --Policy name p_metric_name =>'reuseMaxRep', --Metric name p_policy_label_nlsid =>'PWD_REUSE_MAX_NAME', p_description =>'Ensures that all profiles have PASSWORD_REUSE_MAX set to a reasonable number of times', p_description_nlsid =>'PWD_REUSE_MAX_DESC', p_impact => 'Old passwords are usually the best guesses for the current password. '|| 'A low value for the PASSWORD_REUSE_MAX parameter may cause serious '|| 'database security issues by allowing users to reuse their old passwords more often.', p_impact_nlsid =>'PWD_REUSE_MAX_IMPACT', p_recommendation =>'Set the PASSWORD_REUSE_MAX parameter to no less than 20 times for all profiles.', p_recommendation_nlsid =>'PWD_REUSE_MAX_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'decode(:limit,''UNLIMITED'', 2147483647,to_number(:limit)) < :MIN_PASSWORD_REUSE_MAX', p_message =>'Database is in an insecure state. PASSWORD_REUSE_MAX is set to %limit% times for the profile %profile%.', p_message_nlsid =>'PWD_REUSE_MAX_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_policy_param_list =>l_paramDefList, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Metric may collect more than one row per database target -- These may be non-violation or violation row -- Policy may result in more than one violation row 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 => 'profile', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Profile', p_column_label_nlsid =>'PROFILE_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'limit', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Password Reuse Time', p_column_label_nlsid =>'PWD_REUSE_TIME_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'passwdReuseTimeRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Password Reuse Time', p_metric_label_nlsid => 'PWD_REUSE_TIME_METRIC_LABEL', p_description => 'Collect setting of PASSWORD_REUSE_TIME', p_description_nlsid => 'PWD_REUSE_TIME_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 limit, value2 as profile ' ||'FROM esm_collection_latest ' ||'WHERE property =''password_reuse_time''', 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 => 'passwdReuseTimeRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Password Reuse Time', p_metric_label_nlsid => 'PWD_REUSE_TIME_METRIC_LABEL', p_description => 'Collect setting of PASSWORD_REUSE_TIME', p_description_nlsid => 'PWD_REUSE_TIME_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 limit, value2 as profile ' ||'FROM esm_collection_latest ' ||'WHERE property =''password_reuse_time''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'profile', p_url_link_template =>'database/security/profile?oname=^profile^\&event=edit\&otype=PROFILE\&target=^TARGET_NAME^\&type=^TARGET_TYPE^\&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'limit')); l_paramDefList := MGMT_POLICY_PARAM_DEF_ARRAY( MGMT_POLICY_PARAM_DEF( param_name => 'MIN_PASSWORD_REUSE_TIME', param_name_nlsid => 'MIN_PWD_REUSE_TIME_PNAME', param_type => MGMT_POLICY.G_PARAM_TYPE_NUMBER)); l_defaultParamValList := MGMT_POLICY_PARAM_VAL_ARRAY( MGMT_POLICY_PARAM_VAL.NEW( p_param_name => 'MIN_PASSWORD_REUSE_TIME', p_warn_threshold => '2147483647')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL, p_param_values => l_defaultParamValList)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Password_Reuse_Time', --Policy name p_metric_name =>'passwdReuseTimeRep', --Metric name p_policy_label_nlsid =>'PWD_REUSE_TIME_NAME', p_description =>'Ensures that all profiles have PASSWORD_REUSE_TIME set to a reasonable number of days', p_description_nlsid =>'PWD_REUSE_TIME_DESC', p_impact => 'A low value for the PASSWORD_REUSE_TIME parameter may cause serious '|| 'database security issues by allowing users to reuse their old passwords more often.', p_impact_nlsid =>'PWD_REUSE_TIME_IMPACT', p_recommendation =>'Set the PASSWORD_REUSE_TIME parameter to UNLIMITED for all profiles.', p_recommendation_nlsid =>'PWD_REUSE_TIME_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'decode(:limit,''UNLIMITED'',2147483647,to_number(:limit)) < :MIN_PASSWORD_REUSE_TIME', -- If not unlimited p_message =>'Database is in an insecure state. PASSWORD_REUSE_TIME is set to %limit% for the profile %profile%.', p_message_nlsid =>'PWD_REUSE_TIME_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_policy_param_list =>l_paramDefList, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; ESM.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name =>'Password_Reuse_Time', --Policy name p_metric_name =>'passwdReuseTimeRep', --Metric name p_policy_label_nlsid =>'PWD_REUSE_TIME_NAME', p_description =>'Ensures that all profiles have PASSWORD_REUSE_TIME set to a reasonable number of days', p_description_nlsid =>'PWD_REUSE_TIME_DESC', p_impact => 'A low value for the PASSWORD_REUSE_TIME parameter may cause serious '|| 'database security issues by allowing users to reuse their old passwords more often.', p_impact_nlsid =>'PWD_REUSE_TIME_IMPACT', p_recommendation =>'Set the PASSWORD_REUSE_TIME parameter to UNLIMITED for all profiles.', p_recommendation_nlsid =>'PWD_REUSE_TIME_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'decode(:limit,''UNLIMITED'',2147483647,to_number(:limit)) < :MIN_PASSWORD_REUSE_TIME', -- If not unlimited p_message =>'Database is in an insecure state. PASSWORD_REUSE_TIME is set to %limit% for the profile %profile%.', p_message_nlsid =>'PWD_REUSE_TIME_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_policy_param_list =>l_paramDefList, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- DB parameter Metric -- Metric may collect one row per database target -- This may be non-violation or violation row -- Policy may result in one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'value', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Trace Files Public', p_column_label_nlsid =>'TRACE_FILES_PUB_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'trcFilePublicRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Trace Files Public ', p_metric_label_nlsid => 'TRACE_FILES_PUB_METRIC_LABEL', p_description => 'Collect the information of _TRACE_FILES_PUBLIC initialization parameter', p_description_nlsid => 'TRACE_FILES_PUB_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 ' ||'FROM esm_collection_latest ' ||'WHERE property =''trace_files_public''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ); COMMIT ; -- Policies -- This Url will direct to Initialization Parameters page -- From there you can edit setting for Initialization Parameters l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'value', p_url_link_template =>'database/instance/initParameters?event=findParameter\&target=^TARGET_NAME^\&type=^TARGET_TYPE^\&initParamName=_trace_files_public\&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'PUBLIC_Trace_Files', --Policy Name p_metric_name => 'trcFilePublicRep', p_policy_label_nlsid =>'TRACE_FILES_PUB_NAME', p_description =>'Ensures database trace files are not public readable', p_description_nlsid =>'TRACE_FILES_PUBLIC_DESC', p_impact => 'If trace files are readable by the PUBLIC group, a malicious user '|| 'may attempt to read the trace files that could lead to sensitive information being exposed.', p_impact_nlsid =>'TRACE_FILES_PUBLIC_IMPACT', p_recommendation =>'Set the initialization parameter _TRACE_FILES_PUBLIC to FALSE.', p_recommendation_nlsid =>'TRACE_FILES_PUBLIC_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>':value <> ''FALSE''', p_message =>'Database is in an insecure state. _TRACE_FILES_PUBLIC initialization parameter is set to :%value%.', p_message_nlsid =>'TRACE_FILES_PUBLIC_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Metric may collect one row per database target -- This may be non-violation or violation row -- Policy may result in one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'value', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Audit Trail', p_column_label_nlsid =>'AUDIT_TRAIL_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'auditTrailRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Audit Trail', p_metric_label_nlsid => 'AUDIT_TRAIL_METRIC_LABEL', p_description => 'Collect the information of AUDIT_TRAIL initialization parameter', p_description_nlsid => 'AUDIT_TRAIL_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 ' ||'FROM esm_collection_latest ' ||'WHERE property =''audit_trail''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ); COMMIT ; -- Policies -- This Url will direct to Initialization Parameters page -- From there you can edit setting for Initialization Parameters l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'value', p_url_link_template =>'database/instance/initParameters?event=findParameter\&target=^TARGET_NAME^\&type=^TARGET_TYPE^\&initParamName=audit_trail\&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Audit_Trail', --Policy name p_metric_name =>'auditTrailRep', --Metric name p_policy_label_nlsid =>'AUDIT_TRAIL_NAME', p_description =>'Ensures basic auditing features are enabled', p_description_nlsid =>'AUDIT_TRAIL_DESC', p_impact => 'Not enabling basic audit features might increase the likelihood '|| 'of a denial of service attack going unnoticed.', p_impact_nlsid =>'AUDIT_TRAIL_IMPACT', p_recommendation =>'Enable auditing by setting the AUDIT_TRAIL initialization parameter '|| 'to a value other than NONE or FALSE. A value of OS is the most secure setting.', p_recommendation_nlsid =>'AUDIT_TRAIL_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'(upper(:value) = ''NONE'') OR (upper(:value) = ''FALSE'')', p_message =>'Database is in an insecure state. AUDIT_TRAIL initialization parameter is set to %value%.', p_message_nlsid =>'AUDIT_TRAIL_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Metric may collect one row per database target -- This may be non-violation or violation row -- Policy may result in one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'value', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Remote Listener', p_column_label_nlsid =>'RMT_LSNR_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'rmtLsnrRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Remote Listener', p_metric_label_nlsid => 'RMT_LSNR_METRIC_LABEL', p_description => 'Collect the information of REMOTE_LISTENER initialization parameter', p_description_nlsid => 'RMT_LSNR_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 ' ||'FROM esm_collection_latest ' ||'WHERE property =''remote_listener''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ); COMMIT ; -- This Url will direct to Initialization Parameters page -- From there you can edit setting for Initialization Parameters l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'value', p_url_link_template =>'database/instance/initParameters?event=findParameter\&target=^TARGET_NAME^\&type=^TARGET_TYPE^\&initParamName=remote_listener\&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Remote_Listener_Instances',-- Policy Name p_metric_name =>'rmtLsnrRep', --Metric name p_policy_label_nlsid =>'RMT_LSNR_NAME', p_description =>'Ensures listener instances on a remote machine separate from the database instance are not used', p_description_nlsid =>'RMT_LSNR_DESC', p_impact => 'The REMOTE_LISTENER initialization parameter can be used to allow '|| 'a listener on a remote machine to access the database. This parameter '|| 'is not applicable in a multi-master replication or RAC environment '|| 'where this setting provides a load balancing mechanism for the listener.', p_impact_nlsid =>'RMT_LSNR_IMPACT', p_recommendation =>'REMOTE_LISTENER should be set null string. This parameter '|| 'is not applicable in a multi-master replication or RAC environment where '|| 'this setting provides a load balancing mechanism for the listener.', p_recommendation_nlsid =>'RMT_LSNR_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>':value <> '' ''', p_message =>'Database may be in an insecure state as REMOTE_LISTENER initialization parameter is set to %value%.', p_message_nlsid =>'RMT_LSNR_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Not sure about this one -- Metric may collect one row per database target -- This may be non-violation or violation row -- Policy may result in one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'value', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Os Authentication Prefix', p_column_label_nlsid =>'OS_AUTHENT_PREFIX_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'osAuthRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Os Authentication Prefix', p_metric_label_nlsid => 'OS_AUTHENT_PREFIX_METRIC_LABEL', p_description => 'Collect the information of OS_AUTHENT_PREFIX initialization parameter', p_description_nlsid => 'OS_AUTHENT_PREFIX_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 ' ||'FROM esm_collection_latest ' ||'WHERE property =''os_authent_prefix''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ); COMMIT ; -- This Url will direct to Initialization Parameters page -- From there you can edit setting for Initialization Parameters l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'value', p_url_link_template =>'database/instance/initParameters?event=findParameter\&target=^TARGET_NAME^\&type=^TARGET_TYPE^\&initParamName=os_authent_prefix\&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Os_Authent_Prefix',--Policy Name p_metric_name =>'osAuthRep', --Metric name p_policy_label_nlsid =>'OS_AUTH_PREFIX_NAME', p_description =>'Ensures that the OS_AUTHENT_PREFIX is set to a value other than OPS$ or null string ("")', p_description_nlsid =>'OS_AUTH_PREFIX_DESC', p_impact => 'Setting this ensures that the only way an account can be used externally is by specifying IDENTIFIED EXTERNALLY when creating a user.', p_impact_nlsid =>'OS_AUTH_PREFIX_IMPACT', p_recommendation =>'The identified externally approach should only be used on development and test databases. On a production system, ensure that the user cannot get to the operating system level.', p_recommendation_nlsid =>'OS_AUTH_PREFIX_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'(:value = '' '') OR (:value = ''OPS$'')' , p_message =>'Database is in an insecure state. Operating System authentication prefix is set to %value%.', p_message_nlsid =>'OS_AUTH_PREFIX_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Metric may collect one row per database target -- This may be non-violation or violation row -- Policy may result in one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'value', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Sql92 Security', p_column_label_nlsid =>'SQL92_SECURITY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'sql92Rep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Sql92 Security', p_metric_label_nlsid => 'SQL92_SECURITY_METRIC_LABEL', p_description => 'Collect the information of SQL92_SECURITY initialization parameter', p_description_nlsid => 'SQL92_SECURITY_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 ' ||'FROM esm_collection_latest ' ||'WHERE property =''sql92_security''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ); COMMIT ; -- This Url will direct to Initialization Parameters page -- From there you can edit setting for Initialization Parameters l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'value', p_url_link_template =>'database/instance/initParameters?event=findParameter\&target=^TARGET_NAME^\&type=^TARGET_TYPE^\&initParamName=sql92_security\&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Sql92_Security',--Policy Name p_metric_name =>'sql92Rep', --Metric name p_policy_label_nlsid =>'SQL92_SECURITY_NAME', p_description =>'Ensures use of SQL92 security features', p_description_nlsid =>'SQL92_SECURITY_DESC', p_impact => 'If SQL92 security features are not enabled, a user might be able to '|| 'execute an UPDATE or DELETE statement using a WHERE clause without having select privilege on a table.', p_impact_nlsid =>'SQL92_SECURITY_IMPACT', p_recommendation =>'Enable SQL92 security features by setting the initialization '|| 'parameter SQL92_SECURITY to TRUE.', p_recommendation_nlsid =>'SQL92_SECURITY_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>':value = ''FALSE''', p_message => 'Database is in an insecure state. SQL92 security features are not enabled.', p_message_nlsid =>'SQL92_SECURITY_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Data Base Link Metric -- Metric may collect one row per database target -- This may be non-violation or violation row -- Policy may result in one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'value', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Global Name', p_column_label_nlsid =>'GLOBAL_NAME_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'dbLinkGBLNameRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Global Name', p_metric_label_nlsid => 'GLOBAL_NAME_METRIC_LABEL', p_description => 'Collect the information of GLOBAL_NAME initialization parameter', p_description_nlsid => 'GLOBAL_NAME_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 ' ||'FROM esm_collection_latest ' ||'WHERE property =''db_link_global_name''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; --Policies -- This Url will direct to Initialization Parameters page -- From there you can edit setting for Initialization Parameters l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'value', p_url_link_template =>'database/instance/initParameters?event=findParameter\&target=^TARGET_NAME^\&type=^TARGET_TYPE^\&initParamName=global_name\&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'DB_Link_Global_Name',--Policy Name p_metric_name =>'dbLinkGBLNameRep', --Metric name p_policy_label_nlsid =>'GLOBAL_NAME_NAME', p_description =>'Ensures that the name of a database link is the same as that of the remote database', p_description_nlsid =>'GLOBAL_NAME_DESC', p_impact => 'Database link names that do not match the global names of the databases to which '|| 'they are connecting can cause an administrator to inadvertently give access '|| 'to a production server from a test or development server. Knowledge of '|| 'this can be used by a malicious user to gain access to the target database.', p_impact_nlsid =>'GLOBAL_NAME_IMPACT', p_recommendation =>'If you use or plan to use distributed processing, Oracle Corporation '|| 'recommends that you set the GLOBAL_NAMES initialization parameter to TRUE to '|| 'ensure the use of consistent naming conventions for databases and links in a networked environment.', p_recommendation_nlsid =>'GLOBAL_NAME_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>':value <> ''TRUE''', p_message =>'Database is in an insecure state. The GLOBAL_NAMES parameter is set to %:value%.', p_message_nlsid => 'GLOBAL_NAME_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Metric may collect more than one row per database target -- These may be non-violation or violation row -- Policy may result in more than one violation row 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 => 'link', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Database Link', p_column_label_nlsid =>'DBLINK_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'dbLinkPwdRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Database Link With Hard Coded Password', p_metric_label_nlsid => 'DBLINK_METRIC_LABEL', p_description => 'Collect the information about database link with hard coded password', p_description_nlsid => 'DBLINK_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 link,value as dummy ' ||'FROM esm_collection_latest ' ||'WHERE property =''db_link_with_password''', 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 => 'dbLinkPwdRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Database Link With Hard Coded Password', p_metric_label_nlsid => 'DBLINK_METRIC_LABEL', p_description => 'Collect the information about database link with hard coded password', p_description_nlsid => 'DBLINK_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 link,value as dummy ' ||'FROM esm_collection_latest ' ||'WHERE property =''db_link_with_password''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT; -- This Url will take user to Database link page -- From there he/she can delete link with hard coded password l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'link', p_url_link_template =>'database/databaseObjectsSearch?target=^TARGET_NAME^\&type=^TARGET_TYPE^\&otype=schema\&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP)); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'DB_Link_With_Password',--Policy Name p_metric_name =>'dbLinkPwdRep', --Metric name p_policy_label_nlsid =>'DB_LINK_WITH_PWD_NAME', p_description =>'Ensures database links with clear text passwords are not used', p_description_nlsid =>'DB_LINK_WITH_PWD_DESC', p_impact => 'The table SYS.LINK$ contains the clear text password used by the database link. '|| 'A malicious user can read clear text password from SYS.LINK$ table that can lead to undesirable consequences.', p_impact_nlsid =>'DB_LINK_WITH_PWDIMPACT', p_recommendation =>'Avoid creating fixed user database links.', p_recommendation_nlsid =>'DB_LINK_WITH_PWD_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, --p_condition =>':property =''db_link_with_password''' , p_condition =>'rownum > 0' , p_message =>'Database is in an insecure state. Database link %link% has clear text password.', p_message_nlsid =>'DB_LINK_WITH_PWD_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; ESM.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name =>'DB_Link_With_Password',--Policy Name p_metric_name =>'dbLinkPwdRep', --Metric name p_policy_label_nlsid =>'DB_LINK_WITH_PWD_NAME', p_description =>'Ensures database links with clear text passwords are not used', p_description_nlsid =>'DB_LINK_WITH_PWD_DESC', p_impact => 'The table SYS.LINK$ contains the clear text password used by the database link. '|| 'A malicious user can read clear text password from SYS.LINK$ table that can lead to undesirable consequences.', p_impact_nlsid =>'DB_LINK_WITH_PWDIMPACT', p_recommendation =>'Avoid creating fixed user database links.', p_recommendation_nlsid =>'DB_LINK_WITH_PWD_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, --p_condition =>':property =''db_link_with_password''' , p_condition =>'rownum > 0' , p_message =>'Database is in an insecure state. Database link %link% has clear text password.', p_message_nlsid =>'DB_LINK_WITH_PWD_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; -- Umask Setting Metric -- Metric may collect more than one row per database target -- These may be non-violation or violation row -- Policy may result in more than one violation row l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( p_catprop_3_choices => MGMT_CATEGORY_PROP_ARRAY(' ')) ); l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'umask', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Umask Value (In Octal)', p_column_label_nlsid =>'UMASK_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'file_name', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Picked up From', p_column_label_nlsid =>'FILE_ENV_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'umaskSettingRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Umask Setting on Unix', p_metric_label_nlsid => 'UMASK_METRIC_LABEL', p_description => 'Collect the information of umask setting of owner of Oracle home', p_description_nlsid => 'UMASK_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 umask,value2 as file_name,value2 as dummy ' ||'FROM esm_collection_latest ' ||'WHERE property=''umask_setting''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ); COMMIT ; -- Policy l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'umask'), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'file_name')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Umask_Setting', --Policy name p_metric_name =>'umaskSettingRep', --Metric name p_policy_label_nlsid =>'UMASK_SETTING_NAME', p_description =>'On UNIX systems, ensure that the owner of the Oracle software has an '|| 'appropriate umask value of 022 set', p_description_nlsid =>'UMASK_SETTING_DESC', p_impact => 'If umask is not set to an appropriate value (like 022), log or trace files '|| 'might become accessible to public exposing sensitive information.', p_impact_nlsid =>'UMASK_SETTING_IMPACT', p_recommendation =>'Set umask to 022 for the owner of Oracle software.', p_recommendation_nlsid =>'UMASK_SETTING_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database is in an insecure state. umask for the Oracle softwre owner is set to %umask%.', p_message_nlsid =>'UMASK_SETTING_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; --------------------------------------------------------------------------------------------------- -- Metric may collect more than one row per database target -- These may be non-violation or violation row -- Policy may result in more than one violation row 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 => 'profile', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Profile', p_column_label_nlsid =>'PROFILE_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'limit', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Failed Login Attempts', p_column_label_nlsid =>'FAILED_LOGIN_ATTEMPTS_COL')); MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'unlimitedFailedLoginAttemptsRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Profiles with Unlimited Login Attempts', p_metric_label_nlsid => 'FAILED_LOGIN_ATTEMPTS_METRIC_LABEL', p_description => 'Collect setting of FAILED_LOGIN_ATTEMPTS', p_description_nlsid => 'FAILED_LOGIN_ATTEMPTS_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 profile,value2 as limit ' ||'FROM esm_collection_latest ' ||'WHERE property=''Unlimited_login_attempts''', 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 => 'unlimitedFailedLoginAttemptsRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Profiles with Unlimited Login Attempts', p_metric_label_nlsid => 'FAILED_LOGIN_ATTEMPTS_METRIC_LABEL', p_description => 'Collect setting of FAILED_LOGIN_ATTEMPTS', p_description_nlsid => 'FAILED_LOGIN_ATTEMPTS_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 profile,value2 as limit ' ||'FROM esm_collection_latest ' ||'WHERE property=''Unlimited_login_attempts''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ); COMMIT ; -- Policy l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'profile', p_url_link_template =>'database/security/profile?oname=^profile^\&event=edit\&otype=PROFILE\&target=^TARGET_NAME^\&type=^TARGET_TYPE^\&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'limit')); l_paramDefList := MGMT_POLICY_PARAM_DEF_ARRAY( MGMT_POLICY_PARAM_DEF( param_name => 'MAX_FAILED_LOGIN_ATTEMPTS', param_name_nlsid => 'MAX_FAILED_LOGIN_ATTEMPTS_PNAME', param_type => MGMT_POLICY.G_PARAM_TYPE_NUMBER)); l_defaultParamValList := MGMT_POLICY_PARAM_VAL_ARRAY( MGMT_POLICY_PARAM_VAL.NEW( p_param_name => 'MAX_FAILED_LOGIN_ATTEMPTS', p_crit_threshold => '10')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL, p_param_values => l_defaultParamValList)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Unlimited_login_attempts', --Policy name p_metric_name =>'unlimitedFailedLoginAttemptsRep', --Metric name p_policy_label_nlsid =>'PROFILE_UNLMTED_FAILED_NAME', p_description =>'Ensure that the number of allowed failed login attempts is set to a reasonable number of days for all profiles', p_description_nlsid =>'UNLMT_FAILED_LGIN_DESC', p_impact =>'Permits manual and automated password guessing by a malicious user.', p_impact_nlsid =>'UNLMT_LGIN_IMPACT', p_recommendation =>'Set FAILED_LOGIN_ATTEMPTS in user profiles to no more than 10.', p_recommendation_nlsid =>'UNLMT_FAILED_LGIN_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'decode(:limit ,''UNLIMITED'',2147483647,to_number(:limit)) > :MAX_FAILED_LOGIN_ATTEMPTS', p_message =>'Database is in an insecure state. FAILED_LOGIN_ATTEMPTS is set to %limit% for the profile %profile%.', p_message_nlsid =>'UNLMT_FAILED_LGIN2_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_policy_param_list =>l_paramDefList, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT; ESM.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name =>'Unlimited_login_attempts', --Policy name p_metric_name =>'unlimitedFailedLoginAttemptsRep', --Metric name p_policy_label_nlsid =>'PROFILE_UNLMTED_FAILED_NAME', p_description =>'Ensure that the number of allowed failed login attempts is set to a reasonable number of days for all profiles', p_description_nlsid =>'UNLMT_FAILED_LGIN_DESC', p_impact =>'Permits manual and automated password guessing by a malicious user.', p_impact_nlsid =>'UNLMT_LGIN_IMPACT', p_recommendation =>'Set FAILED_LOGIN_ATTEMPTS in user profiles to no more than 10.', p_recommendation_nlsid =>'UNLMT_FAILED_LGIN_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_CRITICAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'decode(:limit ,''UNLIMITED'',2147483647,to_number(:limit)) > :MAX_FAILED_LOGIN_ATTEMPTS', p_message =>'Database is in an insecure state. FAILED_LOGIN_ATTEMPTS is set to %limit% for the profile %profile%.', p_message_nlsid =>'UNLMT_FAILED_LGIN2_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_policy_param_list =>l_paramDefList, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT; -- This comment is for all the tableAccess policies -- Metric may collect more than one row per database target -- These all are violation row -- Policy may result in more than one violation row 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 => 'grantee', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Grantee', p_column_label_nlsid =>'GRANTEE_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'privilege', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_TRUE, p_column_label => 'Object Privilege on AUD$ Table', p_column_label_nlsid =>'AUD_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'Dummy', p_column_label_nlsid =>'DUMMY_COL')); -- METRIC CREATION MGMT_METRIC.CREATE_METRIC( p_target_type => ESM.DB_TARGET_TYPE, p_metric_name => 'audTabRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Database Users with Access on AUD$', p_metric_label_nlsid => 'AUD_METRIC_LABEL', p_description => 'Collect the database users having access on AUD$', p_description_nlsid => 'AUD_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 grantee, value2 as privilege,value2 as dummy FROM esm_collection_latest ' ||'WHERE property =''access_aud_table''', 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 => 'audTabRep', p_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_valid_if_list => l_valid_if_list, p_metric_type => l_metric_type , p_metric_label => 'Database Users with Access on AUD$', p_metric_label_nlsid => 'AUD_METRIC_LABEL', p_description => 'Collect the database users having access on AUD$', p_description_nlsid => 'AUD_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 grantee, value2 as privilege,value2 as dummy FROM esm_collection_latest ' ||'WHERE property =''access_aud_table''', p_snapshot_name_list => l_snapshot_list, p_metric_column_list => l_metric_column_list ) ; COMMIT ; -- POLICY CREATION l_auto_enable := MGMT_GLOBAL.G_FALSE ; -- auto_enable is disabled for the following policies -- This Url will take user to USER EDIT SETTING PAGE -- From there he/she can modify object privileges assigned to user. l_ctxList := MGMT_VIOL_CTXT_DEF_ARRAY( MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'grantee', p_url_link_template =>'database/security/user?oname=^grantee^\&event=edit\&otype=USER\&target=^TARGET_NAME^\&type=^TARGET_TYPE^\&cancelURL=^CANCEL_URL^', p_url_link_type => MGMT_POLICY.G_URL_TYPE_JSP), MGMT_VIOL_CTXT_DEF.NEW( p_metric_column => 'privilege')); l_defaultSettingsList := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GLOBAL.G_IMPORTANCE_NORMAL)); ESM.CREATE_POLICY( p_target_type => ESM.DB_TARGET_TYPE, p_policy_name =>'Access_To_AUD_Table', --Policy name p_metric_name =>'audTabRep', --Metric name p_policy_label_nlsid =>'USR_ACCSS_AUD_NAME', p_description =>'Ensures restricted access to SYS.AUD$ table', p_description_nlsid =>'USR_ACCSS_AUD_DESC', p_impact => 'A knowlegeable and malicious user can gain access to sensitive audit information.', p_impact_nlsid =>'USR_ACCSS_AUD_IMPACT', p_recommendation =>'Revoke access to SYS.AUD$ table from the non-DBA/SYS database users.' , p_recommendation_nlsid =>'USR_ACCSS_AUD_RECOMM' , p_violation_level =>MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database is in an insecure state. User %grantee% has %privilege% privilege on the SYS.AUD$ table.', p_message_nlsid =>'USR_ACCSS_AUD_MESG', p_auto_enable => l_auto_enable, -- auto_enable is disabled for the following policies p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; ESM.CREATE_POLICY( p_target_type => 'rac_database', p_policy_name =>'Access_To_AUD_Table', --Policy name p_metric_name =>'audTabRep', --Metric name p_policy_label_nlsid =>'USR_ACCSS_AUD_NAME', p_description =>'Ensures restricted access to SYS.AUD$ table', p_description_nlsid =>'USR_ACCSS_AUD_DESC', p_impact => 'A knowlegeable and malicious user can gain access to sensitive audit information.', p_impact_nlsid =>'USR_ACCSS_AUD_IMPACT', p_recommendation =>'Revoke access to SYS.AUD$ table from the non-DBA/SYS database users.' , p_recommendation_nlsid =>'USR_ACCSS_AUD_RECOMM' , p_violation_level =>MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'rownum > 0', p_message =>'Database is in an insecure state. User %grantee% has %privilege% privilege on the SYS.AUD$ table.', p_message_nlsid =>'USR_ACCSS_AUD_MESG', p_auto_enable => l_auto_enable, -- auto_enable is disabled for the following policies p_cs_consider_percentage =>MGMT_GLOBAL.G_FALSE, p_start_type_meta_ver => ESM.DB_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; END ; / show errors ; SET ESCAPE OFF ;