Rem
Rem $Header: policy_tables_downgrade.sql 03-aug-2005.08:04:23 niramach Exp $
Rem
Rem policy_tables_downgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      policy_tables_downgrade.sql - Downgrade scripts for Policy tables.
Rem
Rem    DESCRIPTION
Rem      It has creation of V1 policy tables and removal of V2 (10.2GC) policy
Rem      tables.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    niramach    08/03/05 - niramach_bugfix-4527462
Rem    niramach    04/29/05 - niramach_bug-4293674
Rem    niramach    04/26/05 - Created
Rem
--V1 policy tables which are obsolete in V2 10.2GC policy framework.
--Since it is downgrade those V1 tables should be created.

rem PURPOSE
rem   Each row in the MGMT_POLICY_RULE table represents a rule, identified 
rem   by its name, target_type and category.  Each rule can have zero or more 
rem   columns, zero or more default action, and zero or more parameters 
rem   (with default values).   The combination of NAME, TARGET_TYPE and CATEGORY 
rem   must be unique.
rem COLUMNS
rem   RULE_ID   System generated identifier for the rule.
rem   RULE_NAME Creator supplied name of the rule. 
rem   DISPLAY_ID The id used to construct the resource id for obtaining the
rem     appropriate strings
rem   VERSION Rule version
rem   TARGET_TYPE   An EM target type such as oracle_database, host, or oracle_ias.  
rem     Used to associate rules with appropriate targets.
rem   CATEGORY  Indicates the virtual set of rules to which this rule belongs.  
rem     Examples are: Security, Config???
rem   DESCRIPTION   A description of the rule.
rem   RULE_TYPE The rule type describes how to process the rule.  Types include 
rem     SQL, QUERY, PLSQL and CONFIG.  If type QUERY, then the RULE_OBJECT 
rem     attribute is a SQL select statement that defines the data that the rule 
rem     will test.  If SQL, then the rule object holds a view or table name.  
rem     If CONFIG, then the rule object???s metadata is accessible via the EM 
rem     config framework tables.  How can we simplify this?  What are the actual 
rem     requirements?
rem   RULE_OBJECT   A query, table name, view name or qualified procedure name, 
rem     depending on the rule type.
rem   TEST  This is a predicate to attach to a query against the 
rem     RULE_OBJECT.  This predicate describes the condition for rule violation.  
rem     It can contain parameter references.  All parameters must have an entry 
rem     in the MGMT_POLICY_RULE_DEF_PARAMS table.
rem   PRIORITY Rule severity 4,3,2,1,0,-1 (High, medium, low, informational, 
rem      clear, error)
rem   IMPACT_OF_PROBLEM A text message that can be used to describe the impact 
rem     of a rule violation.
rem   RECOMMENDATION A text message indicating recommendation.
rem   AUTHOR Rule's author; for example, 'Oracle'
rem   CREATION_DATE The date that the rule was created (is the entered by the 
rem     creator, or is it actually the date of registration?)
rem   UPDATOR Who last updated this rule?
rem   LAST_UPDATED  This is the when the last change was made to the rule in the 
rem     repository.
rem   UPDATE_COMMENT Comment associated with last update

CREATE TABLE MGMT_POLICY_RULE
(
  RULE_ID RAW(&GUID_RAW_LENGTH) NOT NULL, 
  RULE_NAME VARCHAR2(&P_NAME_LENGTH), 
  DISPLAY_ID VARCHAR2(&P_DISPLAY_ID_LENGTH),
  VERSION VARCHAR2(&P_VERSION_LENGTH),
  TARGET_TYPE VARCHAR2(&P_TARGET_TYPE_LENGTH), 
  CATEGORY VARCHAR2(&P_CATEGORY_LENGTH), 
  DESCRIPTION VARCHAR2(&P_DESCRIPTION_LENGTH), 
  RULE_TYPE VARCHAR2(&P_RULE_TYPE_LENGTH), 
  RULE_OBJECT VARCHAR2(&P_LARGE_VARCHAR_LENGTH), 
  TEST VARCHAR2(&P_LARGE_VARCHAR_LENGTH), 
  PRIORITY NUMBER,
  IMPACT_OF_PROBLEM VARCHAR2(&P_SHORT_DESCRIPTION_LENGTH), 
  RECOMMENDATION VARCHAR2(&P_SHORT_DESCRIPTION_LENGTH),
  AUTHOR VARCHAR2(&P_AUTHOR_LENGTH),
  CREATION_DATE DATE, 
  UPDATOR VARCHAR2(&P_NAME_LENGTH),
  LAST_UPDATED DATE, 
  UPDATE_COMMENT VARCHAR2(&P_SHORT_DESCRIPTION_LENGTH),
  DISABLED_FLAG VARCHAR2(&P_Y_N_LENGTH) DEFAULT 'N',
  DISABLED_BY VARCHAR2(&P_NAME_LENGTH),
  CONSTRAINT MRD_PK PRIMARY KEY(RULE_ID),
  CONSTRAINT MRD_UK UNIQUE (AUTHOR, TARGET_TYPE, CATEGORY, RULE_NAME) 
) MONITORING;


rem PURPOSE
rem   Each row in this table is a child of a row in the MGMT_POLICY_RULE 
rem   table.  The entries in this table describe the columns that should be 
rem   captured when a rule is violated.  Each column referenced in the rule 
rem   test, or predicate, will typically have a corresponding entry in this 
rem   table.
rem COLUMNS
rem   RULE_ID   The ID of the parent row in the MGMT_POLICY_RULE table.
rem   COLUMN_POSITION   The position of the column in the result.
rem   COLUMN_NAME   The name of the column.  Used when displaying the rule 
rem     violations.
rem   IS_KEY  Is this column part of the row key.
rem   IS_VISIBLE  Do I show this column in the generic UI.
rem   URL A url with column references to be used and substituted with row-column
rem       values when displayed in the generic ui.
rem   DISPLAY_ID The id used to construct the resource id for obtaining the
rem     appropriate strings
rem   DISPLAY_FORMAT The display format to use when displaying the row-column
rem       values in the generic ui.  0,1,2 (textFormat, numberFormat, dateFormat)
CREATE TABLE MGMT_POLICY_RULE_DEF_COLUMNS 
(
  RULE_ID RAW(&GUID_RAW_LENGTH) NOT NULL, 
  COLUMN_POSITION NUMBER(&P_REASONABLE_NUMBER_LENGTH), 
  COLUMN_NAME VARCHAR2(&P_SQL_NAME_LENGTH) NOT NULL, 
  IS_KEY VARCHAR2(&P_Y_N_LENGTH),
  IS_VISIBLE VARCHAR2(&P_Y_N_LENGTH),
  URL VARCHAR2(&P_URL_LENGTH),
  DISPLAY_NAME VARCHAR2(&P_SQL_NAME_LENGTH),
  DISPLAY_FORMAT NUMBER(&P_REASONABLE_NUMBER_LENGTH), 
  CONSTRAINT MRDC_MRD_FK FOREIGN KEY(RULE_ID) 
    REFERENCES MGMT_POLICY_RULE(RULE_ID) 
    ON DELETE CASCADE, 
  CONSTRAINT MRDC_UK UNIQUE(RULE_ID, COLUMN_NAME) 
) MONITORING; 


rem PURPOSE
rem   Each parameter referenced in the RULE_TEST of a rule MUST have an entry in 
rem   this table.  This information will drive the Policy Manager when a policy 
rem   is created using that rule.
rem   Each entry specifies the default value of the parameters.  
rem   Rows in this table are used at runtime when an appropriate entry in the 
rem   MGMT_POLICY_PARAMS tables was not found for a particular rule.
rem COLUMNS
rem   RULE_ID   The ID of the parent row in the RULES table.
rem   PARAM_NAME    The name of the parameter used both to drive the GUI to get 
rem     overriding values at policy creation time, and to match those values to 
rem     the parameter placeholders in the rule test.
rem   PARAM_VALUE   The warning threshold value for this parameter.
rem   DISPLAY_ID The id used to construct the resource id for obtaining the
rem     appropriate strings
rem   DISPLAY_FORMAT The display format to use when displaying the values in
rem       the generic ui.  0,1,2 (textFormat, numberFormat, dateFormat)
CREATE TABLE MGMT_POLICY_RULE_DEF_PARAMS 
(
  RULE_ID RAW(&GUID_RAW_LENGTH) NOT NULL, 
  PARAM_NAME VARCHAR2(&P_NAME_LENGTH) NOT NULL, 
  PARAM_VALUE VARCHAR2(&P_VALUE_LENGTH), 
  DISPLAY_NAME VARCHAR2(&P_NAME_LENGTH),
  DISPLAY_FORMAT NUMBER(&P_REASONABLE_NUMBER_LENGTH), 
  CONSTRAINT MRDP_UK UNIQUE(RULE_ID, PARAM_NAME),
  CONSTRAINT MRDP_MRD_FK FOREIGN KEY(RULE_ID) 
    REFERENCES MGMT_POLICY_RULE(RULE_ID) 
    ON DELETE CASCADE 
) MONITORING;


rem PURPOSE
rem   Each row in this table represents a policy set up by the user via the 
rem   policy manager.  Each policy references one or more rule criteria that 
rem   will be used at evaluation time to determine the actual set of rules.  
rem   The actual set of targets to test is also determined at evaluation time.
rem COLUMNS
rem   POLICY_ID The ID of the policy, system generated.
rem   POLICY_NAME Name of the policy
rem   DISPLAY_ID The id used to construct the resource id for obtaining the
rem     appropriate strings
rem   VERSION For upgrades, used to version the policy.
rem   DESCRIPTION   A description of the policy.  Typically what the goal of the policy 
rem     is.
rem   AUTHOR    The creator of the policy.  
rem   CREATION_DATE The date that the policy was created.
rem   UPDATOR   The em user that last modified this policy
rem   UPDATE_DATE   The date that the policy was last updated.
rem   UPDATE_COMMENT - When an update occurs, a comment can be used to annotate 
rem       the policy.
CREATE TABLE MGMT_POLICY_GROUP 
(
  POLICY_ID RAW(&GUID_RAW_LENGTH) NOT NULL,
  POLICY_NAME VARCHAR2( &P_NAME_LENGTH ),
  DISPLAY_ID VARCHAR2(&P_DISPLAY_ID_LENGTH),
  VERSION VARCHAR2( &P_VERSION_LENGTH ),
  DESCRIPTION VARCHAR2(&P_DESCRIPTION_LENGTH),   
  AUTHOR VARCHAR2(&P_AUTHOR_LENGTH), 
  CREATION_DATE DATE, 
  UPDATOR VARCHAR2(&P_NAME_LENGTH), 
  UPDATE_DATE DATE, 
  UPDATE_COMMENT VARCHAR2(&P_SHORT_DESCRIPTION_LENGTH),
  CONSTRAINT MP_PK PRIMARY KEY(POLICY_ID),
  CONSTRAINT MP_UK UNIQUE (AUTHOR, POLICY_NAME) 
) MONITORING;


rem PURPOSE
rem   In order for the policy engine to evaluate rules, it must use the 
rem   rule_test along with the appropriate parameters.  The policy engine will 
rem   first look in this table to find values for each rule???s parameters.  If 
rem   not found here, it will look in the MGMT_POLICY_RULE_DEF_PARAMS table.
rem COLUMNS
rem   POLICY_ID The ID of the policy, system generated.
rem   RULE_NAME A wild-carded name used when matching the parameter values with 
rem     the actual rules at evaluation time.
rem   TARGET_TYPE   A wild-carded target type used when matching the parameter 
rem     values with the actual rules at evaluation time.
rem   CATEGORY  A wild-carded category used when matching the parameter values 
rem     with the actual rules at evaluation time.
rem   PARAM_NAME    The name of the parameter which is used in the rules 
rem     identified by the RULE_NAME, TARGET_TYPE and CATEGORY values.  
rem   PARAM_VALUE   The value to be used to bind in the rulw.  
rem   Typically the parameter will appear in a test that contains a 
rem     threshold test.   E.g. Issue WARNING alert if security patch has been 
rem     available for more than :DAYS days.  
CREATE TABLE MGMT_POLICY_PARAMS 
(
  POLICY_ID RAW(&GUID_RAW_LENGTH) NOT NULL, 
  AUTHOR VARCHAR2(&P_AUTHOR_LENGTH),
  TARGET_TYPE VARCHAR2(&P_TARGET_TYPE_LENGTH), 
  CATEGORY VARCHAR2(&P_CATEGORY_LENGTH), 
  RULE_NAME VARCHAR2(&P_NAME_LENGTH), 
  PARAM_NAME VARCHAR2(&P_NAME_LENGTH) NOT NULL, 
  PARAM_VALUE VARCHAR2(&P_VALUE_LENGTH), 
  CONSTRAINT MPP_MP_FK FOREIGN KEY(POLICY_ID) 
    REFERENCES MGMT_POLICY_GROUP(POLICY_ID) 
    ON DELETE CASCADE,
  CONSTRAINT MPP_UK UNIQUE(POLICY_ID, AUTHOR, 
    TARGET_TYPE, CATEGORY, RULE_NAME, PARAM_NAME) 
) MONITORING;


rem PURPOSE
rem   Entries in this table are used to determine which rules to use in a policy 
rem   evaluation.  The determination is performed at evaluation time so that 
rem   when new rules are added they will be included in the evaluation if they 
rem   meet the existing policy???s rule criteria.
rem COLUMNS
rem   POLICY_ID The ID of the policy, system generated.
rem   RULE_NAME A wild-carded string for  matching with the rules in the 
rem     MGMT_POLICY_RULE table.
rem   TARGET_TYPE   Wild-carded target type for matching with the rules in the 
rem     MGMT_POLICY_RULE table.
rem   CATEGORY  Wild-carder category for matching with the rules in the 
rem     MGMT_POLICY_RULE table.
CREATE TABLE MGMT_POLICY_RULE_CRITERIA
(
  POLICY_ID RAW(&GUID_RAW_LENGTH) NOT NULL, 
  AUTHOR VARCHAR2(&P_AUTHOR_LENGTH),
  TARGET_TYPE VARCHAR2(&P_TARGET_TYPE_LENGTH), 
  CATEGORY VARCHAR2(&P_CATEGORY_LENGTH), 
  RULE_NAME VARCHAR2(&P_NAME_LENGTH), 
  CONSTRAINT MPRC_MP_FK FOREIGN KEY(POLICY_ID) 
    REFERENCES MGMT_POLICY_GROUP(POLICY_ID) 
    ON DELETE CASCADE,
  CONSTRAINT MPRC_UK UNIQUE(POLICY_ID, AUTHOR, 
    TARGET_TYPE, CATEGORY, RULE_NAME) 
)   MONITORING;

rem PURPOSE
rem   This table is used to associate a list of targets with a policy.  The 
rem   criteria specified in this table are used at policy evaluation time to 
rem   determine the actual list of targets whose data should be tested against 
rem   the policy.
rem COLUMNS
rem   POLICY_ID The ID of the policy, system generated.
rem   NAME_IS_GROUP - Indicates if the target name refers to a group or
rem                         individual target.  'Y' for group, 'N' for target.
rem   TARGET_TYPE   Wild-carded target type for matching with targets in the 
rem     MGMT_TARGETS table.
rem   TARGET_NAME   Wild-carded target name for matching with targets in the 
rem     MGMT_TARGETS table
rem   FILTER_OP LIKE or NOT LIKE.  Used when fetching target list..
CREATE TABLE MGMT_POLICY_TARGET_CRITERIA
(
  POLICY_ID RAW(&GUID_RAW_LENGTH) NOT NULL, 
  NAME_IS_GROUP VARCHAR2(&P_Y_N_LENGTH),
  TARGET_TYPE VARCHAR2(&P_TARGET_TYPE_LENGTH), 
  TARGET_NAME VARCHAR2(&P_NAME_LENGTH), 
  FILTER_OP VARCHAR2(&P_FILTER_OP_LENGTH), 
  CONSTRAINT MPTC_MP_FK FOREIGN KEY(POLICY_ID) 
    REFERENCES MGMT_POLICY_GROUP(POLICY_ID) 
    ON DELETE CASCADE,
  CONSTRAINT MPTC_UK UNIQUE(POLICY_ID, TARGET_TYPE, 
    TARGET_NAME, NAME_IS_GROUP)     
) MONITORING;  

rem PURPOSE
rem   Each row that is returned when a rule is violated gets an entry in this 
rem   table.  Each row is actually identified by POLICY_ID, RULE_ID, TARGET_GUID 
rem   and the set of MGMT_POLICY_VIOLATION_VALUES associated with this row.  If 
rem   the a matching row was already in this table, from a previous evaluation, 
rem   then the row is updated to reflect the new information.  If a row existed 
rem   after a previous evaluation, but not in the latest, then the previous 
rem   entry is removed.  If the rule returned no column values, just TRUE or 
rem   FALSE, then an entry will exist in this table with no entries in the 
rem   corresponding MGMT_POLICY_VIOLATION_VALUES table.
rem   If a rule is modified, such that the column list changes, then older 
rem   violation will be removed, because the new violations will not match 
rem   exactly, and the new row will result in a new entry even if it is actually 
rem   the same as one of the old rows.
rem COLUMNS
rem   POLICY_ID The ID of the policy, system generated.
rem   RULE_ID   The id of the rule that was violated.  
rem   TARGET_GUID   The target guid of the target whose data violated the rule. 
rem   VIOLATION_ID  A unique system generated ID for this rule's violation.  
rem     as applied to the rule???s test.
CREATE TABLE MGMT_POLICY_VIOLATIONS
(
  POLICY_ID RAW(&GUID_RAW_LENGTH), 
  RULE_ID RAW(&GUID_RAW_LENGTH), 
  TARGET_GUID RAW(&GUID_RAW_LENGTH), 
  VIOLATION_ID RAW(&GUID_RAW_LENGTH) NOT NULL, 
  CONSTRAINT MPV_PK PRIMARY KEY(VIOLATION_ID) 
) MONITORING;

rem
rem PURPOSE
rem   For any policy-rule-target triplet, there may be zero or more row violations.
rem   This table is used to represent each row violation for that triplet.
rem   A row violation can be marked ignored.
rem COLUMNS
rem   VIOLATION_ID  Id's the POLICY-RULE-TARGET triplet and points to the parent
rem       table, MGMT_POLICY_VIOLATIONS, for them.
rem   KEY This is a concatenation of the key values for the row.  When updating
rem       the status of this policy-rule-target violation, we want to maintain
rem       a record of how long a particular row violation has been so, and whether
rem       or not it was ignored.  We need a key to identify what is ignored etc.
rem   KEY_REST This is the rest of the key, not indexed, that did not fit in 
rem       the indexed part of the key.
rem   ROW_ID  A guid used to identify this row so the row values can use it instead
rem       of the full key as a foreign key referened.
rem   IGNORED_FLAG  A user can mark this particular row violation as ignored.  Being 
rem     ignored, it will be filtered out from the normal violation display.  
rem   IGNORED_BY    The user who marked this row as ignored.
rem   ROW_COMMENT A comment that can be added by a user regarding this violation.
rem   PRIORITY  The highest severity level, based on parameter severity values 
rem     as applied to the rule???s test. (HIGH=4, MEDIUM=3, LOW=2, INFORMATIONAL=1, CLEAR=0
rem   INITIAL_DATE  The date when this rule was first violated by this 
rem     particular target data.
rem   LAST_DATE The last time this row was tested.
CREATE TABLE MGMT_POLICY_VIOLATION_ROWS
(
  VIOLATION_ID RAW( &GUID_RAW_LENGTH ) NOT NULL,
  KEY VARCHAR2(1024),
  ROW_ID RAW( &GUID_RAW_LENGTH ) NOT NULL,
  IGNORED_FLAG VARCHAR2(&P_Y_N_LENGTH),
  IGNORED_BY VARCHAR2(&P_NAME_LENGTH),
  ROW_COMMENT VARCHAR2( &P_SHORT_DESCRIPTION_LENGTH ),
  PRIORITY NUMBER,
  INITIAL_DATE DATE,
  LAST_DATE DATE,
  CONSTRAINT MPVR_PK PRIMARY KEY(ROW_ID),
  CONSTRAINT MPVR_UK UNIQUE (VIOLATION_ID, KEY ) USING INDEX
      (CREATE UNIQUE INDEX MPVR_UK ON MGMT_POLICY_VIOLATION_ROWS
        (VIOLATION_ID, KEY ) COMPRESS 1),
  CONSTRAINT MPVR_MPV_FK FOREIGN KEY (VIOLATION_ID) 
      REFERENCES MGMT_POLICY_VIOLATIONS(VIOLATION_ID)
      ON DELETE CASCADE
) MONITORING;

rem PURPOSE
rem   For rules whose violations return column values, those values are stored 
rem   in this table.  The violation_id attribute associates each value with its 
rem   parent row entry in MGMT_POLICY_VIOLATIONS table.
rem COLUMNS
rem   ROW_ID        Uniquely identifies The row that his column value is part of.
rem           needed because a policy rule target, which is the key of the parent
rem           table (violation_id) can have more than one row, each has more than
rem           one column. This is also a FK pointing to the row record in
rem           MGMT_POLICY_VIOLATION_ROWS table.
rem   COLUMN_NAME   Identifies which column within the rule that this entry 
rem     represents.
rem   COLUMN_VALUE  The value of the column the last time this rule was tested.
CREATE TABLE MGMT_POLICY_VIOLATION_VALUES
(
  VIOLATION_ID RAW(&GUID_RAW_LENGTH) NOT NULL, 
  ROW_ID RAW(&GUID_RAW_LENGTH) NOT NULL,
  COLUMN_NAME VARCHAR2(&P_SQL_NAME_LENGTH) NOT NULL, 
  COLUMN_VALUE VARCHAR2(&P_VALUE_LENGTH), 
  CONSTRAINT MPVV_PK PRIMARY KEY(ROW_ID, COLUMN_NAME) USING INDEX
    (CREATE UNIQUE INDEX MPVV_PK ON MGMT_POLICY_VIOLATION_VALUES
      (ROW_ID, COLUMN_NAME) COMPRESS 1),
  CONSTRAINT MPVV_MPVR_FK FOREIGN KEY(ROW_ID) 
    REFERENCES MGMT_POLICY_VIOLATION_ROWS(ROW_ID) 
    ON DELETE CASCADE
)  MONITORING;


rem PURPOSE
rem   This table is used to associate a policy with snapshot's whose refresh will
rem   trigger policy evaluation for targets in this policy.  Using the LIKE operator,
rem   the paterns specified in the snapshot_type and target type are used to
rem   match with the snapshot being refreshed.
rem COLUMNS
rem   POLICY_ID     The ID of the policy
rem   SNAPSHOT_TYPE The type of the snapshot upon refresh of should trigger
rem                 policy evaluation.  
rem   TARGET_TYPE   The type of the target upon refresh of should trigger
rem                 policy evaluation.
CREATE TABLE MGMT_POLICY_SNAPSHOT_CRITERIA
(
  POLICY_ID RAW(&GUID_RAW_LENGTH) NOT NULL,
  SNAPSHOT_TYPE VARCHAR2(&P_SNAPSHOT_TYPE_LENGTH),
  TARGET_TYPE VARCHAR2(&P_TARGET_TYPE_LENGTH),
  CONSTRAINT MPSC_MP_FK FOREIGN KEY( POLICY_ID )
    REFERENCES MGMT_POLICY_GROUP( POLICY_ID ) ON DELETE CASCADE,
  CONSTRAINT MPSC_UK UNIQUE(POLICY_ID, SNAPSHOT_TYPE, 
    TARGET_TYPE)     
) MONITORING;


CREATE TABLE MGMT_POLICY_ERRORS 
(
  OPERATION VARCHAR2(128), 
  DATA VARCHAR2(2000),
  RULE_ID RAW(16), 
  POLICY_ID RAW(16), 
  ERR_TIME DATE, 
  TARGET_GUID RAW(16), 
  ERROR VARCHAR2(2000)
) MONITORING;

--tbd:Remove the V2(10.2GC) policy tables.