Rem drv:
Rem
Rem $Header: esm_db_reports.sql 02-aug-2005.11:56:40 dsukhwal Exp $
Rem
Rem esm_db_reports.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.
Rem
Rem NAME
Rem esm_db_reports.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem dsukhwal 08/02/05 - dsukhwal_inf_upg
Rem dsukhwal 07/18/05 - add windows NT report
DECLARE
l_target_types MGMT_IP_TARGET_TYPES;
l_param_values MGMT_IP_PARAM_VALUE_LIST;
l_report_guid RAW(16);
l_element_guid RAW(16);
BEGIN
l_target_types := MGMT_IP_TARGET_TYPES(1);
l_target_types.extend(1);
l_target_types(1) := 'oracle_database';
--------------------OWNERSHIP OF ORACLE_HOME START-------------------------------------------------
l_target_types := MGMT_IP_TARGET_TYPES(1);
l_target_types.extend(1);
l_target_types(1) := 'oracle_database';
/*Delete earlier report of this name if any*/
BEGIN
SELECT REPORT_GUID
INTO l_report_guid
FROM MGMT_IP_REPORT_DEF
WHERE title_nlsid = 'Ownership_Ora_Home_Title_NLSID';
mgmt_ip.delete_report(l_report_guid,1);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_report_guid := NULL;
END;
l_report_guid := mgmt_ip.create_report_definition (
p_title_nlsid => 'Ownership_Ora_Home_Title_NLSID',
p_description_nlsid => 'Ownership_Ora_Home_Desc_NLSID',
p_owner => 'SYSMAN',
p_category_nlsid => 'IPMSG_SECURITY_CATEGORY',
p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY',
p_late_binding_target_types => l_target_types,
p_late_binding_multi_targets => 0,
p_show_table_of_contents => 0,
p_system_report => 1
);
--instruction text begin--------------------------------
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(3);
--The text to be displayed
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage',
'ORAHOME_OWN_INSTR_TEXT');
--The resource bundle containing the text
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName',
'oracle.sysman.resources.eml.esa.EsaMsg');
--Specifies the style class
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass',
'OraInstructionText');
--Adding instruction text to the created report definition and passing the above parameters
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'IPMSG_STYLED_TEXT',
p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
p_element_order => 1,
p_element_row => 1,
p_parameters => l_param_values,
p_targets => null);
--instruction text end--------------------------------
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(7);
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
'true');
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement',
'BEGIN
DECLARE
TYPE CURSOR_TYPE IS REF CURSOR;
result_cursor_out CURSOR_TYPE;
tgt_guid_in RAW(16);
start_date_in DATE DEFAULT NULL;
end_date_in DATE DEFAULT NULL;
query_string VARCHAR(6000);
BEGIN
result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??;
tgt_guid_in := ??EMIP_BIND_TARGET_GUID??;
query_string := ''SELECT principal AS FILE_ID ,object_name AS OWNER_ID
FROM MGMT$ESA_OH_OWNERSHIP_REPORT
WHERE target_guid = ''''''||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.extend(1);
l_target_types(1) := 'oracle_database';
/*Delete earlier report of this name if any*/
BEGIN
SELECT REPORT_GUID
INTO l_report_guid
FROM MGMT_IP_REPORT_DEF
WHERE title_nlsid = 'Owner_Ora_Home_NT_Title_NLSID';
mgmt_ip.delete_report(l_report_guid,1);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_report_guid := NULL;
END;
l_report_guid := mgmt_ip.create_report_definition (
p_title_nlsid => 'Owner_Ora_Home_NT_Title_NLSID',
p_description_nlsid => 'Ownership_Ora_Home_Desc_NLSID',
p_owner => 'SYSMAN',
p_category_nlsid => 'IPMSG_SECURITY_CATEGORY',
p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY',
p_late_binding_target_types => l_target_types,
p_late_binding_multi_targets => 0,
p_show_table_of_contents => 0,
p_system_report => 1
);
--instruction text begin--------------------------------
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(3);
--The text to be displayed
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage',
'ORAHOME_OWN_INSTR_TEXT');
--The resource bundle containing the text
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName',
'oracle.sysman.resources.eml.esa.EsaMsg');
--Specifies the style class
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass',
'OraInstructionText');
--Adding instruction text to the created report definition and passing the above parameters
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'IPMSG_STYLED_TEXT',
p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
p_element_order => 1,
p_element_row => 1,
p_parameters => l_param_values,
p_targets => null);
--instruction text end--------------------------------
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(7);
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
'true');
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement',
'BEGIN
DECLARE
TYPE CURSOR_TYPE IS REF CURSOR;
result_cursor_out CURSOR_TYPE;
tgt_guid_in RAW(16);
start_date_in DATE DEFAULT NULL;
end_date_in DATE DEFAULT NULL;
query_string VARCHAR(6000);
BEGIN
result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??;
tgt_guid_in := ??EMIP_BIND_TARGET_GUID??;
query_string := ''SELECT principal AS FILE_ID ,object_name AS OWNER_ID
FROM MGMT$ESA_OH_OWNERSHIP_REPORT
WHERE target_guid = ''''''||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-------------
--------------------OWNERSHIP OF ORACLE_HOME START-------------------------------------------------
l_target_types := MGMT_IP_TARGET_TYPES(1);
l_target_types.extend(1);
l_target_types(1) := 'oracle_database';
/*Delete earlier report of this name if any*/
BEGIN
SELECT REPORT_GUID
INTO l_report_guid
FROM MGMT_IP_REPORT_DEF
WHERE title_nlsid = 'ALL_Ownership_Ora_Home_Title_NLSID';
mgmt_ip.delete_report(l_report_guid,1);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_report_guid := NULL;
END;
l_report_guid := mgmt_ip.create_report_definition (
p_title_nlsid => 'ALL_Ownership_Ora_Home_Title_NLSID',
p_description_nlsid => 'ALL_Ownership_Ora_Home_Desc_NLSID',
p_owner => 'SYSMAN',
p_category_nlsid => 'IPMSG_SECURITY_CATEGORY',
p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY',
p_late_binding_target_types => null,
p_late_binding_multi_targets => 0,
p_show_table_of_contents => 0,
p_system_report => 1
);
--instruction text begin--------------------------------
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(3);
--The text to be displayed
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage',
'ALL_ORAHOME_OWN_INSTR_TEXT');
--The resource bundle containing the text
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName',
'oracle.sysman.resources.eml.esa.EsaMsg');
--Specifies the style class
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass',
'OraInstructionText');
--Adding instruction text to the created report definition and passing the above parameters
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'IPMSG_STYLED_TEXT',
p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
p_element_order => 1,
p_element_row => 1,
p_parameters => l_param_values,
p_targets => null);
--instruction text end--------------------------------
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(5);
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement',
'SELECT target_name as DB_TGT_NAME, object_name AS OWNER_ID, principal AS FILE_ID
FROM MGMT$ESA_OH_OWNERSHIP_REPORT 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.extend(1);
l_target_types(1) := 'oracle_database';
/*Delete earlier report of this name if any*/
BEGIN
SELECT REPORT_GUID
INTO l_report_guid
FROM MGMT_IP_REPORT_DEF
WHERE title_nlsid = 'ALL_Owner_Ora_Home_NT_Title_NLSID';
mgmt_ip.delete_report(l_report_guid,1);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_report_guid := NULL;
END;
l_report_guid := mgmt_ip.create_report_definition (
p_title_nlsid => 'ALL_Owner_Ora_Home_NT_Title_NLSID',
p_description_nlsid => 'ALL_Ownership_Ora_Home_Desc_NLSID',
p_owner => 'SYSMAN',
p_category_nlsid => 'IPMSG_SECURITY_CATEGORY',
p_sub_category_nlsid => 'IPMSG_DATABASE_PRIVILEGES_CATEGORY',
p_late_binding_target_types => null,
p_late_binding_multi_targets => 0,
p_show_table_of_contents => 0,
p_system_report => 1
);
--instruction text begin--------------------------------
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(3);
--The text to be displayed
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage',
'ALL_ORAHOME_OWN_INSTR_TEXT');
--The resource bundle containing the text
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName',
'oracle.sysman.resources.eml.esa.EsaMsg');
--Specifies the style class
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass',
'OraInstructionText');
--Adding instruction text to the created report definition and passing the above parameters
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'IPMSG_STYLED_TEXT',
p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
p_element_order => 1,
p_element_row => 1,
p_parameters => l_param_values,
p_targets => null);
--instruction text end--------------------------------
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(5);
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement',
'SELECT target_name as DB_TGT_NAME, object_name AS OWNER_ID, principal AS FILE_ID
FROM MGMT$ESA_OH_OWNERSHIP_REPORT 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--------------------------
END;
/
COMMIT;