Rem Rem $Header: sdk_credential_pkgdef.sql 03-oct-2007.22:11:26 smalathe Exp $ Rem Rem sdk_credential_pkgdef.sql Rem Rem Copyright (c) 2002, 2007, Oracle. All rights reserved. Rem Rem NAME Rem sdk_credential_pkgdef.sql - Rem Rem DESCRIPTION Rem This package manages the MGMT_CREDENTIALS table Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem smalathe 10/03/07 - Bug 6459937: Handle non-key value insert from Rem Console Rem smalathe 09/13/07 - Bug 5988432: Modify compute_associated_info Rem nqureshi 07/27/07 - add PASSWORD_PROMPT_STRING Rem nqureshi 07/28/07 - Backport nqureshi_bug-6282479 from main Rem lsatyapr 05/30/07 - Bug6060708,6031714 Get key col for job creds Rem lsatyapr 06/03/07 - Backport lsatyapr_bug_6060708a from main Rem nqureshi 04/18/07 - XbranchMerge kmanicka_pdp5 from main Rem neearora 07/25/06 - Backport neearora_bug-5186115 from main Rem kmanicka 05/18/06 - implement pdp Rem neearora 04/12/06 - modify insert_monitoring_credentials to accept Rem p_propogate_to_agent Rem skini 09/01/05 - make task name default to null Rem skini 08/04/05 - Add default target name Rem pshroff 07/13/05 - adding ProvisionContainerType Rem rzazueta 07/06/05 - Fix 4435559 Rem skini 06/20/05 - Deprecate set_ca_credentials Rem pkantawa 04/05/05 - Fix 4185432: Add delete_job_credentials Rem overloaded API Rem skini 04/15/05 - Make 40-style set public Rem skini 10/20/04 - Versioning changes Rem dsahrawa 10/07/04 - Rem skini 09/29/04 - Continue UDM creds impl Rem skini 09/29/04 - Continue UDM creds impl Rem skini 09/28/04 - APIs for UDM credentials Rem dsahrawa 09/23/04 - add set_nested_job_cred_info Rem pshishir 07/15/04 - Modified delete_job_credentials and create delete_ca_credentials. Rem pshishir 07/15/04 - Modified set_job_credentials and create set_ca_credentials. Rem pshishir 07/15/04 - add set_credentials - generic to job and CA. Rem skini 07/26/04 - Add support credential set column values Rem gan 07/06/04 - type meta ver for set Rem skini 06/15/04 - Add set API that takes metaver Rem skini 12/29/03 - Handle overrides for clusters Rem kchiasso 11/14/03 - bugfix 3228611 Rem skini 09/22/03 - Fix trigger issues Rem skini 09/21/03 - Propogation Rem skini 09/20/03 - Propogation Rem skini 09/18/03 - Compute dependent information Rem skini 09/04/03 - Add get job credentials Rem skini 09/02/03 - Remove cluster table Rem skini 08/21/03 - Add defaults to delete procs Rem skini 08/19/03 - Add delete APIs Rem skini 08/18/03 - Do not associate container_type with container Rem skini 08/15/03 - Add set_context Rem skini 08/14/03 - Changes to credential set definition Rem skini 07/10/03 - Schema changes Rem skini 06/09/03 - Define constants for container type, delete APIs Rem skini 05/20/03 - Add new credentials functionality Rem skini 07/02/02 - Add change_password API Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/15/02 - Restructured. rem skini 03/01/02 - Support for monitoring credentials rem mgoodric 02/12/02 - Allow global credentials rem skini 01/10/02 - Encrypt credentials rem skini 12/26/01 - Add container column in credentials table rem skini 10/18/01 - drop credential_type column Rem CREATE OR REPLACE PACKAGE MGMT_CREDENTIAL AS -- For now, we use a constant encryption key. Eventually, we -- will want to generate it at install time.. ENCR_KEY CONSTANT VARCHAR2(32) := '8F8AAD081BD05297E034080020C01F0E'; SYSTEM_USER CONSTANT VARCHAR2(32) := ''; DEFAULT_CONTAINER CONSTANT VARCHAR2(32) := '<0>'; DEFAULT_TARGET_NAME CONSTANT VARCHAR2(32) := '<0>'; -- For now, we use a constant GUID key for global credentials. -- Eventually, we will want to allow null values for target_guid. DEFAULT_GUID CONSTANT VARCHAR2(32) := '00000000000000000000000000000000'; -- Possible values for credential set "usage" MONITORING_SET_USAGE CONSTANT VARCHAR2(32) := 'MONITORING'; PREFCRED_SET_USAGE CONSTANT VARCHAR2(32) := 'PREFERRED_CRED'; SYSTEM_SET_USAGE CONSTANT VARCHAR2(32) := 'SYSTEM'; -- Possible values for the credential set "context-type" TARGET_SET_CONTEXT_TYPE CONSTANT VARCHAR2(32) := 'TARGET'; CONTAINER_SET_CONTEXT_TYPE CONSTANT VARCHAR2(32) := 'CONTAINER'; COLLECTION_SET_CONTEXT_TYPE CONSTANT VARCHAR2(32) := 'COLLECTION'; -- Possible values for container type CONTAINER_TYPE_ORACLEHOME CONSTANT VARCHAR2(32) := 'OracleHome'; CONTAINER_TYPE_APPLTOP CONSTANT VARCHAR2(32) := 'Appltop'; CONTAINER_TYPE_PROVISION CONSTANT VARCHAR2(32) := 'ProvisioningAssignment'; -- Constants for the names of 4.0-style credential sets, used -- for backward-compatibility CRED_SET_40_TARGET_USER CONSTANT VARCHAR2(32) := '_4.0_CREDS_TARGET_USER_'; CRED_SET_40_TARGET_SYSTEM CONSTANT VARCHAR2(32) := '_4.0_CREDS_TARGET_SYSTEM_'; CRED_SET_40_CONTAINER_USER CONSTANT VARCHAR2(32) := '_4.0_CREDS_CONTAINER_USER_'; CRED_SET_40_CONTAINER_SYSTEM CONSTANT VARCHAR2(32) := '_4.0_CREDS_CONTAINER_SYSTEM_'; -- Possible values of object type in calls to set_collection_template_creds() OBJECT_TYPE_TEMPLATE CONSTANT NUMBER(1) := 1; OBJECT_TYPE_TEMPLATE_COPY CONSTANT NUMBER(1) := 2; PDP_SUDO CONSTANT VARCHAR2(64) := 'SUDO'; PDP_POWERBROKER CONSTANT VARCHAR2(64) := 'POWERBROKER'; PDP_COLUMN_RUNAS CONSTANT VARCHAR2(64) := 'RUNAS'; PDP_COLUMN_PROFILE CONSTANT VARCHAR2(64) := 'PROFILE'; PDP_SETTING_REQUIRED CONSTANT NUMBER(1) := 1; PDP_SETTING_NOT_REQUIRED CONSTANT NUMBER(1) := 0; PDP_SUDO_SETTING CONSTANT VARCHAR2(64) := 'SETTINGS'; PDP_SUDO_PROMPT CONSTANT VARCHAR2(64) := 'PDP_SUDO_PROMPTFORPASSWORD'; PDP_POWERBROKER_SETTING CONSTANT VARCHAR2(64) := 'SETTINGS'; PDP_POWERBROKER_PROMPT CONSTANT VARCHAR2(64) := 'PDP_POWERBROKER_PROMPTFORPASSWORD'; PDP_PASSWORD_PROMPT_STRING VARCHAR2(64) := 'PASSWORD_PROMPT_STRING'; PDP_PARAM_COMMAND CONSTANT VARCHAR2(64) := '%command%'; PDP_PARAM_USERNAME CONSTANT VARCHAR2(64) := '%USERNAME%'; PDP_PARAM_PASSWORD CONSTANT VARCHAR2(64) := '%PASSWORD%'; PDP_PARAM_RUNAS CONSTANT VARCHAR2(64) := '%RUNAS%'; PDP_PARAM_PROFILE CONSTANT VARCHAR2(64) := '%PROFILE%'; PDP_ROW_SEPERATOR CONSTANT VARCHAR2(32) := ':'; PDP_ROW_NAME_VALUE_SEPERATOR CONSTANT VARCHAR2(32) := '='; PDP_USERNAME_SEPERATOR CONSTANT VARCHAR2(32) := '$'; -- -- Create a new credential type. Will fail if the type is already present, -- with a UNIQUE CONSTRAINT violation -- -- p_target_type The target type of the credential type -- p_type_name The name of the new type -- p_description A description of the new type -- p_refs References, if any -- p_parent_type_name The parent type name, if applicablr -- p_type_columns The type column -- PROCEDURE create_credential_type(p_target_type VARCHAR2, p_type_name VARCHAR2, p_target_type_meta_ver VARCHAR2, p_type_display_name VARCHAR2, p_type_display_nlsid VARCHAR2, p_description VARCHAR2, p_refs MGMT_CRED_TYPE_REF_ARRAY, p_type_columns MGMT_CRED_TYPE_COL_ARRAY); -- -- Delete the specified credential type. -- -- p_target_type The target type -- p_type_name The credential type name -- PROCEDURE delete_credential_type (p_target_type VARCHAR2, p_type_name VARCHAR2); -- -- Create a new credential set. Will fail if the set is already present, -- with a UNIQUE CONSTRAINT violation -- -- p_target_type The target type of the credential type -- p_target_type_meta_ver The meta version for the target type -- p_set_name Name of the set -- p_set_usage The usage of the new set -- p_set_context_type The context type of the new set -- p_set_context The context of the new set -- p_type_name The credential type of the set -- p_set_columns The credential set columns -- PROCEDURE create_credential_set(p_target_type VARCHAR2, p_target_type_meta_ver VARCHAR2, p_set_name VARCHAR2, p_set_usage VARCHAR2, p_set_context_type VARCHAR2, p_set_context VARCHAR2, p_set_display_name VARCHAR2, p_set_display_nlsid VARCHAR2, p_type_name VARCHAR2, p_set_columns MGMT_CRED_SET_COL_ARRAY); -- -- This api is deprecated. Use the above one. -- Create a new credential set. Will fail if the set is already present, -- with a UNIQUE CONSTRAINT violation -- -- p_target_type The target type of the credential type -- p_set_usage The usage of the new set -- p_set_context_type The context type of the new set -- p_set_context The context of the new set -- p_set_type The type of the set. Must be one of MONITORING, PREF_CREDS -- or APP -- p_type_name The credential type of the set -- p_set_columns The credential set columns -- PROCEDURE create_credential_set(p_target_type VARCHAR2, p_set_name VARCHAR2, p_set_usage VARCHAR2, p_set_context_type VARCHAR2, p_set_context VARCHAR2, p_set_display_name VARCHAR2, p_set_display_nlsid VARCHAR2, p_type_name VARCHAR2, p_set_columns MGMT_CRED_SET_COL_ARRAY); -- -- Delete the specified credential set -- -- p_target_type The target type -- p_set_name The credential set name -- PROCEDURE delete_credential_set (p_target_type VARCHAR2, p_set_name VARCHAR2); -- -- Create a new pdp type. Will fail if the type is already present, -- with a UNIQUE CONSTRAINT violation -- -- p_pdp_typeName of the pdp Type -- p_credential_type The credential type -- p_pdp_columnsArray ofpdp columns which -- p_pdp_prameters Array of pdp parameters -- p_pdp_properties Array of pdp properties -- PROCEDURE create_pdp_type(p_pdp MGMT_PDP_META); -- -- Return list of pdps present in the present in EM -- FUNCTION get_pdp_types RETURN MGMT_PDP_META_ARRAY; -- Delete target credentials PROCEDURE delete_target_credentials(p_credential_set_name VARCHAR2, p_target_name VARCHAR2, p_target_type VARCHAR2, p_user_name VARCHAR2 DEFAULT NULL); -- Delete host-level credentials PROCEDURE delete_host_credentials(p_credential_set_name VARCHAR2, p_host_name VARCHAR2, p_target_type VARCHAR2, p_user_name VARCHAR2 DEFAULT NULL); -- Delete container-level credentials PROCEDURE delete_container_credentials(p_credential_set_name VARCHAR2, p_host_name VARCHAR2, p_container_location VARCHAR2, p_user_name VARCHAR2 DEFAULT NULL); -- Delete collection credentials PROCEDURE delete_collection_credentials(p_credential_set_name VARCHAR2, p_target_name VARCHAR2, p_target_type VARCHAR2, p_metric_name VARCHAR2, p_metric_column VARCHAR2, p_collection_name VARCHAR2); -- Delete collection credentials associated with templates or copies PROCEDURE delete_coll_template_creds(p_credential_set_name VARCHAR2, p_object_guid RAW, p_object_type NUMBER, p_target_name VARCHAR2, p_target_type VARCHAR2, p_metric_name VARCHAR2, p_metric_column VARCHAR2, p_collection_name VARCHAR2); -- Delete enterprise-level credentials PROCEDURE delete_enterprise_credentials(p_credential_set_name VARCHAR2, p_target_type VARCHAR2, p_user_name VARCHAR2 DEFAULT NULL); -- Delete overridden job credentials for the specified job PROCEDURE delete_job_credentials(p_job_name VARCHAR2, p_job_owner VARCHAR2, p_is_library NUMBER); -- Delete overridden job credentials for the specified job PROCEDURE delete_job_credentials(p_job_id RAW); -- Delete overridden ca credentials for the specified job PROCEDURE delete_ca_credentials(p_job_id RAW); -- Insert/update a set of target credentials. -- The credential columns are updated if the rows already exist PROCEDURE set_target_credentials(p_credentials IN MGMT_TARGET_CRED_ARRAY); -- Insert/update a set of monitoring target credentials -- If propogate is set to true, then propogate them to the agent PROCEDURE set_monitoring_credentials(p_credentials MGMT_TARGET_CRED_ARRAY, p_propogate BOOLEAN); -- Insert/update a set of credentials for all targets of a specified -- type on a host PROCEDURE set_host_credentials(p_credentials IN MGMT_HOST_CRED_ARRAY); -- Insert/update a set of credentials for all targets of a specified -- type in the enterprise PROCEDURE set_enterprise_credentials(p_credentials IN MGMT_ENTERPRISE_CRED_ARRAY); -- Insert/update a set of overridden credentials for a job PROCEDURE set_job_credentials(p_job_name VARCHAR2, p_owner VARCHAR2, p_is_library NUMBER, p_credentials IN MGMT_JOB_CRED_ARRAY); -- Insert/update a set of overridden credentials for a job or corrective action PROCEDURE set_job_credentials(p_job_id RAW, p_credentials IN MGMT_JOB_CRED_ARRAY); -- Set nested job credential info PROCEDURE set_nested_job_cred_info(p_job_type_id RAW, p_nested_job_name VARCHAR2, p_credentials IN MGMT_JOB_CRED_ARRAY); -- Insert/update a set of overridden credentials for a CA -- DEPRECATED: Use set_job_credentials(job_id) instead PROCEDURE set_ca_credentials(p_job_id RAW, p_credentials IN MGMT_JOB_CRED_ARRAY); -- Insert/update a set of container credentials for a job PROCEDURE set_container_credentials(p_credentials IN MGMT_CONTAINER_CRED_ARRAY); -- Insert/update a set of collection credentials PROCEDURE set_collection_credentials(p_credentials IN MGMT_COLLECTION_CRED_ARRAY); -- Insert/update a set of collection credentials for a template or -- template copy -- p_object_guid is the template or template copy guid -- p_object_type is one of (OBJECT_TYPE_TEMPLATE, OBJECT_TYPE_TEMPLATE_COPY) PROCEDURE set_collection_template_creds(p_credentials IN MGMT_COLLECTION_CRED_ARRAY, p_object_guid RAW, p_object_type NUMBER); -- Update object guid of collection credentials for a template or -- template copy for a given target -- p_object_guid_old is the old template or template copy guid -- p_object_guid_new is the new template or template copy guid -- p_object_type is one of (OBJECT_TYPE_TEMPLATE, OBJECT_TYPE_TEMPLATE_COPY) -- p_target_guid is the target guid PROCEDURE upd_collection_template_creds(p_object_guid_old RAW, p_object_guid_new RAW, p_object_type NUMBER, p_target_guid RAW); -- Insert/update ARU credentials PROCEDURE set_aru_credentials(p_username VARCHAR2, p_password VARCHAR2); -- Delete ARU credentials PROCEDURE delete_aru_credentials; -- Apply all the collection credentials associated with the specified template -- copy to the specified target PROCEDURE apply_template_copy_coll_creds(p_template_guid RAW, p_target_guid RAW); -- -- Return all the credential columns for the specified credential -- set, in the context of the specified job. Note that all storage -- levels for the specified set are searched, starting at the -- job level -- -- Note can only called by user SYSMAN -- FUNCTION get_job_creds_default(p_job_id RAW, p_job_owner VARCHAR2, p_credential_set VARCHAR2, p_target_type VARCHAR2, p_target_guid RAW, p_container_location VARCHAR2, p_instance_guid RAW DEFAULT NULL, p_instance_type VARCHAR2 DEFAULT NULL, p_job_type_id RAW DEFAULT NULL, p_task_name VARCHAR2 DEFAULT NULL) RETURN MGMT_CRED_RECORD; -- -- Return all the credential columns for the specified credential -- set, given the specified target. Note that all storage -- levels for the specified set are searched -- -- Note Can only be called by user SYSMAN -- FUNCTION get_target_creds_default(p_credential_set VARCHAR2, p_target_name VARCHAR2, p_target_type VARCHAR2, p_user_name VARCHAR2) RETURN MGMT_CRED_RECORD; -- Change all stored credentials for the specified credential column(s) -- for the specified target, given the set of keys. -- p_credential_type The credential type to use. The type must be a "base" -- type, ie, it cannot refer to other types. -- p_key_column The key column for the credential type -- (type column name/value) -- p_non_key_cols The non-key columns to change (type column name/new value) -- p_old_values The old values for the non-key columns -- p_user_only If false, both system and user credentials are changed; -- by default, user credentials only. This argument is ignored when -- called by non-superusers: non-superusers can only update their -- own credentials -- p_propogate If true (1), the password change is propogated to all -- agents that have monitoring credentials using the specified type. PROCEDURE change_credential(p_target_name VARCHAR2, p_target_type VARCHAR2, p_credential_type VARCHAR2, p_key_column SMP_EMD_NVPAIR, p_non_key_cols SMP_EMD_NVPAIR_ARRAY, p_old_values SMP_EMD_STRING_ARRAY, p_user_only INTEGER DEFAULT 1, p_propogate NUMBER DEFAULT 0); ------------------ EM 4.0-style credentials ---------------------- ---------------- For backward compatibility only ----------------- ------ These sdk methods are obsolete, do not use them ----------- -- Insert credentials into the MGMT_CREDENTIALS table of -- the specified columns, target, container, and user. p_credentials is -- a name-value pair array that consists of -- (credential_column, credential_value) pairs. A NO_DATA_FOUND exception -- is thrown if the specified target does not exist. -- If the credentials are "system" credentials, p_user must -- be set to null. -- p_container_location is the full path of the container for the -- target. If the credentials you are inserting are not container- -- dependent, set p_container_location to null. -- If credentials are "global" credentials, p_target_name must -- be set to null. PROCEDURE insert_credentials(p_user IN VARCHAR2, p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_container_location IN VARCHAR2, p_credentials IN SMP_EMD_NVPAIR_ARRAY); -- Insert monitoring credentials for a target. Monitoring credentials -- are really target properties that are used by the emd to monitor -- the target, for collections, etc. PROCEDURE insert_monitoring_credentials(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_credentials IN SMP_EMD_NVPAIR_ARRAY, p_propogate_to_agent IN BOOLEAN DEFAULT FALSE); -- Return the decrypted version of the value stored in the -- MGMT_CREDENTIALS table with the specified credential_column, -- container_location, user and target. This method returns null if -- the specified row was not found. -- If the credentials are "system" credentials, p_user must -- be set to null. -- p_container_location is the full path of the container for the -- target. If the credentials you are inserting are not container- -- dependent, set p_container_location to null. -- If credentials are "global" credentials, p_target_guid must -- be set to null. FUNCTION get_value(p_credential_column IN VARCHAR2, p_user IN VARCHAR2, p_container_location IN VARCHAR2, p_target_guid IN mgmt_targets.target_guid%TYPE) RETURN VARCHAR2; -- Change the password for the specified host and user. This API -- comes with the following caveats: -- It assumes credential columns called OS_USERNAME and OS_PASSWORD. -- It assumes only one set of credentials for every host -- -- p_host_name is the name of the host whose credentials must be modified -- p_user_name is the host user name -- p_password is the host password PROCEDURE change_host_password(p_host_name IN VARCHAR2, p_host_user_name IN VARCHAR2, p_host_password IN VARCHAR2); -- Return all the overridden job credentials for the specified job FUNCTION get_job_credentials(p_job_id RAW) RETURN MGMT_JOB_CRED_ARRAY; -- Internal methods, used by triggers only PROCEDURE compute_associated_info(p_credential_guid RAW, p_target_guid RAW, p_set_name VARCHAR2, p_credential_set_column VARCHAR2, p_credential_value VARCHAR2, p_type_name_out OUT VARCHAR2, p_type_column_out OUT VARCHAR2, p_key_value_out OUT VARCHAR2, p_assoc_target_out OUT RAW); PROCEDURE post_process(p_updating IN BOOLEAN); FUNCTION is_console_insert RETURN BOOLEAN; FUNCTION is_no_recursion RETURN BOOLEAN; -- END Internal methods -- Returns true if the specified set name is a 4.0-style set FUNCTION is_40_style_set(p_set_name VARCHAR2) RETURN BOOLEAN; -- Get the name of the key column for the given credentials set and target type FUNCTION get_credential_set_key_column(p_credset VARCHAR2, p_target_type VARCHAR2) RETURN VARCHAR2; END MGMT_CREDENTIAL; / show errors;