Rem drv: <create type="pkgbodys" pos="credentials/credentials_ui_pkgbody.sql+"/>
Rem
Rem $Header: credentials_pkgbody.sql 24-oct-2006.08:22:24 sradhakr Exp $
Rem
Rem credential_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      credential_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      This package contains internal routines for the credentials
Rem      subsystem    
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED  
Rem       sradhakr   10/23/06 - Backport sradhakr_bug-5382567 from main
Rem       rpinnama   09/12/05 - Fix 4605552 : Add HostCredsUDM credential set, 
Rem                             if missing 
Rem       dsahrawa   09/08/05 - fix bug 4586117, handle credential sets which 
Rem                             use HostCreds 
Rem       rpinnama   08/27/05 - Add Credential type also 
Rem       rpinnama   08/26/05 - Add target_add, metaver_cp_upd callbacks to 
Rem                             add DBCredsUDM credential 
Rem       pkantawa   08/09/05 - Fix 4533905: handle rac/cluster correctly 
Rem       gsbhatia   07/01/05 - New repmgr header impl 
Rem       skini      02/20/05 - Special-case for hosts and databases 
Rem       skini      01/11/05 - Fix pref creds for null emd urls 
Rem       skini      11/24/04 - Delete credentials when user deleted 
Rem       skini      11/01/04 - Update job single target types 
Rem       skini      09/10/04 - skini_3855061_2
Rem       skini      09/10/04 - Code review comments 
Rem

CREATE OR REPLACE PACKAGE BODY EM_CREDENTIAL
AS

  -- This procedure checks for the existance of HostCreds credential
  -- set for the given target_type, type_meta_ver, if the credential
  -- set is not already present, it is created for the given
  -- target_type, type_meta_ver - added with Bug#5382567.
PROCEDURE  add_host_credset(p_target_type VARCHAR2,
                         p_type_meta_ver VARCHAR2) IS
l_host_cred_sets NUMBER;     
l_type_cols MGMT_CRED_TYPE_COL_ARRAY := MGMT_CRED_TYPE_COL_ARRAY();
l_host_type_meta_ver MGMT_TARGETS.type_meta_ver%TYPE;
l_host_ref MGMT_CRED_TYPE_REF;
l_refs MGMT_CRED_TYPE_REF_ARRAY := MGMT_CRED_TYPE_REF_ARRAY();
l_set_cols MGMT_CRED_SET_COL_ARRAY := MGMT_CRED_SET_COL_ARRAY();  

  BEGIN
  
    SELECT  MAX(type_meta_ver) INTO l_host_type_meta_ver
    FROM    MGMT_METRICS
    WHERE   target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE;

    IF l_host_type_meta_ver IS NULL THEN
        l_host_type_meta_ver := '1.0';
    END IF;    
    -- Check whether there are any host credential sets for the
    -- specified target type
    SELECT  COUNT(1) INTO l_host_cred_sets
    FROM    MGMT_CREDENTIAL_SETS s, MGMT_CREDENTIAL_TYPE_REF r
    WHERE   p_target_type=s.target_type
    AND     p_type_meta_ver=s.target_type_meta_ver
    AND     s.credential_type_name=r.type_name
    AND     s.target_type=r.target_type
    AND     s.target_type_meta_ver=r.target_type_meta_ver
    AND     r.ref_target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE
    AND     r.ref_type_name=HOST_CREDS;

    -- If there already is a set of host credentials, return immediately
    IF l_host_cred_sets > 0 THEN
        RETURN;
    END IF;

    -- Create a new type based on host credentials
    l_host_ref := MGMT_CRED_TYPE_REF('HostRef', HOST_CREDS, 
                                     MGMT_GLOBAL.G_HOST_TARGET_TYPE,
                                     l_host_type_meta_ver,
                                     MGMT_GLOBAL.G_HOST_TARGET_TYPE);
    l_refs.extend(1);
    l_refs(1) := l_host_ref;

    l_type_cols.extend(2);
    l_type_cols(1) := MGMT_CRED_TYPE_COL_RECORD('HostUserName', 1,
                                                'HostRef', 'HostUserName',
                                                'Host UserName',
                                                'CREDS_HOSTTYPE_USERNAME',
                                                   null);
    l_type_cols(2) := MGMT_CRED_TYPE_COL_RECORD('HostPassword', 0,
                                                   'HostRef', 'HostPassword',
                                                   'Host Password',
                                                   'CREDS_HOSTTYPE_PASSWORD',
                                                   null);


    BEGIN
        MGMT_CREDENTIAL.create_credential_type(p_target_type,
                                               HOST_CREDS, p_type_meta_ver,
                                               'Host credentials',
                                               'CREDS_HOSTCREDS',
                                               'Host credentials',
                                               l_refs,
                                               l_type_cols);
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            RETURN;
    END;

    -- Create the credential set HostCreds
    l_set_cols.extend(2);
    l_set_cols(1) := MGMT_CRED_SET_COL_RECORD('HostUserName',
                                              'HostUsername',
                                              'Host UserName',
                                              'CREDS_HOST_USER',
                                              null);

    l_set_cols(2) := MGMT_CRED_SET_COL_RECORD('HostPassword',
                                              'HostPassword',
                                              'Host Password',
                                              'CREDS_HOST_PASSWORD',
                                              null);


    BEGIN
        MGMT_CREDENTIAL.create_credential_set(p_target_type, p_type_meta_ver,
                                              HOST_CREDS,
                                              MGMT_CREDENTIAL.PREFCRED_SET_USAGE,
                                              MGMT_CREDENTIAL.TARGET_SET_CONTEXT_TYPE,
                                              null,
                                              'Host Credentials',
                                              'CREDS_HOST',
                                              'HostCreds', l_set_cols);
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            RETURN;
    END;
END add_host_credset;

PROCEDURE add_host_credential_info(p_target_name VARCHAR2,
                                   p_target_type VARCHAR2,
                                   p_target_guid RAW) IS
l_type_meta_ver MGMT_TARGETS.type_meta_ver%TYPE;
l_emd_url MGMT_TARGETS.emd_url%TYPE;

BEGIN

    SELECT  type_meta_ver, emd_url INTO l_type_meta_ver, l_emd_url
    FROM    MGMT_TARGETS
    WHERE   target_guid=p_target_guid;

    -- Do nothing if the target is not an agent-monitored target
    IF l_emd_url IS NULL OR INSTR(l_emd_url, 'http') <=0 THEN
        RETURN;
    END IF;
    
    -- Add this target type to all job types that support target types
    -- with host credentials
    FOR crec IN (SELECT job_type_id
                 FROM   MGMT_JOB_TYPE_INFO
                 WHERE  all_target_types=1) LOOP
        BEGIN
            INSERT INTO MGMT_JOB_SINGLE_TARGET_TYPES(job_type_id, 
                                                     single_target_type)
            VALUES (crec.job_type_id, p_target_type);
        EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                NULL;
        END;
    END LOOP;
    -- call the refactored procedure for adding host creds
    add_host_credset(p_target_type,l_type_meta_ver);
END;

  -- This procedure checks for the existance of HostUDMCreds credential
  --  set for the given target_type, type_meta_ver.
  -- If the credential set is not already present, it is created
  -- for the given target_type, type_meta_ver.
  PROCEDURE add_hostudmcreds_credset(p_target_type VARCHAR2,
                                     p_type_meta_ver VARCHAR2)
  IS
    l_hostudmcreds_cnt NUMBER;
    l_set_cols MGMT_CRED_SET_COL_ARRAY := MGMT_CRED_SET_COL_ARRAY();
    l_hostcredstyp_cnt NUMBER;
    l_type_cols MGMT_CRED_TYPE_COL_ARRAY := MGMT_CRED_TYPE_COL_ARRAY();
  BEGIN
    -- Check to see if the HostUDMCreds credential set is already present
    SELECT COUNT(1) INTO l_hostudmcreds_cnt
      FROM MGMT_CREDENTIAL_SETS s
     WHERE s.target_type = p_target_type
       AND s.target_type_meta_ver = p_type_meta_ver
       AND s.set_name = EM_CREDENTIAL.G_HOST_UDM_CRED_SET;

    IF (l_hostudmcreds_cnt = 0) THEN

      -- Check to see if DBCreds credential type exists
      SELECT COUNT(1) INTO l_hostcredstyp_cnt
        FROM MGMT_CREDENTIAL_TYPES t
       WHERE t.target_type = p_target_type
         AND t.target_type_meta_ver = p_type_meta_ver
         AND t.type_name = EM_CREDENTIAL.HOST_CREDS;

      IF (l_hostcredstyp_cnt = 0) THEN

        l_type_cols.extend(2);
        l_type_cols(1) := MGMT_CRED_TYPE_COL_RECORD(
                            type_column_name => 'HostUserName',
                            key => 1,
                            ref_name => NULL,
                            ref_column_name => NULL, 
                            type_column_display_name => 'UserName',
                            type_column_display_nlsid => 'CREDS_HOST_USERNAME',
                            col_values => MGMT_CRED_TYPE_COL_VAL_ARRAY());

        l_type_cols(2) := MGMT_CRED_TYPE_COL_RECORD(
                            type_column_name => 'HostPassword',
                            key => 0,
                            ref_name => NULL,
                            ref_column_name => NULL, 
                            type_column_display_name => 'Password',
                            type_column_display_nlsid => 'CREDS_HOST_Password',
                            col_values => MGMT_CRED_TYPE_COL_VAL_ARRAY());

        BEGIN
          mgmt_credential.create_credential_type(
            p_target_type => MGMT_GLOBAL.G_HOST_TARGET_TYPE,
            p_type_name => EM_CREDENTIAL.HOST_CREDS,
            p_target_type_meta_ver => p_type_meta_ver,
            p_type_display_name => 'Host Credentials',
            p_type_display_nlsid => 'CREDS_HOST_HOSTCREDS',
            p_description => '' ,
            p_refs => MGMT_CRED_TYPE_REF_ARRAY(),
            p_type_columns => l_type_cols);

        EXCEPTION
          WHEN OTHERS THEN
            mgmt_log.log_error(ADD_CRED_ACTION, -1,
                'Error creating HostCreds credential type for ' ||
                ' (target_type = ' || p_target_type || ') ' ||
                ' (type_meta_ver = ' || p_type_meta_ver || ') ' ||
                ' (error msg = ' || SQLERRM || ')');
            -- Ignore the error

        END;

      END IF; -- l_hostcredstype_cnt = 0

      -- Create HostUDMCreds credential set
      l_set_cols.extend(2);
      l_set_cols(1) := MGMT_CRED_SET_COL_RECORD(
                         type_column_name => 'HostUserName',
                         set_column_name => 'username',
                         set_column_values => MGMT_CRED_SET_COL_VAL_ARRAY(),
                         set_column_display_name => 'UserName',
                         set_column_display_nlsid => 'CREDS_UDM_USER');

      l_set_cols(2) := MGMT_CRED_SET_COL_RECORD(
                         type_column_name => 'HostPassword',
                         set_column_name => 'password',
                         set_column_values => MGMT_CRED_SET_COL_VAL_ARRAY(),
                         set_column_display_name => 'Password',
                         set_column_display_nlsid => 'CREDS_UDM_PASSWORD');

      BEGIN
        mgmt_credential.create_credential_set(
          p_target_type       => p_target_type,
          p_target_type_meta_ver => p_type_meta_ver,
          p_set_name          => EM_CREDENTIAL.G_HOST_UDM_CRED_SET,
          p_set_usage         => MGMT_CREDENTIAL.MONITORING_SET_USAGE,
          p_set_context_type  => MGMT_CREDENTIAL.COLLECTION_SET_CONTEXT_TYPE,
          p_set_context       => NULL,
          p_set_display_name  => NULL,
          p_set_display_nlsid => NULL,
          p_type_name         => EM_CREDENTIAL.HOST_CREDS,
          p_set_columns       => l_set_cols);

      EXCEPTION
        WHEN OTHERS THEN
          mgmt_log.log_error(ADD_CRED_ACTION, -2,
                'Error creating HostUDMCreds credential set for ' ||
                ' (target_type = ' || p_target_type || ') ' ||
                ' (type_meta_ver = ' || p_type_meta_ver || ') ' ||
                ' (error msg = ' || SQLERRM || ')');
      END;

    END IF;

  END add_hostudmcreds_credset;

  -- Target addition callback 
  -- This callback ensures that the HostUDMCreds credential set
  -- is available for the given type meta ver.
  PROCEDURE handle_host_addition(p_target_name VARCHAR2,
                                 p_target_type VARCHAR2,
                                 p_target_guid RAW)
  IS
    l_type_meta_ver mgmt_targets.type_meta_ver%TYPE;
  BEGIN
    IF (p_target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE) THEN
      SELECT type_meta_ver INTO l_type_meta_ver
        FROM mgmt_targets
       WHERE target_guid = p_target_guid;

      -- Add HostUDMCreds for the given type metaver
      add_hostudmcreds_credset(p_target_type, l_type_meta_ver);
    END IF;
  END handle_host_addition;

  -- Type_meta_ver for targets  updated callback Bug#5382567
  -- This callback ensures that the Host credential set
  -- is available for all targtes with the new type meta ver  
  PROCEDURE handle_target_metaver_update(
     p_meta_ver_cbk_obj IN MGMT_TARGET_META_VER_CBK_OBJ)
  IS
  BEGIN
    -- If to and from meta vers dont match, create Host Creds for the
    -- targets upgraded to new metaver.
    IF NOT (p_meta_ver_cbk_obj.to_meta_ver = p_meta_ver_cbk_obj.from_meta_ver) THEN
        add_host_credset(p_meta_ver_cbk_obj.target_type,
                             p_meta_ver_cbk_obj.to_meta_ver);
    END IF;

  END handle_target_metaver_update;

  -- Type_meta_ver, cat_prop updated callback
  -- This callback ensures that the HostUDMCreds credential set
  -- is available for the new type meta ver
  PROCEDURE handle_host_metaver_update(
     p_meta_ver_cbk_obj IN MGMT_TARGET_META_VER_CBK_OBJ)
  IS
  BEGIN
    -- If to and from meta vers dont match, create HostUDMCreds for the
    -- new metaver.
    IF NOT (p_meta_ver_cbk_obj.to_meta_ver = p_meta_ver_cbk_obj.from_meta_ver) THEN

      IF (p_meta_ver_cbk_obj.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE) THEN
        add_hostudmcreds_credset(p_meta_ver_cbk_obj.target_type,
                             p_meta_ver_cbk_obj.to_meta_ver);
      END IF;

    END IF;

  END handle_host_metaver_update;


  -- This procedure checks for the existance of DBCredsUDM credential
  --  set for the given target_type, type_meta_ver.
  -- If the credential set is not already present, it is created
  -- for the given target_type, type_meta_ver.
  PROCEDURE add_dbcredsudm_credset(p_target_type VARCHAR2,
                                   p_type_meta_ver VARCHAR2)
  IS
    l_dbcredsudm_cnt NUMBER;
    l_set_cols MGMT_CRED_SET_COL_ARRAY := MGMT_CRED_SET_COL_ARRAY();
    l_dbcredstyp_cnt NUMBER;
    l_type_cols MGMT_CRED_TYPE_COL_ARRAY := MGMT_CRED_TYPE_COL_ARRAY();
  BEGIN
    -- Check to see if the DBCredsUDM credential set is already present
    SELECT COUNT(1) INTO l_dbcredsudm_cnt
      FROM MGMT_CREDENTIAL_SETS s
     WHERE s.target_type = p_target_type
       AND s.target_type_meta_ver = p_type_meta_ver
       AND s.set_name = EM_CREDENTIAL.G_DATABASE_UDM_CRED_SET;

    IF (l_dbcredsudm_cnt = 0) THEN

      -- Check to see if DBCreds credential type exists
      SELECT COUNT(1) INTO l_dbcredstyp_cnt
        FROM MGMT_CREDENTIAL_TYPES t
       WHERE t.target_type = p_target_type
         AND t.target_type_meta_ver = p_type_meta_ver
         AND t.type_name = EM_CREDENTIAL.G_DATABASE_CRED_TYPE;

      IF (l_dbcredstyp_cnt = 0) THEN

        l_type_cols.extend(3);
        l_type_cols(1) := MGMT_CRED_TYPE_COL_RECORD(
                            type_column_name => 'DBUserName',
                            key => 1,
                            ref_name => NULL,
                            ref_column_name => NULL, 
                            type_column_display_name => 'UserName',
                            type_column_display_nlsid => 'CREDS_DATABASE_USERNAME',
                            col_values => MGMT_CRED_TYPE_COL_VAL_ARRAY());

        l_type_cols(2) := MGMT_CRED_TYPE_COL_RECORD(
                            type_column_name => 'DBPassword',
                            key => 0,
                            ref_name => NULL,
                            ref_column_name => NULL, 
                            type_column_display_name => 'Password',
                            type_column_display_nlsid => 'CREDS_DATABASE_PASSWORD',
                            col_values => MGMT_CRED_TYPE_COL_VAL_ARRAY());

        l_type_cols(3) := MGMT_CRED_TYPE_COL_RECORD(
                            type_column_name => 'DBRole',
                            key => 0,
                            ref_name => NULL,
                            ref_column_name => NULL,
                            type_column_display_name => 'Role',
                            type_column_display_nlsid => 'CREDS_DATABASE_ROLE',
                            col_values => MGMT_CRED_TYPE_COL_VAL_ARRAY());

        BEGIN
          mgmt_credential.create_credential_type(
            p_target_type => MGMT_GLOBAL.G_DATABASE_TARGET_TYPE,
            p_type_name => EM_CREDENTIAL.G_DATABASE_CRED_TYPE,
            p_target_type_meta_ver => p_type_meta_ver,
            p_type_display_name => 'Database Credentials',
            p_type_display_nlsid => 'CREDS_DATABASE_DBCREDS',
            p_description => '' ,
            p_refs => MGMT_CRED_TYPE_REF_ARRAY(),
            p_type_columns => l_type_cols);
        EXCEPTION
          WHEN OTHERS THEN
            mgmt_log.log_error(ADD_CRED_ACTION, -1,
                'Error creating DBCreds credential type for ' ||
                ' (target_type = ' || p_target_type || ') ' ||
                ' (type_meta_ver = ' || p_type_meta_ver || ') ' ||
                ' (error msg = ' || SQLERRM || ')');
            -- Ignore the error

        END;

      END IF;

      -- Create DBCredsUDM credential set
      l_set_cols.extend(3);
      l_set_cols(1) := MGMT_CRED_SET_COL_RECORD(
                         type_column_name => 'DBUserName',
                         set_column_name => 'UserName',
                         set_column_values => MGMT_CRED_SET_COL_VAL_ARRAY(),
                         set_column_display_name => 'UDM UserName',
                         set_column_display_nlsid => 'CREDS_UDM_USER');

      l_set_cols(2) := MGMT_CRED_SET_COL_RECORD(
                         type_column_name => 'DBPassword',
                         set_column_name => 'password',
                         set_column_values => MGMT_CRED_SET_COL_VAL_ARRAY(),
                         set_column_display_name => 'UDM Password',
                         set_column_display_nlsid => 'CREDS_UDM_PASSWORD');

      l_set_cols(3) := MGMT_CRED_SET_COL_RECORD(
                         type_column_name => 'DBRole',
                         set_column_name => 'Role',
                         set_column_values => MGMT_CRED_SET_COL_VAL_ARRAY(),
                         set_column_display_name => 'UDM Role',
                         set_column_display_nlsid => 'CREDS_UDM_ROLE');

      BEGIN
        mgmt_credential.create_credential_set(
          p_target_type       => p_target_type,
          p_target_type_meta_ver => p_type_meta_ver,
          p_set_name          => EM_CREDENTIAL.G_DATABASE_UDM_CRED_SET,
          p_set_usage         => MGMT_CREDENTIAL.MONITORING_SET_USAGE,
          p_set_context_type  => MGMT_CREDENTIAL.COLLECTION_SET_CONTEXT_TYPE,
          p_set_context       => NULL,
          p_set_display_name  => 'Database UDM Credentials',
          p_set_display_nlsid => 'CREDS_DATABASE_DBCREDS_UDM',
          p_type_name         => EM_CREDENTIAL.G_DATABASE_CRED_TYPE,
          p_set_columns       => l_set_cols);
      EXCEPTION
        WHEN OTHERS THEN
          mgmt_log.log_error(ADD_CRED_ACTION, -2,
                'Error creating DBCredsUDM credential set for ' ||
                ' (target_type = ' || p_target_type || ') ' ||
                ' (type_meta_ver = ' || p_type_meta_ver || ') ' ||
                ' (error msg = ' || SQLERRM || ')');
      END;

    END IF;

  END add_dbcredsudm_credset;


  -- Target addition callback 
  -- This callback ensures that the DBCredsUDM credential set
  -- is available for the given type meta ver.
  PROCEDURE handle_db_addition(p_target_name VARCHAR2,
                               p_target_type VARCHAR2,
                               p_target_guid RAW)
  IS
    l_type_meta_ver mgmt_targets.type_meta_ver%TYPE;
  BEGIN
    IF (p_target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE) THEN
      SELECT type_meta_ver INTO l_type_meta_ver
        FROM mgmt_targets
       WHERE target_guid = p_target_guid;

      -- Add DBCredsUDM for the given type metaver
      add_dbcredsudm_credset(p_target_type, l_type_meta_ver);
    END IF;
  END handle_db_addition;

  -- Type_meta_ver, cat_prop updated callback
  -- This callback ensures that the DBCredsUDM credential set
  -- is available for the new type meta ver
  PROCEDURE handle_db_metaver_update(
     p_meta_ver_cbk_obj IN MGMT_TARGET_META_VER_CBK_OBJ)
  IS
  BEGIN
    -- If to and from meta vers dont match, create DBCredsUDM for the
    -- new metaver.
    IF NOT (p_meta_ver_cbk_obj.to_meta_ver = p_meta_ver_cbk_obj.from_meta_ver) THEN

      IF (p_meta_ver_cbk_obj.target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE) THEN
        add_dbcredsudm_credset(p_meta_ver_cbk_obj.target_type,
                             p_meta_ver_cbk_obj.to_meta_ver);
      END IF;

    END IF;

  END handle_db_metaver_update;


-- Delete a user's preferred credentials when a user is deleted
PROCEDURE user_deleted(p_user_name VARCHAR2, p_type NUMBER) IS
BEGIN
    DELETE FROM MGMT_TARGET_CREDENTIALS WHERE user_name=p_user_name;
    DELETE FROM MGMT_CONTAINER_CREDENTIALS WHERE user_name=p_user_name;
    DELETE FROM MGMT_HOST_CREDENTIALS WHERE user_name=p_user_name;
    DELETE FROM MGMT_ENTERPRISE_CREDENTIALS WHERE user_name=p_user_name;
END;

-- Delete a user's preferred credentials when a user is deleted and
-- objects are reassigned to someone else
PROCEDURE user_reassigned(p_user_name VARCHAR2, p_new_user VARCHAR2,
                          p_type NUMBER) IS
BEGIN
    user_deleted(p_user_name, p_type);
END;

END EM_CREDENTIAL;
/

show errors;