Rem
Rem $Header: host_admin_pkgbody.sql 18-apr-2007.01:19:22 nqureshi Exp $
Rem
Rem host_admin_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      host_admin_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    nqureshi    04/18/07 - XbranchMerge kmanicka_pdp5 from main
Rem    kmanicka    05/10/06 - implement PDP
Rem    ajere       04/11/05 - Add getTargetTimeZone 
Rem    ramalhot    01/17/05 - g_member_guid --> g_contains_guid 
Rem    ajere       11/08/04 - Add a procedure getExecutionID for Abort functionality 
Rem    ajere       10/22/04 - Add a procedure getTargetHostName 
Rem    ajere       10/20/04 - Update getCmdShell for oracle_database and oracle_ias targets 
Rem    ajere       09/14/04 - Support for Windows OS 
Rem    ramalhot    08/30/04 - cutover to new assoc tables 
Rem    ajere       08/13/04 - Network Shell Page procedures 
Rem    ajere       06/16/04 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

CREATE OR REPLACE PACKAGE BODY HOST_ADMIN AS

  PROCEDURE getCmdShell (target_name_in  IN  mgmt_targets.target_name%TYPE,
                         target_type_in  IN  mgmt_targets.target_type%TYPE,
                         cmd_shell_out   OUT VARCHAR2
                        )
  IS

  l_host_guid          mgmt_targets.target_guid%TYPE;
  l_oper_system        mgmt_target_properties.property_value%TYPE;
  l_host_name          mgmt_target_properties.property_value%TYPE;

  BEGIN
    cmd_shell_out := '/bin/sh';

    -- Get the host name
    IF (target_type_in='host') THEN
      l_host_name := target_name_in;
    ELSE
      getTargetHostName(target_name_in, target_type_in, l_host_name);
    END IF;

--dbms_output.put_line('l_host_name = '||l_host_name);

    -- Get the target_guid
    l_host_guid := getTargetGuid(l_host_name, 'host');

    -- Get the target host OS
    BEGIN
      SELECT property_value
      INTO l_oper_system
      FROM mgmt_target_properties
      WHERE target_guid = l_host_guid
      AND property_name = 'OS';

      EXCEPTION
        WHEN OTHERS THEN NULL;
    END;

--dbms_output.put_line('os = '||l_oper_system);

    -- Return correct command shell
    IF (UPPER(l_oper_system)='WINDOWS')
    THEN
      cmd_shell_out := 'cmd';
    END IF; 
  END ;

  PROCEDURE checkPrefCreds (
              target_name_in  IN  mgmt_targets.target_name%TYPE,
              target_type_in  IN  mgmt_targets.target_type%TYPE,
              em_user_in      IN  mgmt_target_credentials.user_name%TYPE,
              normal_exist    OUT BOOLEAN,
              priv_exist      OUT BOOLEAN)
  IS

  l_target_guid mgmt_targets.target_guid%TYPE;
  l_credential_guid mgmt_target_credentials.credential_guid%TYPE;
  l_cred_sets HA_HOST_CREDS_SET_ARR := HA_HOST_CREDS_SET_ARR(2);

  BEGIN

    normal_exist := FALSE;
    priv_exist := FALSE;

  	l_cred_sets := HA_HOST_CREDS_SET_ARR('HostCredsNormal' , 'HostCredsPriv');

		-- Get the target_guid
		l_target_guid := getTargetGuid(target_name_in, target_type_in);

    FOR i IN 1..l_cred_sets.LAST LOOP
    
      -- Get the credential guid
      l_credential_guid := getCredentialGuid(l_target_guid, UPPER(em_user_in), l_cred_sets(i));
--dbms_output.put_line('l_credential_guid = '||l_credential_guid);
      
      -- Get the username and password
      IF (l_credential_guid IS NOT NULL) THEN
        IF(l_cred_sets(i) = 'HostCredsNormal') THEN
          normal_exist := TRUE;
        ELSIF (l_cred_sets(i) = 'HostCredsPriv') THEN
          priv_exist := TRUE;
        END IF;
      END IF;

    END LOOP;

  END;
  
  PROCEDURE getPrefCreds (
           target_name_in  IN  mgmt_targets.target_name%TYPE,
           target_type_in  IN  mgmt_targets.target_type%TYPE,
           em_user_in      IN  mgmt_target_credentials.user_name%TYPE,
           credentials_out OUT HA_HOST_CREDS_ARR)
  IS

  l_target_guid mgmt_targets.target_guid%TYPE;
  l_credential_guid mgmt_target_credentials.credential_guid%TYPE;
  l_cred_sets HA_HOST_CREDS_SET_ARR := HA_HOST_CREDS_SET_ARR(2);
  l_username mgmt_credentials2.credential_value%TYPE;
  l_password mgmt_credentials2.credential_value%TYPE;

  BEGIN

  	l_cred_sets := HA_HOST_CREDS_SET_ARR('HostCredsNormal' , 'HostCredsPriv');
    credentials_out := HA_HOST_CREDS_ARR();

    -- Target level credentials
		-- Get the target_guid
		l_target_guid := getTargetGuid(target_name_in, target_type_in);

    FOR i IN 1..l_cred_sets.LAST LOOP
    
      --Extend the array by 1
      credentials_out.extend(1);

      -- Get the credential guid
      l_credential_guid := getCredentialGuid(l_target_guid, UPPER(em_user_in), l_cred_sets(i));
--dbms_output.put_line('l_credential_guid = '||l_credential_guid);
      
      -- Get the username and password
      IF (l_credential_guid IS NOT NULL) THEN

        l_username := getCredColumn(l_credential_guid, 'username');
        l_password := getCredColumn(l_credential_guid, 'password');
  
        --Store in the array
        credentials_out(i) := HA_HOST_CREDS_OBJ(l_cred_sets(i),
                                                l_username, 
                                                l_password);

      ELSE
        credentials_out(i) := HA_HOST_CREDS_OBJ(l_cred_sets(i),
                                                NULL, 
                                                NULL);
      END IF;

    END LOOP;

    -- Enterprise level credentials (default credentials)
    FOR i IN 1..l_cred_sets.LAST LOOP
    
      --Extend the array by 1
      credentials_out.extend(1);

      -- Get the credential guid
      l_credential_guid := getCredentialGuid(l_target_guid, UPPER(em_user_in), l_cred_sets(i), FALSE);
--dbms_output.put_line('l_credential_guid = '||l_credential_guid);
      
      -- Get the username and password
      IF (l_credential_guid IS NOT NULL) THEN

        l_username := getCredColumn(l_credential_guid, 'username');
        l_password := getCredColumn(l_credential_guid, 'password');
  
        --Store in the array
        credentials_out(i+2) := HA_HOST_CREDS_OBJ(l_cred_sets(i),
                                                l_username, 
                                                l_password);

      ELSE
        credentials_out(i+2) := HA_HOST_CREDS_OBJ(l_cred_sets(i),
                                                NULL, 
                                                NULL);
      END IF;

    END LOOP;

  END;
  
  PROCEDURE checkCmdShellAndPrefCreds (
              target_name_in  IN  mgmt_targets.target_name%TYPE,
              target_type_in  IN  mgmt_targets.target_type%TYPE,
              em_user_in      IN  mgmt_target_credentials.user_name%TYPE,
              cmd_shell_out   OUT VARCHAR2,
              normal_exist    OUT BOOLEAN,
              priv_exist      OUT BOOLEAN
            )
  IS
  
  BEGIN
    --Get command shell
    getCmdShell(target_name_in, target_type_in, cmd_shell_out);
    
    --Check Host Preferred Credentials
    checkPrefCreds(target_name_in, target_type_in, em_user_in, normal_exist, priv_exist);
  END;

  PROCEDURE getCmdShellAndPrefCreds (
             target_name_in  IN  mgmt_targets.target_name%TYPE,
             target_type_in  IN  mgmt_targets.target_type%TYPE,
             em_user_in      IN  mgmt_target_credentials.user_name%TYPE,
             cmd_shell_out   OUT VARCHAR2,
             credentials_out OUT HA_HOST_CREDS_ARR
          )
  IS
  
  BEGIN
    --Get command shell
    getCmdShell(target_name_in, target_type_in, cmd_shell_out);
    
    --Get Host Preferred Credentials
    getPrefCreds(target_name_in, target_type_in, em_user_in, credentials_out);
  END;

  FUNCTION getTargetGuid (
             							target_name_in IN mgmt_targets.target_name%TYPE,
             							target_type_in IN mgmt_targets.target_type%TYPE
           								) RETURN mgmt_targets.target_guid%TYPE
	IS

  l_target_guid  mgmt_targets.target_guid%TYPE;

	BEGIN

    SELECT target_guid
    INTO   l_target_guid
    FROM   mgmt_targets
    WHERE  target_name = target_name_in
    AND    target_type = target_type_in;

		return l_target_guid;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      RAISE MGMT_GLOBAL.target_does_not_exist;

		return null;

	END;
  
  FUNCTION getCredentialGuid (
             							target_guid_in            IN mgmt_targets.target_guid%TYPE,
                          em_user_in                IN mgmt_target_credentials.user_name%TYPE,
             							cred_set_name_in          IN mgmt_target_credentials.credential_set_name%TYPE,
                          get_target_level_creds_in IN BOOLEAN DEFAULT TRUE
           								) RETURN mgmt_target_credentials.credential_guid%TYPE
	IS

  l_credential_guid mgmt_target_credentials.credential_guid%TYPE;

	BEGIN

    IF (get_target_level_creds_in = TRUE) THEN
      -- Target level Host Credentials
      SELECT credential_guid
      INTO   l_credential_guid
      FROM   mgmt_target_credentials
      WHERE  target_guid = target_guid_in
      AND    user_name = em_user_in
      AND    credential_set_name = cred_set_name_in;
    ELSE
      -- Enterprise level Host Credentials
      SELECT credential_guid
      INTO   l_credential_guid
      FROM   mgmt_enterprise_credentials
      WHERE  target_type = 'host'
      AND    user_name = em_user_in
      AND    credential_set_name = cred_set_name_in;
    END IF;

		return l_credential_guid;

    EXCEPTION
      WHEN OTHERS THEN NULL;

		return null;

	END;
  
  FUNCTION getCredColumn (
               cred_guid_in       IN  mgmt_target_credentials.credential_guid%TYPE,
               cred_set_column_in IN  mgmt_credentials2.credential_set_column%TYPE
            ) RETURN mgmt_credentials2.credential_value%TYPE
  IS

  l_credential_value mgmt_credentials2.credential_value%TYPE;

	BEGIN

    SELECT decrypt(credential_value)
     INTO   l_credential_value
     FROM   mgmt_credentials2
     WHERE  credential_guid = cred_guid_in
     AND    credential_set_column = cred_set_column_in;

		return l_credential_value;

    EXCEPTION
      WHEN OTHERS THEN NULL;

		return null;

	END;
  
 PROCEDURE getPrefCreds (
             target_name_in     IN  mgmt_targets.target_name%TYPE,
             target_type_in     IN  mgmt_targets.target_type%TYPE,
             em_user_in         IN  mgmt_target_credentials.user_name%TYPE,
             cred_set_name_in IN  mgmt_target_credentials.credential_set_name%TYPE,
             user_name_out      OUT mgmt_credentials2.credential_value%TYPE,
             password_out       OUT mgmt_credentials2.credential_value%TYPE
            )
  IS

  l_target_guid mgmt_targets.target_guid%TYPE;
  l_credential_guid mgmt_target_credentials.credential_guid%TYPE;

  BEGIN

    user_name_out := null;
    password_out := null;

    -- Get the target_guid
    l_target_guid := getTargetGuid(target_name_in, target_type_in);

    -- Get the credential guid
    l_credential_guid := getCredentialGuid(l_target_guid, UPPER(em_user_in), cred_set_name_in);

--dbms_output.put_line('l_credential_guid = '||l_credential_guid);

    -- Get the username and password
    IF (l_credential_guid IS NOT NULL) THEN
     -- username
     SELECT decrypt(credential_value)
     INTO   user_name_out
     FROM   mgmt_credentials2
     WHERE  credential_guid = l_credential_guid
     AND    credential_set_column = 'username';

     -- password
     SELECT decrypt(credential_value)
     INTO   password_out
     FROM   mgmt_credentials2
     WHERE  credential_guid = l_credential_guid
     AND    credential_set_column = 'password';

    END IF;
  END ;

  PROCEDURE saveAsPreferredCreds (
              target_name_in  IN  mgmt_targets.target_name%TYPE,
              target_type_in  IN  mgmt_targets.target_type%TYPE,
              em_user_in      IN  mgmt_target_credentials.user_name%TYPE,
              user_name_in    IN mgmt_credentials2.credential_value%TYPE,
              password_in     IN mgmt_credentials2.credential_value%TYPE,
              cred_set_name_in IN  mgmt_target_credentials.credential_set_name%TYPE
            )
  IS

  l_mgmt_creds_row MGMT_CRED_ROW_RECORD;
  l_mgmt_creds_row_arr MGMT_CRED_ROW_ARRAY;
  l_mgmt_cred_record MGMT_CRED_RECORD;
  l_mgmt_target_cred_record MGMT_TARGET_CRED_RECORD;
  l_mgmt_target_cred_arr MGMT_TARGET_CRED_ARRAY;

  BEGIN
    -- Credential row array
    l_mgmt_creds_row_arr := MGMT_CRED_ROW_ARRAY();
    l_mgmt_creds_row_arr.extend(2);
    l_mgmt_creds_row := MGMT_CRED_ROW_RECORD('username', user_name_in);
    l_mgmt_creds_row_arr(1) := l_mgmt_creds_row;
    l_mgmt_creds_row := MGMT_CRED_ROW_RECORD('password', password_in);
    l_mgmt_creds_row_arr(2) := l_mgmt_creds_row;

    -- Credential record
    l_mgmt_cred_record := MGMT_CRED_RECORD.NEW(UPPER(em_user_in), cred_set_name_in, l_mgmt_creds_row_arr); 

    -- Target credential array
    l_mgmt_target_cred_arr := MGMT_TARGET_CRED_ARRAY();
    l_mgmt_target_cred_arr.extend(1);
    l_mgmt_target_cred_record := MGMT_TARGET_CRED_RECORD.NEW(target_name_in, target_type_in, l_mgmt_cred_record);
    l_mgmt_target_cred_arr(1) := l_mgmt_target_cred_record;

    -- Save credentials to the repository
    mgmt_credential.set_target_credentials(l_mgmt_target_cred_arr);
    commit;
  END ;

  PROCEDURE getMemberHosts (
              group_target_name_in IN  mgmt_targets.target_name%TYPE,
              member_host_list_out OUT cursorType
            )
  IS
  BEGIN

  OPEN member_host_list_out FOR
  SELECT mt.target_name
  FROM   mgmt_target_assocs, 
         mgmt_targets ct,
         mgmt_targets mt
  WHERE  ct.target_name = group_target_name_in
    AND  assoc_guid = MGMT_ASSOC.g_contains_guid
    AND  ct.target_guid = source_target_guid
    AND  mt.target_guid = assoc_target_guid 
    AND  mt.target_type = 'host';

  END;

  PROCEDURE getTargetHostName (target_name_in  IN  mgmt_targets.target_name%TYPE,
                               target_type_in  IN  mgmt_targets.target_type%TYPE,
                               host_name_out   OUT VARCHAR2
                              )
  IS
  BEGIN
    -- Get the target host name
    BEGIN
      SELECT host_name
      INTO host_name_out
      FROM mgmt_targets
      WHERE target_name = target_name_in
      AND   target_type = target_type_in;
 
      EXCEPTION
        WHEN OTHERS THEN NULL;
    END;
  END ;

  PROCEDURE getExecutionID ( 
                             execution_id_out OUT NUMBER
                           )
  IS
  BEGIN
    execution_id_out := -1;

    -- Get the next sequence number 
    BEGIN
      SELECT mgmt_job_sequence.nextval
      INTO execution_id_out
      FROM dual;
 
      EXCEPTION
        WHEN OTHERS THEN NULL;
    END;
  END ;

  PROCEDURE getTargetTimeZone (
              target_name_in  IN  mgmt_targets.target_name%TYPE,
              target_type_in  IN  mgmt_targets.target_type%TYPE,
              time_zone_out   OUT VARCHAR2
            )
  IS
  BEGIN
    time_zone_out := null;

    -- Get the target timezone
    BEGIN
      SELECT timezone_region
      INTO time_zone_out
      FROM mgmt_targets
      WHERE target_name = target_name_in
      AND target_type = target_type_in;

      EXCEPTION
        WHEN OTHERS THEN NULL;
    END;
  END ;

END HOST_ADMIN;
/