Rem drv:
Rem $Header: db_outofbox_audit.sql 08-nov-2005.13:25:55 mnihalan Exp $
Rem
Rem db_outofbox_storage.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.
Rem
Rem NAME
Rem db_outofbox_storage.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem !!! Please update upgrade script if updates are made. !!!
Rem
Rem MODIFIED (MM/DD/YY)
Rem mnihalan 11/08/05 - Fix bug 4630436
Rem qsong 08/29/05 - 4574075
Rem qsong 08/19/05 - bug 4563360
Rem chyu 07/20/05 - modifying the type to post_creation
Rem gsbhatia 07/11/05 - New repmgr header impl
Rem lgloyd 06/24/05 - normalize category strings
Rem mnihalan 06/09/05 - Change metric name
Rem mnihalan 05/31/05 - Support rac database
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',
'IPMSG_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',
'IPMSG_DATABASE_TARGET_TYPE',
'DBMSG_DB_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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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(2);
l_target_types(1) := 'oracle_database';
l_target_types(2) := 'rac_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 => 'IPMSG_SECURITY_CATEGORY',
p_sub_category_nlsid => 'IPMSG_DATABASE_TARGET_TYPE',
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',
'IPMSG_DATABASE_TARGET_TYPE',
'',
1,
1,
null,
null);
l_element_guid := mgmt_ip.add_element_to_report_def (
l_report_guid,
'DBMSG_AUDIT_SETTING_WARNING',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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(2);
l_target_types(1) := 'oracle_database';
l_target_types(2) := 'rac_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 => 'IPMSG_SECURITY_CATEGORY',
p_sub_category_nlsid => 'IPMSG_DATABASE_TARGET_TYPE',
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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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(2);
l_target_types(1) := 'oracle_database';
l_target_types(2) := 'rac_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 => 'IPMSG_SECURITY_CATEGORY',
p_sub_category_nlsid => 'IPMSG_DATABASE_TARGET_TYPE',
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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_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',
'IPMSG_DATABASE_TARGET_TYPE',
'DBMSG_SYS_USER_OPERATIONS_DETAILS_DESC',
4,
4,
null,
null);
----------------------------------------------------------------------------------------
-- Database Group Audit Failed Report
----------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------
-- REPORT 3: Database Group Audit Failed Report
--
-- Description: This report displays a summary of failed login attempts for each
-- database in the group.
-- -------------------------------------------------------------------------
-- -------------------------------------------
-- 1.0 Report Definition
-- -------------------------------------------
-- 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');
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 => 'IPMSG_SECURITY_CATEGORY',
p_sub_category_nlsid => 'IPMSG_DATABASE_TARGET_TYPE',
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 1.1: Database Group Audit Failed Report Instruction Text
--
-- Data Scope: Message Bundle
-- Chart Type: Text
-- Agent Version: All
--
-- Description: Instructions for this 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',
'GROUP_FAILED_LOGIN_AUDIT_REPORT_INSTRUCTION');
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle',
'OraInstructionText');
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'IPMSG_STYLED_TEXT',
p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
p_header_nlsid => '',
p_element_order => 1,
p_element_row => 1,
p_parameters => l_param_values,
p_targets => null);
-- ----------------------------------------------------------------
-- Element 1.2: Total failed login count
--
-- Data Scope: Repository
-- Chart Type: Label Value Pair
-- Agent Version: All
--
-- Description: Name value pair of total failed login count and last
-- Collection timestamp.
-- ----------------------------------------------------------------
-- ---------------------------------
-- 1.2 Register SQL for this element
-- ---------------------------------
l_sql :=
'SELECT
sum(round((md.average * md.sample_count),0)) AS TOTAL_FAILED_LOGINS,
max(to_timestamp_tz(md.key_value, ''YYYY-MM-DD HH24:MI:SS TZR'')) 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.key_value>= to_char(CAST(??EMIP_BIND_START_DATE?? AS TIMESTAMP) AT TIME ZONE ''GMT'', ''YYYY-MM-DD HH24:MI:SS TZD'') AND
md.key_value <= to_char(CAST(??EMIP_BIND_END_DATE?? AS TIMESTAMP) AT TIME ZONE ''GMT'', ''YYYY-MM-DD HH24:MI:SS TZD'')';
mgmt_ip.register_sql_statement (
p_version => '10.2',
p_name => 'oracle.sysman.db.security.audit.reports.groupTotalFailedLoginCount',
p_sql_statement => l_sql);
-- ---------------------------------
-- 1.2 Add this Element To the Report
-- ---------------------------------
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(4);
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.sqlStatement.bindSingleTarget',
'true');
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'DBMSG_GROUP_USER_TABLE_FROM_SQL_ELEM',
p_element_type_nlsid => 'DBMSG_GROUP_TARGET_TYPE',
p_header_nlsid => '',
p_element_order => 2,
p_element_row => 2,
p_parameters => l_param_values,
p_targets => null);
-- ----------------------------------------------------------------
-- Element 1.3: Historical Group Failed Login Count(table)
--
-- Data Scope: Repository
-- Chart Type: Table
-- Agent Version: All
--
-- Description: Displays the historical group failed login count.
-- ----------------------------------------------------------------
-- ---------------------------------
-- 1.3 Register SQL for this element
-- ---------------------------------
l_sql :=
'SELECT
to_timestamp_tz(md.key_value, ''YYYY-MM-DD HH24:MI:SS TZR'') 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.key_value>= to_char(CAST(??EMIP_BIND_START_DATE?? AS TIMESTAMP) AT TIME ZONE ''GMT'', ''YYYY-MM-DD HH24:MI:SS TZD'') AND
md.key_value <= to_char(CAST(??EMIP_BIND_END_DATE?? AS TIMESTAMP) AT TIME ZONE ''GMT'', ''YYYY-MM-DD HH24:MI:SS TZD'')
GROUP BY md.key_value';
mgmt_ip.register_sql_statement (
p_version => '10.2',
p_name => 'oracle.sysman.db.security.audit.reports.groupDailyFailedLoginCount',
p_sql_statement => l_sql);
-- ---------------------------------
-- 1.3 Add this Element To the Report
-- ---------------------------------
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');
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'DBMSG_GROUP_USER_TABLE_FROM_SQL_ELEM',
p_element_type_nlsid => 'DBMSG_GROUP_TARGET_TYPE',
p_header_nlsid => 'DBMSG_GROUP_DAILY_FAILED_LOGINS_TABLE',
p_element_order => 3,
p_element_row => 3,
p_parameters => l_param_values,
p_targets => null);
-- ----------------------------------------------------------------
-- Element 1.4: Historical Group Failed Login Count( bar chart)
--
-- Data Scope: Repository
-- Chart Type: Chart
-- Agent Version: All
--
-- Description: Displays a bar chart of historical failed logins
-- ----------------------------------------------------------------
-- ---------------------------------
-- 1.4 Register SQL for this element
-- ---------------------------------
l_sql :=
'SELECT db.group_name,
to_timestamp_tz(md.key_value, ''YYYY-MM-DD HH24:MI:SS TZR'') 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.key_value>= to_char(CAST(??EMIP_BIND_START_DATE?? AS TIMESTAMP) AT TIME ZONE ''GMT'', ''YYYY-MM-DD HH24:MI:SS TZD'') AND
md.key_value <= to_char(CAST(??EMIP_BIND_END_DATE?? AS TIMESTAMP) AT TIME ZONE ''GMT'', ''YYYY-MM-DD HH24:MI:SS TZD'')
GROUP BY db.group_name, md.key_value';
mgmt_ip.register_sql_statement (
p_version => '10.2',
p_name => 'oracle.sysman.db.security.audit.reports.groupDailyFailedLoginCountForChart',
p_sql_statement => l_sql);
-- ---------------------------------
-- 1.4 Add this Element To the Report
-- ---------------------------------
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');
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'DBMSG_USER_CHART_FROM_SQL_ELEM',
p_element_type_nlsid => 'DBMSG_DBINSTANCE_RAC_TARGET_TYPE',
p_header_nlsid => 'DBMSG_GROUP_DAILY_FAILED_LOGINS_CHART',
p_element_order => 4,
p_element_row => 3,
p_parameters => l_param_values,
p_targets => null);
-- ----------------------------------------------------------------
-- Element 1.5: Top five databases by failed login count
--
-- Data Scope: Repository
-- Chart Type: Table
-- Agent Version: All
--
-- Description: Displays a table of the top five databases in the group
-- with the failed login counts for each database
-- ----------------------------------------------------------------
-- ---------------------------------
-- 1.5 Register SQL for this element
-- ---------------------------------
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.key_value>= to_char(CAST(??EMIP_BIND_START_DATE?? AS TIMESTAMP) AT TIME ZONE ''GMT'', ''YYYY-MM-DD HH24:MI:SS TZD'') AND
md.key_value <= to_char(CAST(??EMIP_BIND_END_DATE?? AS TIMESTAMP) AT TIME ZONE ''GMT'', ''YYYY-MM-DD HH24:MI:SS TZD'')
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);
-- ---------------------------------
-- 1.5 Add this Element To the 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.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');
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'DBMSG_GROUP_USER_TABLE_FROM_SQL_ELEM',
p_element_type_nlsid => 'DBMSG_GROUP_TARGET_TYPE',
p_header_nlsid => 'DBMSG_GROUP_HISTORICAL_TOP_5_FAILED_LOGIN_BY_DB_DESC',
p_element_order => 5,
p_element_row => 4,
p_parameters => l_param_values,
p_targets => null);
-- ----------------------------------------------------------------
-- Element 1.6: Top 5 Databases failed login trend ( bar chart)
--
-- Data Scope: Repository
-- Chart Type: Chart
-- Agent Version: All
--
-- Description: Displays a bar chart of the top 5 databases failed login
-- trend.
-- ----------------------------------------------------------------
-- ---------------------------------
-- 1.6 Register SQL for this element
-- ---------------------------------
l_sql :=
'SELECT
m.target_name AS DB_NAME,
to_timestamp_tz(key_value, ''YYYY-MM-DD HH24:MI:SS TZR'') 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.key_value>= to_char(CAST(??EMIP_BIND_START_DATE?? AS TIMESTAMP) AT TIME ZONE ''GMT'', ''YYYY-MM-DD HH24:MI:SS TZD'') AND
md.key_value <= to_char(CAST(??EMIP_BIND_END_DATE?? AS TIMESTAMP) AT TIME ZONE ''GMT'', ''YYYY-MM-DD HH24:MI:SS TZD'')
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.key_value>= to_char(CAST(??EMIP_BIND_START_DATE?? AS TIMESTAMP) AT TIME ZONE ''GMT'', ''YYYY-MM-DD HH24:MI:SS TZD'') AND
m.key_value <= to_char(CAST(??EMIP_BIND_END_DATE?? AS TIMESTAMP) AT TIME ZONE ''GMT'', ''YYYY-MM-DD HH24:MI:SS TZD'')';
-- ---------------------------------
-- 1.6 Add this Element To the Report
-- ---------------------------------
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');
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'DBMSG_USER_CHART_FROM_SQL_ELEM',
p_element_type_nlsid => 'DBMSG_DBINSTANCE_RAC_TARGET_TYPE',
p_header_nlsid => 'DBMSG_GROUP_HISTORICAL_TOP_5_FAILED_LOGIN_BY_DB_CHART',
p_element_order => 6,
p_element_row => 4,
p_parameters => l_param_values,
p_targets => null);
COMMIT;
END;
END;
/
SET DEFINE ON