Rem drv: Rem Rem $Header: esm_post_creation.sql 31-jul-2005.22:49:36 dkjain Exp $ Rem Rem esm_post_creation.sql Rem Rem Copyright (c) 2005, Oracle. All rights reserved. Rem Rem NAME Rem esm_post_creation.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem dkjain 07/31/05 - Making identical to Beta upgr Rem chyu 07/19/05 - modifying the new rep manager header Rem dkjain 07/11/05 - Registering the oracle_security_inst for esm Rem tables and metadata Rem dsukhwal 06/24/05 - remove esm_ui_metrics Rem dsukhwal 03/14/05 - host_policies and ui_metrics Rem eujang 03/10/05 - eujang_rep_upgrade_debug Rem dsukhwal 02/23/05 - Created Rem -- This is the SQL script to create the ESM_COLLECTION and MGMT_ESA_REPORT table and associated registeration of metadata -- for the ECM Security snapshot. -- This section was largely BUT NOT COMPLETELY generated by the ECM Snapshot registry, invoking the command -- >> java -classpath $ORACLE_HOME/emdw/sysman/jlib/emCORE.jar:$ORACLE_HOME/xdk/lib/xmlparserv2.jar:$ORACLE_HOME/emdw/sysman/jlib/log4j-core.jar oracle.sysman.eml.ecm.snap.MetadataRegistry esm.xml esm.sql Y -- where the contents of the file esm.xml are as follows: -- -- -- Security best practices -- -- ESM Collection -- Property -- Value1 -- Value2 --
-- -- -- -- -- Security best practices -- -- ESA REPORTS -- Principal -- Object_name -- Report_name --
-- -- -- define EM_SQL_ROOT = "&1" -- The resulting SQL has been modified in the following ways: -- 1. The ESM_COLLECTION table is to be used for security snapshots of ALL existing target types, not just "host". -- 2. The length for the PROPERTY and VALUE columns has been extracted out as variables in the DECLARE statement. -- -- ---------------------------------------------------------------------------------------------------------------------- --------------- Start autogenerated file ----------------------------------------------------------------------------- SET DEFINE OFF -- set serveroutput on ALTER SESSION SET CONSTRAINTS = DEFERRED; / DECLARE metadata_id RAW(16) ; property_length INTEGER := 64; -- CAUTION: Must match the column description in the CREATE statement value1_length INTEGER := 512; -- CAUTION: Must match the column description in the CREATE statement value2_length INTEGER := 512; -- CAUTION: Must match the column description in the CREATE statement type TARGET_LIST is table of MGMT_TARGET_TYPES.TARGET_TYPE%TYPE; ttype MGMT_TARGET_TYPES.TARGET_TYPE%TYPE; ttypeList TARGET_LIST; BEGIN -- Need to specify explicitly all target types here that are checked for security ttypeList := TARGET_LIST('host', 'oracle_database', 'rac_database', 'oracle_listener', 'oracle_ias', 'oracle_emd', 'oracle_apache', 'oracle_webcache'); -------------------------------------------- -- Registering metadata Security best practices for target type host and snapshot type security -------------------------------------------- -- Remove all related metadatas FOR i IN ttypeList.first..ttypeList.last LOOP ttype := ttypeList(i); metadata_id := sys_guid(); -- Remove all related metadatas DELETE FROM MGMT_ECM_SNAPSHOT_MD_TABLES WHERE METADATA_ID IN (SELECT METADATA_ID FROM MGMT_ECM_SNAPSHOT_METADATA WHERE TARGET_TYPE = ttype AND SNAPSHOT_TYPE = 'oracle_security'); DELETE FROM MGMT_ECM_SNAPSHOT_METADATA WHERE TARGET_TYPE = ttype AND SNAPSHOT_TYPE = 'oracle_security'; -- Register metadata as loader metadata INSERT INTO MGMT_ECM_SNAPSHOT_METADATA (SNAPSHOT_TYPE, TARGET_TYPE, KIND, METADATA_ID, UI_NAME, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, LINK_TABLE_NAME, AFTER_LOAD_PROC_NAME, AFTER_IMPORT_PROC_NAME, METADATA_VERSION, VIEW_URL, COMPARE_URL, HISTORY_URL, SAME_TARGET_COMPARE) VALUES ('oracle_security', ttype, 'L', metadata_id, 'Security best practices', 'N', 'N', 'Y', 'N', 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'H'); -- Register metadata as presentation metadata INSERT INTO MGMT_ECM_SNAPSHOT_METADATA (SNAPSHOT_TYPE, TARGET_TYPE, KIND, METADATA_ID, UI_NAME, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, LINK_TABLE_NAME, AFTER_LOAD_PROC_NAME, AFTER_IMPORT_PROC_NAME, METADATA_VERSION, VIEW_URL, COMPARE_URL, HISTORY_URL, SAME_TARGET_COMPARE) VALUES ('oracle_security', ttype, 'P', metadata_id, 'Security best practices', 'N', 'N', 'Y', 'N', 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'H'); -- Register metadata for table ESM_COLLECTION INSERT INTO MGMT_ECM_SNAPSHOT_MD_TABLES (METADATA_ID, NAME, UI_NAME, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, PARENT_TABLE_NAME, IS_SINGLE_ROW, TBL_ORDER) VALUES (metadata_id, 'ESM_COLLECTION', 'ESM Collection', 'N', 'N', 'Y', 'N', 'N', NULL, 'N', 0); INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER) VALUES (metadata_id, 'ESM_COLLECTION', 'PROPERTY', 'Property', 'S', property_length, 'N', 'N', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 0); INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER) VALUES (metadata_id, 'ESM_COLLECTION', 'VALUE', 'Value', 'S', value1_length, 'N', 'N', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 1); INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER) VALUES (metadata_id, 'ESM_COLLECTION', 'VALUE2', 'Value2', 'S', value2_length, 'N', 'N', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 2); -- Register metadata for table MGMT_ESA_REPORT INSERT INTO MGMT_ECM_SNAPSHOT_MD_TABLES (METADATA_ID, NAME, UI_NAME, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, PARENT_TABLE_NAME, IS_SINGLE_ROW, TBL_ORDER) VALUES (metadata_id, 'MGMT_ESA_REPORT', 'ESA REPORTS', 'N', 'N', 'Y', 'N', 'N', NULL, 'N', 0); INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER) VALUES (metadata_id, 'MGMT_ESA_REPORT', 'PRINCIPAL', 'Principal', 'S', '512', 'N', 'N', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 0); INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER) VALUES (metadata_id, 'MGMT_ESA_REPORT', 'OBJECT_NAME', 'Object_name', 'S', '512', 'N', 'N', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 1); INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER) VALUES (metadata_id, 'MGMT_ESA_REPORT', 'REPORT_NAME', 'Report_name', 'S', '512', 'N', 'N', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 2); END LOOP; END; / show errors ; ALTER SESSION SET CONSTRAINTS = IMMEDIATE; COMMIT; -- End of metadata registration for metadata Security best practices for esm_collection and mgmt_esa_report ---------------------------------------------------------------------------------------------------- -- MGMT_ESA_REPORT Registration -- Registering metadata Security best practices for all target type and snapshot type oracle_security ------------------------------------------------------------------------------------------------------ /* ALTER SESSION SET CONSTRAINTS = DEFERRED; DECLARE metadata_id RAW(16) := SYS_GUID(); BEGIN -- Remove all related metadatas DELETE FROM MGMT_ECM_SNAPSHOT_MD_TABLES WHERE METADATA_ID IN (SELECT METADATA_ID FROM MGMT_ECM_SNAPSHOT_METADATA WHERE TARGET_TYPE = 'oracle_database' AND SNAPSHOT_TYPE = 'oracle_security'); DELETE FROM MGMT_ECM_SNAPSHOT_METADATA WHERE TARGET_TYPE = 'oracle_database' AND SNAPSHOT_TYPE = 'oracle_security'; -- Register metadata as loader metadata INSERT INTO MGMT_ECM_SNAPSHOT_METADATA (SNAPSHOT_TYPE, TARGET_TYPE, KIND, METADATA_ID, UI_NAME, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, LINK_TABLE_NAME, AFTER_LOAD_PROC_NAME, AFTER_IMPORT_PROC_NAME, METADATA_VERSION, VIEW_URL, COMPARE_URL, HISTORY_URL, SAME_TARGET_COMPARE) VALUES ('oracle_security', 'oracle_database', 'L', metadata_id, 'Security best practices', 'N', 'N', 'Y', 'N', 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'H'); -- Register metadata as presentation metadata INSERT INTO MGMT_ECM_SNAPSHOT_METADATA (SNAPSHOT_TYPE, TARGET_TYPE, KIND, METADATA_ID, UI_NAME, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, LINK_TABLE_NAME, AFTER_LOAD_PROC_NAME, AFTER_IMPORT_PROC_NAME, METADATA_VERSION, VIEW_URL, COMPARE_URL, HISTORY_URL, SAME_TARGET_COMPARE) VALUES ('oracle_security', 'oracle_database', 'P', metadata_id, 'Security best practices', 'N', 'N', 'Y', 'N', 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'H'); -- Register metadata for table MGMT_ESA_REPORT INSERT INTO MGMT_ECM_SNAPSHOT_MD_TABLES (METADATA_ID, NAME, UI_NAME, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, PARENT_TABLE_NAME, IS_SINGLE_ROW, TBL_ORDER) VALUES (metadata_id, 'MGMT_ESA_REPORT', 'ESA REPORTS', 'N', 'N', 'Y', 'N', 'N', NULL, 'N', 0); INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER) VALUES (metadata_id, 'MGMT_ESA_REPORT', 'PRINCIPAL', 'Principal', 'S', '512', 'N', 'N', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 0); INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER) VALUES (metadata_id, 'MGMT_ESA_REPORT', 'OBJECT_NAME', 'Object_name', 'S', '512', 'N', 'N', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 1); INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER) VALUES (metadata_id, 'MGMT_ESA_REPORT', 'REPORT_NAME', 'Report_name', 'S', '512', 'N', 'N', 'Y', 'N', 'N', 'Y', 'N', 'N', 'N', NULL, 2); END; / show errors ; ALTER SESSION SET CONSTRAINTS = IMMEDIATE; COMMIT; */ -- End of metadata registration for metadata Security best practices for mgmt_esa_report ---------------- End autogenerated file ----------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------- SET DEFINE ON --All the below(commented) ESA stuff have to be run once their dependencies are resolved @&EM_SQL_ROOT/core/v102010/esm/host_policies.sql --@&EM_SQL_ROOT/core/v102010/esm/esm_ui_metrics.sql /* --10gR1 policies for target types other than Host, Database and Listener. --These policies need to be migrated to 10gR2 policies by the resp. teams. ESM.create_rule('HTTP Server Directory Indexing', 'Check that Directory Indexing is disabled on this HTTP Server', 'C', 'knowledgeable and malicious users can view restricted files and directories in the "DocumentRoot" Directory.' , 'Oracle recommmends that Directory Indexing should be turned off.', 'oracle_apache', 'metric_name', 'ApacheDirectoryIndexing', 'VALUE', 'T', 'Directory Indexing', 'VALUE = ''enabled''', null, 'OHS_DIRECTORY_INDEX', 'DIRECTORY_INDEXING'); ESM.create_rule('HTTP Server Access Logging', 'Check that HTTP Server access logging is enabled', 'C', 'Access Log contains vital information about requests and users who access HTTP Server, absence of an access log could severly cripple administrators'' ability to monitor malicious attacks.', 'Oracle recommends that the access logging should be enabled for HTTP Server .', 'oracle_apache', 'metric_name', 'ApacheAccessLogging', 'VALUE', 'T', 'Access Logging', 'VALUE <> ''enabled''', null, 'OHS_ACCESS_LOGGING', 'ACCESS_LOGGING'); ESM.create_rule('HTTP Server Owner and setuid bit', 'Check the httpd binary is not owned by root and setuid bit is not set', 'C', 'If httpd is owned by root and setuid bit is set,knowledgeable and malicious users may be able to gain access to the system as a superuser.', 'Oracle recommends that the httpd binary should not be owned by root and set uid bit should not be set.', 'oracle_apache', 'metric_name', 'HttpdProcessOwnedBy', 'VALUE', 'T', 'Httpd owned by', 'VALUE = ''root''', null, 'OHS_SETUID', 'HTTPD_OWNED_BY'); ESM.create_rule('Web Cache access logging', 'Check that Web Cache access logging is enabled', 'C', 'Access Log contains vital information about requests and users who access Web Cache,absence of an access log could severly cripple administrators'' ability to monitor malicious attacks.', 'Oracle recommends that the access logging should be enabled for Web Cache.', 'oracle_webcache', 'metric_name', 'WebCacheAccessLogging', 'VALUE', 'T', 'Access Logging', 'VALUE <> ''enabled''', null, 'WEBCACHE_ACCESS_LOGGING', 'ACCESS_LOGGING'); ESM.create_rule('Web Cache owner and setuid bit', 'Check that webcached binary is not owned by root and setuid is not set', 'C', 'If webcached is owned by root and setuid bit is set, knowledgeable and malicious users may be able to gain access to the system as a superuser.', 'Oracle recommends that the webcached binary should not be owned by root and setuid bit should not be set. ', 'oracle_webcache', 'metric_name', 'WebCacheProcess', 'VALUE', 'T', 'webcached owned by', 'VALUE = ''root''', null, 'WEBCACHE_SETUID', 'WEBCACHED_OWNED_BY'); ESM.create_rule('HTTP Server Writable files', 'Check that there are no group or world writable files in the Document Root directory', 'W', 'Knowledgeable and malicious users could overwrite a writable file in the DocumentRoot Directory.', 'Oracle recommends that there should not be any group and world writeble files in the DocumentRoot.', 'oracle_apache', 'metric_name', 'OHSWritableFile', 'VALUE', 'T', 'Number of writeable file(s):', null, null, 'OHS_WRITEABLE_FILES', 'WRITEABLE_FILES'); ESM.create_rule('Web Cache Writable files', 'Check that there are no group or world writable files in the Document Root directory', 'W', 'Knowledgeable and malicious users could overwrite a writable file in the DocumentRoot Directory.', 'Oracle recommends that there should not be any group and world writeble files in the DocumentRoot.', 'oracle_webcache', 'metric_name', 'WebCacheWritableFile', 'VALUE', 'T', 'Number of writeable file(s):', null, null, 'WEBCACHE_WRITEABLE_FILES', 'WRITEABLE_FILES'); */ /** ESM.create_rule('SSL on SSO OHS', 'Check that SSL is enabled on SSO OHS', 'C', 'If SSL is not enabled on SSO OHS, username/passwd entered by user can be sniffed by malicious parties', 'Oracle recommends that SSL must be enabled on SSO OHS for secure transmission of username/passwd', 'oracle_apache', 'metric_name', 'OHSSSLEnable', 'VALUE', 'T', 'OHS is not running on SSL', null, null, 'OHS_SSL_ENABLED', 'SSO_SSL'); */ /* ESM.create_rule('WebCache Dummy wallet', 'Check that Dummy wallet is not used for production SSL load', 'C', 'Use of a dummy wallet provided by Oracle could severely compromise the security of the site.', 'Oracle recommends that a dummy wallet should not be used for production SSL load.', 'oracle_webcache', 'metric_name', 'WebCacheDummyWallet', 'VALUE', 'T', 'Dummy wallet', null, null, 'WEBCACHE_DUMMY_WALLET', 'DUMMY_WALLET'); ESM.create_rule('HTTP Server Dummy wallet', 'Check that Dummy wallet is not used for production SSL load', 'C', 'Use of a dummy wallet provided by Oracle could severely compromise the security of the site.', 'Oracle recommends that a dummy wallet should not be used for production SSL load.', 'oracle_apache', 'metric_name', 'OHSDummyWallet', 'VALUE', 'T', 'Dummy wallet', null, null, 'OHS_DUMMY_WALLET', 'DUMMY_WALLET'); */ show errors ;