Rem drv: <migrate type="schema_upgrade" version="10.2.0.4"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/10.2.0.4/credentials/credentials_schema_upgrade.sql /st_emcore_10.2.0.4.4db11.2.0.3/1 2011/04/07 18:16:45 rpinnama Exp $
Rem
Rem credentials_schema_upgrade.sql
Rem
Rem Copyright (c) 2006, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      credentials_schema_upgrade.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    rkpandey    02/26/09 - remove iot before adding column
Rem    dgiaimo     04/30/07 - Changing 10.2.4.0 to 10.2.0.4
Rem    nqureshi    04/23/07 - 
Rem    kmanicka    10/09/06 - varchar -> varchar2
Rem    nqureshi    09/12/06 - adding credentials table
Rem    nqureshi    09/08/06 - add is_required to pdp settings
Rem    kmanicka    07/03/06 - Created
Rem


ALTER TABLE MGMT_TARGET_CREDENTIALS ADD pdp_data VARCHAR2(512) DEFAULT NULL;
ALTER TABLE MGMT_HOST_CREDENTIALS ADD pdp_data VARCHAR2(512) DEFAULT NULL;
ALTER TABLE MGMT_ENTERPRISE_CREDENTIALS ADD pdp_data VARCHAR2(512) DEFAULT NULL;
@&EM_SQL_ROOT/core/10.2.0.4.2/credentials/credentials_tables.sql
ALTER TABLE MGMT_JOB_CREDENTIALS ADD pdp_data VARCHAR2(512) DEFAULT NULL;
ALTER TABLE MGMT_COLLECTION_CREDENTIALS ADD pdp_data VARCHAR2(512) DEFAULT NULL;
ALTER TABLE MGMT_COLLECTION_TEMPLATE_CREDS ADD pdp_data VARCHAR2(512) DEFAULT NULL;
ALTER TABLE MGMT_NESTED_JOB_CRED_INFO ADD pdp_data VARCHAR2(512) DEFAULT NULL;


REM
REM The MGMT_PDP_METADATA table contains metadata about a PDP (Privilege 
REM Delegation Provider) such as sudo or powerbroker. 
REM
REM pdp_guid                A unique guid to be used as the primary key 
REM pdp_name                The PDP name, for example sudo or powerbroker 
REM credential_type_name    The credential_type that this PDP attaches to, 
REM                         for example HostCreds 
CREATE TABLE MGMT_PDP_METADATA 
( 
    pdp_guid              RAW(16) NOT NULL, 
    pdp_name              VARCHAR2(64) NOT NULL, 
    credential_type_name  VARCHAR2(64) NOT NULL, 
    CONSTRAINT PK_MGMT_PDP_METADATA PRIMARY KEY(pdp_guid) 
) MONITORING;

REM
REM The MGMT_PDP_COLUMN_METADATA table contains additional metadata 
REM about a PDP, in particular the credential columns that this PDP 
REM adds to the Credential Type it is attached to. 
REM
REM pdp_guid         The PDP guid 
REM pdp_column_name  The credential column contributed to the 
REM                  credential type by the PDP,for example RunAs  
REM is_required      Is this column required. 
REM 
CREATE TABLE MGMT_PDP_COLUMN_METADATA 
( 
    pdp_guid        RAW(16) NOT NULL, 
    pdp_column_name VARCHAR2(64) NOT NULL, 
    is_required     NUMBER(1) DEFAULT 0, 
    CONSTRAINT PK_MGMT_PDP_CRED_METADATA PRIMARY KEY(pdp_guid, pdp_column_name), 
    CONSTRAINT FK_MGMT_PDP_CRED_METADATA FOREIGN KEY(pdp_guid) REFERENCES MGMT_PDP_METADATA(pdp_guid) ON DELETE CASCADE 
) MONITORING;


REM
REM The MGMT_PDP_SETTING_METADATA table contains additional metadata 
REM about a PDP, in particular the settings that can be used in named 
REM settings of this PDP. 
REM
REM pdp_guid             	The PDP guid 
REM pdp_setting_name     	The setting name, for example 'command' 
REM is_required                 Is this is a required setting
REM display_name_nlsid   	The NLS id of the display name 
REM display_name         	The default display Name 
REM hint_nlsid           	The NLS id of the Hint 
REM hint                 	The Default Hint 
REM
 
CREATE TABLE MGMT_PDP_SETTING_METADATA 
( 
    pdp_guid              RAW(16) NOT NULL, 
    pdp_setting_name      VARCHAR2(64) NOT NULL, 
    is_required           NUMBER(1) DEFAULT 0,
    display_name_nlsid    VARCHAR2(64) DEFAULT NULL,  
    display_name          VARCHAR2(64) DEFAULT NULL,  
    hint_nlsid            VARCHAR2(64) DEFAULT NULL,  
    hint                  VARCHAR2(256) DEFAULT NULL, 
    CONSTRAINT PK_MGMT_PDP_PROP_METADATA PRIMARY KEY(pdp_guid, pdp_setting_name), 
    CONSTRAINT FK_MGMT_PDP_PROP_METADATA FOREIGN KEY(pdp_guid) REFERENCES MGMT_PDP_METADATA(pdp_guid) ON DELETE CASCADE 
) MONITORING; 

REM
REM The MGMT_PDP_PARAM_METADATA table contains additional metadata 
REM about a PDP, in particular the parameters or 'placeholders' that 
REM this PDP knows about. 
REM
REM pdp_guid        	The PDP guid 
REM pdp_param_name  	The parameter the PDP know about, for example command,runas  
REM description_nlsid   The NLS id of the description 
REM description         Description 
REM 

CREATE TABLE MGMT_PDP_PARAM_METADATA 
( 
    pdp_guid            	RAW(16) NOT NULL, 
    pdp_param_name      	VARCHAR2(64) NOT NULL, 
    description_nlsid   	VARCHAR2(64) DEFAULT NULL,  
    description         	VARCHAR2(256) DEFAULT NULL, 
    CONSTRAINT PK_MGMT_PDP_PARAM_METADATA PRIMARY KEY(pdp_guid, pdp_param_name), 
    CONSTRAINT FK_MGMT_PDP_PARAM_METADATA FOREIGN KEY(pdp_guid) REFERENCES MGMT_PDP_METADATA(pdp_guid) ON DELETE CASCADE 
) MONITORING; 



Rem
Rem PURPOSE
Rem   Stores the pdp settings of a host. This table will not contain an entry if the target does not have any settings.
Rem
Rem COLUMNS
Rem   target_guid       target guid of the host
Rem   pdp_type          The Pdp type
Rem   pdp_setting_name    EMPDP_SUDO_SETTINGS / EMPDP_PBRUN_SETTINGS
Rem   pdp_setting_value   the value of the setting /bin/sudo
Rem
CREATE TABLE MGMT_PDP_HOST_SETTING
(
     target_guid        RAW(16)     NOT NULL,
     pdp_type             VARCHAR2(256) DEFAULT NULL,
     pdp_setting_name     VARCHAR2(256) DEFAULT NULL,
     pdp_setting_value    VARCHAR2(256) DEFAULT NULL,
     CONSTRAINT PK_MGMT_PDP_HOST_SETTING
                               PRIMARY KEY (target_guid, pdp_type, pdp_setting_name)
) MONITORING;

REM
REM The MGMT_PDP_SETTINGS table contains information about
REM named PDP settings that can be applied to targets.

REM
REM setting_guid        The Setting guid
REM setting_name        The name of this PDP setting, for example 'DefaultSudoTemplate'
REM pdp_type      Type of pdp setting sudo / pbrun

REM created_by    The User who created the PDP Setting

REM last_modified       Time when the Setting was Last Modified
REM

CREATE TABLE MGMT_PDP_SETTINGS
(
    setting_guid               RAW(16) NOT NULL,
    setting_name               VARCHAR2(64) NOT NULL,
    pdp_type                   VARCHAR2(64) NOT NULL,
    created_by                 VARCHAR2(256),
    last_modified              DATE,
    is_enabled                 NUMBER(1),
    CONSTRAINT PK_MGMT_PDP_TEMPLATES PRIMARY KEY(setting_name,pdp_type)
);


REM
REM The MGMT_PDP_SETTING_VALUES table contains information about
REM named PDP settings that can be applied to targets.
REM setting_guid        The setting guid
REM value_name    The name of this PDP setting, for example 'DefaultSudoTemplate'
REM value   Value of the property, for example 'sudo -u %runas% %command%', 'yes'
REM

CREATE TABLE MGMT_PDP_SETTING_VALUES
(
    setting_guid        RAW(16),
    value_name   VARCHAR2(64),
    value   VARCHAR2(64)
);

rem
rem MGMT_UPDATE_PDP_DATA_MAP
rem
rem PURPOSE
rem   Stores the mapping of OPERATION_GUID to setting_guid.
rem
rem COLUMNS
rem   OPERATION_GUID - Identifies data for a particular data propagation job
rem   setting_guid - GUID of the setting
rem   applied_by name os the user apllied the setting
rem
CREATE TABLE MGMT_UPDATE_PDP_DATA_MAP
    (
     operation_guid           RAW(16)          NOT NULL,
     setting_guid             RAW(16)          NOT NULL,
     applied_by               VARCHAR2(256)    NOT NULL
    ) MONITORING;

rem
rem MGMT_UPDATE_PDP_DATA_COPY
rem
rem PURPOSE
rem   Stores update data for the apply PDP operation.
rem
rem COLUMNS
rem   DATA_SET_GUID   - Identifies update data for a particular update
rem                     job; used to join this table with
rem                     MGMT_UPDATE_OPERATIONS_DATA
rem  setting_name     - name of the setting (command/prompt_for_password etc)
rem  setting_value     - actual value of the setting
CREATE TABLE MGMT_UPDATE_PDP_DATA_COPY
    (operation_guid           RAW(16)          NOT NULL,
     setting_name            VARCHAR2(64)     NOT NULL,
     setting_value           VARCHAR2(64)
    ) MONITORING;



-- ALTER MGMT_CRED_RECORD
ALTER TYPE MGMT_CRED_RECORD ADD ATTRIBUTE (pdp_data VARCHAR2(512)) CASCADE;
ALTER TYPE MGMT_CRED_RECORD ADD 
CONSTRUCTOR FUNCTION MGMT_CRED_RECORD(p_user_name VARCHAR2,
                                          p_credential_set_name VARCHAR2,
                                          p_creds MGMT_CRED_ROW_ARRAY
                                          )
    RETURN SELF AS RESULT CASCADE;
ALTER TYPE MGMT_CRED_RECORD ADD
    STATIC FUNCTION NEW(p_user_name VARCHAR2,
                        p_credential_set_name VARCHAR2,
                        p_creds MGMT_CRED_ROW_ARRAY,
                        p_pdp_data VARCHAR2 DEFAULT NULL
                        )
    RETURN MGMT_CRED_RECORD CASCADE;


ALTER TYPE MGMT_TARGET_CRED_RECORD ADD
    STATIC FUNCTION NEW(p_target_name VARCHAR2,
                        p_target_type VARCHAR2,
                        p_credential MGMT_CRED_RECORD
                        )
    RETURN MGMT_TARGET_CRED_RECORD CASCADE;

ALTER TYPE MGMT_CLUSTER_CRED_RECORD ADD
    STATIC FUNCTION NEW(p_cluster_name VARCHAR2,
                        p_cluster_type VARCHAR2,
                        p_target_type VARCHAR2,
                        p_credential MGMT_CRED_RECORD
                        )
    RETURN MGMT_CLUSTER_CRED_RECORD CASCADE;


ALTER TYPE MGMT_HOST_CRED_RECORD ADD
    STATIC FUNCTION NEW(p_host_name VARCHAR2,
                        p_target_type VARCHAR2,
                        p_credential MGMT_CRED_RECORD
                        )
    RETURN MGMT_HOST_CRED_RECORD CASCADE;


ALTER TYPE MGMT_ENTERPRISE_CRED_RECORD ADD
    STATIC FUNCTION NEW(p_target_type VARCHAR2,
                        p_credential MGMT_CRED_RECORD
                        )
    RETURN MGMT_ENTERPRISE_CRED_RECORD CASCADE;

ALTER TYPE MGMT_JOB_CRED_RECORD ADD
    STATIC FUNCTION NEW(p_target_name VARCHAR2,
                        p_target_type VARCHAR2,
                        p_container_location VARCHAR2,
                        p_credential MGMT_CRED_RECORD
                        )
    RETURN MGMT_JOB_CRED_RECORD CASCADE;

ALTER TYPE MGMT_CONTAINER_CRED_RECORD ADD
    STATIC FUNCTION NEW(p_target_name VARCHAR2,
                        p_target_type VARCHAR2,
                        p_container_location VARCHAR2,
                        p_credential MGMT_CRED_RECORD
                        )
    RETURN MGMT_CONTAINER_CRED_RECORD CASCADE;

ALTER TYPE MGMT_COLLECTION_CRED_RECORD ADD
    STATIC FUNCTION NEW(p_target_name VARCHAR2,
                        p_target_type VARCHAR2,
                        p_metric_name VARCHAR2,
                        p_metric_column VARCHAR2,
                        p_collection_name VARCHAR2,
                        p_credential MGMT_CRED_RECORD
                        )
    RETURN MGMT_COLLECTION_CRED_RECORD CASCADE;

CREATE OR REPLACE TYPE MGMT_PDP_COLUMN_META AS OBJECT (
    pdp_column_name  VARCHAR2(64),
    is_required  NUMBER(1),
    STATIC FUNCTION NEW(p_pdp_column_name VARCHAR2,
                        p_is_required NUMBER
                        )
    RETURN MGMT_PDP_COLUMN_META                
);
/

CREATE OR REPLACE TYPE MGMT_PDP_COLUMN_META_ARRAY AS TABLE OF MGMT_PDP_COLUMN_META;
/

CREATE OR REPLACE TYPE MGMT_PDP_SETTING_META AS OBJECT (
    pdp_setting_name    VARCHAR2(64),
    is_required         NUMBER(1),
    display_name_nlsid  VARCHAR2(64),
    display_name        VARCHAR2(64),
    hint_nlsid          VARCHAR2(64),
    hint                VARCHAR2(264),
    STATIC FUNCTION NEW(p_pdp_setting_name VARCHAR2,
                        p_is_required NUMBER,
                        p_display_name_nlsid VARCHAR2,
                        p_display_name VARCHAR2,
                        p_hint_nlsid VARCHAR2,
                        p_hint VARCHAR2
                        )
    RETURN MGMT_PDP_SETTING_META                
);
/

CREATE OR REPLACE TYPE MGMT_PDP_SETTING_META_ARRAY AS TABLE OF MGMT_PDP_SETTING_META;
/

CREATE OR REPLACE TYPE MGMT_PDP_PARAM_META AS OBJECT (
    pdp_param_name      VARCHAR2(64),
    description_nlsid   VARCHAR2(64),
    description         VARCHAR2(256),
    STATIC FUNCTION NEW(p_pdp_param_name VARCHAR2,
                        p_description_nlsid VARCHAR2,
                        p_description VARCHAR2
                        )
    RETURN MGMT_PDP_PARAM_META    
);
/

CREATE OR REPLACE TYPE MGMT_PDP_PARAM_META_ARRAY AS TABLE OF MGMT_PDP_PARAM_META;
/

CREATE OR REPLACE TYPE MGMT_PDP_META AS OBJECT (
    pdp_name                VARCHAR2(64),
    credential_type_name    VARCHAR2(64),
    pdp_columns             MGMT_PDP_COLUMN_META_ARRAY,
    pdp_settings            MGMT_PDP_SETTING_META_ARRAY,
    pdp_params              MGMT_PDP_PARAM_META_ARRAY,
    STATIC FUNCTION NEW(p_pdp_name VARCHAR2,
                        p_credential_type_name VARCHAR2,
                        p_pdp_columns MGMT_PDP_COLUMN_META_ARRAY,
                        pdp_settings MGMT_PDP_SETTING_META_ARRAY,
                        pdp_params MGMT_PDP_PARAM_META_ARRAY
                        )
    RETURN MGMT_PDP_META    
);
/

CREATE OR REPLACE TYPE MGMT_PDP_META_ARRAY AS TABLE OF MGMT_PDP_META;
/

CREATE OR REPLACE TYPE MGMT_PDP_ACTUAL_SETTING AS OBJECT (
pdp_value_name           VARCHAR2(256),
pdp_value                VARCHAR2(256)
);
/

CREATE OR REPLACE TYPE MGMT_PDP_ACTUAL_SETTING_ARRAY AS TABLE OF MGMT_PDP_ACTUAL_SETTING;
/