Rem Rem $Header: sdk_credential_pkgbody.sql 03-oct-2007.22:11:08 smalathe Exp $ Rem Rem sdk_credential_pkgbody.sql Rem Rem Copyright (c) 2002, 2007, Oracle. All rights reserved. Rem Rem NAME Rem sdk_credential_pkgbody.sql - Rem Rem DESCRIPTION Rem 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 sradhakr 08/11/07 - BLR backport of bug 5840623 to 10.2.0.4. 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/20/07 - Rem kmanicka 05/10/06 - implement pdp Rem kmanicka 05/10/06 - implement pdp Rem nqureshi 04/18/07 - XbranchMerge kmanicka_pdp5 from main Rem kmanicka 08/01/06 - Backport kmanicka_bug5336173 from main Rem neearora 07/25/06 - Backport neearora_bug-5186115 from main Rem skini 07/18/06 - Backport skini_bug-4890971 from main Rem neearora 04/12/06 - modify insert_monitoring_credentials to accept Rem p_propogate_to_agent and call Rem set_monitoring_credentials from Rem insert_monitoring_credentials Rem jgwang 12/14/05 - correct sdk_credential_pkgbody.sql Rem nqureshi 09/17/05 - Use job owner as the user for nested job Rem credentials Rem nqureshi 09/16/05 - Fix overridden credentials issue with MT jobs Rem dsahrawa 09/08/05 - allow SYS to run get_target_creds_default Rem skini 09/01/05 - make task name default to null Rem skini 08/28/05 - Get rid of nested job creds callback Rem skini 08/22/05 - Fix get_job_creds_default Rem skini 08/04/05 - Nested job creds no longer have target guid Rem rzazueta 07/06/05 - Fix 4435559 Rem pkantawa 06/07/05 - Exclude CAs from queries Rem dsahrawa 05/23/05 - fix for bug 4333625, override creds not picked up on Rem retry of mt jobs Rem rzazueta 05/18/05 - Fix generation of collection credential guid Rem jgwang 05/11/05 - adjust for removal of column from metric guid Rem computation for UDMs Rem skini 04/14/05 - Broken CA work Rem skini 04/14/05 - Broken creds callbacks Rem rzazueta 04/11/05 - Remove metric_column from metric_guid Rem pkantawa 04/05/05 - Fix 4185432: Add delete_job_credentials Rem overloaded API Rem skini 03/27/05 - Fix overridden OH creds Rem kmanicka 03/04/05 - move to EMDW_LOG from dbms_output Rem rzazueta 03/04/05 - Fix UDM creds template application Rem jgwang 01/26/05 - add support for UDM cred propagation Rem ramalhot 01/17/05 - g_member_guid->g_contains_guid Rem skini 12/29/04 - Fix column name Rem skini 10/20/04 - Versioning changes Rem dsahrawa 10/21/04 - insert NO_GUID instead of nulls for target_guid Rem in mgmt_nested_job_cred_info Rem dsahrawa 10/07/04 - resolve nested job override creds Rem skini 10/05/04 - Allow superusers to set credentials for other Rem users 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 skini 09/17/04 - Fix bug in set_job_credentials Rem ramalhot 08/25/04 - cutover to new assoc tables 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 dcawley 07/07/04 - Increase user name size Rem gan 07/06/04 - type meta ver for set Rem skini 06/15/04 - Add set API that takes metaver Rem skini 05/18/04 - Fix bug 3528216: monitoring credentials Rem skini 04/19/04 - Fix get_value() Rem skini 12/29/03 - Handle overrides for clusters Rem skini 12/07/03 - Fix outer join Rem skini 12/04/03 - Use type_meta_ver for refs Rem kchiasso 11/14/03 - bugfix 3228611 Rem skini 11/06/03 - Account for target type meta ver in queries Rem dsahrawa 10/29/03 - bug 3215373 Rem skini 09/25/03 - Perform updates for 4.0 style sets Rem skini 09/24/03 - Fix changePasswd query Rem skini 09/22/03 - Fix trigger issues Rem skini 09/21/03 - Creds propogation Rem skini 09/20/03 - Propogation Rem skini 09/18/03 - Compute dependent information Rem skini 09/03/03 - Encrypt aru credentials Rem skini 09/02/03 - Remove cluster table Rem skini 08/22/03 - Fix bug in get_cred_rows Rem skini 08/21/03 - unobfuscate credentials data Rem skini 08/19/03 - Validation Rem skini 08/18/03 - Do not associate container_type with container Rem skini 08/15/03 - Finish up SDK work Rem skini 08/14/03 - Changes to credential set definition Rem skini 07/09/03 - Credential 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 - Created Rem CREATE OR REPLACE PACKAGE BODY MGMT_CREDENTIAL AS -- When inserting into the MGMT_CREDENTIALS2 table, this indicates -- whether the insert is coming from the console or the agent s_console_insert BOOLEAN := false; -- The current credential guid, type name and key type column set -- when processing an update from the agent. s_current_cred_guid RAW(16) := null; s_current_cred_type_name MGMT_CREDENTIAL_TYPES.type_name%TYPE := null; s_current_cred_type_column MGMT_CREDENTIAL_TYPE_COLUMNS.type_column_name%TYPE := null; -- Avoid recursion in after insert / update trigger on MGMT_CREDENTIALS2 table s_no_recursion BOOLEAN := false; -- credential guid, key column name value list to be used in post_process s_new_cred_guid RAW(16) := null; s_cred_key_columns SMP_EMD_NVPAIR_ARRAY := null; -- Forward declarations PROCEDURE resolve_ref(p_target_type VARCHAR2, p_meta_ver VARCHAR2, p_ref_name VARCHAR2, p_ref_column_name VARCHAR2, p_type_name VARCHAR2, p_target_guid RAW, p_type_name_out OUT VARCHAR2, p_meta_ver_out OUT VARCHAR2, p_type_column_name_out OUT VARCHAR2, p_key_column_out OUT VARCHAR2, p_assoc_target_out OUT RAW); -- END Forward declarations FUNCTION get_container_location(p_container_location IN VARCHAR2) RETURN VARCHAR2 IS BEGIN IF p_container_location IS NULL THEN RETURN DEFAULT_CONTAINER; ELSE RETURN p_container_location; END IF; END; -- Procedures to generate credential GUIDs FUNCTION generate_target_cred_guid(p_target_guid RAW, p_cred_set_name VARCHAR2, p_username VARCHAR2) RETURN RAW IS BEGIN RETURN DBMS_OBFUSCATION_TOOLKIT.md5( input => UTL_RAW.cast_to_raw(p_target_guid || ';' || p_cred_set_name || ';' || UPPER(p_username))); END; -- Note : Bug 5336173 -- monitoring creds guid = MD5("target_type;target_name;cred_set_name") FUNCTION generate_monitoring_cred_guid(p_target_type VARCHAR2, p_target_name VARCHAR2, p_cred_set_name VARCHAR2) RETURN RAW IS BEGIN RETURN DBMS_OBFUSCATION_TOOLKIT.md5( input => UTL_RAW.cast_to_raw(p_target_type || ';' || p_target_name || ';' || p_cred_set_name)); END; FUNCTION generate_host_cred_guid(p_host_guid RAW, p_target_type VARCHAR2, p_cred_set_name VARCHAR2, p_username VARCHAR2) RETURN RAW IS BEGIN RETURN DBMS_OBFUSCATION_TOOLKIT.md5( input => UTL_RAW.cast_to_raw(p_host_guid || ';' || p_target_type || ';' || p_cred_set_name || ';' || UPPER(p_username))); END; FUNCTION generate_enterprise_cred_guid(p_target_type VARCHAR2, p_cred_set_name VARCHAR2, p_username VARCHAR2) RETURN RAW IS BEGIN RETURN DBMS_OBFUSCATION_TOOLKIT.md5( input => UTL_RAW.cast_to_raw(p_target_type || ';' || p_cred_set_name || ';' || UPPER(p_username))); END; FUNCTION generate_container_cred_guid(p_host_guid RAW, p_container_location VARCHAR2, p_cred_set_name VARCHAR2, p_username VARCHAR2) RETURN RAW IS BEGIN RETURN DBMS_OBFUSCATION_TOOLKIT.md5( input => UTL_RAW.cast_to_raw(p_host_guid || ';' || get_container_location(p_container_location) || ';' || p_cred_set_name || ';' || UPPER(p_username))); END; FUNCTION generate_collection_cred_guid(p_target_type VARCHAR2, p_target_name VARCHAR2, p_cred_set_name VARCHAR2, p_collection_name VARCHAR2, p_metric_name VARCHAR2) RETURN RAW IS BEGIN RETURN DBMS_OBFUSCATION_TOOLKIT.md5( input => UTL_RAW.cast_to_raw(p_target_type || ';' || p_target_name || ';' || p_cred_set_name || ';' || p_collection_name || ';' || p_metric_name) ); END; FUNCTION generate_collection_copy_guid(p_copy_guid RAW, p_target_guid RAW, p_metric_guid RAW, p_collection_name VARCHAR2, p_cred_set_name VARCHAR2) RETURN RAW IS BEGIN RETURN DBMS_OBFUSCATION_TOOLKIT.md5( input => UTL_RAW.cast_to_raw(p_copy_guid || ';' || p_target_guid || ';' || p_metric_guid || ';' || p_collection_name || ';' || p_cred_set_name || ';' || SYSTEM_USER) ); END; FUNCTION generate_job_cred_guid(p_job_id RAW, p_target_guid RAW, p_target_type VARCHAR2, p_container_location VARCHAR2, p_cred_set_name VARCHAR2, p_username VARCHAR2) RETURN RAW IS BEGIN RETURN DBMS_OBFUSCATION_TOOLKIT.md5( input => UTL_RAW.cast_to_raw(p_job_id || ';' || p_target_guid || ';' || p_target_type || ';' || get_container_location(p_container_location) || ';' || p_cred_set_name || ';' || UPPER(p_username))); END; FUNCTION generate_nested_job_cred_guid(p_job_type_id RAW, p_nested_job_name VARCHAR2, p_target_name VARCHAR2, p_target_type VARCHAR2, p_container_location VARCHAR2, p_cred_set_name VARCHAR2, p_username VARCHAR2) RETURN RAW IS begin RETURN DBMS_OBFUSCATION_TOOLKIT.md5( input => UTL_RAW.cast_to_raw(p_job_type_id || ';' || p_nested_job_name || ':' || p_target_name || ';' || p_target_type || ';' || get_container_location(p_container_location) || ';' || p_cred_set_name || ';' || UPPER(p_username))); END; -- Deal with predefined associations FUNCTION get_predefined_assoc(p_target_guid RAW, p_association VARCHAR2) RETURN RAW IS l_assoc_guid MGMT_TARGETS.target_guid%TYPE; BEGIN IF p_association IS NULL OR p_association=MGMT_TARGET.ASSOC_SELF THEN RETURN p_target_guid; END IF; IF p_association=MGMT_TARGET.ASSOC_HOST THEN SELECT host.target_guid INTO l_assoc_guid FROM MGMT_TARGETS t, MGMT_TARGETS host WHERE t.target_guid=p_target_guid AND t.host_name=host.target_name AND host.target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE; ELSIF p_association=MGMT_TARGET.ASSOC_AGENT THEN SELECT agent.target_guid INTO l_assoc_guid FROM MGMT_TARGETS t, MGMT_TARGETS agent WHERE t.target_guid=p_target_guid AND t.emd_url=agent.emd_url AND agent.target_type=MGMT_GLOBAL.G_AGENT_TARGET_TYPE; ELSIF p_association=MGMT_TARGET.ASSOC_PARENT THEN -- TODO Not supported? RETURN null; ELSE RETURN null; END IF; RETURN l_assoc_guid; END; -- Resolve the specified reference. Return the resolved type -- in p_type_name, and the type column name in p_type_column_name. -- Return the key column and associated guid as well. Note that -- if the type specified by the ref itself references other -- types, those are resolved as well PROCEDURE resolve_ref(p_target_type VARCHAR2, p_meta_ver VARCHAR2, p_ref_name VARCHAR2, p_ref_column_name VARCHAR2, p_type_name VARCHAR2, p_target_guid RAW, p_type_name_out OUT VARCHAR2, p_meta_ver_out OUT VARCHAR2, p_type_column_name_out OUT VARCHAR2, p_key_column_out OUT VARCHAR2, p_assoc_target_out OUT RAW) IS l_ref_type_name MGMT_CREDENTIAL_TYPE_REF.ref_type_name%TYPE; l_ref_target_type MGMT_CREDENTIAL_TYPE_REF.ref_target_type%TYPE; l_ref_meta_ver MGMT_CREDENTIAL_TYPE_REF.ref_type_meta_ver%TYPE; l_association MGMT_CREDENTIAL_TYPE_REF.association%TYPE; l_type_name MGMT_CREDENTIAL_TYPE_COLUMNS.type_name%TYPE := p_type_name; l_new_ref_name MGMT_CREDENTIAL_TYPE_COLUMNS.ref_name%TYPE; l_new_ref_column_name MGMT_CREDENTIAL_TYPE_COLUMNS.ref_column_name%TYPE; l_key_column MGMT_CREDENTIAL_TYPE_COLUMNS.type_column_name%TYPE; BEGIN SELECT ref_type_name, ref_target_type, ref_type_meta_ver, association INTO l_ref_type_name, l_ref_target_type, l_ref_meta_ver, l_association FROM MGMT_CREDENTIAL_TYPE_REF WHERE target_type=p_target_type AND target_type_meta_ver=p_meta_ver AND type_name=p_type_name AND ref_name=p_ref_name; -- Check whether the specified column refers to another -- type or not SELECT ref_name, ref_column_name INTO l_new_ref_name, l_new_ref_column_name FROM MGMT_CREDENTIAL_TYPE_COLUMNS WHERE target_type=l_ref_target_type AND target_type_meta_ver=l_ref_meta_ver AND type_name=l_ref_type_name AND type_column_name=p_ref_column_name; IF l_new_ref_name IS NULL THEN -- We've resolved the ref p_type_name_out := l_ref_type_name; p_meta_ver_out := l_ref_meta_ver; p_type_column_name_out := p_ref_column_name; -- Return the key column BEGIN SELECT type_column_name INTO p_key_column_out FROM MGMT_CREDENTIAL_TYPE_COLUMNS WHERE target_type=l_ref_target_type AND target_type_meta_ver=l_ref_meta_ver AND type_name=l_ref_type_name AND key=1; EXCEPTION WHEN NO_DATA_FOUND THEN p_key_column_out := null; END; -- Fetch the associated target p_assoc_target_out := get_predefined_assoc(p_target_guid, l_association); IF p_assoc_target_out IS NULL THEN SELECT assoc_target_guid INTO p_assoc_target_out FROM MGMT_TARGET_ASSOC_INSTANCE WHERE target_guid=p_target_guid AND association=l_association; END IF; ELSE -- The type specified by the ref is itself a -- reference to another type. So resolve that resolve_ref(l_ref_target_type, l_ref_meta_ver, l_new_ref_name, l_new_ref_column_name, l_ref_type_name, p_target_guid, p_type_name_out, p_meta_ver_out, p_type_column_name_out, l_key_column, p_assoc_target_out); -- Note: the key column is always returned relative -- to the current type. SELECT type_column_name INTO p_key_column_out FROM MGMT_CREDENTIAL_TYPE_COLUMNS WHERE target_type=l_ref_target_type AND target_type_meta_ver=l_ref_meta_ver AND type_name=l_ref_type_name AND ref_name=l_new_ref_name AND ref_column_name=l_key_column; END IF; END; -- Post-process after an update. This is called after every update. It -- checks to see if a key column has been updated; if so, it -- deletes all non-key columns -- Bug 5988432: Updating the key values is done here to avoid -- table mutating error PROCEDURE post_process (p_updating BOOLEAN) IS l_key_value MGMT_CREDENTIALS2.credential_value%TYPE; BEGIN -- For Console insert, don't do any processing here IF s_no_recursion OR s_console_insert THEN RETURN; END IF; -- Perform post-processing: update all the key values -- that did not get updated earlier IF s_cred_key_columns IS NOT NULL AND s_cred_key_columns.COUNT > 0 THEN s_no_recursion := true; FOR i IN 1..s_cred_key_columns.COUNT LOOP BEGIN -- Select the key value, update it SELECT decrypt(credential_value) INTO l_key_value FROM MGMT_CREDENTIALS2 WHERE credential_guid=s_new_cred_guid AND credential_set_column=s_cred_key_columns(i).value; UPDATE MGMT_CREDENTIALS2 SET key_value=l_key_value, credential_value=decrypt(credential_value) WHERE credential_guid=s_new_cred_guid AND credential_set_column=s_cred_key_columns(i).name; EXCEPTION -- No data found means key value is not yet inserted WHEN NO_DATA_FOUND THEN s_no_recursion := false; RETURN; WHEN OTHERS THEN s_cred_key_columns := null; s_new_cred_guid := null; s_no_recursion := false; RAISE; END; END LOOP; s_cred_key_columns := null; s_new_cred_guid := null; s_no_recursion := false; END IF; IF p_updating AND s_current_cred_guid IS NOT NULL THEN DELETE FROM MGMT_CREDENTIALS2 WHERE credential_guid=s_current_cred_guid AND credential_type_name=s_current_cred_type_name AND credential_type_column != s_current_cred_type_column; END IF; IF s_current_cred_guid IS NOT NULL THEN s_current_cred_guid := null; s_current_cred_type_name := null; s_current_cred_type_column := null; s_console_insert := false; END IF; END; -- Return true if the current insert is from the console FUNCTION is_console_insert RETURN BOOLEAN IS BEGIN RETURN s_console_insert; END; -- Return true if the s_no_recursion, calling from post_process FUNCTION is_no_recursion RETURN BOOLEAN IS BEGIN RETURN s_no_recursion; END; -- Compute and populate all associated information for the -- specified credential set, column and target guid. -- This procedure does not assume that the key value is -- already inserted; so it may not be able to find the -- key value. In such cases, the s_cred_key_columns parameter -- is populated with the credential set column (name) -- and the corresponding set column of the key (value) 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) IS l_type_name MGMT_CREDENTIAL_TYPES.type_name%TYPE; l_meta_ver MGMT_TARGETS.type_meta_ver%TYPE; l_target_type MGMT_TARGETS.target_type%TYPE; l_type_column_name MGMT_CREDENTIAL_SET_COLUMNS.type_column_name%TYPE; l_ref_name MGMT_CREDENTIAL_TYPE_COLUMNS.ref_name%TYPE; l_ref_column_name MGMT_CREDENTIAL_TYPE_COLUMNS.ref_column_name%TYPE; l_actual_type_name MGMT_CREDENTIAL_TYPES.type_name%TYPE; l_actual_meta_ver MGMT_CREDENTIAL_TYPES.target_type_meta_ver%TYPE; l_actual_type_column_name MGMT_CREDENTIAL_TYPE_COLUMNS.ref_column_name%TYPE; l_key_column MGMT_CREDENTIAL_TYPE_COLUMNS.type_column_name%TYPE; l_key_value MGMT_CREDENTIALS2.credential_value%TYPE; l_key_set_column MGMT_CREDENTIAL_SET_COLUMNS.set_column_name%TYPE; l_assoc_target_guid MGMT_TARGETS.target_guid%TYPE := null; BEGIN -- Do not compute associated info for 4.0-style sets IF is_40_style_set(p_set_name) THEN RETURN; END IF; SELECT target_type, type_meta_ver INTO l_target_type, l_meta_ver FROM MGMT_TARGETS WHERE target_guid=p_target_guid; SELECT credential_type_name INTO l_type_name FROM MGMT_CREDENTIAL_SETS WHERE target_type=l_target_type AND target_type_meta_ver=l_meta_ver AND set_name=p_set_name; SELECT type_column_name INTO l_type_column_name FROM MGMT_CREDENTIAL_SET_COLUMNS WHERE target_type=l_target_type AND target_type_meta_ver=l_meta_ver AND set_name=p_set_name AND set_column_name=p_credential_set_column; SELECT ref_name, ref_column_name INTO l_ref_name, l_ref_column_name FROM MGMT_CREDENTIAL_TYPE_COLUMNS WHERE target_type=l_target_type AND target_type_meta_ver=l_meta_ver AND type_name=l_type_name AND type_column_name=l_type_column_name; -- Note that we have to resolve this to the base type -- name and column. IF l_ref_name IS NOT NULL THEN -- The ref needs to be resolved resolve_ref(l_target_type, l_meta_ver, l_ref_name, l_ref_column_name, l_type_name, p_target_guid, l_actual_type_name, l_actual_meta_ver, l_actual_type_column_name, l_key_column, l_assoc_target_guid); ELSE l_actual_type_name := l_type_name; l_actual_type_column_name := l_type_column_name; l_assoc_target_guid := p_target_guid; END IF; -- Fetch the key value and we're done -- Note. We have to use the target type -- of the associated target, if there is one -- In cases where there is no key column -- for example listener credential types -- return a null key column (bug 3215373) IF l_ref_name IS NOT NULL THEN BEGIN SELECT type_column_name INTO l_key_column FROM MGMT_CREDENTIAL_TYPE_COLUMNS c, MGMT_TARGETS t WHERE c.target_type=t.target_type AND t.target_guid=l_assoc_target_guid AND target_type_meta_ver=l_actual_meta_ver AND type_name=l_actual_type_name AND key=1; EXCEPTION WHEN NO_DATA_FOUND THEN l_key_column := null; END; ELSE BEGIN SELECT type_column_name INTO l_key_column FROM MGMT_CREDENTIAL_TYPE_COLUMNS WHERE target_type=l_target_type AND target_type_meta_ver=l_meta_ver AND type_name=l_type_name AND key=1; EXCEPTION WHEN NO_DATA_FOUND THEN l_key_column := null; END; END IF; IF l_type_column_name=l_key_column THEN l_key_value := p_credential_value; -- Set the current credential guid. This assumes -- that during updates, only one row will -- be updated IF NOT s_console_insert THEN -- This insert is coming from the agent. The -- agent always inserts the key value first, so -- there is no need to store the key values. s_current_cred_guid := p_credential_guid; s_current_cred_type_name := l_actual_type_name; s_current_cred_type_column := l_actual_type_column_name; END IF; ELSIF l_key_column IS NOT NULL THEN -- We need to figure out the set column -- corresponding to the key. This code assumes -- that the key is always inserted first SELECT set_column_name INTO l_key_set_column FROM MGMT_CREDENTIAL_SET_COLUMNS WHERE target_type=l_target_type AND target_type_meta_ver=l_meta_ver AND set_name=p_set_name AND type_column_name=l_key_column; -- Bug 5988432, Select on mgmt_credentials2 will lead to -- mutating table error when called from mgmt_creds_ins_upd trigger IF NOT s_console_insert THEN IF s_cred_key_columns IS NULL THEN s_cred_key_columns := SMP_EMD_NVPAIR_ARRAY(); END IF; s_cred_key_columns.extend(1); s_cred_key_columns(s_cred_key_columns.count) := SMP_EMD_NVPAIR(p_credential_set_column, l_key_set_column); s_new_cred_guid := p_credential_guid; END IF; -- key_value will be updated as part of post_process called from -- after insert trigger l_key_value := null; END IF; -- Set the out variables and we're done p_type_name_out := l_actual_type_name; p_type_column_out := l_actual_type_column_name; p_key_value_out := l_key_value; p_assoc_target_out := l_assoc_target_guid; END; -- -- 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_ref_type If this credential type references another credential type, -- this is the name of that type -- p_map_target_type The target type of the map type -- p_association The association, if there is a mapping type -- 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) IS BEGIN -- Create an entry for the credential type INSERT INTO MGMT_CREDENTIAL_TYPES (target_type, target_type_meta_ver, type_name, type_display_name, type_display_nlsid, description) VALUES (p_target_type, p_target_type_meta_ver, p_type_name, p_type_display_name, p_type_display_nlsid, p_description); -- If this type references another type, insert an entry for it IF p_refs IS NOT NULL THEN FOR i IN 1..p_refs.count LOOP INSERT INTO MGMT_CREDENTIAL_TYPE_REF (target_type, target_type_meta_ver, type_name, ref_name, ref_type_name, ref_target_type, ref_type_meta_ver, association) VALUES (p_target_type, p_target_type_meta_ver, p_type_name, p_refs(i).ref_name, p_refs(i).type_name, p_refs(i).target_type, p_refs(i).ref_type_meta_ver, p_refs(i).association); END LOOP; END IF; -- Insert the type columns IF p_type_columns IS NOT NULL THEN FOR i IN 1..p_type_columns.COUNT LOOP INSERT INTO MGMT_CREDENTIAL_TYPE_COLUMNS (target_type, target_type_meta_ver, type_name, type_column_name, type_column_display_name, type_column_display_nlsid, ref_name, ref_column_name, key) VALUES (p_target_type, p_target_type_meta_ver, p_type_name, p_type_columns(i).type_column_name, p_type_columns(i).type_column_display_name, p_type_columns(i).type_column_display_nlsid, p_type_columns(i).ref_name, p_type_columns(i).ref_column_name, p_type_columns(i).key); -- Insert the default values for this column, if any IF p_type_columns(i).col_values IS NOT NULL THEN FOR j IN 1..p_type_columns(i).col_values.COUNT LOOP INSERT INTO MGMT_CREDENTIAL_TYPE_COL_VALS (target_type, target_type_meta_ver, type_name, type_column_name, default_value, value) VALUES (p_target_type, p_target_type_meta_ver, p_type_name, p_type_columns(i).type_column_name, p_type_columns(i).col_values(j).default_value, p_type_columns(i).col_values(j).value); END LOOP; END IF; END LOOP; END IF; END; -- -- 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) IS BEGIN DELETE FROM MGMT_CREDENTIAL_TYPES WHERE target_type=p_target_type AND type_name=p_type_name; END; -- -- 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) IS BEGIN -- Insert an entry for the credential set INSERT INTO MGMT_CREDENTIAL_SETS(target_type, target_type_meta_ver, set_name, set_display_name, set_display_nlsid, credential_type_name, set_usage, set_context_type, set_context) VALUES (p_target_type, p_target_type_meta_ver, p_set_name, p_set_display_name, p_set_display_nlsid, p_type_name, p_set_usage, p_set_context_type, p_set_context); -- Insert the credential set columns IF p_set_columns IS NOT NULL THEN FOR i IN 1..p_set_columns.COUNT LOOP INSERT INTO MGMT_CREDENTIAL_SET_COLUMNS(target_type, target_type_meta_ver, set_name, set_column_name, set_column_display_name, set_column_display_nlsid, type_column_name) VALUES (p_target_type, p_target_type_meta_ver, p_set_name, p_set_columns(i).set_column_name, p_set_columns(i).set_column_display_name, p_set_columns(i).set_column_display_nlsid, p_set_columns(i).type_column_name); -- Insert set column values for each column, if specified IF p_set_columns(i).set_column_values IS NOT NULL THEN FOR j IN 1..p_set_columns(i).set_column_values.COUNT LOOP INSERT INTO MGMT_CREDENTIAL_SET_COL_VALS(target_type, target_type_meta_ver, set_name, set_column_name, default_value, value) VALUES (p_target_type, p_target_type_meta_ver, p_set_name, p_set_columns(i).set_column_name, p_set_columns(i).set_column_values(j).default_value, p_set_columns(i).set_column_values(j).value); END LOOP; END IF; END LOOP; END IF; END; -- -- Create a new credential set. Will fail if the set is already present, -- with a UNIQUE CONSTRAINT violation. Deprecated. -- -- 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) IS BEGIN create_credential_set(p_target_type, '1.0', p_set_name, p_set_usage, p_set_context_type, p_set_context, p_set_display_name, p_set_display_nlsid, p_type_name, p_set_columns); END; -- -- 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) IS BEGIN DELETE FROM MGMT_CREDENTIAL_SETS WHERE target_type=p_target_type AND set_name=p_set_name; END; -- Returns the set usage and context type PROCEDURE get_set_usage(p_credential_set_name VARCHAR2, p_target_type VARCHAR2, p_target_guid RAW, p_usage_out OUT VARCHAR2, p_context_type_out OUT VARCHAR2) IS BEGIN IF p_credential_set_name = CRED_SET_40_TARGET_SYSTEM THEN p_usage_out := SYSTEM_SET_USAGE; p_context_type_out := TARGET_SET_CONTEXT_TYPE; ELSIF p_credential_set_name = CRED_SET_40_TARGET_USER THEN p_usage_out := PREFCRED_SET_USAGE; p_context_type_out := TARGET_SET_CONTEXT_TYPE; ELSIF p_credential_set_name = CRED_SET_40_CONTAINER_SYSTEM THEN p_usage_out := SYSTEM_SET_USAGE; p_context_type_out := CONTAINER_SET_CONTEXT_TYPE; ELSIF p_credential_set_name = CRED_SET_40_CONTAINER_USER THEN p_usage_out := PREFCRED_SET_USAGE; p_context_type_out := CONTAINER_SET_CONTEXT_TYPE; ELSE IF p_target_guid IS NOT NULL AND p_target_guid != DEFAULT_GUID THEN -- Use the credential set with the appropriate -- meta version SELECT set_usage, set_context_type INTO p_usage_out, p_context_type_out FROM MGMT_CREDENTIAL_SETS cs, MGMT_TARGETS t WHERE cs.target_type=p_target_type AND cs.set_name=p_credential_set_name AND cs.target_type_meta_ver=t.type_meta_ver AND t.target_guid=p_target_guid; ELSE -- Use the first credential set available SELECT set_usage, set_context_type INTO p_usage_out, p_context_type_out FROM MGMT_CREDENTIAL_SETS cs WHERE cs.target_type=p_target_type AND cs.set_name=p_credential_set_name AND ROWNUM=1; END IF; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_CRED_SET_ERR, 'Could not find credential set ' || p_credential_set_name || ' for target type ' || p_target_type); END; -- -- 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) IS l_pdp_guid RAW(16); BEGIN -- Insert an entry for the credential set INSERT INTO MGMT_PDP_METADATA(pdp_guid, pdp_name, credential_type_name) VALUES (sys_guid, p_pdp.pdp_name, p_pdp.credential_type_name) RETURNING pdp_guid INTO l_pdp_guid; -- Insert the pdp columns IF p_pdp.pdp_columns IS NOT NULL THEN FOR i IN 1..p_pdp.pdp_columns.COUNT LOOP INSERT INTO MGMT_PDP_COLUMN_METADATA(pdp_guid, pdp_column_name, is_required) VALUES (l_pdp_guid, p_pdp.pdp_columns(i).pdp_column_name, p_pdp.pdp_columns(i).is_required); END LOOP; END IF; -- Insert the pdp settings IF p_pdp.pdp_settings IS NOT NULL THEN FOR i IN 1..p_pdp.pdp_settings.COUNT LOOP INSERT INTO MGMT_PDP_SETTING_METADATA(pdp_guid, pdp_setting_name, is_required, display_name_nlsid, display_name, hint_nlsid, hint) VALUES (l_pdp_guid, p_pdp.pdp_settings(i).pdp_setting_name, p_pdp.pdp_settings(i).is_required, p_pdp.pdp_settings(i).display_name_nlsid, p_pdp.pdp_settings(i).display_name, p_pdp.pdp_settings(i).hint_nlsid, p_pdp.pdp_settings(i).hint); END LOOP; END IF; -- Insert the pdp param IF p_pdp.pdp_params IS NOT NULL THEN FOR i IN 1..p_pdp.pdp_params.COUNT LOOP INSERT INTO MGMT_PDP_PARAM_METADATA(pdp_guid, pdp_param_name, description_nlsid, description) VALUES (l_pdp_guid, p_pdp.pdp_params(i).pdp_param_name, p_pdp.pdp_params(i).description_nlsid, p_pdp.pdp_params(i).description); END LOOP; END IF; END; -- -- Return list of pdps present in the present in EM -- FUNCTION get_pdp_types RETURN MGMT_PDP_META_ARRAY IS l_pdp_array MGMT_PDP_META_ARRAY := MGMT_PDP_META_ARRAY(); l_pdp_column_array MGMT_PDP_COLUMN_META_ARRAY := MGMT_PDP_COLUMN_META_ARRAY(); l_pdp_setting_array MGMT_PDP_SETTING_META_ARRAY := MGMT_PDP_SETTING_META_ARRAY(); l_pdp_param_array MGMT_PDP_PARAM_META_ARRAY := MGMT_PDP_PARAM_META_ARRAY(); BEGIN FOR crec IN (SELECT pdp_guid, pdp_name, credential_type_name FROM MGMT_PDP_METADATA) LOOP SELECT MGMT_PDP_COLUMN_META.NEW(pdp_column_name,is_required) BULK COLLECT INTO l_pdp_column_array FROM MGMT_PDP_COLUMN_METADATA WHERE pdp_guid=crec.pdp_guid; SELECT MGMT_PDP_SETTING_META.NEW(pdp_setting_name,is_required,display_name_nlsid, display_name,hint_nlsid,hint) BULK COLLECT INTO l_pdp_setting_array FROM MGMT_PDP_SETTING_METADATA WHERE pdp_guid=crec.pdp_guid; SELECT MGMT_PDP_PARAM_META.NEW(pdp_param_name,description_nlsid,description) BULK COLLECT INTO l_pdp_param_array FROM MGMT_PDP_PARAM_METADATA WHERE pdp_guid=crec.pdp_guid ORDER BY pdp_param_name; l_pdp_array.extend(1); l_pdp_array(l_pdp_array.COUNT) := MGMT_PDP_META.NEW(crec.pdp_name, crec.credential_type_name, l_pdp_column_array, l_pdp_setting_array, l_pdp_param_array); END LOOP; RETURN l_pdp_array; END; -- Return the appropriate username for the credential set FUNCTION get_set_user_name(p_credential_set_name VARCHAR2, p_target_type VARCHAR2, p_target_guid RAW, p_user_name VARCHAR2) RETURN VARCHAR2 IS l_set_usage MGMT_CREDENTIAL_SETS.set_usage%TYPE; l_set_context_type MGMT_CREDENTIAL_SETS.set_context_type%TYPE; BEGIN get_set_usage(p_credential_set_name, p_target_type, p_target_guid, l_set_usage, l_set_context_type); IF l_set_usage=SYSTEM_SET_USAGE OR l_set_usage=MONITORING_SET_USAGE THEN IF p_user_name IS NOT NULL THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid username for SYSTEM credential set ' || p_credential_set_name); END IF; RETURN SYSTEM_USER; ELSE IF p_user_name IS NOT NULL AND p_user_name != MGMT_USER.get_current_em_user THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Cannot set/delete credentials for users other than the current user'); END IF; RETURN MGMT_USER.get_current_em_user; END IF; END; -- 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) IS l_target_guid MGMT_TARGETS.target_guid%TYPE; l_user_name VARCHAR2(256); BEGIN BEGIN SELECT target_guid INTO l_target_guid FROM MGMT_TARGETS WHERE target_name=p_target_name AND target_type=p_target_type; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_TARGET_ERR, p_target_name || ':' || p_target_type); END; l_user_name := get_set_user_name(p_credential_set_name, p_target_type, l_target_guid, p_user_name); DELETE FROM MGMT_TARGET_CREDENTIALS WHERE target_guid=l_target_guid AND credential_set_name=p_credential_set_name AND user_name=l_user_name; END; -- 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) IS l_host_guid MGMT_TARGETS.target_guid%TYPE; l_user_name VARCHAR2(256); BEGIN BEGIN SELECT target_guid INTO l_host_guid FROM MGMT_TARGETS WHERE target_name=p_host_name AND target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_TARGET_ERR, p_host_name); END; l_user_name := get_set_user_name(p_credential_set_name, MGMT_GLOBAL.G_HOST_TARGET_TYPE, l_host_guid, p_user_name); DELETE FROM MGMT_HOST_CREDENTIALS WHERE host_guid=l_host_guid AND target_type=p_target_type AND credential_set_name=p_credential_set_name AND user_name=l_user_name; END; -- Delete container credentials PROCEDURE delete_container_credentials(p_credential_set_name VARCHAR2, p_host_name VARCHAR2, p_container_location VARCHAR2, p_user_name VARCHAR2 DEFAULT NULL) IS l_host_guid MGMT_TARGETS.target_guid%TYPE; l_user_name VARCHAR2(256); BEGIN BEGIN SELECT target_guid INTO l_host_guid FROM MGMT_TARGETS WHERE target_name=p_host_name AND target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_TARGET_ERR, p_host_name); END; l_user_name := get_set_user_name(p_credential_set_name, MGMT_GLOBAL.G_HOST_TARGET_TYPE, l_host_guid, p_user_name); DELETE FROM MGMT_CONTAINER_CREDENTIALS WHERE target_guid=l_host_guid AND container_location=p_container_location AND credential_set_name=p_credential_set_name AND user_name=l_user_name; END; -- 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) IS l_target_guid MGMT_TARGETS.target_guid%TYPE; l_metric_guid MGMT_METRICS.metric_guid%TYPE; BEGIN BEGIN SELECT target_guid INTO l_target_guid FROM MGMT_TARGETS WHERE target_name=p_target_name AND target_type=p_target_type; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_TARGET_ERR, p_target_name); END; l_metric_guid := MGMT_METRIC.get_metric_guid( p_target_type, p_metric_name); DELETE FROM MGMT_COLLECTION_CREDENTIALS WHERE target_guid=l_target_guid AND metric_guid=l_metric_guid AND coll_name=p_collection_name AND credential_set_name=p_credential_set_name; END; -- Delete collection copy credentials 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) IS l_target_guid MGMT_TARGETS.target_guid%TYPE; l_metric_guid MGMT_METRICS.metric_guid%TYPE; BEGIN BEGIN SELECT target_guid INTO l_target_guid FROM MGMT_TARGETS WHERE target_name=p_target_name AND target_type=p_target_type; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_TARGET_ERR, p_target_name); END; l_metric_guid := MGMT_METRIC.get_metric_guid( p_target_type, p_metric_name); DELETE FROM MGMT_COLLECTION_TEMPLATE_CREDS WHERE object_guid=p_object_guid AND object_type=p_object_type AND target_guid=l_target_guid AND metric_guid=l_metric_guid AND coll_name=p_collection_name AND credential_set_name=p_credential_set_name; END; -- Delete enterprise-level credentials PROCEDURE delete_enterprise_credentials(p_credential_set_name VARCHAR2, p_target_type VARCHAR2, p_user_name VARCHAR2 DEFAULT NULL) IS l_user_name VARCHAR2(256); BEGIN l_user_name := get_set_user_name(p_credential_set_name, p_target_type, null, p_user_name); DELETE FROM MGMT_ENTERPRISE_CREDENTIALS WHERE target_type=p_target_type AND credential_set_name=p_credential_set_name AND user_name=l_user_name; END; -- -- Return the user name specified in the credential record -- FUNCTION get_user_name(p_user_name IN VARCHAR2) RETURN VARCHAR2 IS BEGIN IF p_user_name IS NULL THEN RETURN SYSTEM_USER; ELSE RETURN p_user_name; END IF; END; -- Validate that the current user can view/manipulate -- the credential set specified. If p_job_creds is true, -- then credentials are being set at the job level PROCEDURE validate_creds(p_credential_set_name VARCHAR2, p_target_type VARCHAR2, p_target_guid RAW, p_container_location VARCHAR2, p_username VARCHAR2, p_job_creds BOOLEAN, p_nested_job_creds BOOLEAN) IS l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user; l_set_usage MGMT_CREDENTIAL_SETS.set_usage%TYPE; l_set_context_type MGMT_CREDENTIAL_SETS.set_context_type%TYPE; BEGIN IF NOT p_nested_job_creds THEN get_set_usage(p_credential_set_name, p_target_type, p_target_guid, l_set_usage, l_set_context_type); ELSE -- Nested job creds are assumed to be preferred cred sets l_set_usage := PREFCRED_SET_USAGE; l_set_context_type := TARGET_SET_CONTEXT_TYPE; END IF; -- Check that credentials are set at the correct storage IF NOT p_job_creds AND NOT p_nested_job_creds THEN IF l_set_context_type = CONTAINER_SET_CONTEXT_TYPE AND p_target_guid IS NOT NULL THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Cannot insert target credentials for container credential set ' || p_credential_set_name); ELSIF l_set_context_type = TARGET_SET_CONTEXT_TYPE AND p_container_location IS NOT NULL THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Cannot insert container credentials for target credential set ' || p_credential_set_name); END IF; END IF; IF l_set_usage = MONITORING_SET_USAGE THEN -- Only users with operator privilege over the target can -- set monitoring credentials. Furthermore, monitoring credentials -- *have* to be set for a target IF p_target_guid IS NULL THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Monitoring credentials can only be set at the target level'); END IF; -- Monitoring credentials can only be inserted for system users IF UPPER(p_username) != SYSTEM_USER THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Cannot set monitoring credentials for an EM user'); END IF; -- Validate that the user has at least operator on the target IF MGMT_USER.has_priv(l_current_user, MGMT_USER.OPERATOR_TARGET, p_target_guid) != MGMT_USER.USER_HAS_PRIV THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'The current user does not have enough privileges to perform this action'); END IF; ELSIF l_set_usage = PREFCRED_SET_USAGE AND NOT p_nested_job_creds AND NOT p_job_creds THEN -- Super users can set preferred credentials for other users. -- Other than that, users can only set their own preferred -- credentials IF MGMT_USER.has_priv(l_current_user, MGMT_USER.SUPER_USER) != MGMT_USER.USER_HAS_PRIV AND l_current_user != UPPER(p_username) THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'User ' || l_current_user || ' cannot insert credentials for user ' || p_username); END IF; ELSIF l_set_usage = SYSTEM_SET_USAGE THEN -- Only superusers can set system credentials IF UPPER(p_username) != SYSTEM_USER THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Cannot set system credentials for an EM user'); END IF; IF MGMT_USER.has_priv(l_current_user, MGMT_USER.SUPER_USER) != MGMT_USER.USER_HAS_PRIV THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'Only superusers can set system credentials'); END IF; END IF; END; -- Delete overridden job credentials for the specified job PROCEDURE delete_job_or_ca_credentials(p_job_id RAW) IS l_credential_guids MGMT_USER_GUID_ARRAY; l_override_creds MGMT_JOB_CRED_ARRAY; l_target_guids MGMT_USER_GUID_ARRAY; BEGIN -- Select all credentials for processing by the job system SELECT MGMT_JOB_CRED_RECORD.NEW(target_name, jc.target_type, container_location, MGMT_CRED_RECORD.NEW(user_name, credential_set_name, null)), jc.target_guid, jc.credential_guid BULK COLLECT INTO l_override_creds, l_target_guids, l_credential_guids FROM MGMT_JOB_CREDENTIALS jc, MGMT_TARGETS t WHERE jc.target_guid=t.target_guid (+) AND job_id=p_job_id; -- Delete all the credential entries IF l_credential_guids IS NOT NULL THEN FORALL i IN 1..l_credential_guids.COUNT DELETE FROM MGMT_CREDENTIALS2 WHERE credential_guid=l_credential_guids(i); END IF; -- Delete the referring rows DELETE FROM MGMT_JOB_CREDENTIALS WHERE job_id=p_job_id; -- Call the job system callback FOR i IN 1..l_override_creds.COUNT LOOP IF NOT is_40_style_set(l_override_creds(i).credential.credential_set_name) THEN MGMT_JOB_ENGINE.override_creds_deleted(p_job_id, l_target_guids(i), l_override_creds(i).container_location, l_override_creds(i).credential.credential_set_name, l_override_creds(i).target_type); END IF; END LOOP; -- Suspend all scheduled credential executions are blocked on -- at least one non-available credential set MGMT_JOB_ENGINE.suspend_cred_execs; END; -- Delete overridden job credentials for the specified job PROCEDURE delete_job_credentials(p_job_name VARCHAR2, p_job_owner VARCHAR2, p_is_library NUMBER) IS l_job_id MGMT_JOB.job_id%TYPE; BEGIN SELECT job_id INTO l_job_id FROM MGMT_JOB WHERE job_name=p_job_name AND job_owner=p_job_owner AND is_library=p_is_library AND nested=0 AND is_corrective_action=0; delete_job_or_ca_credentials(l_job_id); END; -- Delete overridden job credentials for the specified job PROCEDURE delete_job_credentials(p_job_id RAW) IS l_job_id MGMT_JOB.job_id%TYPE; BEGIN BEGIN SELECT job_id INTO l_job_id FROM MGMT_JOB WHERE job_id=p_job_id; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Specified job does not exist'); END; delete_job_or_ca_credentials(l_job_id); END; -- Delete overridden job credentials for the specified CA PROCEDURE delete_ca_credentials(p_job_id RAW) IS l_is_corrective_action MGMT_JOB.is_corrective_action%TYPE; BEGIN SELECT is_corrective_action INTO l_is_corrective_action FROM MGMT_JOB WHERE job_id=p_job_id; IF l_is_corrective_action=1 THEN delete_job_or_ca_credentials(p_job_id); END IF; END; FUNCTION is_40_style_set(p_set_name VARCHAR2) RETURN BOOLEAN IS BEGIN RETURN p_set_name=CRED_SET_40_TARGET_USER OR p_set_name=CRED_SET_40_TARGET_SYSTEM OR p_set_name=CRED_SET_40_CONTAINER_USER OR p_set_name=CRED_SET_40_CONTAINER_SYSTEM; END; -- Insert credentials into the credentials table PROCEDURE insert_creds(p_credential_guid IN RAW, p_credentials IN MGMT_CRED_RECORD, p_target_guid RAW, p_container_location VARCHAR2, p_target_type VARCHAR2, p_username VARCHAR2, p_job_creds BOOLEAN DEFAULT false, p_nested_job_creds BOOLEAN DEFAULT false) IS l_type_name MGMT_CREDENTIALS2.credential_type_name%TYPE; l_type_name_array SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY(); l_type_column MGMT_CREDENTIALS2.credential_type_column%TYPE; l_type_column_array SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY(); l_key_value MGMT_CREDENTIALS2.key_value%TYPE; l_key_value_actual MGMT_CREDENTIALS2.key_value%TYPE; l_assoc_target MGMT_CREDENTIALS2.assoc_target_guid%TYPE; l_assoc_target_array MGMT_TARGET_GUID_ARRAY := MGMT_TARGET_GUID_ARRAY(); BEGIN validate_creds(p_credentials.credential_set_name, p_target_type, p_target_guid, p_container_location, p_username, p_job_creds, p_nested_job_creds); -- Note that we always delete existing rows with the same -- credential GUID. If we did an upsert, we could end up -- with bad values if only some of the columns of the set -- were specified IF NOT is_40_style_set(p_credentials.credential_set_name) THEN -- We do not delete existing rows for 4.0 sets since -- 4.0 sets simulate a set wrapper around uniquely -- named credential columns DELETE FROM MGMT_CREDENTIALS2 WHERE credential_guid=p_credential_guid; END IF; -- Indicate to the triggers that we are inserting from -- the console s_console_insert := true; FOR i IN 1..p_credentials.creds.count LOOP IF p_target_guid IS NOT NULL THEN compute_associated_info(p_credential_guid, p_target_guid, p_credentials.credential_set_name, p_credentials.creds(i).credential_set_column, p_credentials.creds(i).credential_value, l_type_name, l_type_column, l_key_value, l_assoc_target); END IF; -- Store the type_name, type_column, assoc_target_guid l_type_name_array.extend(1); l_type_name_array(i) := l_type_name; l_type_column_array.extend(1); l_type_column_array(i) := l_type_column; l_assoc_target_array.extend(1); l_assoc_target_array(i) := l_assoc_target; IF l_key_value IS NOT NULL THEN l_key_value_actual := l_key_value; END IF; END LOOP; -- Bug 6459937: Non key_value row may get inserted first. Get associated -- info and key_value for all credential records and then do insert FOR i IN 1..p_credentials.creds.count LOOP -- Note: always insert without encrypting, -- trigger encrypts BEGIN INSERT INTO MGMT_CREDENTIALS2(credential_guid, credential_set_column, credential_value, credential_type_name, credential_type_column, key_value, assoc_target_guid) VALUES (p_credential_guid, p_credentials.creds(i).credential_set_column, p_credentials.creds(i).credential_value, l_type_name_array(i), l_type_column_array(i), l_key_value_actual, l_assoc_target_array(i)); EXCEPTION -- Updates are necessary for 4.0-style sets WHEN DUP_VAL_ON_INDEX THEN UPDATE MGMT_CREDENTIALS2 SET credential_value=p_credentials.creds(i).credential_value WHERE credential_guid=p_credential_guid AND credential_set_column=p_credentials.creds(i).credential_set_column; END; END LOOP; s_console_insert := false; EXCEPTION WHEN OTHERS THEN s_console_insert := false; RAISE; END; -- 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) IS l_credential_guid MGMT_CREDENTIALS2.credential_guid%TYPE; l_target_guid MGMT_TARGETS.target_guid%TYPE; l_user_name MGMT_TARGET_CREDENTIALS.user_name%TYPE; l_usage MGMT_CREDENTIAL_SETS.set_usage%TYPE; BEGIN FOR i IN 1..p_credentials.COUNT LOOP BEGIN SELECT target_guid INTO l_target_guid FROM MGMT_TARGETS WHERE target_name=p_credentials(i).target_name AND target_type=p_credentials(i).target_type; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_TARGET_ERR, p_credentials(i).target_name || ':' || p_credentials(i).target_type); END; l_user_name := get_user_name(p_credentials(i).credential.user_name); BEGIN SELECT set_usage INTO l_usage FROM MGMT_CREDENTIAL_SETS s, MGMT_TARGETS t WHERE s.set_name=p_credentials(i).credential.credential_set_name AND s.target_type=p_credentials(i).target_type AND s.target_type_meta_ver=t.type_meta_ver AND t.target_name=p_credentials(i).target_name AND t.target_type=p_credentials(i).target_type; EXCEPTION WHEN NO_DATA_FOUND THEN IF is_40_style_set(p_credentials(i).credential.credential_set_name) THEN l_usage := PREFCRED_SET_USAGE; ELSE raise_application_error(MGMT_GLOBAL.INVALID_CRED_SET_ERR, 'Invalid credential set ' || p_credentials(i).credential.credential_set_name); END IF; END; IF l_usage = MONITORING_SET_USAGE THEN l_credential_guid := generate_monitoring_cred_guid(p_credentials(i).target_type, p_credentials(i).target_name, p_credentials(i).credential.credential_set_name); ELSE l_credential_guid := generate_target_cred_guid(l_target_guid, p_credentials(i).credential.credential_set_name, l_user_name); END IF; -- Insert the referring entry in the MGMT_TARGET_CREDENTIALS table BEGIN INSERT INTO MGMT_TARGET_CREDENTIALS(target_guid, credential_set_name, user_name, pdp_data, credential_guid) VALUES (l_target_guid, p_credentials(i).credential.credential_set_name, l_user_name, p_credentials(i).credential.pdp_data, l_credential_guid); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- PDP_DATA might Change UPDATE MGMT_TARGET_CREDENTIALS SET pdp_data = p_credentials(i).credential.pdp_data WHERE target_guid = l_target_guid AND credential_set_name = p_credentials(i).credential.credential_set_name AND user_name = l_user_name; END; -- Upsert the credential columns into the credential table insert_creds(l_credential_guid, p_credentials(i).credential, l_target_guid, null, p_credentials(i).target_type, l_user_name); -- Callback the job system about the credentials being set IF NOT is_40_style_set(p_credentials(i).credential.credential_set_name) THEN MGMT_JOB_ENGINE.target_creds_added(l_target_guid, null, p_credentials(i).credential.credential_set_name, p_credentials(i).target_type, l_user_name); END IF; END LOOP; MGMT_JOB_ENGINE.resume_cred_execs; END; -- Propogate monitoring credentials to the respective agents. A separate -- propogation operation is submitted for each one PROCEDURE propogate_monitoring_creds(p_credentials MGMT_TARGET_CRED_ARRAY) IS l_data_set_guid RAW(16); l_op_guid RAW(16); l_data_set_guid_array MGMT_USER_GUID_ARRAY; l_targets MGMT_JOB_TARGET_LIST := MGMT_JOB_TARGET_LIST(); BEGIN -- Get a data set guid for each set of credentials l_targets := MGMT_JOB_TARGET_LIST(); l_targets.extend(1); l_data_set_guid_array := MGMT_USER_GUID_ARRAY(); l_data_set_guid_array.extend(1); FOR i IN 1..p_credentials.COUNT LOOP l_data_set_guid := MGMT_TARGET_UPDATE.submit_credential_data(p_credentials(i).target_name, p_credentials(i).target_type, p_credentials(i).credential.credential_set_name, p_credentials(i).credential.creds); l_targets(1) := MGMT_JOB_TARGET_RECORD(p_credentials(i).target_name, p_credentials(i).target_type); l_data_set_guid_array(1) := l_data_set_guid; -- Submit a data propogation operation l_op_guid := MGMT_TARGET_UPDATE.submit_update_operation(l_data_set_guid_array, l_targets, 1); END LOOP; END; -- Propogate UDM credentials to the respective agents. A separate -- propogation operation is submitted for each one PROCEDURE propogate_udm_creds(p_credentials MGMT_COLLECTION_CRED_ARRAY) IS l_data_set_guid RAW(16); l_op_guid RAW(16); l_data_set_guid_array MGMT_USER_GUID_ARRAY; l_targets MGMT_JOB_TARGET_LIST := MGMT_JOB_TARGET_LIST(); BEGIN -- Get a data set guid for each set of credentials l_targets := MGMT_JOB_TARGET_LIST(); l_targets.extend(1); l_data_set_guid_array := MGMT_USER_GUID_ARRAY(); l_data_set_guid_array.extend(1); FOR i IN 1..p_credentials.COUNT LOOP l_data_set_guid := MGMT_TARGET_UPDATE.submit_coll_credential_data( p_credentials(i).target_name, p_credentials(i).target_type, p_credentials(i).metric_name, p_credentials(i).metric_column, p_credentials(i).collection_name, p_credentials(i).credential.credential_set_name, p_credentials(i).credential.creds); l_targets(1) := MGMT_JOB_TARGET_RECORD(p_credentials(i).target_name, p_credentials(i).target_type); l_data_set_guid_array(1) := l_data_set_guid; -- Submit a data propogation operation l_op_guid := MGMT_TARGET_UPDATE.submit_update_operation(l_data_set_guid_array, l_targets, 1); END LOOP; END; -- Set monitoring credentials PROCEDURE set_monitoring_credentials(p_credentials MGMT_TARGET_CRED_ARRAY, p_propogate BOOLEAN) IS l_usage MGMT_CREDENTIAL_SETS.set_usage%TYPE; l_context_type MGMT_CREDENTIAL_SETS.set_context_type%TYPE; BEGIN -- Validate that each of the credentials is a monitoring -- credential set FOR i IN 1..p_credentials.COUNT LOOP SELECT set_usage, set_context_type INTO l_usage, l_context_type FROM MGMT_CREDENTIAL_SETS s, MGMT_TARGETS t WHERE s.set_name=p_credentials(i).credential.credential_set_name AND s.target_type=p_credentials(i).target_type AND s.target_type_meta_ver=t.type_meta_ver AND t.target_name=p_credentials(i).target_name AND t.target_type=p_credentials(i).target_type; IF l_usage != MONITORING_SET_USAGE AND l_context_type != TARGET_SET_CONTEXT_TYPE THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Set ' || p_credentials(i).credential.credential_set_name || ' is not a monitoring credential set'); END IF; END LOOP; -- Set the credentials; monitoring credentials, are, after all, -- target credentials set_target_credentials(p_credentials); IF p_propogate THEN propogate_monitoring_creds(p_credentials); END IF; END; -- 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) IS l_credential_guid MGMT_CREDENTIALS2.credential_guid%TYPE; l_host_guid MGMT_TARGETS.target_guid%TYPE; l_user_name MGMT_HOST_CREDENTIALS.user_name%TYPE; BEGIN FOR i IN 1..p_credentials.COUNT LOOP BEGIN SELECT target_guid INTO l_host_guid FROM MGMT_TARGETS WHERE target_name=p_credentials(i).host_name AND target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_TARGET_ERR, p_credentials(i).host_name || ':' || MGMT_GLOBAL.G_HOST_TARGET_TYPE); END; l_user_name := get_user_name(p_credentials(i).credential.user_name); l_credential_guid := generate_host_cred_guid(l_host_guid, p_credentials(i).target_type, p_credentials(i).credential.credential_set_name, l_user_name); -- Insert the referring entry in the MGMT_HOST_CREDENTIALS table BEGIN INSERT INTO MGMT_HOST_CREDENTIALS(host_guid, target_type, credential_set_name, user_name, pdp_data, credential_guid) VALUES (l_host_guid, p_credentials(i).target_type, p_credentials(i).credential.credential_set_name, l_user_name, p_credentials(i).credential.pdp_data, l_credential_guid); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- PDP_DATA might change UPDATE MGMT_HOST_CREDENTIALS SET pdp_data = p_credentials(i).credential.pdp_data WHERE host_guid = l_host_guid AND target_type = p_credentials(i).target_type AND credential_set_name = p_credentials(i).credential.credential_set_name AND user_name = l_user_name; END; -- Upsert the credential columns into the credential table insert_creds(l_credential_guid, p_credentials(i).credential, null, null, p_credentials(i).target_type, l_user_name); -- Callback the job system about the credentials being set IF NOT is_40_style_set(p_credentials(i).credential.credential_set_name) THEN MGMT_JOB_ENGINE.target_creds_added(null, l_host_guid, p_credentials(i).credential.credential_set_name, p_credentials(i).target_type, l_user_name); END IF; END LOOP; MGMT_JOB_ENGINE.resume_cred_execs; END; -- 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) IS l_credential_guid MGMT_CREDENTIALS2.credential_guid%TYPE; l_user_name MGMT_ENTERPRISE_CREDENTIALS.user_name%TYPE; BEGIN FOR i IN 1..p_credentials.COUNT LOOP l_user_name := get_user_name(p_credentials(i).credential.user_name); l_credential_guid := generate_enterprise_cred_guid(p_credentials(i).target_type, p_credentials(i).credential.credential_set_name, l_user_name); -- Insert the referring entry in the MGMT_ENT_CREDENTIALS table BEGIN INSERT INTO MGMT_ENTERPRISE_CREDENTIALS( target_type, credential_set_name, user_name, pdp_data, credential_guid) VALUES (p_credentials(i).target_type, p_credentials(i).credential.credential_set_name, l_user_name, p_credentials(i).credential.pdp_data, l_credential_guid); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE MGMT_ENTERPRISE_CREDENTIALS SET pdp_data = p_credentials(i).credential.pdp_data WHERE target_type = p_credentials(i).target_type AND credential_set_name = p_credentials(i).credential.credential_set_name AND user_name = l_user_name; END; -- Upsert the credential columns into the credential table insert_creds(l_credential_guid, p_credentials(i).credential, null, null, p_credentials(i).target_type, l_user_name); -- Callback the job system about the credentials being set IF NOT is_40_style_set(p_credentials(i).credential.credential_set_name) THEN MGMT_JOB_ENGINE.target_creds_added(null, null, p_credentials(i).credential.credential_set_name, p_credentials(i).target_type, l_user_name); END IF; END LOOP; MGMT_JOB_ENGINE.resume_cred_execs; END; -- Insert/update a set of overridden credentials for a job PROCEDURE set_overridden_credentials(p_job_id RAW, p_credentials IN MGMT_JOB_CRED_ARRAY) IS l_credential_guid MGMT_CREDENTIALS2.credential_guid%TYPE; l_target_guid MGMT_TARGETS.target_guid%TYPE; l_username VARCHAR2(256); l_container_location MGMT_JOB_CREDENTIALS.container_location%TYPE; l_set_usage MGMT_CREDENTIAL_SETS.set_usage%TYPE; l_set_context_type MGMT_CREDENTIAL_SETS.set_context_type%TYPE; l_job_owner MGMT_JOB.job_owner%TYPE; l_current_user MGMT_CREATED_USERS.user_name%TYPE := MGMT_USER.get_current_em_user; BEGIN -- Make sure that the current user can set credentials for the job MGMT_JOB_ENGINE.check_modify_job(p_job_id, true); IF p_job_id IS NULL THEN raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR, 'Job does not exist'); ELSE BEGIN SELECT job_owner INTO l_job_owner FROM MGMT_JOB WHERE job_id=p_job_id; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR, 'Job does not exist'); END; END IF; FOR i IN 1..p_credentials.COUNT LOOP IF p_credentials(i).target_name IS NOT NULL THEN BEGIN SELECT target_guid INTO l_target_guid FROM MGMT_TARGETS WHERE target_name=p_credentials(i).target_name AND target_type=p_credentials(i).target_type; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_TARGET_ERR, p_credentials(i).target_name || ':' || p_credentials(i).target_type); END; ELSE l_target_guid := DEFAULT_GUID; END IF; -- Validate that the credential set type is the same as -- the target type get_set_usage(p_credentials(i).credential.credential_set_name, p_credentials(i).target_type, l_target_guid, l_set_usage, l_set_context_type); -- Credentials are always set for the current job owner l_username := l_job_owner; l_container_location := get_container_location(p_credentials(i).container_location); l_credential_guid := generate_job_cred_guid(p_job_id, l_target_guid, p_credentials(i).target_type, l_container_location, p_credentials(i).credential.credential_set_name, l_username); -- Insert the referring entry in the MGMT_JOB_CREDENTIALS table BEGIN INSERT INTO MGMT_JOB_CREDENTIALS(job_id, target_guid, target_type, container_location, credential_set_name, user_name, pdp_data, credential_guid) VALUES (p_job_id, l_target_guid, p_credentials(i).target_type, l_container_location, p_credentials(i).credential.credential_set_name, l_username, p_credentials(i).credential.pdp_data, l_credential_guid); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE MGMT_JOB_CREDENTIALS SET pdp_data = p_credentials(i).credential.pdp_data WHERE job_id = p_job_id AND target_guid = l_target_guid AND target_type = p_credentials(i).target_type AND container_location = l_container_location AND credential_set_name = p_credentials(i).credential.credential_set_name AND user_name = p_credentials(i).credential.pdp_data; END; -- For validation purposes, pass in null if the target -- guid was a default IF l_target_guid=DEFAULT_GUID THEN l_target_guid := null; END IF; -- Insert the credential columns into the credential table insert_creds(l_credential_guid, p_credentials(i).credential, l_target_guid, p_credentials(i).container_location, p_credentials(i).target_type, l_username, true); END LOOP; -- Resume any suspended executions if required IF p_job_id IS NOT NULL THEN MGMT_JOB_ENGINE.override_creds_added(p_job_id, p_credentials); MGMT_JOB_ENGINE.resume_cred_execs; END IF; END; -- 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) IS l_job_id MGMT_JOB.job_id%TYPE; BEGIN BEGIN SELECT job_id INTO l_job_id FROM MGMT_JOB WHERE job_name=p_job_name AND job_owner=p_owner AND is_library=p_is_library AND nested=0 AND is_corrective_action=0; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR, p_job_name || ':' || p_owner); END; set_overridden_credentials(l_job_id, p_credentials); END; -- Insert/update a set of overridden credentials for a job PROCEDURE set_job_credentials(p_job_id RAW, p_credentials IN MGMT_JOB_CRED_ARRAY) IS BEGIN set_overridden_credentials(p_job_id, p_credentials); END; -- Insert/update a set of overridden credentials for a Corrective action PROCEDURE set_ca_credentials(p_job_id RAW, p_credentials IN MGMT_JOB_CRED_ARRAY) IS l_is_corrective_action MGMT_JOB.is_corrective_action%TYPE; BEGIN BEGIN SELECT is_corrective_action INTO l_is_corrective_action FROM MGMT_JOB WHERE job_id=p_job_id; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR, p_job_id); END; set_overridden_credentials(p_job_id, p_credentials); END; -- 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) IS l_credential_guid MGMT_CREDENTIALS2.credential_guid%TYPE; l_target_name MGMT_TARGETS.target_name%TYPE; l_username VARCHAR2(256) := SYSTEM_USER; l_container_location MGMT_JOB_CREDENTIALS.container_location%TYPE; BEGIN FOR i IN 1..p_credentials.COUNT LOOP l_container_location := get_container_location(p_credentials(i).container_location); IF p_credentials(i).target_name IS NULL THEN l_target_name := DEFAULT_TARGET_NAME; ELSE l_target_name := p_credentials(i).target_name; END IF; l_credential_guid := generate_nested_job_cred_guid(p_job_type_id, p_nested_job_name, l_target_name, p_credentials(i).target_type, l_container_location, p_credentials(i).credential.credential_set_name, l_username); -- Insert the credential columns into the credential table insert_creds(l_credential_guid, p_credentials(i).credential, null, p_credentials(i).container_location, p_credentials(i).target_type, l_username, false, true); -- Insert the nested job override creds info BEGIN INSERT INTO MGMT_NESTED_JOB_CRED_INFO (job_type_id, nested_job_name, target_name, target_type, container_location, credential_set_name, pdp_data, credential_guid) VALUES (p_job_type_id, p_nested_job_name, l_target_name, p_credentials(i).target_type, l_container_location, p_credentials(i).credential.credential_set_name, p_credentials(i).credential.pdp_data, l_credential_guid); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE MGMT_NESTED_JOB_CRED_INFO SET pdp_data = p_credentials(i).credential.pdp_data WHERE job_type_id = p_job_type_id AND nested_job_name = p_nested_job_name AND target_name = l_target_name AND target_type = p_credentials(i).target_type AND container_location = l_container_location AND credential_set_name = p_credentials(i).credential.credential_set_name; END; END LOOP; END set_nested_job_cred_info; -- Insert/update a set of container credentials for a job PROCEDURE set_container_credentials(p_credentials IN MGMT_CONTAINER_CRED_ARRAY) IS l_credential_guid MGMT_CREDENTIALS2.credential_guid%TYPE; l_target_guid MGMT_TARGETS.target_guid%TYPE; l_user_name MGMT_CONTAINER_CREDENTIALS.user_name%TYPE; BEGIN FOR i IN 1..p_credentials.COUNT LOOP BEGIN SELECT target_guid INTO l_target_guid FROM MGMT_TARGETS WHERE target_name=p_credentials(i).target_name AND target_type=p_credentials(i).target_type; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_TARGET_ERR, p_credentials(i).target_name || ':' || p_credentials(i).target_type); END; -- Insert the credential columns into the credential table l_user_name := get_user_name(p_credentials(i).credential.user_name); l_credential_guid := generate_container_cred_guid(l_target_guid, p_credentials(i).container_location, p_credentials(i).credential.credential_set_name, l_user_name); insert_creds(l_credential_guid, p_credentials(i).credential, null, p_credentials(i).container_location, p_credentials(i).target_type, l_user_name); -- Insert the referring entry in the MGMT_CONTAINER_CREDENTIALS table BEGIN INSERT INTO MGMT_CONTAINER_CREDENTIALS(target_guid, container_location, credential_set_name, user_name, pdp_data, credential_guid) VALUES (l_target_guid, p_credentials(i).container_location, p_credentials(i).credential.credential_set_name, l_user_name, p_credentials(i).credential.pdp_data, l_credential_guid); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE MGMT_CONTAINER_CREDENTIALS SET pdp_data = p_credentials(i).credential.pdp_data WHERE target_guid = l_target_guid AND container_location = p_credentials(i).container_location AND credential_set_name = p_credentials(i).credential.credential_set_name AND user_name = l_user_name; END; -- Callback the job system about the credentials being set IF NOT is_40_style_set(p_credentials(i).credential.credential_set_name) THEN MGMT_JOB_ENGINE.container_creds_added(l_target_guid, p_credentials(i).container_location, p_credentials(i).credential.credential_set_name, p_credentials(i).target_type, l_user_name); END IF; END LOOP; MGMT_JOB_ENGINE.resume_cred_execs; END; -- Insert/update a set of collection credentials for a job PROCEDURE set_collection_credentials(p_credentials IN MGMT_COLLECTION_CRED_ARRAY) IS l_credential_guid MGMT_CREDENTIALS2.credential_guid%TYPE; l_target_guid MGMT_TARGETS.target_guid%TYPE; l_metric_guid MGMT_METRICS.metric_guid%TYPE; BEGIN FOR i IN 1..p_credentials.COUNT LOOP BEGIN SELECT target_guid INTO l_target_guid FROM MGMT_TARGETS WHERE target_name=p_credentials(i).target_name AND target_type=p_credentials(i).target_type; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_TARGET_ERR, p_credentials(i).target_name || ':' || p_credentials(i).target_type); END; l_metric_guid := MGMT_METRIC.get_metric_guid( p_credentials(i).target_type, p_credentials(i).metric_name); -- Insert the credential columns into the credential table l_credential_guid := generate_collection_cred_guid(p_credentials(i).target_type, p_credentials(i).target_name, p_credentials(i).credential.credential_set_name, p_credentials(i).collection_name, p_credentials(i).metric_name); insert_creds(l_credential_guid, p_credentials(i).credential, l_target_guid, null, p_credentials(i).target_type, SYSTEM_USER); -- Insert the referring entry in the MGMT_COLLECTION_CREDENTIALS table BEGIN INSERT INTO MGMT_COLLECTION_CREDENTIALS(target_guid, metric_guid, coll_name, credential_set_name, pdp_data, credential_guid) VALUES (l_target_guid, l_metric_guid, p_credentials(i).collection_name, p_credentials(i).credential.credential_set_name, p_credentials(i).credential.pdp_data, l_credential_guid); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE MGMT_COLLECTION_CREDENTIALS SET pdp_data = p_credentials(i).credential.pdp_data WHERE target_guid = l_target_guid AND metric_guid = l_metric_guid AND coll_name = p_credentials(i).collection_name AND credential_set_name = p_credentials(i).credential.credential_set_name; END; END LOOP; END; -- Insert/update a set of collection credentials for template copy -- p_copy_guid is the template being copied PROCEDURE set_collection_template_creds(p_credentials IN MGMT_COLLECTION_CRED_ARRAY, p_object_guid RAW, p_object_type NUMBER) IS l_credential_guid MGMT_CREDENTIALS2.credential_guid%TYPE; l_target_guid MGMT_TARGETS.target_guid%TYPE; l_metric_guid MGMT_METRICS.metric_guid%TYPE; BEGIN FOR i IN 1..p_credentials.COUNT LOOP BEGIN SELECT target_guid INTO l_target_guid FROM MGMT_TARGETS WHERE target_name=p_credentials(i).target_name AND target_type=p_credentials(i).target_type; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_TARGET_ERR, p_credentials(i).target_name || ':' || p_credentials(i).target_type); END; l_metric_guid := MGMT_METRIC.get_metric_guid( p_credentials(i).target_type, p_credentials(i).metric_name); -- Insert the credential columns into the credential table l_credential_guid := generate_collection_copy_guid(p_object_guid, l_target_guid, l_metric_guid, p_credentials(i).collection_name, p_credentials(i).credential.credential_set_name); insert_creds(l_credential_guid, p_credentials(i).credential, l_target_guid, null, p_credentials(i).target_type, SYSTEM_USER); -- Insert the referring entry in the MGMT_COLLECTION_CREDENTIALS table BEGIN INSERT INTO MGMT_COLLECTION_TEMPLATE_CREDS(object_guid, object_type, target_guid, metric_guid, coll_name, credential_set_name, pdp_data, credential_guid) VALUES (p_object_guid, p_object_type, l_target_guid, l_metric_guid, p_credentials(i).collection_name, p_credentials(i).credential.credential_set_name, p_credentials(i).credential.pdp_data, l_credential_guid); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE MGMT_COLLECTION_TEMPLATE_CREDS SET pdp_data = p_credentials(i).credential.pdp_data WHERE object_guid = p_object_guid AND object_type = p_object_type AND target_guid = l_target_guid AND metric_guid = l_metric_guid AND coll_name = p_credentials(i).collection_name AND credential_set_name = p_credentials(i).credential.credential_set_name; END; END LOOP; END; -- Update object guid of collection credentials for a template or -- template copy for a given target PROCEDURE upd_collection_template_creds(p_object_guid_old RAW, p_object_guid_new RAW, p_object_type NUMBER, p_target_guid RAW) IS BEGIN UPDATE MGMT_COLLECTION_TEMPLATE_CREDS SET object_guid = p_object_guid_new WHERE object_guid = p_object_guid_old AND object_type = p_object_type AND target_guid = p_target_guid; END; -- Insert/update ARU credentials PROCEDURE set_aru_credentials(p_username VARCHAR2, p_password VARCHAR2) IS l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user; BEGIN -- Only superusers can set ARU credentials IF MGMT_USER.has_priv(l_current_user, MGMT_USER.SUPER_USER) != MGMT_USER.USER_HAS_PRIV THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'Only Superusers can set ARU credentials'); END IF; DELETE FROM MGMT_ARU_CREDENTIALS; INSERT INTO MGMT_ARU_CREDENTIALS(aru_username, aru_password) VALUES (encrypt(p_username), encrypt(p_password)); END; -- Delete ARU credentials PROCEDURE delete_aru_credentials IS l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user; BEGIN -- Only superusers can set ARU credentials IF MGMT_USER.has_priv(l_current_user, MGMT_USER.SUPER_USER) != MGMT_USER.USER_HAS_PRIV THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'Only Superusers can delete ARU credentials'); END IF; DELETE FROM MGMT_ARU_CREDENTIALS; END; -- Return the credential columns for the specified credential guid PROCEDURE get_cred_rows(p_credential_guid RAW, p_cred_rows_out OUT NOCOPY MGMT_CRED_ROW_ARRAY) IS BEGIN SELECT MGMT_CRED_ROW_RECORD(credential_set_column, decode(credential_value, null, null, decrypt(credential_value))) BULK COLLECT INTO p_cred_rows_out FROM MGMT_CREDENTIALS2 WHERE credential_guid=p_credential_guid; END; -- Return all the credential columns for the specified credential -- set at enterprise level given the specified host. FUNCTION get_enterprise_creds(p_credential_set VARCHAR2, p_target_type VARCHAR2, p_user_name VARCHAR2) RETURN MGMT_CRED_RECORD IS l_ent_cred_guid RAW(16); l_cred_rows MGMT_CRED_ROW_ARRAY; l_pdp_data VARCHAR(512); l_user_name VARCHAR2(256) := get_user_name(p_user_name); BEGIN l_ent_cred_guid := generate_enterprise_cred_guid(p_target_type, p_credential_set, l_user_name); -- Look for credentials at enterprise level get_cred_rows(l_ent_cred_guid, l_cred_rows); IF l_cred_rows IS NOT NULL AND l_cred_rows.COUNT > 0 THEN SELECT pdp_data INTO l_pdp_data FROM MGMT_ENTERPRISE_CREDENTIALS WHERE target_type = p_target_type AND credential_set_name = p_credential_set AND user_name = l_user_name; RETURN MGMT_CRED_RECORD(l_user_name,p_credential_set, l_cred_rows,l_pdp_data); END IF; RETURN NULL; END; -- Return the default enterprise credentials FUNCTION get_enterprise_creds_default(p_credential_set VARCHAR2, p_target_type VARCHAR2, p_user_name VARCHAR2, p_instance_type VARCHAR2 DEFAULT NULL) RETURN MGMT_CRED_RECORD IS l_creds MGMT_CRED_RECORD; BEGIN l_creds := get_enterprise_creds(p_credential_set, p_target_type, p_user_name); IF l_creds IS NOT NULL THEN RETURN l_creds; END IF; IF p_instance_type IS NOT NULL THEN RETURN get_enterprise_creds(p_credential_set, p_instance_type, p_user_name); END IF; RETURN NULL; END; -- Return host credentials for the specified host/username. FUNCTION get_host_credentials(p_credential_set VARCHAR2, p_target_type VARCHAR2, p_host_guid RAW, p_user_name VARCHAR2) RETURN MGMT_CRED_RECORD IS l_host_cred_guid RAW(16); l_cred_rows MGMT_CRED_ROW_ARRAY; l_pdp_data VARCHAR(512); l_user_name VARCHAR2(256) := get_user_name(p_user_name); BEGIN l_host_cred_guid := generate_host_cred_guid(p_host_guid, p_target_type, p_credential_set, l_user_name); -- Look for credentials at host level get_cred_rows(l_host_cred_guid, l_cred_rows); IF l_cred_rows IS NOT NULL AND l_cred_rows.COUNT > 0 THEN SELECT pdp_data INTO l_pdp_data FROM MGMT_HOST_CREDENTIALS WHERE host_guid = p_host_guid AND target_type = p_target_type AND credential_set_name = p_credential_set AND user_name = l_user_name; RETURN MGMT_CRED_RECORD(l_user_name,p_credential_set, l_cred_rows,l_pdp_data); END IF; RETURN NULL; END; -- Return all the credential columns for the specified credential -- set starting at host level given the specified host. -- Note that all storage levels for the specified set are searched, -- starting at host level FUNCTION get_host_creds_default(p_credential_set VARCHAR2, p_target_type VARCHAR2, p_host_guid RAW, p_user_name VARCHAR2, p_instance_guid RAW DEFAULT NULL, p_instance_type VARCHAR2 DEFAULT NULL) RETURN MGMT_CRED_RECORD IS l_creds MGMT_CRED_RECORD; BEGIN l_creds := get_host_credentials(p_credential_set, p_target_type, p_host_guid, p_user_name); IF l_creds IS NOT NULL THEN RETURN l_creds; END IF; -- Look for instance level credentials if an instance has -- been specified IF p_instance_guid IS NOT NULL THEN l_creds := get_host_credentials(p_credential_set, p_instance_type, p_host_guid, p_user_name); IF l_creds IS NOT NULL THEN RETURN l_creds; END IF; END IF; -- If we're here, we didn't find credentials at host level. -- Look for them at the enterprise level RETURN get_enterprise_creds_default(p_credential_set, p_target_type, p_user_name, p_instance_type); END; -- Get container credentials for the specified target/cred set FUNCTION get_container_credentials(p_credential_set VARCHAR2, p_target_type VARCHAR2, p_target_guid RAW, p_container_location VARCHAR2, p_user_name VARCHAR2) RETURN MGMT_CRED_RECORD IS l_cont_cred_guid RAW(16); l_cred_rows MGMT_CRED_ROW_ARRAY; l_pdp_data VARCHAR2(512); l_user_name VARCHAR2(256) := get_user_name(p_user_name); BEGIN l_cont_cred_guid := generate_container_cred_guid(p_target_guid, p_container_location, p_credential_set, l_user_name); -- Look for credentials at cntainer level get_cred_rows(l_cont_cred_guid, l_cred_rows); IF l_cred_rows IS NOT NULL AND l_cred_rows.COUNT > 0 THEN SELECT pdp_data INTO l_pdp_data FROM MGMT_CONTAINER_CREDENTIALS WHERE target_guid = p_target_guid AND container_location = p_container_location AND credential_set_name = p_credential_set AND user_name = l_user_name; RETURN MGMT_CRED_RECORD(l_user_name,p_credential_set, l_cred_rows,l_pdp_data); END IF; RETURN NULL; END; -- Return all the credential columns for the specified credential -- set, given the specified target/container. Note that all storage -- levels for the specified set are searched, starting with the -- container level FUNCTION get_container_creds_default(p_credential_set VARCHAR2, p_target_type VARCHAR2, p_target_guid RAW, p_container_location VARCHAR2, p_user_name VARCHAR2, p_instance_guid RAW DEFAULT NULL, p_instance_type RAW DEFAULT NULL) RETURN MGMT_CRED_RECORD IS l_creds MGMT_CRED_RECORD; BEGIN l_creds := get_container_credentials(p_credential_set, p_target_type, p_target_guid, p_container_location, p_user_name); IF l_creds IS NOT NULL THEN RETURN l_creds; END IF; -- Check for credentials at the instance level IF p_instance_guid IS NOT NULL THEN l_creds := get_container_credentials(p_credential_set, p_instance_type, p_instance_guid, p_container_location, p_user_name); IF l_creds IS NOT NULL THEN RETURN l_creds; END IF; END IF; -- If we're here, we didn't find credentials at the container -- level, look for the host level. Note that the target itself -- must be a host RETURN get_host_creds_default(p_credential_set, p_target_type, p_target_guid, p_user_name, p_instance_guid, p_instance_type); END; -- Return the target credentials for the specified target, and type FUNCTION get_target_credentials(p_credential_set VARCHAR2, p_target_type VARCHAR2, p_target_guid RAW, p_user_name VARCHAR2) RETURN MGMT_CRED_RECORD IS l_target_cred_guid RAW(16); l_cluster_cred_guid RAW(16); l_cred_rows MGMT_CRED_ROW_ARRAY; l_cluster_guid MGMT_TARGETS.target_guid%TYPE; l_user_name VARCHAR2(256) := get_user_name(p_user_name); l_pdp_data VARCHAR(512); BEGIN l_target_cred_guid := generate_target_cred_guid(p_target_guid, p_credential_set, l_user_name); -- Look for credentials at target level get_cred_rows(l_target_cred_guid, l_cred_rows); IF l_cred_rows IS NOT NULL AND l_cred_rows.COUNT > 0 THEN SELECT pdp_data INTO l_pdp_data FROM MGMT_TARGET_CREDENTIALS WHERE target_guid = p_target_guid AND credential_set_name = p_credential_set AND user_name = l_user_name; RETURN MGMT_CRED_RECORD(l_user_name,p_credential_set, l_cred_rows,l_pdp_data); END IF; -- See if the target is a member of a target that is a cluster -- If so, then look for credentials at cluster level BEGIN -- This query assumes an instance cannot be a member -- of more than one cluster SELECT target_guid INTO l_cluster_guid FROM MGMT_TARGETS t, MGMT_TARGET_ASSOCS m, MGMT_TYPE_PROPERTIES p WHERE t.target_guid=m.source_target_guid AND m.assoc_target_guid=p_target_guid AND m.assoc_guid = MGMT_ASSOC.g_contains_guid AND t.target_type=p.target_type AND p.property_name=MGMT_GLOBAL.G_IS_CLUSTER_PROP AND p.property_value=1; -- See if credentials are stored at the cluster level l_cluster_cred_guid := generate_target_cred_guid(l_cluster_guid, p_credential_set, l_user_name); get_cred_rows(l_cluster_cred_guid, l_cred_rows); IF l_cred_rows IS NOT NULL AND l_cred_rows.COUNT > 0 THEN SELECT pdp_data INTO l_pdp_data FROM MGMT_TARGET_CREDENTIALS WHERE target_guid = l_cluster_guid AND credential_set_name = p_credential_set AND user_name = l_user_name; RETURN MGMT_CRED_RECORD(l_user_name, p_credential_set, l_cred_rows,l_pdp_data); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN -- OK, this target is not a member of a cluster; -- let's move on NULL; END; RETURN null; END; -- 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 FUNCTION get_target_creds_default_guid(p_credential_set VARCHAR2, p_target_type VARCHAR2, p_target_guid RAW, p_user_name VARCHAR2, p_instance_guid RAW DEFAULT NULL, p_instance_type VARCHAR2 DEFAULT NULL) RETURN MGMT_CRED_RECORD IS l_creds MGMT_CRED_RECORD; l_cluster_guid MGMT_TARGETS.target_guid%TYPE; l_host_guid MGMT_TARGETS.target_guid%TYPE; BEGIN l_creds := get_target_credentials(p_credential_set, p_target_type, p_target_guid, p_user_name); IF l_creds IS NOT NULL THEN RETURN l_creds; END IF; IF p_instance_guid IS NOT NULL THEN -- See whether the instance has any target credentials stored l_creds := get_target_credentials(p_credential_set, p_instance_type, p_instance_guid, p_user_name); IF l_creds IS NOT NULL THEN RETURN l_creds; END IF; END IF; -- If we're here, we couldn't find the credentials at the -- target or cluster level. Look at the host level BEGIN -- Look for the host target SELECT target_guid INTO l_host_guid FROM MGMT_TARGETS t WHERE target_name=(SELECT host_name FROM MGMT_TARGETS WHERE target_guid=p_target_guid) AND target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN get_enterprise_creds_default(p_credential_set, p_target_type, p_user_name, p_instance_type); END; RETURN get_host_creds_default(p_credential_set, p_target_type, l_host_guid, p_user_name, p_instance_guid, p_instance_type); END; 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 IS l_target_guid MGMT_TARGETS.target_guid%TYPE; BEGIN -- This is only callable by the repository owner, sys and system IF USER NOT IN (MGMT_USER.GET_REPOSITORY_OWNER, 'SYS') THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, USER || ' attempted to call this procedure. This procedure can only be called by the repository owner or sys'); END IF; l_target_guid := MGMT_TARGET.get_target_guid(p_target_name, p_target_type); RETURN get_target_creds_default_guid(p_credential_set, p_target_type, l_target_guid, p_user_name); END; -- Return all the overridden job credentials for the specified job FUNCTION get_job_credentials(p_job_id RAW) RETURN MGMT_JOB_CRED_ARRAY IS CURSOR C(p_job_id RAW) IS SELECT jc.credential_guid, t.target_name, jc.target_type, container_location, credential_set_name, user_name, pdp_data, c.credential_set_column, decode(c.credential_value, null, null, decrypt(credential_value)) FROM MGMT_JOB_CREDENTIALS jc, MGMT_TARGETS t, MGMT_CREDENTIALS2 c, MGMT_JOB j WHERE t.target_guid(+) = jc.target_guid AND jc.credential_guid=c.credential_guid AND jc.job_id=j.job_id AND j.job_id=p_job_id ORDER BY jc.credential_guid; l_credential_guids MGMT_JOB_GUID_ARRAY; l_target_names SMP_EMD_STRING_ARRAY; l_target_types SMP_EMD_STRING_ARRAY; l_container_locations SMP_EMD_STRING_ARRAY; l_credential_set_names SMP_EMD_STRING_ARRAY; l_user_names SMP_EMD_STRING_ARRAY; l_pdp_data SMP_EMD_STRING_ARRAY; l_credential_columns SMP_EMD_STRING_ARRAY; l_credential_values MGMT_JOB_VECTOR_PARAMS; l_job_creds MGMT_JOB_CRED_ARRAY := null; l_current_record MGMT_JOB_CRED_RECORD := null; l_curr_cred_guid RAW(16); l_curr_cred_values MGMT_CRED_ROW_ARRAY; l_index NUMBER := 0; l_cred_index NUMBER := 0; BEGIN OPEN C(p_job_id); FETCH C BULK COLLECT INTO l_credential_guids, l_target_names, l_target_types, l_container_locations, l_credential_set_names, l_user_names, l_pdp_data, l_credential_columns, l_credential_values; CLOSE C; IF l_credential_guids IS NULL OR l_credential_guids.COUNT=0 THEN RETURN null; END IF; l_job_creds := MGMT_JOB_CRED_ARRAY(); FOR i IN 1..l_credential_guids.COUNT LOOP IF l_curr_cred_guid IS NULL OR l_curr_cred_guid != l_credential_guids(i) THEN -- Finish up the old record IF l_current_record IS NOT NULL THEN l_job_creds(l_index).credential.creds := l_curr_cred_values; l_curr_cred_values := null; END IF; -- Start a new job record l_current_record := MGMT_JOB_CRED_RECORD.NEW(l_target_names(i), l_target_types(i), l_container_locations(i), MGMT_CRED_RECORD.NEW(l_user_names(i), l_credential_set_names(i), null,l_pdp_data(i))); l_job_creds.extend(1); l_index := l_index+1; l_job_creds(l_index) := l_current_record; l_curr_cred_values := MGMT_CRED_ROW_ARRAY(); l_cred_index := 0; l_curr_cred_guid := l_credential_guids(i); END IF; l_curr_cred_values.extend(1); l_cred_index := l_cred_index+1; l_curr_cred_values(l_cred_index) := MGMT_CRED_ROW_RECORD(l_credential_columns(i), l_credential_values(i)); END LOOP; -- Finish up the last record IF l_current_record IS NOT NULL THEN l_job_creds(l_index).credential.creds := l_curr_cred_values; END IF; RETURN l_job_creds; END; FUNCTION get_nested_job_credentials(p_job_type_id RAW, p_nested_job_name VARCHAR2, p_credential_set VARCHAR2, p_target_name VARCHAR2, p_target_type VARCHAR2, p_container_location VARCHAR2) RETURN MGMT_CRED_RECORD IS l_job_cred_guid RAW(16); l_cred_rows MGMT_CRED_ROW_ARRAY; l_pdp_data VARCHAR2(512); l_container_location MGMT_JOB_CREDENTIALS.container_location%TYPE; BEGIN l_container_location := get_container_location(p_container_location); l_job_cred_guid := generate_nested_job_cred_guid(p_job_type_id, p_nested_job_name, p_target_name, p_target_type, l_container_location, p_credential_set, SYSTEM_USER); -- Look for the credentials at the job level get_cred_rows(l_job_cred_guid, l_cred_rows); IF l_cred_rows IS NOT NULL AND l_cred_rows.COUNT > 0 THEN SELECT pdp_data INTO l_pdp_data FROM MGMT_NESTED_JOB_CRED_INFO WHERE job_type_id = p_job_type_id AND nested_job_name = p_nested_job_name AND target_name = p_target_name AND target_type = p_target_type AND container_location = l_container_location AND credential_set_name = p_credential_set; RETURN MGMT_CRED_RECORD(SYSTEM_USER,p_credential_set, l_cred_rows,l_pdp_data); END IF; RETURN null; END; -- Return all the credentials at job level for the specified target, -- or for all targets of that type, if target_guid==DEFAULT_GUID FUNCTION get_job_credentials(p_job_id RAW, p_job_owner VARCHAR2, p_credential_set VARCHAR2, p_target_type VARCHAR2, p_target_guid RAW, p_container_location VARCHAR2) RETURN MGMT_CRED_RECORD IS l_job_cred_guid RAW(16); l_cred_rows MGMT_CRED_ROW_ARRAY; l_pdp_data VARCHAR2(512); l_container_location MGMT_JOB_CREDENTIALS.container_location%TYPE; BEGIN l_container_location := get_container_location(p_container_location); l_job_cred_guid := generate_job_cred_guid(p_job_id, p_target_guid, p_target_type, l_container_location, p_credential_set, p_job_owner); -- Look for the credentials at the job level get_cred_rows(l_job_cred_guid, l_cred_rows); IF l_cred_rows IS NOT NULL AND l_cred_rows.COUNT > 0 THEN SELECT pdp_data INTO l_pdp_data FROM MGMT_JOB_CREDENTIALS WHERE job_id = p_job_id AND target_guid = p_target_guid AND target_type = p_target_type AND container_location = l_container_location AND credential_set_name = p_credential_set AND user_name = p_job_owner; RETURN MGMT_CRED_RECORD(p_job_owner,p_credential_set, l_cred_rows,l_pdp_data); END IF; RETURN NULL; END; -- 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. When the target in question is a cluster, then -- p_instance_guid, if non-null, refers to the target guid of the -- instance. Note: p_target_type refers to the target type associated -- with the credential set -- If p_job_type_id and p_task_name are not null, then we are computing -- credential information for a multitask job. The first level of access -- is the nested job credentials level -- 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 IS l_set_usage MGMT_CREDENTIAL_SETS.set_usage%TYPE; l_set_context_type MGMT_CREDENTIAL_SETS.set_context_type%TYPE; l_creds MGMT_CRED_RECORD; l_job_cred_guid RAW(16); l_container_cred_guid RAW(16); l_host_cred_guid RAW(16); l_enterprise_cred_guid RAW(16); l_user_name VARCHAR2(256); l_target_type_actual MGMT_TARGETS.target_type%TYPE := p_target_type; l_target_type_default MGMT_TARGETS.target_type%TYPE; l_target_name MGMT_TARGETS.target_name%TYPE; l_instance_name MGMT_TARGETS.target_name%TYPE; BEGIN -- This is only callable by the repository owner, sys and system IF USER NOT IN (MGMT_USER.GET_REPOSITORY_OWNER, 'SYS') THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, USER || ' attempted to call this procedure. This procedure can only be called by the repository owner or sys'); END IF; get_set_usage(p_credential_set, p_target_type, p_target_guid, l_set_usage, l_set_context_type); -- For 4.0-style creds, set the target type to be the -- target type of the target IF p_target_type IS NULL THEN IF is_40_style_set(p_credential_set) THEN SELECT target_type INTO l_target_type_actual FROM MGMT_TARGETS WHERE target_guid=p_target_guid; ELSE raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'The target type cannot be null'); END IF; END IF; -- Note: always try with the target type of the given target SELECT target_name, target_type INTO l_target_name, l_target_type_default FROM MGMT_TARGETS WHERE target_guid=p_target_guid; IF p_task_name IS NOT NULL THEN l_creds := get_nested_job_credentials(p_job_type_id, p_task_name, p_credential_set, l_target_name, l_target_type_default, p_container_location); ELSE -- Overridden credentials at target level l_creds := get_job_credentials(p_job_id, p_job_owner, p_credential_set, l_target_type_default, p_target_guid, p_container_location); END IF; IF l_creds IS NOT NULL THEN RETURN l_creds; END IF; -- For container credentials, first check all containers -- for a target IF l_set_context_type=CONTAINER_SET_CONTEXT_TYPE THEN IF p_task_name IS NOT NULL THEN l_creds := get_nested_job_credentials(p_job_type_id, p_task_name, p_credential_set, l_target_name, l_target_type_default, null); ELSE l_creds := get_job_credentials(p_job_id, p_job_owner, p_credential_set, l_target_type_default, p_target_guid, null); END IF; IF l_creds IS NOT NULL THEN RETURN l_creds; END IF; END IF; -- Default credentials (for all targets of that type) IF p_task_name IS NOT NULL THEN l_creds := get_nested_job_credentials(p_job_type_id, p_task_name, p_credential_set, DEFAULT_TARGET_NAME, l_target_type_default, null); ELSE l_creds := get_job_credentials(p_job_id, p_job_owner, p_credential_set, l_target_type_default, DEFAULT_GUID, null); END IF; IF l_creds IS NOT NULL THEN RETURN l_creds; END IF; -- If the target is a cluster and an instance is passed in, -- repeat this exercise at the instance level IF p_instance_guid IS NOT NULL THEN -- Overridden credentials at instance level. Note: if an -- instance is passed in, always use the target type -- associated with the credential set, since it must be -- the same as the target type SELECT target_name INTO l_instance_name FROM MGMT_TARGETS WHERE target_guid=p_instance_guid; IF p_task_name IS NOT NULL THEN -- instead of passing l_target_type_actual, we pass in p_instance_type in case -- of cluster targets, this would enable us to pick the right creds for the cluster -- type, more details on bug#5840623 l_creds := get_nested_job_credentials(p_job_type_id, p_task_name, p_credential_set, l_instance_name, p_instance_type, p_container_location); ELSE l_creds := get_job_credentials(p_job_id, p_job_owner, p_credential_set, p_instance_type, p_instance_guid, p_container_location); END IF; IF l_creds IS NOT NULL THEN RETURN l_creds; END IF; -- do not need the below tgt type search anymore -- **************************************** -- Default credentials (for all targets of that type) -- Note: always try with the target type of the given target -- SELECT target_type INTO l_target_type_default FROM MGMT_TARGETS -- WHERE target_guid=p_target_guid; --- **************************************** IF p_task_name IS NOT NULL THEN l_creds := get_nested_job_credentials(p_job_type_id, p_task_name, p_credential_set, DEFAULT_TARGET_NAME, p_instance_type, p_container_location); ELSE l_creds := get_job_credentials(p_job_id, p_job_owner, p_credential_set, p_instance_type, DEFAULT_GUID, p_container_location); END IF; IF l_creds IS NOT NULL THEN RETURN l_creds; END IF; END IF; -- Ok, we weren't able to find it at the job level. Look at -- target level or container level, depending on the set -- context type IF l_set_context_type = TARGET_SET_CONTEXT_TYPE THEN IF l_set_usage = PREFCRED_SET_USAGE THEN l_user_name := p_job_owner; ELSE l_user_name := SYSTEM_USER; END IF; RETURN get_target_creds_default_guid(p_credential_set, l_target_type_actual, p_target_guid, l_user_name, p_instance_guid, p_instance_type); ELSIF l_set_context_type = CONTAINER_SET_CONTEXT_TYPE THEN IF l_set_usage = PREFCRED_SET_USAGE THEN l_user_name := p_job_owner; ELSE l_user_name := SYSTEM_USER; END IF; RETURN get_container_creds_default(p_credential_set, l_target_type_actual, p_target_guid, p_container_location, l_user_name, p_instance_guid, p_instance_type); END IF; END; -- 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) IS l_new_cred_guid RAW(16); BEGIN FOR crec IN (SELECT creds.target_guid, creds.metric_guid, creds.coll_name, creds.credential_set_name, creds.credential_guid, t.target_name, t.target_type, m.metric_name FROM MGMT_COLLECTION_TEMPLATE_CREDS creds, MGMT_TARGETS t, MGMT_METRICS m WHERE creds.object_guid=p_template_guid AND creds.object_type=OBJECT_TYPE_TEMPLATE_COPY AND creds.target_guid=p_target_guid AND creds.target_guid=t.target_guid AND creds.metric_guid=m.metric_guid AND m.target_type = t.target_type AND m.type_meta_ver = t.type_meta_ver AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ')) LOOP -- Insert the credential columns into the credential table l_new_cred_guid := generate_collection_cred_guid(crec.target_type, crec.target_name, crec.credential_set_name, crec.coll_name, crec.metric_name); s_console_insert := true; -- Insert all the credential rows -- It is OK to do a direct insert without calling -- insert_creds() here since we are copying the -- associated info over as well FOR crec2 IN (SELECT credential_set_column, credential_value, credential_type_name, credential_type_column, key_value, assoc_target_guid FROM MGMT_CREDENTIALS2 WHERE credential_guid=crec.credential_guid) LOOP BEGIN INSERT INTO MGMT_CREDENTIALS2(credential_guid, credential_set_column, credential_value, credential_type_name, credential_type_column, key_value, assoc_target_guid) VALUES (l_new_cred_guid, crec2.credential_set_column, decrypt(crec2.credential_value), crec2.credential_type_name, crec2.credential_type_column, crec2.key_value, crec2.assoc_target_guid); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE MGMT_CREDENTIALS2 SET credential_value=decrypt(crec2.credential_value), credential_type_name=crec2.credential_type_name, credential_type_column=crec2.credential_type_column, key_value=crec2.key_value, assoc_target_guid=crec2.assoc_target_guid WHERE credential_guid=l_new_cred_guid AND credential_set_column=crec2.credential_set_column; END; END LOOP; -- end loop of crec2 s_console_insert := false; -- Insert the referring entry in the MGMT_COLLECTION_CREDENTIALS table BEGIN INSERT INTO MGMT_COLLECTION_CREDENTIALS(target_guid, metric_guid, coll_name, credential_set_name, credential_guid) VALUES (crec.target_guid, crec.metric_guid, crec.coll_name, crec.credential_set_name, l_new_cred_guid); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- There already is a row for this credential. -- No need for an update since the credential GUID is the same NULL; END; END LOOP; EXCEPTION WHEN OTHERS THEN s_console_insert := false; RAISE; END; -- 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. (Note: monitoring credentials are considered -- system 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) IS l_super_user NUMBER; l_user_only NUMBER := p_user_only; l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user; l_target_guid MGMT_TARGETS.target_guid%TYPE; l_cred_guids MGMT_USER_GUID_ARRAY; l_cred_rows MGMT_CRED_ROW_ARRAY; l_target_creds MGMT_TARGET_CRED_ARRAY; l_cred_set_names SMP_EMD_STRING_ARRAY; -- for UDM's l_cred_guids2 MGMT_USER_GUID_ARRAY; l_cred_rows2 MGMT_CRED_ROW_ARRAY; l_coll_creds2 MGMT_COLLECTION_CRED_ARRAY; l_cred_set_names2 SMP_EMD_STRING_ARRAY; l_metrics2 SMP_EMD_STRING_ARRAY; l_columns2 SMP_EMD_STRING_ARRAY; l_collections2 SMP_EMD_STRING_ARRAY; BEGIN l_target_guid := MGMT_TARGET.get_target_guid(p_target_name, p_target_type); l_super_user := MGMT_USER.has_priv(l_current_user, MGMT_USER.SUPER_USER); IF l_super_user=0 THEN l_user_only := 1; END IF; s_console_insert := true; -- Loop through the non-key values, set them using the key FOR i IN 1..p_non_key_cols.COUNT LOOP IF l_user_only=0 THEN -- Update all credentials UPDATE MGMT_CREDENTIALS2 c SET credential_value=p_non_key_cols(i).value WHERE credential_type_name=p_credential_type AND credential_type_column=p_non_key_cols(i).name AND credential_value=encrypt(p_old_values(i)) AND key_value=p_key_column.value AND assoc_target_guid=l_target_guid; ELSE -- Update only credentials set for the specified user UPDATE MGMT_CREDENTIALS2 c SET credential_value=p_non_key_cols(i).value WHERE credential_type_name=p_credential_type AND credential_type_column=p_non_key_cols(i).name AND credential_value=encrypt(p_old_values(i)) AND key_value=p_key_column.value AND assoc_target_guid=l_target_guid AND EXISTS (SELECT credential_guid FROM MGMT_TARGET_CREDENTIALS tc WHERE tc.credential_guid=c.credential_guid AND tc.user_name=UPPER(l_current_user) ); END IF; END LOOP; s_console_insert := false; IF p_propogate=0 THEN RETURN; ELSE -- Figure out all monitoring credential sets that were -- affected by this change SELECT credential_guid, cs.set_name BULK COLLECT INTO l_cred_guids, l_cred_set_names FROM MGMT_TARGET_CREDENTIALS tc, MGMT_CREDENTIAL_SETS cs WHERE tc.credential_set_name=cs.set_name AND tc.target_guid=l_target_guid AND cs.set_usage=MONITORING_SET_USAGE AND EXISTS (SELECT 1 FROM MGMT_CREDENTIALS2 WHERE credential_guid=tc.credential_guid AND key_value=p_key_column.value); IF l_cred_guids IS NULL OR l_cred_guids.COUNT=0 THEN IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('Found no monitoring cred sets',EM_CREDENTIAL.MODULE_NAME); END IF; END IF; l_target_creds := MGMT_TARGET_CRED_ARRAY(); l_target_creds.extend(l_cred_guids.COUNT); FOR i IN 1..l_cred_guids.COUNT LOOP IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('Propogating monitoring credential set ' || l_cred_set_names(i),EM_CREDENTIAL.MODULE_NAME); END IF; SELECT MGMT_CRED_ROW_RECORD(credential_set_column, decrypt(credential_value)) BULK COLLECT INTO l_cred_rows FROM MGMT_CREDENTIALS2 WHERE credential_guid=l_cred_guids(i) AND credential_value IS NOT NULL; l_target_creds(i) := MGMT_TARGET_CRED_RECORD.NEW(p_target_name, p_target_type, MGMT_CRED_RECORD.NEW(null, l_cred_set_names(i), l_cred_rows)); END LOOP; propogate_monitoring_creds(l_target_creds); END IF; -- Figure out all UDM credential sets that were -- affected by this change IF p_target_type!='oracle_database' AND p_target_type!='host' THEN RETURN; END IF; SELECT cc.credential_guid, cs.set_name, mm.metric_name, mm.metric_column, cc.coll_name BULK COLLECT INTO l_cred_guids2, l_cred_set_names2, l_metrics2, l_columns2, l_collections2 FROM MGMT_COLLECTION_CREDENTIALS cc, MGMT_CREDENTIAL_SETS cs, MGMT_METRICS mm WHERE cc.credential_set_name=cs.set_name AND cs.target_type = p_target_type AND cc.target_guid=l_target_guid AND cc.metric_guid=mm.metric_guid AND EXISTS (SELECT 1 FROM MGMT_CREDENTIALS2 WHERE credential_guid=cc.credential_guid AND key_value=p_key_column.value); IF l_cred_guids2 IS NULL OR l_cred_guids2.COUNT=0 THEN IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('Found no UDM cred sets',EM_CREDENTIAL.MODULE_NAME); END IF; RETURN; END IF; l_coll_creds2 := MGMT_COLLECTION_CRED_ARRAY(); l_coll_creds2.extend(l_cred_guids2.COUNT); FOR i IN 1..l_cred_guids2.COUNT LOOP SELECT MGMT_CRED_ROW_RECORD(credential_set_column, decrypt(credential_value)) BULK COLLECT INTO l_cred_rows2 FROM MGMT_CREDENTIALS2 WHERE credential_guid=l_cred_guids2(i) AND credential_value IS NOT NULL; l_coll_creds2(i) := MGMT_COLLECTION_CRED_RECORD.NEW(p_target_name, p_target_type, l_metrics2(i), l_columns2(i), l_collections2(i), MGMT_CRED_RECORD.NEW(null, l_cred_set_names2(i), l_cred_rows2)); END LOOP; propogate_udm_creds(l_coll_creds2); EXCEPTION WHEN OTHERS THEN s_console_insert := false; RAISE; END; ------------------ EM 4.0-style credentials ---------------------- ---------------- For backward compatibility only ----------------- -- The 4.0 insert_credentials procedure. This ends up inserting -- credentials using one of four "built-in" credential sets, -- which are inserted in the usual way 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, p_monitoring IN NUMBER) IS l_user VARCHAR2(256); l_target_creds MGMT_TARGET_CRED_RECORD; l_target_creds_array MGMT_TARGET_CRED_ARRAY; l_container_creds MGMT_CONTAINER_CRED_RECORD; l_container_creds_array MGMT_CONTAINER_CRED_ARRAY; l_cred_rows MGMT_CRED_ROW_ARRAY; l_creds MGMT_CRED_RECORD; BEGIN IF p_user IS NULL THEN l_user := SYSTEM_USER; ELSE l_user := upper(p_user); END IF; IF p_target_name IS NULL THEN raise_application_error(MGMT_GLOBAL.INVALID_TARGET_ERR, 'Target Name not provided'); END IF; -- First, compose a cred array using the nvpairs l_cred_rows := MGMT_CRED_ROW_ARRAY(); l_cred_rows.extend(p_credentials.COUNT); FOR i IN 1..p_credentials.COUNT LOOP l_cred_rows(i) := MGMT_CRED_ROW_RECORD(p_credentials(i).name, p_credentials(i).value); END LOOP; -- Insert into the appropriate tables using the default credential -- set names IF p_container_location IS NULL THEN -- These are target credentials IF l_user = SYSTEM_USER THEN l_creds := MGMT_CRED_RECORD.NEW(l_user, CRED_SET_40_TARGET_SYSTEM, l_cred_rows); ELSE l_creds := MGMT_CRED_RECORD.NEW(l_user, CRED_SET_40_TARGET_USER, l_cred_rows); END IF; l_target_creds := MGMT_TARGET_CRED_RECORD.NEW(p_target_name, p_target_type, l_creds); l_target_creds_array := MGMT_TARGET_CRED_ARRAY(); l_target_creds_array.extend(1); l_target_creds_array(1) := l_target_creds; set_target_credentials(l_target_creds_array); ELSE -- These are container credentials IF l_user = SYSTEM_USER THEN l_creds := MGMT_CRED_RECORD.NEW(l_user, CRED_SET_40_CONTAINER_SYSTEM, l_cred_rows); ELSE l_creds := MGMT_CRED_RECORD.NEW(l_user, CRED_SET_40_CONTAINER_USER, l_cred_rows); END IF; l_container_creds := MGMT_CONTAINER_CRED_RECORD.NEW(p_target_name, p_target_type, p_container_location, l_creds); l_container_creds_array := MGMT_CONTAINER_CRED_ARRAY(); l_container_creds_array.extend(1); l_container_creds_array(1) := l_container_creds; set_container_credentials(l_container_creds_array); END IF; END; 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) IS BEGIN insert_credentials(p_user, p_target_name, p_target_type, p_container_location, p_credentials, 0); END; 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) IS l_cred_set_name MGMT_CREDENTIAL_SETS.set_name%TYPE; l_cred_record MGMT_TARGET_CRED_RECORD; l_cred_rows MGMT_CRED_ROW_ARRAY; l_cred_array MGMT_TARGET_CRED_ARRAY; BEGIN -- We assume here that there is only one monitoring credential -- set defined for the target BEGIN SELECT set_name INTO l_cred_set_name FROM MGMT_CREDENTIAL_SETS cs, MGMT_TARGETS t WHERE t.target_name=p_target_name AND t.target_type=p_target_type AND t.target_type=cs.target_type AND cs.target_type_meta_ver=t.type_meta_ver AND set_usage=MGMT_CREDENTIAL.MONITORING_SET_USAGE AND ROWNUM=1; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'No monitoring credential set found for type ' || p_target_type); END; -- Create a target credential record from the specified -- name-value pairs l_cred_rows := MGMT_CRED_ROW_ARRAY(); l_cred_rows.extend(p_credentials.COUNT); FOR i IN 1..l_cred_rows.COUNT LOOP l_cred_rows(i) := MGMT_CRED_ROW_RECORD(p_credentials(i).name, p_credentials(i).value); END LOOP; l_cred_record := MGMT_TARGET_CRED_RECORD.NEW(p_target_name, p_target_type, MGMT_CRED_RECORD.NEW(null, l_cred_set_name, l_cred_rows)); l_cred_array := MGMT_TARGET_CRED_ARRAY(); l_cred_array.extend(1); l_cred_array(1) := l_cred_record; MGMT_CREDENTIAL.set_monitoring_credentials(l_cred_array,p_propogate_to_agent); END; 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 IS l_credential_value VARCHAR2(256); l_user VARCHAR2(256); l_cred_set_name MGMT_CREDENTIAL_SETS.set_name%TYPE; l_credential_guid RAW(16); BEGIN IF p_user IS NULL THEN l_user := SYSTEM_USER; ELSE l_user := upper(p_user); END IF; IF p_container_location IS NULL THEN -- These are target credentials IF l_user = SYSTEM_USER THEN l_cred_set_name := CRED_SET_40_TARGET_SYSTEM; ELSE l_cred_set_name := CRED_SET_40_TARGET_USER; END IF; ELSE -- These are container credentials IF l_user = SYSTEM_USER THEN l_cred_set_name := CRED_SET_40_CONTAINER_SYSTEM; ELSE l_cred_set_name := CRED_SET_40_CONTAINER_USER; END IF; END IF; IF p_target_guid IS NULL THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'target guid cannot be null'); END IF; -- First, try to get the credential guid IF p_container_location IS NULL THEN SELECT credential_guid INTO l_credential_guid FROM MGMT_TARGET_CREDENTIALS WHERE target_guid=p_target_guid AND credential_set_name=l_cred_set_name; ELSE SELECT credential_guid INTO l_credential_guid FROM MGMT_CONTAINER_CREDENTIALS WHERE target_guid=p_target_guid AND credential_set_name=l_cred_set_name AND container_location=p_container_location; END IF; -- Select the appropriate credential column now that we have -- the credential guid SELECT decode(credential_value, null, null, decrypt(credential_value)) INTO l_credential_value FROM MGMT_CREDENTIALS2 WHERE credential_guid=l_credential_guid AND credential_set_column=p_credential_column; RETURN l_credential_value; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END; PROCEDURE change_host_password(p_host_name IN VARCHAR2, p_host_user_name IN VARCHAR2, p_host_password IN VARCHAR2) IS l_host_target_guid RAW(16); l_host_user_name VARCHAR2(256) := p_host_user_name; l_emd_url VARCHAR2(1024); l_encrypted_password VARCHAR2(256) := encrypt(p_host_password); BEGIN SELECT target_guid, emd_url INTO l_host_target_guid, l_emd_url FROM MGMT_TARGETS WHERE target_name=p_host_name AND target_type='host'; -- First change the password for all entries that are -- associated with the host FOR crec IN ( SELECT container_location, user_name FROM MGMT_CREDENTIALS WHERE target_guid=l_host_target_guid AND credential_column='OS_USER' AND credential_value=encrypt(l_host_user_name) ) LOOP UPDATE MGMT_CREDENTIALS SET credential_value=l_encrypted_password WHERE target_guid=l_host_target_guid AND credential_column='OS_PASSWORD' AND container_location=crec.container_location AND user_name=crec.user_name; END LOOP; -- Now change the password for all entries associated with -- targets on the host. Note that container_location cannot -- be set for such entries FOR crec2 IN ( SELECT target_guid FROM MGMT_TARGETS WHERE emd_url=l_emd_url ) LOOP -- Update all credentials for this target FOR crec3 IN ( SELECT user_name, container_location FROM MGMT_CREDENTIALS WHERE target_guid=crec2.target_guid AND credential_column='OAPPS_OS_USER' AND credential_value=encrypt(l_host_user_name) ) LOOP UPDATE MGMT_CREDENTIALS SET credential_value=l_encrypted_password WHERE target_guid=crec2.target_guid AND credential_column='OAPPS_OS_PASSWORD' AND container_location=crec3.container_location AND user_name=crec3.user_name; END LOOP; END LOOP; END; FUNCTION get_credential_set_key_column(p_credset VARCHAR2, p_target_type VARCHAR2) RETURN VARCHAR2 IS l_key_col MGMT_CREDENTIAL_SET_COLUMNS.set_column_name%TYPE; BEGIN SELECT csc.set_column_name INTO l_key_col FROM MGMT_CREDENTIAL_TYPE_COLUMNS ctc, MGMT_CREDENTIAL_SET_COLUMNS csc WHERE ctc.type_column_name = csc.type_column_name AND ctc.target_type = csc.target_type AND ctc.key = 1 AND ctc.target_type_meta_ver = csc.target_type_meta_ver AND csc.set_name = p_credset AND csc.target_type = p_target_type AND TO_NUMBER(csc.target_type_meta_ver) = (SELECT MAX(TO_NUMBER(target_type_meta_ver)) FROM MGMT_CREDENTIAL_SETS WHERE target_type = p_target_type); RETURN l_key_col; END; END MGMT_CREDENTIAL; / show errors;