Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\db\v102010\reports\db_outofbox_audit.sql
Rem drv: <migrate type="data_upgrade" version="10.2.0.0" pos="reports/db_outofbox_elements.sql+" condition="EM_REPOS_MODE=CENTRAL"/> Rem Rem $Header: db_outofbox_audit.sql 28-nov-2006.06:33:37 mnihalan Exp $ Rem Rem db_outofbox_storage.sql Rem Rem Copyright (c) 2004, 2006, Oracle. All rights reserved. Rem Rem NAME Rem db_outofbox_storage.sql - <one-line expansion of the name> Rem Rem DESCRIPTION Rem <short description of component this file declares/defines> Rem Rem NOTES Rem !!! Please update upgrade script if updates are made. !!! Rem Rem MODIFIED (MM/DD/YY) Rem mnihalan 11/28/06 - Fix bug 5683385 Rem qsong 08/25/05 - bug 4574597 Rem qsong 08/22/05 - bug 4563360 Rem chyu 07/12/05 - adding the new rep manager upgrade header Rem qsong 04/08/05 - fixes due to UI review Rem xshen 03/21/05 - add comments Rem qsong 02/23/05 - swap instruction and sperator position Rem qsong 02/14/05 - add instruction texts for the reports Rem qsong 02/03/05 - remove junk char Rem qsong 01/11/05 - use beans to check audit settings Rem qsong 01/05/05 - qsong_more_report Rem qsong 12/02/04 - Creation Rem SET DEFINE OFF BEGIN DECLARE l_target_types MGMT_IP_TARGET_TYPES; l_param_classes MGMT_IP_PARAM_CLASSES; l_param_values MGMT_IP_PARAM_VALUE_LIST; l_targets MGMT_IP_TARGET_LIST; l_report_guid RAW(16); l_element_guid RAW(16); l_sql VARCHAR2(32767); BEGIN -- ------------------------------------------------------------------------- -- Element and Report Definitions -- ------------------------------------------------------------------------- -- ---------------------------------------------- -- REPORT - Database Failed Login Count by User -- ---------------------------------------------- -- Element Definition - Instruction Text For General Audit Report l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'AUDIT_REPORT_INSTRUCTION'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle', 'OraInstructionText'); mgmt_ip.add_element_definition ('DBMSG_REPORT_INSTRUCTION', 'database_target_type', 'DBMSG_REPORT_INSTRUCTION_DESC', 'oracle.sysman.eml.ip.render.elem.TextRenderController', 1, null, l_param_values); -- DB Failed Login Instruction Text l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'DB_FAILED_LOGIN_AUDIT_REPORT_INSTRUCTION'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle', 'OraInstructionText'); mgmt_ip.add_element_definition ('DBMSG_DB_FAILED_LOGIN_REPORT_INSTRUCTION', 'database_target_type', 'DBMSG_DB_FAILED_LOGIN_REPORT_INSTRUCTION_DESC', 'oracle.sysman.eml.ip.render.elem.TextRenderController', 1, null, l_param_values); -- Group Failed Login Instruction Text l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'GROUP_FAILED_LOGIN_AUDIT_REPORT_INSTRUCTION'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle', 'OraInstructionText'); mgmt_ip.add_element_definition ('DBMSG_GROUP_FAILED_LOGIN_REPORT_INSTRUCTION', 'database_target_type', 'DBMSG_GROUP_FAILED_LOGIN_REPORT_INSTRUCTION_DESC', 'oracle.sysman.eml.ip.render.elem.TextRenderController', 1, null, l_param_values); -- DB Successful Login Instruction Text l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'DB_SUCC_LOGIN_AUDIT_REPORT_INSTRUCTION'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle', 'OraInstructionText'); mgmt_ip.add_element_definition ('DBMSG_DB_SUCC_LOGIN_REPORT_INSTRUCTION', 'database_target_type', 'DBMSG_DB_SUCC_LOGIN_REPORT_INSTRUCTION_DESC', 'oracle.sysman.eml.ip.render.elem.TextRenderController', 1, null, l_param_values); -- SYS Operations Instruction Text l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'SYS_OP_AUDIT_REPORT_INSTRUCTION'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle', 'OraInstructionText'); mgmt_ip.add_element_definition ('DBMSG_SYS_OP_REPORT_INSTRUCTION', 'database_target_type', 'DBMSG_SYS_OP_REPORT_INSTRUCTION_DESC', 'oracle.sysman.eml.ip.render.elem.TextRenderController', 1, null, l_param_values); -- Element Definition - Messagebox to show any audit related warnings, if any mgmt_ip.add_element_definition ('DBMSG_AUDIT_SETTING_WARNING', 'database_target_type', 'DBMSG_AUDIT_SETTING_WARNING_DESC', 'oracle.sysman.emo.util.reports.security.audit.AuditSettingWarningTableRenderController', 1, null, null); -- Element Definition - Total Failed Login Count (nameValuePair) l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(2); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.nameValueDisplay', '1'); mgmt_ip.add_element_definition ('DBMSG_TOTAL_FAILED_LOGIN_COUNT', 'database_target_type', 'DBMSG_TOTAL_FAILED_LOGIN_COUNT_DESC', 'oracle.sysman.emo.util.reports.security.audit.AuditTotalFailedLogonTableRenderController', 1, null, l_param_values); -- ------------------------------------------------------------ -- TABLE 1.1: Total Login Failed Count By User -- -- Data Scope: DB (Realtime Data) -- Chart Type: Table -- Description: Shows 1 label/value pairs: -- Total Failed Login Count -- Shows Table with 2 columns -- User Name, Failed Login Count -- -- ------------------------------------------------------------ -- Element Definition - Total Failed Login Count (Table) l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); mgmt_ip.add_element_definition ('DBMSG_FAILED_LOGINS_BY_USER', 'database_target_type', 'DBMSG_FAILED_LOGINS_BY_USER_DESC', 'oracle.sysman.emo.util.reports.security.audit.AuditFailedLoginTableRenderController', 1, null, l_param_values); -- Element Definition - Historical Total for User with top-5 Failed Logon l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(5); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType', 'barChart'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.stacked', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.yAxisLabel', 'TOTAL_FAILED_LOGINS'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.legendPosition', 'south'); mgmt_ip.add_element_definition ('DBMSG_HISTORICAL_TOTAL_TOP_5_FAILED_LOGIN', 'database_target_type', 'DBMSG_HISTORICAL_TOTAL_TOP_5_FAILED_LOGIN_DESC', 'oracle.sysman.emo.util.reports.security.audit.AuditLoginFailedChartRenderController', 1, null, l_param_values); -- ------------------------------------------------------------------------- -- Element and Report Definitions -- ------------------------------------------------------------------------- -- ---------------------------------------------- -- REPORT - Database Failed Login Count by User -- ---------------------------------------------- -- ------------------------------------------------------------ -- TABLE 1.1: Total Login Failed Count By User -- -- Data Scope: DB (Realtime Data) -- Chart Type: Table -- Description: Shows 1 label/value pairs: -- Total Failed Login Count -- Shows Table with 2 columns -- User Name, Failed Login Count -- -- ------------------------------------------------------------ -- Element Definition - Total Failed Login By User Detail l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '3'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); mgmt_ip.add_element_definition ('DBMSG_FAILED_LOGIN_DETAILS', 'database_target_type', 'DBMSG_FAILED_LOGIN_DETAILS_DESC', 'oracle.sysman.emo.util.reports.security.audit.AuditFailedLoginDetailTableRenderController', 1, null, l_param_values); -- Report Definition l_target_types := MGMT_IP_TARGET_TYPES(); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodOption', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodUserCust', 'true'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodParam', '0:1'); -- failed login for last 7 days l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'DBMSG_DATABASE_FAILED_LOGINS', p_description_nlsid => 'DBMSG_DATABASE_FAILED_LOGINS_DESC', p_owner => 'SYSMAN', p_category_nlsid => 'Security', p_sub_category_nlsid => 'Database', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1, p_parameters => l_param_values); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_DB_FAILED_LOGIN_REPORT_INSTRUCTION', 'database_target_type', '', 1, 1, null, null); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_AUDIT_SETTING_WARNING', 'database_target_type', '', 2, 2, null, null); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_TOTAL_FAILED_LOGIN_COUNT', 'database_target_type', '', 3, 3, null, null); -- Element 2 - Total Failed Login Count By User (table) l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_FAILED_LOGINS_BY_USER', 'database_target_type', 'DBMSG_FAILED_LOGINS_BY_USER_DESC', 4, 4, null, null); -- Element 3 - Historical Top 5 Failed Login (chart) l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_HISTORICAL_TOTAL_TOP_5_FAILED_LOGIN', 'database_target_type', 'DBMSG_HISTORICAL_TOTAL_TOP_5_FAILED_LOGIN_DESC', 5, 4, null, null); -- Element 4 - Detailed Login Failed (table) l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_FAILED_LOGIN_DETAILS', 'database_target_type', 'DBMSG_FAILED_LOGIN_DETAILS_DESC', 6, 5, null, null); -- Report 2: Database Successful Login Summary l_target_types := MGMT_IP_TARGET_TYPES(); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodOption', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodUserCust', 'true'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodParam', '0:0'); -- successfull login for last 24 hours l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'DBMSG_SUCCESSFUL_LOGINS', p_description_nlsid => 'DBMSG_SUCCESSFUL_LOGINS_DESC', p_owner => 'SYSMAN', p_category_nlsid => 'Security', p_sub_category_nlsid => 'Database', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1, p_parameters => l_param_values); -- Element Definition - Total Successful Login Count (nameValuePair) l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(2); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.nameValueDisplay', '1'); mgmt_ip.add_element_definition ('DBMSG_TOTAL_SUCC_LOGIN_COUNT', 'database_target_type', 'DBMSG_TOTAL_SUCC_LOGIN_COUNT_DESC', 'oracle.sysman.emo.util.reports.security.audit.AuditTotalSuccLogonTableRenderController', 1, null, l_param_values); -- Element Definition - Successful Login Count By User (table) l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); mgmt_ip.add_element_definition ('DBMSG_SUCCESSFUL_LOGIN_COUNT_BY_USER', 'database_target_type', 'DBMSG_SUCCESSFUL_LOGIN_COUNT_BY_USER_DESC', 'oracle.sysman.emo.util.reports.security.audit.AuditSuccLoginTableRenderController', 1, null, l_param_values); -- Element Definition - Successful Login Detail l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); mgmt_ip.add_element_definition ('DBMSG_SUCCESSFUL_LOGIN_DETAILS', 'database_target_type', 'DBMSG_SUCCESSFUL_LOGIN_DETAILS_DESC', 'oracle.sysman.emo.util.reports.security.audit.AuditSuccLoginDetailTableRenderController', 1, null, l_param_values); -- Element 2 - Audit report instruction (text) l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_DB_SUCC_LOGIN_REPORT_INSTRUCTION', 'database_target_type', '', 1, 1, null, null); -- Element 2 - Audit setting warning text(table) l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_AUDIT_SETTING_WARNING', 'database_target_type', '', 2, 2, null, null); -- Element 3 - Total Successful login count (nameValuePair) l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_TOTAL_SUCC_LOGIN_COUNT', 'database_target_type', '', 3, 3, null, null); -- Element 4 - Successful login count by user (table) l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_SUCCESSFUL_LOGIN_COUNT_BY_USER', 'database_target_type', 'DBMSG_SUCCESSFUL_LOGIN_COUNT_BY_USER_DESC', 4, 4, null, null); -- Element 5 - Successful login detail(table) l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_SUCCESSFUL_LOGIN_DETAILS', 'database_target_type', 'DBMSG_SUCCESSFUL_LOGIN_DETAILS_DESC', 5, 5, null, null); -- Report 3: Sys operations audit report l_target_types := MGMT_IP_TARGET_TYPES(); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodOption', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodUserCust', 'true'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodParam', '0:1'); -- sys operations for last 7 days l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'DBMSG_SYS_USER_OPERATIONS', p_description_nlsid => 'DBMSG_SYS_USER_OPERATIONS_DESC', p_owner => 'SYSMAN', p_category_nlsid => 'Security', p_sub_category_nlsid => 'Database', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1, p_parameters => l_param_values); -- Element Definition - Instruction Text l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'AUDIT_REPORT_INSTRUCTION'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle', 'OraInstructionText'); mgmt_ip.add_element_definition ('DBMSG_REPORT_INSTRUCTION', 'database_target_type', 'DBMSG_REPORT_INSTRUCTION_DESC', 'oracle.sysman.eml.ip.render.elem.TextRenderController', 1, null, l_param_values); -- Element 1 - Sys User Operations Summary(nameValue) -- Element Definition - nameValue display: Sys User Operations Summary l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(2); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.nameValueDisplay', '1'); mgmt_ip.add_element_definition ('DBMSG_SYS_USER_OPERATIONS_COUNT', 'database_target_type', 'DBMSG_SYS_USER_OPERATIONS_COUNT_DESC', 'oracle.sysman.emo.util.reports.security.audit.AuditSysOpSummaryTableRenderController', 1, null, l_param_values); -- Element 2 - Sys User Operations (Table) -- Element Definition - Table: Sys User Operations l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '3'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); mgmt_ip.add_element_definition ('DBMSG_SYS_USER_OPERATIONS_DETAILS', 'database_target_type', 'DBMSG_SYS_USER_OPERATIONS_DETAILS_DESC', 'oracle.sysman.emo.util.reports.security.audit.AuditSysOpTableRenderController', 2, null, l_param_values); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_SEPARATOR', 'database_target_type', '', 1, 1, null, null); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_SYS_OP_REPORT_INSTRUCTION', 'database_target_type', '', 2, 2, null, null); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_SYS_USER_OPERATIONS_COUNT', 'database_target_type', '', 3, 3, null, null); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_SYS_USER_OPERATIONS_DETAILS', 'database_target_type', 'DBMSG_SYS_USER_OPERATIONS_DETAILS_DESC', 4, 4, null, null); ---------------------------------------------------------------------------------------- -- Database Group Audit Failed Report ---------------------------------------------------------------------------------------- -- Element Definition - Total Failed Login Count (nameValuePair) l_sql := 'SELECT sum(round((md.average * md.sample_count),0)) AS TOTAL_FAILED_LOGINS, max(md.rollup_timestamp) AS LAST_COLLECTION_TIMESTAMP FROM mgmt$metric_daily md, (SELECT gm.member_target_guid as target_guid, gm.member_target_name as database_name FROM mgmt$group_flat_memberships gm, mgmt$target t WHERE gm.composite_target_guid = ??EMIP_BIND_TARGET_GUID?? AND (gm.member_target_type=''rac_database'' OR (gm.member_target_type=''oracle_database'' AND t.target_guid=gm.member_target_guid AND t.type_qualifier3 != ''RACINST''))) db WHERE md.target_guid=db.target_guid AND md.metric_name=''audit_failed_logins_historical'' AND md.metric_column=''failed_login_count'' AND md.rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND md.rollup_timestamp <= ??EMIP_BIND_END_DATE?? '; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.security.audit.reports.groupTotalFailedLoginCount', p_sql_statement => l_sql); l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(5); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.nameValueDisplay', '1'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'oracle.sysman.db.security.audit.reports.groupTotalFailedLoginCount'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); mgmt_ip.add_element_definition ('DBMSG_GROUP_TOTAL_FAILED_LOGIN_COUNT', 'database_target_type', 'DBMSG_GROUP_TOTAL_FAILED_LOGIN_COUNT_DESC', 'oracle.sysman.eml.ip.render.elem.TableRenderController', 1, null, l_param_values); -- Element Definition - Historical Group Failed Login Count(table) l_sql := 'SELECT md.rollup_timestamp AS DATE_COLUMN, sum(round((md.average * md.sample_count),0)) AS FAILED_LOGINS FROM mgmt$metric_daily md, (SELECT gm.member_target_guid as target_guid, gm.member_target_name as database_name FROM mgmt$group_flat_memberships gm, mgmt$target t WHERE gm.composite_target_guid = ??EMIP_BIND_TARGET_GUID?? AND (gm.member_target_type=''rac_database'' OR (gm.member_target_type=''oracle_database'' AND t.target_guid=gm.member_target_guid AND t.type_qualifier3 != ''RACINST''))) db WHERE md.target_guid=db.target_guid AND md.metric_name=''audit_failed_logins_historical'' AND md.metric_column=''failed_login_count'' AND md.rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND md.rollup_timestamp <= ??EMIP_BIND_END_DATE?? GROUP BY md.rollup_timestamp'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.security.audit.reports.groupDailyFailedLoginCount', p_sql_statement => l_sql); l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(2); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'oracle.sysman.db.security.audit.reports.groupDailyFailedLoginCount'); mgmt_ip.add_element_definition ('DBMSG_GROUP_DAILY_FAILED_LOGINS_TABLE', 'database_target_type', 'DBMSG_GROUP_DAILY_FAILED_LOGINS_TABLE_DESC', 'oracle.sysman.eml.ip.render.elem.TableRenderController', 1, null, l_param_values); -- Element Definition - Historical Group Failed Login Count( bar chart) l_sql := 'SELECT db.group_name, md.rollup_timestamp AS TIMESTAMP, sum(round((md.average * md.sample_count),0)) AS FAILED_LOGIN_COUNT FROM mgmt$metric_daily md, (SELECT gm.composite_target_name as group_name, gm.member_target_guid as target_guid, gm.member_target_name as database_name FROM mgmt$group_flat_memberships gm, mgmt$target t WHERE gm.composite_target_guid = ??EMIP_BIND_TARGET_GUID?? AND (gm.member_target_type=''rac_database'' OR (gm.member_target_type=''oracle_database'' AND t.target_guid=gm.member_target_guid AND t.type_qualifier3 != ''RACINST''))) db WHERE md.target_guid=db.target_guid AND md.metric_name=''audit_failed_logins_historical'' AND md.metric_column=''failed_login_count'' AND md.rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND md.rollup_timestamp <= ??EMIP_BIND_END_DATE?? GROUP BY db.group_name, md.rollup_timestamp'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.security.audit.reports.groupDailyFailedLoginCountForChart', p_sql_statement => l_sql); l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(5); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'oracle.sysman.db.security.audit.reports.groupDailyFailedLoginCountForChart'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType', 'timeSeriesBarChart'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.yAxisLabel', 'FAILED_LOGIN_COUNT'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.legendPosition', 'south'); mgmt_ip.add_element_definition ('DBMSG_GROUP_DAILY_FAILED_LOGINS_CHART', 'database_target_type', 'DBMSG_GROUP_DAILY_FAILED_LOGINS_CHART_DESC', 'oracle.sysman.eml.ip.render.elem.ChartRenderController', 1, null, l_param_values); -- Element Definition - Group Total Failed Login Count (nameValuePair) l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(2); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.nameValueDisplay', '1'); mgmt_ip.add_element_definition ('DBMSG_GROUP_CURRENT_TOTAL_FAILED_LOGIN', 'database_target_type', 'DBMSG_GROUP_CURRENT_TOTAL_FAILED_LOGIN_DESC', 'oracle.sysman.emo.util.reports.security.audit.GroupCurrFailedLoginSummaryTableRenderController', 1, null, l_param_values); -- Element Definition - Historical Failed Login Count By Database(table) l_sql := 'SELECT DB_NAME, FAILED_LOGINS FROM (SELECT md.target_name AS DB_NAME, sum(round((md.average * md.sample_count),0)) AS FAILED_LOGINS FROM mgmt$metric_daily md, (SELECT gm.member_target_guid as target_guid, gm.member_target_name as database_name FROM mgmt$group_flat_memberships gm, mgmt$target t WHERE gm.composite_target_guid = ??EMIP_BIND_TARGET_GUID?? AND (gm.member_target_type=''rac_database'' OR (gm.member_target_type=''oracle_database'' AND t.target_guid=gm.member_target_guid AND t.type_qualifier3 != ''RACINST''))) db WHERE md.target_guid=db.target_guid AND md.metric_name=''audit_failed_logins_historical'' AND md.metric_column=''failed_login_count'' AND md.rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND md.rollup_timestamp <= ??EMIP_BIND_END_DATE?? GROUP BY md.target_name ORDER BY FAILED_LOGINS DESC) WHERE rownum <= 5'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.security.audit.reports.topDBFailedLoginCount', p_sql_statement => l_sql); l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'oracle.sysman.db.security.audit.reports.topDBFailedLoginCount'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues', 'true'); mgmt_ip.add_element_definition ('DBMSG_GROUP_HISTORICAL_TOP_5_FAILED_LOGIN_BY_DB_TABLE', 'database_target_type', '', 'oracle.sysman.eml.ip.render.elem.TableRenderController', 1, null, l_param_values); -- Element Definition - Historical Failed Login Count By Database( bar chart) l_sql := 'SELECT m.target_name AS DB_NAME, to_date(to_char(m.rollup_timestamp,''YY-MM-DD''), ''YY-MM-DD'') as TIMESTAMP, round((m.average * m.sample_count),0) AS FAILED_LOGIN_COUNT FROM mgmt$metric_daily m, (SELECT target_guid FROM (SELECT md.target_guid AS TARGET_GUID, sum(round((md.average * md.sample_count),0)) AS TOTAL_FAILED_LOGINS FROM mgmt$metric_daily md, (SELECT gm.member_target_guid as target_guid, gm.member_target_name as database_name FROM mgmt$group_flat_memberships gm, mgmt$target t WHERE gm.composite_target_guid = ??EMIP_BIND_TARGET_GUID?? AND (gm.member_target_type=''rac_database'' OR (gm.member_target_type=''oracle_database'' AND t.target_guid=gm.member_target_guid AND t.type_qualifier3 != ''RACINST''))) db WHERE md.target_guid=db.target_guid AND md.metric_name=''audit_failed_logins_historical'' AND md.metric_column=''failed_login_count'' AND md.rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND md.rollup_timestamp <= ??EMIP_BIND_END_DATE?? GROUP BY md.target_guid ORDER BY TOTAL_FAILED_LOGINS DESC) WHERE rownum <= 5) t WHERE m.target_guid = t.target_guid AND m.metric_name=''audit_failed_logins_historical'' AND m.metric_column=''failed_login_count'' AND m.rollup_timestamp >= ??EMIP_BIND_START_DATE?? AND m.rollup_timestamp <= ??EMIP_BIND_END_DATE??'; mgmt_ip.register_sql_statement ( p_version => '10.2', p_name => 'oracle.sysman.db.security.audit.reports.topDBFailedLoginCountByDay', p_sql_statement => l_sql); l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(6); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle', 'oracle.sysman.db.rsc.sec.AuditMsg'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.legendPosition', 'south'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.NamedSqlStatement', 'oracle.sysman.db.security.audit.reports.topDBFailedLoginCountByDay'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType', 'timeSeriesBarChart'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.yAxisLabel', 'FAILED_LOGIN_COUNT'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.stacked', 'true'); mgmt_ip.add_element_definition ('DBMSG_GROUP_HISTORICAL_TOP_5_FAILED_LOGIN_BY_DB_CHART', 'database_target_type', 'DBMSG_GROUP_HISTORICAL_TOP_5_FAILED_LOGIN_BY_DB_CHART_DESC', 'oracle.sysman.eml.ip.render.elem.ChartRenderController', 1, null, l_param_values); -- Report Definition (Database Group Failed Login Report) l_target_types := MGMT_IP_TARGET_TYPES(); l_target_types.extend(1); l_target_types(1) := 'composite'; l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodOption', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodUserCust', 'true'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodParam', '0:1'); -- failed login for last 7 days l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'DBMSG_DATABASE_GROUP_FAILED_LOGIN', p_description_nlsid => 'DBMSG_DATABASE_GROUP_FAILED_LOGIN_DESC', p_owner => 'SYSMAN', p_category_nlsid => 'Security', p_sub_category_nlsid => 'DBMSG_DATABASE', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1, p_parameters => l_param_values); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_GROUP_FAILED_LOGIN_REPORT_INSTRUCTION', 'database_target_type', '', 1, 1, null, null); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_GROUP_CURRENT_TOTAL_FAILED_LOGIN', 'database_target_type', '', 2, 2, null, null); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_GROUP_DAILY_FAILED_LOGINS_TABLE', 'database_target_type', 'DBMSG_GROUP_DAILY_FAILED_LOGINS_TABLE_DESC', 3, 3, null, null); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_GROUP_DAILY_FAILED_LOGINS_CHART', 'database_target_type', 'DBMSG_GROUP_DAILY_FAILED_LOGINS_CHART_DESC', 4, 3, null, null); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_GROUP_HISTORICAL_TOP_5_FAILED_LOGIN_BY_DB_TABLE', 'database_target_type', 'DBMSG_GROUP_HISTORICAL_TOP_5_FAILED_LOGIN_BY_DB_DESC', 5, 4, null, null); l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'DBMSG_GROUP_HISTORICAL_TOP_5_FAILED_LOGIN_BY_DB_CHART', 'database_target_type', 'DBMSG_GROUP_HISTORICAL_TOP_5_FAILED_LOGIN_BY_DB_CHART_DESC', 6, 4, null, null); COMMIT; END; END; / SET DEFINE ON
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de