Rem drv: Rem Rem $Header: esm_db_reports.sql 12-jul-2005.11:56:16 chyu Exp $ Rem Rem esm_db_reports.sql Rem Rem Copyright (c) 2005, Oracle. All rights reserved. Rem Rem NAME Rem esm_db_reports.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem chyu 07/12/05 - adding the new rep manager upgrade header Rem dsukhwal 07/11/05 - complete path reports Rem lgloyd 06/24/05 - normalize category strings Rem dsukhwal 06/22/05 - UI exit review fixes Rem dkjain 05/09/05 - dkjain_bug-4340963_main Rem dkjain 04/26/05 - Created Rem --------------------POWERFUL PRIVILEGES START------------------------------------------------------ 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_order NUMBER; l_target VARCHAR(256); BEGIN l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; -----------POWERFUL PRIVILEGES(COMPLETE PRIVILEGE PATH) START---------------- --------------------POWERFUL PRIVILEGES START------------------------------------------------------ l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'Power_Priv_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'Power_Priv_Title_NLSID', p_description_nlsid => 'Power_Priv_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'POW_PRIV_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT DISTINCT r.principal as USER_OR_ROLE FROM mgmt$esa_power_priv_report r,mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Powerful_Privileges_Header_NLSID', p_element_order => 9, p_element_row => 5, p_parameters => l_param_values, p_targets => null ); 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.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; /*query_string := ''SELECT * FROM (SELECT object_name AS "Privilege", COUNT(principal) as "Number of users/Roles" FROM mgmt$esa_power_priv_report WHERE target_guid = :1 GROUP BY object_name ORDER BY "Number of users/Roles" desc) WHERE rownum < 16 '';*/ query_string := ''SELECT * FROM (SELECT object_name AS Privilege, COUNT(principal) as "Number of Users" FROM mgmt$esa_power_priv_report WHERE target_guid = :1 GROUP BY object_name ORDER BY "Number of Users" DESC) WHERE rownum < 16 ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '2'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Powerful_Privileges_Header_Chart_NLSID', p_element_order => 10, p_element_row => 5, p_parameters => l_param_values, p_targets => null ); -- Table element for CREATE PRIV ----- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT DISTINCT r.principal as USER_OR_ROLE FROM MGMT$ESA_CREATE_PRIV_REPORT r,mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Users_With_Create_Priv_Title_NLSID', p_element_order => 3, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); 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.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT * FROM (SELECT object_name AS Privilege, COUNT(principal) as "Number of Users" FROM MGMT$ESA_CREATE_PRIV_REPORT WHERE target_guid = :1 GROUP BY object_name ORDER BY "Number of Users" DESC) WHERE rownum < 16 ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '2'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def (--Too many values, ditch chart for now p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Users_With_Create_Priv_Title_Chart_NLSID', p_element_order => 4, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); -- Table element for ANY DIC PRIV --- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; /*query_string := ''SELECT r.principal AS USER_OR_ROLE, r.object_name AS PRIV_OR_ROLE FROM mgmt$esa_any_dict_report r,mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid '';*/ query_string := ''SELECT DISTINCT r.principal as USER_OR_ROLE FROM mgmt$esa_any_dict_report r,mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Users_With_Access_To_Any_Dict_Header_NLSID', p_element_order => 5, p_element_row => 3, p_parameters => l_param_values, p_targets => null ); 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.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT * FROM (SELECT object_name AS Privilege, COUNT(principal) as "Number of Users" FROM mgmt$esa_any_dict_report WHERE target_guid = :1 GROUP BY object_name ORDER BY "Number of Users" DESC) WHERE rownum < 16''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '2'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def (--Too many values, ditch chart for now p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Users_With_Access_To_Any_Dict_Header_Chart_NLSID', p_element_order => 6, p_element_row => 3, p_parameters => l_param_values, p_targets => null ); --- Table element for ANY PRIV---- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; /*query_string := ''SELECT r.principal as USER_OR_ROLE, r.object_name AS PRIV_OR_ROLE FROM mgmt$esa_any_priv_report r,mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid '';*/ query_string := ''SELECT DISTINCT r.principal as USER_OR_ROLE FROM mgmt$esa_any_priv_report r,mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Any_In_Priv_Header_NLSID', p_element_order => 7, p_element_row => 4, p_parameters => l_param_values, p_targets => null ); 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.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT * FROM (SELECT object_name AS "Privilege", COUNT(principal) as "Number of users" FROM mgmt$esa_any_priv_report WHERE target_guid = :1 GROUP BY object_name ORDER BY "Number of users" DESC) WHERE rownum < 16''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '2'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Any_In_Priv_Header_Chart_NLSID', p_element_order => 8, p_element_row => 4, p_parameters => l_param_values, p_targets => null ); COMMIT ; ------------------POWERFUL PRIVILEGES(COMPLETE PRIVILEGE PATH) END------------ --- ---------------------- Direct Priv Report ------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'T_Dir_Priv_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'T_Dir_Priv_Title_NLSID', p_description_nlsid => 'T_Dir_Priv_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'DIR_PRIV_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- -- Table element for DIRECT PRIV -- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT r.principal AS USER_ID, object_name AS PRIVILEGE_ID FROM mgmt$esa_direct_priv_report r,mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Users_With_Direct_Priv_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); 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.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT * FROM (SELECT object_name AS Privilege, COUNT(principal) as "Number of Users" FROM mgmt$esa_direct_priv_report WHERE target_guid = :1 GROUP BY object_name order by "Number of Users" desc) WHERE rownum < 16 ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '2'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Users_With_Direct_Priv_Header_Chart_NLSID', p_element_order => 3, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); COMMIT ; ---------------------------------------- Diirect Priv End ---------------- --------------------ALL PRIVILEGES START------------------------------------------------------ l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'All_Priv_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid =>'All_Priv_Title_NLSID', p_description_nlsid =>'All_Priv_Desc_NLSID', p_owner =>'SYSMAN', p_category_nlsid =>'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid =>'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types =>l_target_types, p_late_binding_multi_targets =>0, p_show_table_of_contents =>0, p_system_report =>1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'ALL_PRIV_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT distinct r.principal as USER_OR_ROLE FROM mgmt$esa_all_privs_report r,mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'All_Priv_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); -- Table element for ADUIT SYSTEM ---- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT r.principal AS USER_OR_ROLE FROM mgmt$esa_audit_system_report r,mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Users_With_Audit_System_Priv_Title_NLSID', p_element_order => 3, p_element_row => 3, p_parameters => l_param_values, p_targets => null ); -- Table element for BECOME USER --- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT distinct r.principal AS USER_OR_ROLE FROM mgmt$esa_become_user_report r,mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Users_With_Become_User_Priv_Title_NLSID', p_element_order => 4, p_element_row => 4, p_parameters => l_param_values, p_targets => null ); -- Table element for EXEMPT ACCESS POLICY l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT r.principal AS USER_OR_ROLE FROM mgmt$esa_exmpt_access_report r,mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Users_With_Exempt_Access_Policy_Title_NLSID', p_element_order => 5, p_element_row => 5, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------ALL PRIVILEGES END------------------------------------------------------ --------------------PUBLIC EXECUTE PRIVILEGE OF PACKAGES START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'Pub_Exe_Priv_Sys_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'Pub_Exe_Priv_Sys_Title_NLSID', p_description_nlsid => 'Pub_Exe_Priv_Sys_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'PUB_EXE_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT r.object_name AS PACKAGE_ID FROM mgmt$esa_sys_pub_pkg_report r,mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', -- p_header_nlsid => 'Pub_Exe_Priv_Sys_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------PUBLIC EXECUTE PRIVILEGE OF PACKAGES END------------------------------------------------------ --------------------USERS WITH CONNECT/RESOURCE PRIVILEGE START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'Users_With_Conn_Res_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'Users_With_Conn_Res_Title_NLSID', p_description_nlsid => 'Users_With_Conn_Res_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'CONN_RES_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- -- Table element for CONNECT/RESOURCE ---- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT r.principal as USER_OR_ROLE FROM mgmt$esa_conn_priv_report r, mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Users_With_Conn_Res_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); 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.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT * FROM (SELECT object_name AS "Privilege", COUNT(principal) as "Number of users" FROM mgmt$esa_conn_priv_report WHERE target_guid = :1 GROUP BY object_name ORDER BY "Number of users" DESC) WHERE rownum < 16''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '2'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Users_With_Conn_Res_Header_Chart_NLSID', p_element_order => 3, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); -- Table element for CATALOG ---- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT r.principal AS USER_OR_ROLE FROM mgmt$esa_catalog_report r,mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Users_With_Role_Like_Catalog_Title_NLSID', p_element_order => 4, p_element_row => 3, p_parameters => l_param_values, p_targets => null ); 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.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT * FROM (SELECT object_name AS Privilege, COUNT(principal) as "Number of Users" FROM mgmt$esa_catalog_report WHERE target_guid = :1 GROUP BY object_name ORDER BY "Number of Users" DESC) WHERE rownum < 16''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '2'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def (--Too many values, ditch chart for now p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Users_With_Role_Like_Catalog_Title_Chart_NLSID', p_element_order => 5, p_element_row => 3, p_parameters => l_param_values, p_targets => null ); -- Table element for DBA role ------ l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT r.principal AS USER_OR_ROLE FROM mgmt$esa_dba_role_report r,mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Users_With_Dba_Role_Title_NLSID', p_element_order => 6, p_element_row => 4, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------USERS WITH CONNECT/RESOURCE AND ALIKE CATALOG PRIVILEGE END------------------------------------------------------ --------------------TABLESPACE OWNERS START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'Tablespace_Owners_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'Tablespace_Owners_Title_NLSID', p_description_nlsid => 'Tablespace_Owners_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'TABSP_OWN_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT r.principal AS TABSP_ID, r.object_name AS USER_ID FROM mgmt$esa_tabsp_owners_report r,mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', -- p_header_nlsid => 'Tablespace_Owners_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------TABLESPACE OWNERS END------------------------------------------------------ --------------------USERS HAVING WITH GRANT IN PRIVILEGE START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'Users_Having_With_Grant_In_Priv_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'Users_Having_With_Grant_In_Priv_Title_NLSID', p_description_nlsid => 'Users_Having_With_Grant_In_Priv_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'WITH_GRANT_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT r.principal AS USER_OR_ROLE, object_name AS PRIV_OR_ROLE FROM mgmt$esa_with_grant_report r,mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'With_Grant_Priv_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); 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.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT * FROM (SELECT object_name AS Privilege, COUNT(principal) as "Number of Users" FROM mgmt$esa_with_grant_report WHERE target_guid = :1 GROUP BY object_name order by "Number of Users" desc) WHERE rownum < 16 ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'With_Grant_Priv_Header_Chart_NLSID', p_element_order => 3, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); -- Table element for WITH ADMIN ---- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT r.principal AS USER_OR_ROLE, r.object_name AS PRIV_OR_ROLE FROM mgmt$esa_with_admin_report r,mgmt$target t WHERE r.target_guid = :1 AND t.target_guid = r.target_guid ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Users_Having_With_Admin_In_Priv_Header_NLSID', p_element_order => 4, p_element_row => 3, p_parameters => l_param_values, p_targets => null ); 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.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT * FROM (SELECT object_name AS Privilege, COUNT(principal) as "Number of Users" FROM mgmt$esa_with_admin_report WHERE target_guid = :1 GROUP BY object_name order by "Number of Users" desc) WHERE rownum < 16 ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Users_Having_With_Admin_In_Priv_Header_Chart_NLSID', p_element_order => 5, p_element_row => 3, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------USERS HAVING WITH GRANT AND WITH ADMIN IN PRIVILEGE END------------------------------------------------------ --------------------MEMBERS OF DBA GROUP START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'Members_Dba_Grp_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'Members_Dba_Grp_Title_NLSID', p_description_nlsid => 'Members_Dba_Grp_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'DBA_GRP_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT r.principal AS OS_USER_ID FROM mgmt$esa_dba_group_report r, mgmt$target t WHERE r.target_guid = t.target_guid and r.target_guid = :1 ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', -- p_header_nlsid => 'Members_Dba_Grp_Title_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------MEMBERS OF DBA GROUP END------------------------------------------------------ --------------------OWNERSHIP OF ORACLE_HOME START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'Ownership_Ora_Home_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'Ownership_Ora_Home_Title_NLSID', p_description_nlsid => 'Ownership_Ora_Home_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'ORAHOME_OWN_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT principal AS FILE_ID ,object_name AS OWNER_ID FROM MGMT$ESA_OH_OWNERSHIP_REPORT WHERE target_guid = :1 ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Own_Ora_Home_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); --------------------OWNERSHIP OF ORACLE_HOME END------------------------------------------------------ l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT principal AS FILE_ID, object_name AS PERMISSION_ID FROM MGMT$ESA_OH_PERMISSION_REPORT WHERE target_guid = :1 ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Permission_Ora_Home_Title_NLSID', p_element_order => 3, p_element_row => 3, p_parameters => l_param_values, p_targets => null ); --------------------PERMISSION OF ORACLE_HOME END------------------------------------------------------ -- Audit and trace files permissions ------ l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT object_name as PURPOSE_ID, principal as FILE_ID, permission as PERMISSION_ID FROM MGMT$ESA_TRC_AUD_PERM_REPORT WHERE target_guid = :1 and rownum < 15''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Permission_Trc_Aud_Files_Title_NLSID', p_element_order => 4, p_element_row => 4, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------PERMISSIONS OF TRACE/AUDIT FILES END------------------------------------------------------ --------------------PUBLIC PRIVILEGES START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'Public_Permissions_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'Public_Permissions_Title_NLSID', p_description_nlsid => 'Public_Permissions_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'PUB_PERM_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- 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.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT object_name as "Privilege", count(object_name) AS "Number of Objects" FROM mgmt$esa_pub_priv_report r,mgmt$target t WHERE r.target_guid = :1 AND r.target_guid = t.target_guid GROUP BY object_name''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Public_Permissions_Header_NLSID', p_element_order => 3, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT r.principal as OBJECT_ID, r.object_name as PRIVILEGE_ID FROM mgmt$esa_pub_priv_report r, mgmt$target t where r.target_guid = t.target_guid and r.target_guid = :1 ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Public_Permissions_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------PUBLIC PERMISSIONS END------------------------------------------------------ --------------------KEY OBJECTS START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'Key_Objects_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'Key_Objects_Title_NLSID', p_description_nlsid => 'Key_Objects_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'KEY_OBJ_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- 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.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT "object_name" as "Object Name" , count(distinct "user") AS "Number of Users/Roles" FROM mgmt$esa_key_objects_report r,mgmt$target t WHERE r.target_guid = :1 AND r.target_guid = t.target_guid GROUP BY "object_name" ORDER BY "Number of Users/Roles" desc ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '2'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Key_Objects_Header_NLSID', p_element_order => 3, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; tgt_guid_in RAW(16); start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; tgt_guid_in := ??EMIP_BIND_TARGET_GUID??; query_string := ''SELECT distinct "user" as USER_OR_ROLE, "object_name" as OBJECT_NAME_ID, "privilege" as PRIVILEGE_ID FROM mgmt$esa_key_objects_report r, mgmt$target t where r.target_guid = t.target_guid and r.target_guid = :1 ''; OPEN result_cursor_out for query_string using tgt_guid_in; END; END;'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement.bindSingleTarget', 'true'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Key_Objects_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------KEY OBJECTS END------------------------------------------------------ END; / COMMIT; -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- ------ ALL TARGETS START --------------------------------------------------------------- -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------- --------------------POWERFUL PRIVILEGES START------------------------------------------------- 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_order NUMBER; l_target VARCHAR(256); BEGIN /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'ALL_Power_Priv_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'ALL_Power_Priv_Title_NLSID', p_description_nlsid => 'ALL_Power_Priv_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => null, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'ALL_POW_PRIV_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- 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.sqlStatement', 'SELECT principal AS USER_OR_ROLE, target_name AS DB_TGT_NAME FROM mgmt$esa_power_priv_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'ALL_Powerful_Privileges_Header_NLSID', p_element_order => 5, p_element_row => 5, p_parameters => l_param_values, p_targets => null ); -- Table element for CREATE PRIV --- 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.sqlStatement', 'SELECT principal as USER_OR_ROLE, target_name AS DB_TGT_NAME FROM mgmt$esa_create_priv_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Create_Priv_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); -- Table element for ANY DIC --- 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.sqlStatement', 'SELECT principal as USER_OR_ROLE, target_name AS DB_TGT_NAME FROM mgmt$esa_any_dict_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Any_Dict_Header_NLSID', p_element_order => 3, p_element_row => 3, p_parameters => l_param_values, p_targets => null ); -- Table element for ANY in PRIV 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.sqlStatement', 'SELECT principal AS USER_OR_ROLE, target_name AS DB_TGT_NAME FROM mgmt$esa_any_priv_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'ALL_Any_In_Priv_Header_NLSID', p_element_order => 4, p_element_row => 4, p_parameters => l_param_values, p_targets => null ); COMMIT; --- ---------------------- Direct Priv Report ------------------------------- /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'A_Dir_Priv_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'A_Dir_Priv_Title_NLSID', p_description_nlsid => 'A_Dir_Priv_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'ALL_DIR_PRIV_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- -- Table element for DIRECT PRIV --- 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.sqlStatement', 'SELECT principal as USER_ID, object_name AS PRIVILEGE_ID, target_name AS DB_TGT_NAME FROM mgmt$esa_direct_priv_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', -- p_header_nlsid => 'Direct_Priv_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------POWERFUL PRIVILEGES END------------------------------------------------------ --------------------ALL PRIVILEGES START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'ALL_All_Priv_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'ALL_All_Priv_Title_NLSID', p_description_nlsid => 'ALL_All_Priv_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => null, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'ALL_ALL_PRIV_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- 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.sqlStatement', 'SELECT principal AS USER_OR_ROLE, target_name AS DB_TGT_NAME FROM mgmt$esa_all_privs_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'All_Privileges_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); -- Table element for AUDIT SYS --- 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.sqlStatement', 'SELECT principal as USER_OR_ROLE , target_name AS DB_TGT_NAME FROM mgmt$esa_audit_system_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Aud_Sys_Priv_Header_NLSID', p_element_order => 3, p_element_row => 3, p_parameters => l_param_values, p_targets => null ); -- Table element for EXEMPT ACCESS --- 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.sqlStatement', 'SELECT principal as USER_OR_ROLE, target_name AS DB_TGT_NAME FROM mgmt$esa_exmpt_access_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Exempt_Access_Policy_Header_NLSID', p_element_order => 4, p_element_row => 4, p_parameters => l_param_values, p_targets => null ); -- Table element for BECOME USER --- 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.sqlStatement', 'SELECT principal as USER_OR_ROLE, target_name AS DB_TGT_NAME FROM mgmt$esa_become_user_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Bec_User_Priv_Header_NLSID', p_element_order => 5, p_element_row => 5, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------ALL PRIVILEGES END------------------------------------------------------ --------------------PUBLIC EXECUTE PRIVILEGE OF PACKAGES START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'ALL_Pub_Exe_Priv_Sys_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'ALL_Pub_Exe_Priv_Sys_Title_NLSID', p_description_nlsid => 'ALL_Pub_Exe_Priv_Sys_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => null, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'ALL_PUB_EXE_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- 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.sqlStatement', 'SELECT object_name AS PACKAGE_ID, target_name AS DB_TGT_NAME FROM mgmt$esa_sys_pub_pkg_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', -- p_header_nlsid => 'Pub_Exe_Priv_Sys_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------PUBLIC EXECUTE PRIVILEGE OF PACKAGES END------------------------------------------------------ --------------------USERS WITH CONNECT/RESOURCE PRIVILEGE START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'ALL_Users_With_Conn_Res_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'ALL_Users_With_Conn_Res_Title_NLSID', p_description_nlsid => 'ALL_Users_With_Conn_Res_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => null, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'ALL_CONN_RES_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- 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.sqlStatement', 'SELECT principal as USER_OR_ROLE, target_name AS DB_TGT_NAME FROM mgmt$esa_conn_priv_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Conn_Res_Priv_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); -- Table element for CATA LOG --- 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.sqlStatement', 'SELECT principal as USER_OR_ROLE, target_name AS DB_TGT_NAME FROM mgmt$esa_catalog_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Catalog_Role_Header_NLSID', p_element_order => 3, p_element_row => 3, p_parameters => l_param_values, p_targets => null ); -- Table element for DBA ROLE -- 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.sqlStatement', 'SELECT principal as USER_OR_ROLE, target_name AS DB_TGT_NAME FROM mgmt$esa_dba_role_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Dba_Role_Header_NLSID', p_element_order => 4, p_element_row => 4, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------USERS WITH CONNECT/RESOURCE PRIVILEGE END------------------------------------------------------ --------------------USERS HAVING WITH GRANT IN PRIVILEGE START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'ALL_Users_Having_With_Grant_In_Priv_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'ALL_Users_Having_With_Grant_In_Priv_Title_NLSID', p_description_nlsid => 'ALL_Users_Having_With_Grant_In_Priv_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => null, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'ALL_WITH_GRANT_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- 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.sqlStatement', 'SELECT principal as USER_OR_ROLE, object_name AS PRIV_OR_ROLE, target_name AS DB_TGT_NAME FROM mgmt$esa_with_grant_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'With_Grant_Priv_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); 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.sqlStatement', 'SELECT principal as USER_OR_ROLE, object_name AS PRIV_OR_ROLE, target_name AS DB_TGT_NAME FROM mgmt$esa_with_admin_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'With_Admin_Priv_Header_NLSID', p_element_order => 3, p_element_row => 3, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------USERS HAVING WITH GRANT IN PRIVILEGE END--------------------------------------------------- --------------------MEMBERS OF DBA GROUP START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'ALL_Members_Dba_Grp_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'ALL_Members_Dba_Grp_Title_NLSID', p_description_nlsid => 'ALL_Members_Dba_Grp_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => null, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'ALL_DBA_GRP_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- 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.sqlStatement', 'SELECT principal as OS_USER_ID, target_name AS DB_TGT_NAME FROM mgmt$esa_dba_group_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', -- p_header_nlsid => 'Dba_Group_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------MEMBERS OF DBA GROUP END--------------------------------------------------- --------------------OWNERSHIP OF ORACLE_HOME START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'ALL_Ownership_Ora_Home_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'ALL_Ownership_Ora_Home_Title_NLSID', p_description_nlsid => 'ALL_Ownership_Ora_Home_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => null, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'ALL_ORAHOME_OWN_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- 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.sqlStatement', 'SELECT target_name as DB_TGT_NAME, object_name AS OWNER_ID, principal AS FILE_ID FROM MGMT$ESA_OH_OWNERSHIP_REPORT '); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '2'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Own_Ora_Home_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------OWNERSHIP OF ORACLE_HOME END--------------------------------------------------- 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.sqlStatement', 'SELECT target_name as DB_TGT_NAME , object_name AS PERMISSION_ID,principal AS FILE_ID FROM MGMT$ESA_OH_PERMISSION_REPORT '); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '2'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Perm_Ora_Home_Header_NLSID', p_element_order => 3, p_element_row => 3, p_parameters => l_param_values, p_targets => null ); --------------------PERMISSIONS OF ORACLE_HOME END--------------------------------------------------- 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.sqlStatement', 'SELECT target_name as DB_TGT_NAME, object_name as PURPOSE_ID, principal as FILE_ID, permission as PERMISSION_ID FROM MGMT$ESA_TRC_AUD_PERM_REPORT '); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '2'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Perm_Trc_Aud_Header_NLSID', p_element_order => 4, p_element_row => 4, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------PERMISSIONS OF TRACE/AUDIT FILES END--------------------------------------------------- --------------------PUBLIC PERMISSIONS START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'ALL_Public_Permissions_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'ALL_Public_Permissions_Title_NLSID', p_description_nlsid => 'ALL_Public_Permissions_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => null, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'ALL_PUB_PERM_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- 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.sqlStatement', 'SELECT principal as OBJECT_ID, object_name as PRIVILEGE_ID, target_name AS DB_TGT_NAME FROM mgmt$esa_pub_priv_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => 'Public_Perm_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------PUBLIC PERMISSIONS END--------------------------------------------------- --------------------TABLESPACE OWNERS START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'ALL_Tablespace_Owners_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'ALL_Tablespace_Owners_Title_NLSID', p_description_nlsid => 'ALL_Tablespace_Owners_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => null, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'ALL_TABSP_OWN_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- 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.sqlStatement', 'SELECT principal as TABSP_ID, object_name as OWNER_ID, target_name AS DB_TGT_NAME FROM mgmt$esa_tabsp_owners_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '0'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', -- p_header_nlsid => 'Tabsp_Owners_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------TABLESPACE OWNERS END--------------------------------------------------- --------------------KEY OBJECTS START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types.extend(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN SELECT REPORT_GUID INTO l_report_guid FROM MGMT_IP_REPORT_DEF WHERE title_nlsid = 'ALL_Key_Objects_Title_NLSID'; mgmt_ip.delete_report(l_report_guid,1); EXCEPTION WHEN NO_DATA_FOUND THEN l_report_guid := NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'ALL_Key_Objects_Title_NLSID', p_description_nlsid => 'ALL_Key_Objects_Desc_NLSID', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_SECURITY_CATEGORY', p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY', p_late_binding_target_types => null, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1 ); --instruction text begin-------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); --The text to be displayed l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'ALL_KEY_OBJ_INSTR_TEXT'); --The resource bundle containing the text l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.esa.EsaMsg'); --Specifies the style class l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass', 'OraInstructionText'); --Adding instruction text to the created report definition and passing the above parameters 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_element_order => 1, p_element_row => 1, p_parameters => l_param_values, p_targets => null); --instruction text end-------------------------------- 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.sqlStatement', 'SELECT DISTINCT "user" as USER_OR_ROLE, "object_name" as OBJECT_NAME_ID, "privilege" as PRIVILEGE_ID, target_name AS DB_TGT_NAME from mgmt$esa_key_objects_report'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '25'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.esa.EsaMsg');--was OOTB l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '1'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'descending'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', -- p_header_nlsid => 'Key_Objects_Header_NLSID', p_element_order => 2, p_element_row => 2, p_parameters => l_param_values, p_targets => null ); COMMIT; --------------------KEY OBJECTS END--------------------------------------------------- END; / COMMIT;