Rem
Rem $Header: user_login_assistant_pkgbody.sql 23-mar-2004.03:42:49 skini Exp $
Rem
Rem user_login_assistant_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2004, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      user_login_assistant_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      Allows login assistants to be registered, deregistered
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED (MM/DD/YY)
Rem    skini     03/23/04 - Fix ARU authentication 
Rem    rpinnama  05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama  05/15/02 - Restructured.
Rem    skini     03/10/02 - skini_add_target_command
Rem    skini     03/04/02 - Created
Rem

CREATE OR REPLACE PACKAGE BODY MGMT_LOGIN_ASSISTANT AS

-- Register a new login assistant. The new assistant will be
-- registered at the end of the existing list.
PROCEDURE register_login_assistant(p_name IN VARCHAR2,
                                   p_classname IN VARCHAR2) IS
l_max_index NUMBER;
BEGIN
    SELECT NVL(MAX(login_index), 0) INTO l_max_index FROM MGMT_LOGIN_ASSISTANTS;

    INSERT INTO MGMT_LOGIN_ASSISTANTS(login_asst_name, login_index,
                                      login_asst_classname)
       VALUES (upper(p_name), l_max_index+1, p_classname);
END;

-- Specify which login assistants are enabled, and in what order.
-- p_names is an array of login assistant names. The assistants
-- will be tried in the same order as specified in the array.
-- Any assistants not in the array will be disabled.
--
PROCEDURE login_assistant_order(p_names IN SMP_EMD_STRING_ARRAY) IS
BEGIN
    -- First, disable all assistants. We will only enable ones that
    -- are in the list.
    UPDATE MGMT_LOGIN_ASSISTANTS SET login_index=-1;

    FOR i IN 1..p_names.count LOOP
        UPDATE MGMT_LOGIN_ASSISTANTS SET login_index=i WHERE
            login_asst_name=upper(p_names(i));

        IF SQL%ROWCOUNT=0 THEN
            raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
              'Invalid login assistant: ' || p_names(i));
        END IF;
    END LOOP;
END;

-- Configure ARU authentication
PROCEDURE configure_aru_authentication(p_aru_host VARCHAR2,
                                       p_aru_port NUMBER,
                                       p_aru_sid VARCHAR2,
                                       p_aru_username VARCHAR2,
                                       p_aru_password VARCHAR2) IS
unique_constraint EXCEPTION;
PRAGMA EXCEPTION_INIT(unique_constraint, -1);

l_connect_string VARCHAR2(256);
l_credentials SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY();
l_max_index NUMBER;
BEGIN
    l_connect_string := p_aru_host || ':' || p_aru_port || ':' ||
                     p_aru_sid;

    -- The host, port and sid are stored as a connect string in the
    -- MGMT_PARAMETERS table.
    BEGIN
        INSERT INTO MGMT_PARAMETERS(parameter_name, parameter_value, 
                                    parameter_comment) VALUES
          (ARU_LOC_PARAM, l_connect_string, 
            'ARU location for ARU-based authentication');
    EXCEPTION
    WHEN unique_constraint THEN
        UPDATE MGMT_PARAMETERS SET parameter_value=l_connect_string
            WHERE parameter_name=ARU_LOC_PARAM;
    END;

    -- Insert the ARU username
    BEGIN
        INSERT INTO MGMT_PARAMETERS(parameter_name, parameter_value, 
                                    parameter_comment) VALUES
          (ARU_USERNAME_PARAM, encrypt(p_aru_username),
            'ARU username for ARU-based authentication');
    EXCEPTION
    WHEN unique_constraint THEN
        UPDATE MGMT_PARAMETERS SET parameter_value=encrypt(p_aru_username)
            WHERE parameter_name=ARU_USERNAME_PARAM;
    END;

    -- Insert the ARU password
    BEGIN
        INSERT INTO MGMT_PARAMETERS(parameter_name, parameter_value, 
                                    parameter_comment) VALUES
          (ARU_PASSWORD_PARAM, encrypt(p_aru_password),
            'ARU password for ARU-based authentication');
    EXCEPTION
    WHEN unique_constraint THEN
        UPDATE MGMT_PARAMETERS SET parameter_value=encrypt(p_aru_password)
            WHERE parameter_name=ARU_PASSWORD_PARAM;
    END;       

    SELECT NVL(MAX(login_index), 0) INTO l_max_index FROM MGMT_LOGIN_ASSISTANTS;
    UPDATE MGMT_LOGIN_ASSISTANTS SET login_index=l_max_index+1 WHERE 
        login_asst_name=ARU_LOGIN_ASSISTANT;
END;

PROCEDURE get_aru_parameters(p_aru_connect_string_out OUT VARCHAR2,
                             p_aru_username_out OUT VARCHAR2,
                             p_aru_password_out OUT VARCHAR2) IS
BEGIN
    SELECT parameter_value INTO p_aru_connect_string_out FROM
        MGMT_PARAMETERS WHERE parameter_name=ARU_LOC_PARAM;

    SELECT decrypt(parameter_value) INTO p_aru_username_out FROM
        MGMT_PARAMETERS WHERE parameter_name=ARU_USERNAME_PARAM;

    SELECT decrypt(parameter_value) INTO p_aru_password_out FROM
        MGMT_PARAMETERS WHERE parameter_name=ARU_PASSWORD_PARAM;

END;

END MGMT_LOGIN_ASSISTANT;
/
show errors;