Rem drv:
Rem
Rem $Header: esm_views.sql 19-jul-2005.19:43:40 dsukhwal Exp $
Rem
Rem esm_views.sql
Rem
Rem Copyright (c) 2003, 2005, Oracle. All rights reserved.
Rem
Rem NAME
Rem esm_views.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem dsukhwal 07/19/05 - fix Oracle Home permission view
Rem rmadampa 07/13/05 - bug4411084 - use snapshottype in
Rem esm_collection_latest
Rem dsukhwal 07/14/05 - use esm_collection_latest
Rem gsbhatia 07/01/05 - New repmgr header impl
Rem gsbhatia 06/26/05 - New repmgr header impl
Rem dsukhwal 05/27/05 - comments for views
Rem dsukhwal 03/01/05 - add new views
Rem dkjain 02/17/05 - rename report views to mgmt$ and join with
Rem mgmt_targets
Rem dkjain 02/17/05 - rename report views to mgmt$ and join with
Rem mgmt_targets
Rem gsbhatia 02/13/05 - updating repmgr header
Rem gsbhatia 02/09/05 - updating repmgr header
Rem gsbhatia 02/07/05 - updating repmgr header
Rem dsukhwal 01/21/05 - add new views
Rem ktlaw 01/11/05 - add repmgr header
Rem dsukhwal 11/16/04 - Fix CATALOG and ROLE_ACCESS view names
Rem dkjain 10/13/04 - Catalog View added & column value1 name changed
Rem dkjain 10/08/04 - ESA 10gR2 Impl
Rem eujang 09/04/03 - eujang_esm_init_no_intgr
Rem anajmi 08/26/03 - Created
Rem
CREATE OR REPLACE VIEW ESM_COLLECTION_LATEST AS
SELECT ecm.target_guid AS "TARGET_GUID",
esm.property AS PROPERTY,
esm.value AS VALUE,
esm.value2 AS VALUE2
FROM esm_collection esm, mgmt_ecm_gen_snapshot ecm
WHERE esm.ecm_snapshot_id = ecm.snapshot_guid
AND ecm.is_current = 'Y'
AND ecm.snapshot_type in ('oracle_security', 'oracle_security_inst');
/
rem
rem PURPOSE
rem The view ESM_COLLECTION_LATEST shows the latest data from the ESM_COLLECTION table.
rem Only the latest data have to be considered for evaluation, hence this view is
rem selected from for evaluation purposes
rem COLUMNS
rem TARGET_GUID : The GUID of the target for which the data is meant
rem PROPERTY : PROPERTY, VALUE, VALUE2 together form a data entry, mainly for the
rem evaluation of policies. Also used for display of policies in some cases
rem VALUE :
rem VALUE2 :
rem
-- ----------------- Views for ESA Reports ---------------------------------------
rem COLUMNS for all the views below
rem TARGET_GUID The GUID of the target for which the report has the data
rem TARGET_NAME The name of the target for which the report has the data
rem PRINCIPAL PRINCIPAL and OBJECT_NAME together form a data entry for the particular target
rem OBJECT_NAME
CREATE OR REPLACE VIEW MGMT$ESA_CATALOG_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'CATALOG_ROLE';
/
rem
rem PURPOSE
rem The view MGMT$ESA_CATALOG_REPORT is used to display a table and a chart
rem having all the users which have a role like '%CATALOG%' in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_DBA_GROUP_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'DBA_GROUP';
/
rem
rem PURPOSE
rem The view MGMT$ESA_DBA_GROUP_REPORT is used to display a table
rem having members of the operating system user group DBA in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_POWER_PRIV_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'POWER_PRIVILEGE';
/
rem
rem PURPOSE
rem The view MGMT$ESA_POWER_PRIV_REPORT is used to display a table and a chart
rem having all the users and roles with 'ALTER SESSION', 'ALTER SYSTEM',
rem 'CREATE PROCEDURE' or 'CREATE LIBRARY' Privileges in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_DBA_ROLE_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'DBA_ROLE';
/
rem
rem PURPOSE
rem The view MGMT$ESA_DBA_ROLE_REPORT is used to display a table having
rem users and roles with the DBA role granted to them in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_ALL_PRIVS_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'ALL_PRIVILEGES';
/
rem
rem PURPOSE
rem The view MGMT$ESA_ALL_PRIVS_REPORT is used to display a table having
rem users and roles with 'GRANT ANY' privilege in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_ANY_PRIV_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'ANY_PRIVILEGE';
/
rem
rem PURPOSE
rem The view MGMT$ESA_ANY_PRIV_REPORT is used to display a table and a chart
rem having users with 'ANY' in some privilege granted to them in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_WITH_GRANT_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'WITH_GRANT';
/
rem
rem PURPOSE
rem The view MGMT$ESA_WITH_GRANT_REPORT is used to display a table and a chart
rem having users and roles having some privileges granted to them with 'WITH GRANT'
rem option in database security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_WITH_ADMIN_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'WITH_ADMIN';
/
rem
rem PURPOSE
rem The view MGMT$ESA_WITH_ADMIN_REPORT is used to display a table and a chart
rem having users and roles having some privileges granted to them with 'WITH ADMIN'
rem option in database security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_SYS_PUB_PKG_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'SYS_PUBLIC_PACKAGE';
/
rem
rem PURPOSE
rem The view MGMT$ESA_SYS_PUB_PKG_REPORT is used to display a table having
rem system packages with public execute privileges in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_ANY_DICT_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'ANY_DICTIONARY';
/
rem
rem PURPOSE
rem The view MGMT$ESA_ANY_DICT_REPORT is used to display a table and a chart
rem having users and roles with access to any dictionary in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_EXMPT_ACCESS_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'EXEMPT_ACCESS_POLICY';
/
rem
rem PURPOSE
rem The view MGMT$ESA_EXMPT_ACCESS_REPORT is used to display a table having
rem users and roles with 'EXEMPT ACCESS POLICY' privilege in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_CREATE_PRIV_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'CREATE_PRIVILEGE';
/
rem
rem PURPOSE
rem The view MGMT$ESA_CREATE_PRIV_REPORT is used to display a table and a chart
rem having users and roles with 'CREATE' in privilege in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_BECOME_USER_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'BECOME_USER';
/
rem
rem PURPOSE
rem The view MGMT$ESA_BECOME_USER_REPORT is used to dusplay a table having
rem users and roles with 'BECOME USER' privilege in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_AUDIT_SYSTEM_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'AUDIT_SYSTEM';
/
rem
rem PURPOSE
rem The view MGMT$ESA_AUDIT_SYSTEM_REPORT is used to display a table having
rem users and roles with 'AUDIT SYSTEM' privilege in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_DIRECT_PRIV_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'DIRECT_PRIVILEGES';
/
rem
rem PURPOSE
rem The view MGMT$ESA_DIRECT_PRIV_REPORT is used to display a table and a chart
rem having privileges granted directly in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_PUB_PRIV_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'PUBLIC_PRIVILEGE';
/
rem
rem PURPOSE
rem The view MGMT$ESA_PUB_PRIV_REPORT is used to display a table and a chart
rem having privileges granted to PUBLIC in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_CONN_PRIV_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'CONNECT_PRIVILEGE';
/
rem
rem PURPOSE
rem The view MGMT$ESA_CONN_PRIV_REPORT is used to display a table and a chart
rem having users and roles with CONNECT or RESOURCE role in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_TABSP_OWNERS_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
esa_report.principal AS "PRINCIPAL",
esa_report.object_name AS "OBJECT_NAME"
FROM mgmt_esa_report esa_report, mgmt_ecm_gen_snapshot ecm, mgmt_targets tgt
WHERE esa_report.ecm_snapshot_id = ecm.snapshot_guid AND
tgt.target_guid = ecm.target_guid AND
ecm.is_current = 'Y' AND
esa_report.report_name = 'TABLESPACE_OWNERS';
/
rem
rem PURPOSE
rem The view MGMT$ESA_TABSP_OWNERS_REPORT is used to display a table
rem having tablespaces and their owners in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_OH_PERMISSION_REPORT AS
SELECT tgt.target_guid AS "TARGET_GUID",
tgt.target_name AS "TARGET_NAME",
collection.value2 as PRINCIPAL,
collection.value as OBJECT_NAME
FROM esm_collection_latest collection, mgmt_targets tgt
WHERE property = 'oh_files_perm' AND
tgt.target_guid = collection.target_guid
/
rem
rem PURPOSE
rem The view MGMT$ESA_OH_PERMISSION_REPORT is used to display a table
rem having file permissions by Oracle Home in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_OH_OWNERSHIP_REPORT AS
SELECT targets.target_guid AS "TARGET_GUID",
targets.target_name AS "TARGET_NAME",
collection.value2 as PRINCIPAL,
collection.value as OBJECT_NAME
FROM esm_collection_latest collection, mgmt_targets targets
WHERE collection.target_guid = targets.target_guid and
property = 'oh_bin_files_owner';
/
rem
rem PURPOSE
rem The view MGMT$ESA_OH_OWNERSHIP_REPORT is used to display a table
rem having file ownership by Oracle Home in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_TRC_AUD_PERM_REPORT AS
SELECT targets.target_guid AS "TARGET_GUID",
targets.target_name AS "TARGET_NAME",
collection.value2 as PRINCIPAL,
decode(property,'audit_file_dest','Audit file destination',
'user_dump_dest','User dump destination',
'background_dump_dest','Background dump destination',
'core_dump_dest','Core dump destination') as OBJECT_NAME,
collection.value AS permission
FROM esm_collection_latest collection, mgmt_targets targets
WHERE collection.target_guid = targets.target_guid and
property in ('audit_file_dest',
'user_dump_dest',
'background_dump_dest',
'core_dump_dest');
/
rem
rem PURPOSE
rem The view MGMT$ESA_TRC_AUD_PERM_REPORT is used to display a table having
rem trace and audit files permissions in database
rem security reports
rem
CREATE OR REPLACE VIEW MGMT$ESA_TRC_AUD_PERM_REP_NT AS
SELECT targets.target_guid AS "TARGET_GUID",
targets.target_name AS "TARGET_NAME",
collection.value2 as PRINCIPAL,
decode(property,'nt_audit_file_dest','Audit file destination',
'nt_user_dump_dest','User dump destination',
'nt_background_dump_dest','Background dump destination',
'nt_core_dump_dest','Core dump destination') as OBJECT_NAME,
collection.value AS permission
FROM esm_collection_latest collection, mgmt_targets targets
WHERE collection.target_guid = targets.target_guid and
property in ('nt_audit_file_dest',
'nt_user_dump_dest',
'nt_background_dump_dest',
'nt_core_dump_dest');
/
rem
rem PURPOSE
rem The view MGMT$ESA_TRC_AUD_PERM_REPORT_NT is used to display a table having
rem trace and audit files permissions in database
rem security reports. This is used for reports about Windows/NT platforms
rem
CREATE OR REPLACE VIEW MGMT$ESA_KEY_OBJECTS_REPORT AS
SELECT targets.target_guid AS "TARGET_GUID",
targets.target_name AS "TARGET_NAME",
collection.value as "user",
decode(property,'access_aud_table','Table AUD$',
'access_user_history','Table USER_HISTORY$',
'access_source_table','Table SOURCE$',
'access_link_table','Table LINK$',
'access_user_table','Table USER$',
'access_sql_text','Table STATS$SQLTEXT',
'access_sql_summary','Table STATS$SQL_SUMMARY',
'access_all_source','View ALL_SOURCE',
'access_dba_roles','View DBA_ROLES',
'access_dba_sysprivs','View DBA_SYS_PRIVS',
'access_dba_roleprivs','View DBA_ROLE_PRIVS',
'access_dba_tabprivs','View DBA_TAB_PRIVS',
'access_dba_users','View DBA_USERS',
'access_role_roleprivs','View ROLE_ROLE_PRIVS',
'access_user_tabprivs','View USER_TAB_PRIVS',
'access_user_roleprivs','View USER_ROLE_PRIVS') as "object_name",
collection.value2 as "privilege"
FROM esm_collection_latest collection, mgmt_targets targets
WHERE collection.target_guid = targets.target_guid and
property in
('access_aud_table',
'access_user_history',
'access_source_table',
'access_link_table',
'access_user_table',
'access_sql_text',
'access_sql_summary',
'access_all_source',
'access_dba_roles',
'access_dba_sysprivs',
'access_dba_roleprivs',
'access_dba_tabprivs',
'access_dba_users',
'access_role_roleprivs',
'access_user_tabprivs',
'access_user_roleprivs');
/
rem
rem PURPOSE
rem The view MGMT$ESA_KEY_OBJECTS_REPORT is used to display a table and a chart
rem having users and roles with access to key objects in database security reports
rem
COMMIT;
show errors ;