Rem Rem $Header: lsnr_policies.sql 12-jan-2007.12:11:28 manosing Exp $ Rem Rem lsnr_policies.sql Rem Rem Copyright (c) 2006, 2007, Oracle. All rights reserved. Rem Rem NAME Rem lsnr_policies.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem manosing 01/12/07 - XbranchMerge manosing_lastpols from main Rem manosing 10/11/06 - ESA listener policies for 11.0.0.0 DB Console Rem manosing 10/11/06 - Created Rem SET ECHO ON SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 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; BEGIN l_metric_type := MGMT_GLOBAL.G_METRIC_TYPE_TABLE ; l_snapshot_list := MGMT_SNAPSHOT_ARRAY( MGMT_SNAPSHOT_OBJ.NEW( p_target_type => ESM.LSNR_TARGET_TYPE, p_snapshot_name =>ESM.SECURITY_SNAPSHOT_TYPE)); ---------->>Sqlnet_Inbound_Connect_Timeout<<------------------------- 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_NUMBER, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'sqlnet_ora_inbound_connect_timeout', p_column_label_nlsid =>'SQLNET_ORA_INBOUND_TIMEOUT_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER, 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.LSNR_TARGET_TYPE, p_metric_name => 'Sqlnet_Inbound_Connect_Timeout_Rep', p_type_meta_ver => ESM.LSNR_TYPE_META_VERSION, p_valid_if_list => NULL, p_metric_type => l_metric_type, p_usage_type => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT, p_metric_label => 'sqlnet ora inbound connect timeout', p_metric_label_nlsid => 'SQLNETORA_INBOUND_CONNECT_TIMEOUT_LABEL', p_description => 'Collect Sqlnetora inbound connect timeout parameter', p_description_nlsid => 'SQLNETORA_INBOUND_CONNECT_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, value as dummy ' ||'FROM esm_collection_latest ' ||'WHERE property =''inbound_connect_timeout''', 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 => 'value')); l_paramDefList := MGMT_POLICY_PARAM_DEF_ARRAY( MGMT_POLICY_PARAM_DEF( param_name => 'DFLT_VAL', param_name_nlsid => 'DFLT_VAL_PNAME', param_type => MGMT_POLICY.G_PARAM_TYPE_NUMBER)); l_defaultParamValList := MGMT_POLICY_PARAM_VAL_ARRAY( MGMT_POLICY_PARAM_VAL.NEW( p_param_name => 'DFLT_VAL', p_warn_threshold => '30')); 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.LSNR_TARGET_TYPE, p_policy_name =>'Sqlnet_Inbound_Connect_Timeout',--Policy Name p_metric_name =>'Sqlnet_Inbound_Connect_Timeout_Rep', --Metric name p_policy_label_nlsid =>'Sqlnetora_Inbound_Connect_Timeout_NAME', p_description =>'Ensures that all incomplete inbound connections to Oracle Net has a limited lifetime', p_description_nlsid =>'Sqlnetora_Inbound_Connect_Timeout_DESC', p_impact => 'Without this parameter or assigning it with a higher value , a client connection to the database server can stay open indefinitely or for the specified duration without authentication. Connections without authentication can introduce possible denial-of-service attacks, whereby malicious clients attempt to flood database servers with connect requests that consume resources. ', p_impact_nlsid =>'Sqlnetora_Inbound_Connect_Timeout_IMPACT', p_recommendation =>'Set the lowest possible value for the SQLNET.INBOUND_CONNECT_TIMEOUT parameter in sqlnet.ora. Ensure that the value of this parameter is higher than the value of INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file.', p_recommendation_nlsid =>'Sqlnetora_Inbound_Cconnect_Timeout_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'to_number(:value) > :DFLT_VAL' , p_message =>'Database is in an insecure state. sqlnet.inbound_connect_timeout parameter is set to %value%.', p_message_nlsid =>'Sqlnetora_Inbound_Connect_Timeout_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.LSNR_TYPE_META_VERSION, p_policy_param_list =>l_paramDefList, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT; ----------<>------------------------- ---------->>Lsnr_Inbound_Connect_Timeout<<------------------------- 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_NUMBER, p_is_key => MGMT_GLOBAL.G_FALSE, p_column_label => 'lsnr_ora_inbound_connect_timeout', p_column_label_nlsid =>'LSNR_ORA_INBOUND_TIMEOUT_COL'), MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => 'dummy', p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER, 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.LSNR_TARGET_TYPE, p_metric_name => 'Lsnr_Inbound_Connect_Timeout_Rep', p_type_meta_ver => ESM.LSNR_TYPE_META_VERSION, p_valid_if_list => NULL, p_metric_type => l_metric_type, p_usage_type => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT, p_metric_label => 'lsnr ora inbound connect timeout', p_metric_label_nlsid => 'LSNRORA_INBOUND_CONNECT_TIMEOUT_LABEL', p_description => 'Collect Lsnrora inbound connect timeout parameter', p_description_nlsid => 'LSNRORA_INBOUND_CONNECT_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 , value as dummy ' ||'FROM esm_collection_latest ' ||'WHERE property =''lsnr_inbound_connect_timeout''', 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 => 'value')); l_paramDefList := MGMT_POLICY_PARAM_DEF_ARRAY( MGMT_POLICY_PARAM_DEF( param_name => 'DFLT_VAL', param_name_nlsid => 'DFLT_VAL_PNAME', param_type => MGMT_POLICY.G_PARAM_TYPE_NUMBER)); l_defaultParamValList := MGMT_POLICY_PARAM_VAL_ARRAY( MGMT_POLICY_PARAM_VAL.NEW( p_param_name => 'DFLT_VAL', 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.LSNR_TARGET_TYPE, p_policy_name =>'Lsnr_Inbound_Connect_Timeout',--Policy Name p_metric_name =>'Lsnr_Inbound_Connect_Timeout_Rep', --Metric name p_policy_label_nlsid =>'Lsnrora_Inbound_Connect_Timeout_NAME', p_description =>'Ensures that all incomplete inbound connections to Oracle Listener has a limited lifetime', p_description_nlsid =>'Lsnrora_Inbound_Connect_Timeout_DESC', p_impact => 'This limit protects the listener from consuming and holding resources for client connection requests that do not complete. A malicious user could use this to flood the listener with requests that result in a denial of service to authorized users.', p_impact_nlsid =>'Lsnrora_Inbound_Connect_Timeout_IMPACT', p_recommendation =>'Set the lowest possible value for the INBOUND_CONNECT_TIMEOUT_listener_name parameter in listener.ora. Ensure that the value of this parameter is lower than the value of SQLNET.INBOUND_CONNECT_TIMEOUT parameter in the sqlnet.ora file.', p_recommendation_nlsid =>'Lsnrora_Inbound_Cconnect_Timeout_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'to_number(:value) > :DFLT_VAL' , p_message =>'Database is in an insecure state. lsnr.inbound_connect_timeout parameter is set to %value%.', p_message_nlsid =>'Lsnrora_Inbound_Cconnect_Timeout_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.LSNR_TYPE_META_VERSION, p_policy_param_list =>l_paramDefList, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT ; ----------<>------------------------- ---------->>Sqlnet_Ssl_Server_DN_Match<<------------------------- 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 => 'Ssl_Server_DN_Match', p_column_label_nlsid =>'SSL_SERVER_DN_MATCH_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.LSNR_TARGET_TYPE, p_metric_name => 'Ssl_Server_DN_Match_Rep', p_type_meta_ver => ESM.LSNR_TYPE_META_VERSION, p_valid_if_list => NULL, p_metric_type => l_metric_type, p_usage_type => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT, p_metric_label => 'sqlnet ora ssl_server_dn_match', p_metric_label_nlsid => 'SSL_SERVER_DN_MATCH_LABEL', p_description => 'Collect Sqlnetora ssl_server_dn_match parameter', p_description_nlsid => 'SSL_SERVER_DN_MATCH_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, value as dummy ' ||'FROM esm_collection_latest ' ||'WHERE property =''ssl_server_dn_match''', 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 => 'value')); 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.LSNR_TARGET_TYPE, p_policy_name =>'Ssl_Server_DN_Match',--Policy Name p_metric_name =>'Ssl_Server_DN_Match_Rep', --Metric name p_policy_label_nlsid =>'Ssl_Server_DN_Match_NAME', p_description =>'Ensures ssl_server_dn_match is enabled in sqlnet.ora and in turn SSL ensures that the certificate is from the server', p_description_nlsid =>'Ssl_Server_DN_Match_DESC', p_impact => 'If ssl_server_dn_match parameter is disabled, then SSL performs the check but allows the connection, regardless if there is a match. Not enforcing the match allows the server to potentially fake its identity.', p_impact_nlsid =>'Ssl_Server_DN_Match_IMPACT', p_recommendation =>'Enable ssl_server_dn_match parameter in the sqlnet.ora file.', p_recommendation_nlsid =>'Ssl_Server_DN_Match_RECOMM', p_violation_level =>MGMT_GLOBAL.G_SEVERITY_WARNING, p_condition_type =>MGMT_GLOBAL.G_CONDITION_SQL, p_condition =>'lower(:value) != ''true'' and lower(:value) != ''yes'' and lower(:value) != ''on''' , p_message =>'Database is in an insecure state. ssl_server_dn_match parameter is set to %value%.', p_message_nlsid =>'Ssl_Server_DN_Match_MESG', p_cs_consider_percentage =>MGMT_GLOBAL.G_TRUE, p_start_type_meta_ver => ESM.LSNR_TYPE_META_VERSION, p_dflt_param_val_list =>l_defaultSettingsList, p_viol_ctxt_list =>l_ctxList ); COMMIT; ----------<>------------------------- END ; / show errors ;