Rem $Header: sdk_udp_pkgdef.sql 06-sep-2006.04:34:53 groyal Exp $
Rem
Rem sdk_udp_pkgdef.sql
Rem
Rem Copyright (c) 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sdk_udp_pkgdef.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    groyal      09/05/06 - User-defined policy support
Rem    groyal      09/05/06 - Created
Rem

CREATE OR REPLACE PACKAGE mgmt_user_defined_policy AS

-- Categories
G_CATEGORY_FAULT             CONSTANT VARCHAR2(32) := mgmt_global.G_FUNC_CATEGORY_FAULT;         -- 'Fault'
G_CATEGORY_WORKLOAD_VOL      CONSTANT VARCHAR2(32) := mgmt_global.G_FUNC_CATEGORY_WORKLOAD_VOL;  -- 'Workload Volume'
G_CATEGORY_WORKLOAD_TYPE     CONSTANT VARCHAR2(32) := mgmt_global.G_FUNC_CATEGORY_WORKLOAD_TYPE; -- 'Workload Type'
G_CATEGORY_PERFORMANCE       CONSTANT VARCHAR2(32) := mgmt_global.G_FUNC_CATEGORY_PERFORMANCE;   -- 'Performance'
G_CATEGORY_CAPACITY          CONSTANT VARCHAR2(32) := mgmt_global.G_FUNC_CATEGORY_CAPACITY;      -- 'Capacity'
G_CATEGORY_CONFIGURATION     CONSTANT VARCHAR2(32) := mgmt_global.G_FUNC_CATEGORY_CONFIGURATION; -- 'Configuration'
G_CATEGORY_SECURITY          CONSTANT VARCHAR2(32) := mgmt_global.G_FUNC_CATEGORY_SECURITY;      -- 'Security'
G_CATEGORY_STORAGE           CONSTANT VARCHAR2(32) := mgmt_global.G_FUNC_CATEGORY_STORAGE;       -- 'Storage'
G_CATEGORY_UNCLASSIFIED      CONSTANT VARCHAR2(32) := mgmt_global.G_FUNC_CATEGORY_UNCLASSIFIED;  -- 'Unclassified'

-- Severity level for violation
G_SEVERITY_INFORMATIONAL     CONSTANT NUMBER(2) :=  mgmt_global.G_SEVERITY_INFORMATIONAL;
G_SEVERITY_WARNING           CONSTANT NUMBER(2) :=  mgmt_global.G_SEVERITY_WARNING;
G_SEVERITY_CRITICAL          CONSTANT NUMBER(2) :=  mgmt_global.G_SEVERITY_CRITICAL;
-- Target types
G_HOST_TARGET_TYPE           CONSTANT VARCHAR2(32) := mgmt_global.G_HOST_TARGET_TYPE;                 -- 'host';
G_DATABASE_TARGET_TYPE       CONSTANT VARCHAR2(32) := mgmt_global.G_DATABASE_TARGET_TYPE;             -- 'oracle_database';
G_LISTENER_TARGET_TYPE       CONSTANT VARCHAR2(32) := mgmt_global.G_LISTENER_TARGET_TYPE;             -- 'oracle_listener';
G_CLUSTER_TARGET_TYPE        CONSTANT VARCHAR2(32) := mgmt_global.G_CLUSTER_TARGET_TYPE;              -- 'cluster';
G_RAC_DATABASE_TARGET_TYPE   CONSTANT VARCHAR2(32) := mgmt_global.G_RAC_DATABASE_TARGET_TYPE;         -- 'rac_database';
G_REDUNDANCY_GROUP_TARGET_TYPE  CONSTANT VARCHAR2(32) := mgmt_global.G_REDUNDANCY_GROUP_TARGET_TYPE;  -- 'generic_redundancy_group';
G_COMPOSITE_TARGET_TYPE      CONSTANT VARCHAR2(32) := mgmt_global.G_COMPOSITE_TARGET_TYPE;            -- 'composite';
G_HOST_GROUP_TARGET_TYPE     CONSTANT VARCHAR2(32) := mgmt_global.G_HOST_GROUP_TARGET_TYPE;           -- 'host_group';
G_DATABASE_GROUP_TARGET_TYPE CONSTANT VARCHAR2(32) := mgmt_global.G_DATABASE_GROUP_TARGET_TYPE;       -- 'oracle_database_group';
G_IAS_TARGET_TYPE            CONSTANT VARCHAR2(32) := mgmt_global.G_IAS_TARGET_TYPE;                  -- 'oracle_ias';
G_WEBSITE_TARGET_TYPE        CONSTANT VARCHAR2(32) := mgmt_global.G_WEBSITE_TARGET_TYPE;              -- 'website';
G_FORMSAPP_TARGET_TYPE       CONSTANT VARCHAR2(32) := mgmt_global.G_FORMSAPP_TARGET_TYPE;             -- 'formsapp';
G_HTTP_SERVER_TARGET_TYPE    CONSTANT VARCHAR2(32) := mgmt_global.G_HTTP_SERVER_TARGET_TYPE;          -- 'oracle_apache';
G_WEBCACHE_TARGET_TYPE       CONSTANT VARCHAR2(32) := mgmt_global.G_WEBCACHE_TARGET_TYPE;             -- 'oracle_webcache';
G_OC4J_TARGET_TYPE           CONSTANT VARCHAR2(32) := mgmt_global.G_OC4J_TARGET_TYPE;                 -- 'oc4j';
G_BC4J_TARGET_TYPE           CONSTANT VARCHAR2(32) := mgmt_global.G_BC4J_TARGET_TYPE;                 -- 'oracle_bc4j';
G_LDAP_TARGET_TYPE           CONSTANT VARCHAR2(32) := mgmt_global.G_LDAP_TARGET_TYPE;                 -- 'oracle_ldap';
G_PORTAL_TARGET_TYPE         CONSTANT VARCHAR2(32) := mgmt_global.G_PORTAL_TARGET_TYPE;               -- 'oracle_portal';
G_APPLICATION_TARGET_TYPE    CONSTANT VARCHAR2(32) := mgmt_global.G_APPLICATION_TARGET_TYPE;          -- 'oracle_application';
G_APPS_SYSTEM_TARGET_TYPE    CONSTANT VARCHAR2(32) := mgmt_global.G_APPS_SYSTEM_TARGET_TYPE;          -- 'oracle_apps_system';
G_ASM_TARGET_TYPE            CONSTANT VARCHAR2(32) := mgmt_global.G_ASM_TARGET_TYPE;                  -- 'osm_instance';

-- Condition operators
G_THRESHOLD_EQ               CONSTANT NUMBER(1) := mgmt_global.G_THRESHOLD_EQ;        -- =
G_THRESHOLD_LT               CONSTANT NUMBER(1) := mgmt_global.G_THRESHOLD_LT;        -- <
G_THRESHOLD_GT               CONSTANT NUMBER(1) := mgmt_global.G_THRESHOLD_GT;        -- >
G_THRESHOLD_LE               CONSTANT NUMBER(1) := mgmt_global.G_THRESHOLD_LE;        -- <=
G_THRESHOLD_GE               CONSTANT NUMBER(1) := mgmt_global.G_THRESHOLD_GE;        -- >=
G_THRESHOLD_NE               CONSTANT NUMBER(1) := mgmt_global.G_THRESHOLD_NE;        -- !=
G_THRESHOLD_CONTAINS         CONSTANT NUMBER(1) := mgmt_global.G_THRESHOLD_CONTAINS;  -- contains string
G_THRESHOLD_MATCH            CONSTANT NUMBER(1) := mgmt_global.G_THRESHOLD_MATCH;     -- contains pattern

-- Parameter data types
G_PARAM_TYPE_NUMBER          CONSTANT NUMBER(2) := mgmt_policy.G_PARAM_TYPE_NUMBER;
G_PARAM_TYPE_STRING          CONSTANT NUMBER(2) := mgmt_policy.G_PARAM_TYPE_STRING;


TYPE UDP_PARAMETER IS RECORD
(
  param_name        mgmt_policy_parameters.param_name%TYPE,
  param_type        mgmt_policy_parameters.param_type%TYPE,
  threshold_value   mgmt_policy_assoc_cfg_params.crit_threshold%TYPE
);

TYPE UDP_PARAMETERS iS TABLE of UDP_PARAMETER;

-- -----------------------------------------------------------------------------
--
-- Procedure CREATE_POLICY
-- 
-- Description: Creates a user-defined policy where the policy's implementation
-- is sql based.
--
-- Parameters:
--  p_policy_name       - Name of the policy to be created
--
--  p_target_type       - Type of target to which this policy is applicable
--                        Example: 
--                          G_HOST_TARGET_TYPE (Host)  
--                          G_DATABASE_TARGET_TYPE (Oracle Database Instance)
--
--  p_sql_text          - SQL used to retrieve data to be tested from the Management 
--                        Repository
--
--                        Note: The specified SQL needs to satisfy the following 
--                        requirements.
--
--                        The first 'n' selected columns, where 'n' equals p_num_keys, 
--                        are columns that are used to uniquely identify a row.  The 
--                        SQL query must not return more than one row with the same
--                        key.  Furthermore, one of these key columns must be a 
--                        column called TARGET_GUID and contains the target guid of 
--                        the target to which the violation of the policy should be 
--                        associated with.
--
--                        The remaining selected columns, which there must be 
--                        at least one of, return the value(s) that is then 
--                        tested by the policy's test.  
--
--                        Example:
--                          SELECT target_guid, name, value
--                          FROM mgmt$db_init_params
--                          WHERE name =''O7_DICTIONARY_ACCESSIBILITY''
--
--  p_test              - Test to apply to the rows returned by p_sql_text in order   
--                        to identify violations of the policy.  The test can be any
--                        valid SQL expression.  It can also reference columns in the
--                        select list from p_sql_text and/or parameters specified
--                        in p_parameters.
--
--                        To reference columns in the select column list or a 
--                        parameter, simply prefix the name with a colon (:).                 
--                      
--                        Example:
--                          UPPER(NVL(:value, ''TRUE'')) = ''TRUE'' 
--
--  p_parameters        - Tuples containing a list of parameters and the
--                        default value to be used when evaluating the policy
-- 
--                        Example: 
--                          Assume a policy that ensures a sufficient number of 
--                          control files are used.  By default, the policy will 
--                          ensure more than one control file is used; however,
--                          through the use of a parameter, a user is able to 
--                          override this default with a value more appropriate for
--                          their environment.
--                        
--                          SQL
--                            SELECT target_guid, control_file_count
--                            FROM 
--                              (SELECT target_guid, COUNT(file_name) control_file_count
--                              FROM mgmt$db_controlfiles
--                              GROUP BY target_guid)
--
--                          Test (compares counts against parameter 'min_control_file_count')
--                            :control_file_count < :min_control_file_count
--
--                          To define this parameter one needs to declare
--                          p_parameter to be mgmt_user_defined_policy.parameter_tab%type
--                          and initialize it as follows:
--
--                          p_parameter.extend(1) ;
--                          p_parameter(1).param_name := 'min_control_file_count';
--                          p_parameter(1).param_type  := mgmt_user_defined_policy.G_PARAM_TYPE_NUMBER;
--                          p_parameter(1).threshold_value := 1;
--
--  p_num_keys          - Number of columns in the select list that are key 
--                        columns; that is, they uniquely identify a row returned
--                        by p_sql_text
--
--  p_description       - Contains descriptive text for the policy 
-- 
--  p_impact            - Provides text that examples why this policy is
--                        important
--
--  p_recommendation    - Contains information regarding how to bring a target
--                        back into compliance with the policy
--
--  p_severity_level    - Severity level of violation of the policy
--                        Valid values
--                          G_SEVERITY_INFORMATIONAL
--                          G_SEVERITY_WARNING
--                          G_SEVERITY_CRITICAL
--
--  p_category          - Policy category 
--                        Example:
--                          G_CATEGORY_SECURITY
--
--  p_url_link          - URL to be used for additional detailed information 
--                        regarding this policy
--
--  p_violation_message - Message recorded along with violation.  Used for 
--                        notifications, such as emails and paging, that happen 
--                        as a result of a detection of a new violation.
--
--                        It can reference columns in the select list from 
--                        p_sql_text and/or parameters specified in p_parameters.
--    
--                        To reference columns in the select column list or a 
--                        parameter, simply enclose the name with parentheses (%).                 
--
--                        Example:
--                        Insufficient control files: %control_file_count%
--
--  p_clear_message     - Message recorded with the clearing of a violation.  Used for 
--                        notifications, such as emails and paging, that happen 
--                        as a result of a violation clearing.
--
--                        It can reference columns in the select list from 
--                        p_sql_text and/or parameters specified in p_parameters.
--    
--                        To reference columns in the select column list or a 
--                        parameter, simply enclose the name with parentheses (%).                 
--
--                        Example:
--                        Sufficient control files: %control_file_count%
--
--  p_eval_interval     - Evaluation interval expression in number of hours
--
PROCEDURE create_policy
(
    p_policy_name           IN VARCHAR2,
    p_target_type           IN VARCHAR2,
    p_sql_text              IN VARCHAR2,
    p_test                  IN VARCHAR2,   
    p_parameters            IN UDP_PARAMETERS DEFAULT NULL,
    p_num_keys              IN NUMBER         DEFAULT 1,     
    p_description           IN VARCHAR2       DEFAULT ' ',
    p_impact                IN VARCHAR2       DEFAULT ' ',
    p_recommendation        IN VARCHAR2       DEFAULT ' ',
    p_severity_level        IN NUMBER         DEFAULT G_SEVERITY_INFORMATIONAL,
    p_category              IN VARCHAR2       DEFAULT G_CATEGORY_UNCLASSIFIED,
    p_url_link              IN VARCHAR2       DEFAULT NULL,
    p_violation_message     IN VARCHAR2       DEFAULT NULL,
    p_clear_message         IN VARCHAR2       DEFAULT NULL,
    p_eval_interval         IN NUMBER         DEFAULT 24    
);
    
-- -----------------------------------------------------------------------------    
--
-- Procedure CREATE_POLICY
-- 
-- Description: Creates a user-defined policy where the policy's implementation
-- is a simple threshold test.
--
-- Parameters:
--  p_policy_name         - Name of the policy to be created
--
--  p_target_type         - Type of target to which this policy is applicable
--                          Example: 
--                            G_HOST_TARGET_TYPE (Host)  
--                            G_DATABASE_TARGET_TYPE (Oracle Database Instance)
--
--  p_sql_text            - SQL used to retrieve data to be tested from the Management 
--                          Repository
--
--                          Note: The specified SQL needs to satisfy the following 
--                          requirements.
--
--                          The first 'n' selected columns, where 'n' equals p_num_keys, 
--                          are columns that are used to uniquely identify a row.  The 
--                          SQL query must not return more than one row with the same
--                          key.  Furthermore, one of these key columns must be a 
--                          column called TARGET_GUID and contains the target guid of 
--                          the target to which the violation of the policy should be 
--                          associated with.
--
--                          The remaining selected columns, which there must be at
--                          at least one of, return the value that is then 
--                          tested against the policy's p_test_operator and
--                          p_threshold_value.  
--
--                          Example:
--                            SELECT target_guid, name, value
--                            FROM mgmt$db_init_params
--                            WHERE name =''audit_sys_operations''
--
--  p_column_name         - Name of the column from the select list against to 
--                          compare against p_threshold_value.
--
--                          Example:
--                            value
--
--  p_test_operator       - Type of comparison to be performed
--                          Valid values:
--                            G_THRESHOLD_EQ
--                            G_THRESHOLD_NE
--                            G_THRESHOLD_LT
--                            G_THRESHOLD_LE
--                            G_THRESHOLD_GT 
--                            G_THRESHOLD_GE
--                            G_THRESHOLD_CONTAINS
--                            G_THRESHOLD_MATCH
--
--  p_threshold_value     - Value against which comparison is done
--
--  p_threshold_data_type - Data type of the threshold value. 
--                          Valid values
--                            G_PARAM_TYPE_NUMBER
--                            G_PARAM_TYPE_STRING
--
--  p_num_keys          - Number of columns in the select list that are key 
--                        columns; that is, they uniquely identify a row returned
--                        by p_sql_text
--
--  p_description       - Contains descriptive text for the policy 
-- 
--  p_impact            - Provides text that examples why this policy is
--                        important
--
--  p_recommendation    - Contains nformation regarding how to bring a target
--                        back into compliance with the policy
--
--  p_severity_level    - Severity level of violation of the policy
--                        Valid values
--                          G_SEVERITY_INFORMATIONAL
--                          G_SEVERITY_WARNING
--                          G_SEVERITY_CRITICAL
--
--  p_category          - Policy category 
--                        Example:
--                          G_CATEGORY_SECURITY
--
--  p_url_link          - URL to be used for additional detailed information 
--                        regarding this policy
--
--  p_violation_message - Message recorded along with violation.  Used for 
--                        notifications, such as emails and paging, that happen 
--                        as a result of a detection of a new violation.
--
--                        It can reference columns in the select list from 
--                        p_sql_text and/or parameters specified in p_parameters.
--    
--                        To reference columns in the select column list or a 
--                        parameter, simply enclose the name with parentheses (%).                 
--
--                        Example:
--                        Insufficient control files: %control_file_count%
--
--  p_clear_message     - Message recorded with clearing of violation.  Used for 
--                        notifications, such as emails and paging, that happen 
--                        as a result of a violation clearing.
--
--                        It can reference columns in the select list from 
--                        p_sql_text and/or parameters specified in p_parameters.
--    
--                        To reference columns in the select column list or a 
--                        parameter, simply enclose the name with parentheses (%).                 
--
--                        Example:
--                        Sufficient control files: %control_file_count%
--
--  p_eval_interval     - Evaluation interval expression in number of hours
--
PROCEDURE create_policy
(
    p_policy_name           IN VARCHAR2,
    p_target_type           IN VARCHAR2,
    p_sql_text              IN VARCHAR2,
    p_column_name           IN VARCHAR2,
    p_test_operator         IN VARCHAR2,
    p_threshold_value       IN VARCHAR2,
    p_threshold_data_type   IN NUMBER       DEFAULT G_PARAM_TYPE_NUMBER,
    p_num_keys              IN NUMBER       DEFAULT 1,
    p_description           IN VARCHAR2     DEFAULT ' ',
    p_impact                IN VARCHAR2     DEFAULT ' ',
    p_recommendation        IN VARCHAR2     DEFAULT ' ',
    p_severity_level        IN NUMBER       DEFAULT G_SEVERITY_INFORMATIONAL,
    p_category              IN VARCHAR2     DEFAULT G_CATEGORY_UNCLASSIFIED,
    p_url_link              IN VARCHAR2     DEFAULT NULL,
    p_violation_message     IN VARCHAR2     DEFAULT NULL,
    p_clear_message         IN VARCHAR2     DEFAULT NULL,
    p_eval_interval         IN NUMBER       DEFAULT 24
);

-- -----------------------------------------------------------------------------
--
-- Procedure DELETE_POLICY
-- 
-- Description: Deletes a user-defined policy.
--
-- Parameters:
--  p_policy_name         - Name of the policy to be created
--
--  p_target_type         - Type of target to which this policy is applicable
--                          Example: 
--                            G_HOST_TARGET_TYPE (Host)  
--                            G_DATABASE_TARGET_TYPE (Oracle Database Instance)
--
PROCEDURE delete_policy
(
    p_policy_name           IN VARCHAR2,
    p_target_type           IN VARCHAR2
);


-- -----------------------------------------------------------------------------
--
-- Procedure ADD_POLICY_TO_TARGET
-- 
-- Description: Associates a user-defined policy with a target.
--
-- Parameters:
--  p_policy_name         - Name of the policy to be added
--
--  p_target_type         - Type of target to which this policy is applicable
--                          Example: 
--                            G_HOST_TARGET_TYPE (Host)  
--                            G_DATABASE_TARGET_TYPE (Oracle Database Instance)
--
--  p_target_name         - Name of the target to add the policy to
--
PROCEDURE add_policy_to_target
(
    p_policy_name           IN VARCHAR2,
    p_target_type           IN VARCHAR2,
    p_target_name           IN VARCHAR2
);


--
-- Procedure REMOVE_POLICY_FROM_TARGET
-- 
-- Description: Removes the association of the user-defined policy with a target.
--
-- Parameters:
--  p_policy_name         - Name of the policy to be removed
--
--  p_target_type         - Type of target to which this policy is applicable
--                          Example: 
--                            G_HOST_TARGET_TYPE (Host)  
--                            G_DATABASE_TARGET_TYPE (Oracle Database Instance)
--
--  p_target_name         - Name of the target to remove the policy from
--
PROCEDURE remove_policy_from_target
(
    p_policy_name           IN VARCHAR2,
    p_target_type           IN VARCHAR2,
    p_target_name           IN VARCHAR2
 );

END mgmt_user_defined_policy;
/

SHOW ERRORS;