Rem drv:
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 -
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/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