Rem drv: <create type="post_creation" pos="reports/overview/overview_outofbox.sql+" condition="EM_REPOS_MODE=CENTRAL"/>
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 - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem <short description of component this file declares/defines>
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
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;