Rem drv: Rem Rem $Header: esm_db_reports.sql 13-aug-2006.20:53:48 bmallipe Exp $ Rem Rem esm_db_reports.sql Rem Rem Copyright (c) 2005, 2006, 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 bmallipe 08/13/06 - adding the CENTRAL condition Rem rmadampa 06/22/06 - fix bug 5352574 - upgrade issue after create Rem like with same named report,removing hardcoded SYSMAN Rem and replaced with mgmt_user.get_repository_owner Rem dkjain 08/15/05 - Removing extra null entry from Rem MGMT_IP_TARGET_TYPES array Rem chyu 07/20/05 - modifying the type to post_creation Rem dsukhwal 07/18/05 - add windows NT report Rem gsbhatia 07/11/05 - New repmgr header impl Rem dsukhwal 07/01/05 - complete path reports Rem lgloyd 06/24/05 - normalize category strings Rem dsukhwal 06/20/05 - UI exit review fixes Rem dkjain 05/17/05 - Fixed UI exit review bugs Rem dkjain 03/30/05 - Consolidating relevant reports Rem dsukhwal 03/02/05 - Use views for new reports Rem dkjain 02/17/05 - use renamed mgmt$ views Rem dkjain 02/17/05 - use renamed mgmt$ views Rem dsukhwal 02/01/05 - dsukhwal_reports2 Rem dsukhwal 01/24/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 -----------POWERFUL PRIVILEGES(COMPLETE PRIVILEGE PATH) START---------------- --------------------POWERFUL PRIVILEGES START------------------------------------------------------ l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'Power_Priv_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'T_Dir_Priv_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'All_Priv_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 =>mgmt_user.get_repository_owner, 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(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'Pub_Exe_Priv_Sys_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'Users_With_Conn_Res_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'Tablespace_Owners_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'Users_Having_With_Grant_In_Priv_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'Members_Dba_Grp_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'Ownership_Ora_Home_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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 = ''''''||tgt_guid_in||'''''' and (select property_value as OS from MGMT$TARGET_PROPERTIES where target_guid = (select target_guid from mgmt$target where emd_url = (select EMD_URL from MGMT$TARGET where target_guid = ''''''||tgt_guid_in||'''''') AND target_type = ''''oracle_emd'''') AND property_name = ''''OS'''') in (''''Linux'''' , ''''SunOs'''') ''; OPEN result_cursor_out for query_string ; 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 = ''''''||tgt_guid_in||'''''' AND (select property_value as OS from MGMT$TARGET_PROPERTIES where target_guid = (select target_guid from mgmt$target where emd_url = (select EMD_URL from MGMT$TARGET where target_guid = ''''''||tgt_guid_in||'''''') AND target_type = ''''oracle_emd'''') AND property_name = ''''OS'''') in (''''SunOs'''' , ''''Linux'''') ''; OPEN result_cursor_out for query_string; 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 = ''''''||tgt_guid_in||'''''' AND (select property_value as OS from MGMT$TARGET_PROPERTIES where target_guid = (select target_guid from mgmt$target where emd_url = (select EMD_URL from MGMT$TARGET where target_guid = ''''''||tgt_guid_in||'''''') AND target_type = ''''oracle_emd'''') AND property_name = ''''OS'''') in (''''Linux'''', ''''SunOs'''') ''; OPEN result_cursor_out for query_string; 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------------------------------------------------------ --------------------OWNERSHIP OF ORACLE_HOME START(WINDOWS NT)--------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'Owner_Ora_Home_NT_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'Owner_Ora_Home_NT_Title_NLSID', p_description_nlsid => 'Ownership_Ora_Home_Desc_NLSID', p_owner => mgmt_user.get_repository_owner, 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 = ''''''||tgt_guid_in||'''''' and ''''NT'''' = (select property_value as OS from MGMT$TARGET_PROPERTIES where target_guid = (select target_guid from mgmt$target where emd_url = (select EMD_URL from MGMT$TARGET where target_guid = ''''''||tgt_guid_in||'''''') AND target_type = ''''oracle_emd'''') AND property_name = ''''OS'''') ''; OPEN result_cursor_out for query_string; 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------------------------------------------------------ -- 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 = ''''''||tgt_guid_in||'''''' AND ''''NT'''' = (select property_value as OS from MGMT$TARGET_PROPERTIES where target_guid = (select target_guid from mgmt$target where emd_url = (select EMD_URL from MGMT$TARGET where target_guid = ''''''||tgt_guid_in||'''''') AND target_type = ''''oracle_emd'''') AND property_name = ''''OS'''') ''; OPEN result_cursor_out for query_string; 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(WINDOWS NT) END------------- --------------------PUBLIC PRIVILEGES START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'Public_Permissions_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'Key_Objects_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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 mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'ALL_Power_Priv_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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 mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'A_Dir_Priv_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'ALL_All_Priv_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'ALL_Pub_Exe_Priv_Sys_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'ALL_Users_With_Conn_Res_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'ALL_Users_Having_With_Grant_In_Priv_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'ALL_Members_Dba_Grp_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'ALL_Ownership_Ora_Home_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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 rep WHERE (select property_value as OS from MGMT$TARGET_PROPERTIES where target_guid = (select target_guid from mgmt$target where emd_url = (select EMD_URL from MGMT$TARGET where target_guid = rep.target_guid) AND target_type = ''oracle_emd'') AND property_name = ''OS'') in (''Linux'', ''SunOs'') '); 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 rep WHERE (select property_value as OS from MGMT$TARGET_PROPERTIES where target_guid = (select target_guid from mgmt$target where emd_url = (select EMD_URL from MGMT$TARGET where target_guid = rep.target_guid) AND target_type = ''oracle_emd'') AND property_name = ''OS'') in (''Linux'', ''SunOs'') '); 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 rep WHERE (select property_value as OS from MGMT$TARGET_PROPERTIES where target_guid = (select target_guid from mgmt$target where emd_url = (select EMD_URL from MGMT$TARGET where target_guid = rep.target_guid) AND target_type = ''oracle_emd'') AND property_name = ''OS'') in (''Linux'', ''SunOs'') '); 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--------------------------------------------------- --------------------OWNERSHIP OF ORACLE_HOME (Windows NT) START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'ALL_Owner_Ora_Home_NT_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN NULL; END; l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'ALL_Owner_Ora_Home_NT_Title_NLSID', p_description_nlsid => 'ALL_Ownership_Ora_Home_Desc_NLSID', p_owner => mgmt_user.get_repository_owner, 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 rep WHERE ''NT'' = (select property_value as OS from MGMT$TARGET_PROPERTIES where target_guid = (select target_guid from mgmt$target where emd_url = (select EMD_URL from MGMT$TARGET where target_guid = rep.target_guid) AND target_type = ''oracle_emd'') AND property_name = ''OS'') '); 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 PURPOSE_ID, principal as FILE_ID, permission as PERMISSION_ID FROM MGMT$ESA_TRC_AUD_PERM_REP_NT'); 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(Windows NT) END-------------------------- --------------------PUBLIC PERMISSIONS START------------------------------------------------- l_target_types := MGMT_IP_TARGET_TYPES(1); l_target_types(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'ALL_Public_Permissions_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'ALL_Tablespace_Owners_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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(1) := 'oracle_database'; /*Delete earlier report of this name if any*/ BEGIN mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,'ALL_Key_Objects_Title_NLSID',1,0); EXCEPTION WHEN OTHERS THEN 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 => mgmt_user.get_repository_owner, 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;