Rem
Rem $Header: crypt_pkgbody.sql 02-nov-2007.17:04:18 rpinnama Exp $
Rem
Rem crypt_pkgbody.sql
Rem
Rem Copyright (c) 2005, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      crypt_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    rpinnama    12/28/07 - Backport rpinnama_bug-6600690 from main
Rem    shianand    07/21/06 - Backport shianand_bug-5346292 from main 
Rem    shianand    07/12/06 - fix bug 5346292, fix encrypt, decrypt from 
Rem                           rootkit attacks 
Rem    shianand    07/06/06 - fix bug 5346292, fix encrypt, decrypt from 
Rem                           rootkit attacks 
Rem    kmanicka    08/04/05 - emkey install fix
Rem    kmanicka    08/26/05 - add init_out_of_box
Rem    kmanicka    08/07/05 - dont delete the key
Rem    kmanicka    07/24/05 - fix upgrade with new repManager
Rem    kmanicka    07/22/05 - kmanicka_emkey4
Rem    kmanicka    06/10/05 - Created
Rem
CREATE OR REPLACE PACKAGE BODY MGMT_TIME_SYNC AS


TIME_COFF RAW(64) := NULL;
TIME_COFF_VERIFIER CONSTANT VARCHAR2(32) := 'ORACLE ENTERPRISE MANAGER';
TIME_COFF_VERIFIER_PARAM CONSTANT VARCHAR2(32) := 'TIME_COFF_VERIFIER';

-- getEMKey gets the key from the pkg variable set in the session
-- if it dosent find it in the session
-- it tries to get it from the repos.
-- we will hit this scenario during the em repos upgrade
-- NOTE: when the first oms starts up the key will
-- move to the file system.

FUNCTION getTimeCoff RETURN RAW AS
l_time_coff RAW(64) := NULL;           
BEGIN
    -- if key is set in session
    IF MGMT_TIME_SYNC.TIME_COFF IS NOT NULL THEN
      l_time_coff := MGMT_TIME_SYNC.TIME_COFF; 
    ELSE
      BEGIN
        -- if key is not set in session try to get the key from repos
        -- we will hit this scenario during the em repos upgrade
        SELECT time_coff INTO l_time_coff FROM MGMT_REPOS_TIME_COEFFICIENT;   
      EXCEPTION  
        WHEN NO_DATA_FOUND THEN
          NULL;
      END;
    END IF;
    RETURN l_time_coff;
END getTimeCoff;

-- setTimeCoff called by oms to set the emkey in session
-- time_coff -> em key
-- checks the key against verifier stored in MGMT_PATAMETER
-- if check fails raise invalid_em_key exception
-- else set em key in pkg variable

PROCEDURE setTimeCoff(p_time_coff IN RAW) IS
l_dummy VARCHAR2(256);
BEGIN

    TIME_COFF := p_time_coff;

    SELECT PARAMETER_NAME 
    INTO l_dummy
    FROM MGMT_PARAMETERS 
    WHERE PARAMETER_NAME=TIME_COFF_VERIFIER_PARAM
    AND decrypt(PARAMETER_VALUE) = TIME_COFF_VERIFIER;
    
EXCEPTION
  WHEN OTHERS THEN
    TIME_COFF := NULL;
    raise_application_error(MGMT_GLOBAL.INVALID_EM_KEY_ERR,
                               MGMT_GLOBAL.INVALID_EM_KEY_ERR_M);
END setTimeCoff;


-- setTimeCoffNoCheck is used to set the emkey without 
-- verifing it.

PROCEDURE setTimeCoffNoCheck(p_time_coff IN RAW) IS
BEGIN
    TIME_COFF := p_time_coff;    
END setTimeCoffNoCheck;



-- startupSync()  called by the oms during startup to verify emkey
-- time_coff -> em_key
--
-- if oms_em_key is null  check for key in repos
--    if key found in repos return it 
--    else raise key missing exception
-- else
--    set the emkey (which will validate the key too)


FUNCTION startupSync(p_time_coff RAW DEFAULT NULL) RETURN RAW IS
l_time_coff RAW(64) := p_time_coff;
BEGIN
    IF l_time_coff IS NULL THEN
      BEGIN
        SELECT time_coff INTO l_time_coff 
        FROM MGMT_REPOS_TIME_COEFFICIENT;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          raise_application_error(MGMT_GLOBAL.MISSING_EM_KEY_ERR,
                               MGMT_GLOBAL.MISSING_EM_KEY_ERR_M);
      END;
    END IF;
    
    setTimeCoff(l_time_coff);
    
    RETURN l_time_coff;    
END startupSync;


-- internal function to set the emkey in session and generate the 
-- emkey verifier
  PROCEDURE initTimeCoffVerifier(p_time_coff RAW) IS
    l_add_verifier INTEGER := 0;
    l_verifier     mgmt_parameters.parameter_value%type;
  BEGIN

    setTimeCoffNoCheck(p_time_coff);

    -- Check if the parameter is already populated correctly..
    BEGIN
      SELECT decrypt(PARAMETER_VALUE)
        INTO l_verifier
        FROM MGMT_PARAMETERS
       WHERE PARAMETER_NAME=TIME_COFF_VERIFIER_PARAM;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        -- If the verifier is not populated, insert it.
        l_add_verifier := 1;
    END ;

    IF (l_add_verifier = 1) THEN
      -- Verifier does not exist, insert it.
      INSERT INTO MGMT_PARAMETERS (parameter_name, parameter_value,
                               parameter_comment, internal_flag)
        VALUES(MGMT_TIME_SYNC.TIME_COFF_VERIFIER_PARAM,
               encrypt(MGMT_TIME_SYNC.TIME_COFF_VERIFIER),null,0);
    ELSIF NOT (l_verifier = TIME_COFF_VERIFIER) THEN
      -- Verifier already exists but is not valid.
      raise_application_error(MGMT_GLOBAL.INVALID_EM_KEY_ERR,
                               MGMT_GLOBAL.INVALID_EM_KEY_ERR_M);
    ELSE
      -- Verifier already exists and is valid. Do nothing.
      NULL;
    END IF;

  END initTimeCoffVerifier;


-- generate a new key and initilise it
-- this function is present in this pkg because the oracle wrap util does not
-- obfuscates anonymous plsql blocks

PROCEDURE init IS
l_time_coff RAW(64);
BEGIN
l_time_coff := SYS.DBMS_OBFUSCATION_TOOLKIT.md5( 
                       input => SYS.UTL_RAW.cast_to_raw(
                       rawtohex(sys_guid())||rawtohex(sys_guid())));

l_time_coff := l_time_coff || SYS.DBMS_OBFUSCATION_TOOLKIT.md5( 
                                    input => SYS.UTL_RAW.cast_to_raw(
                                    rawtohex(sys_guid())||rawtohex(sys_guid())));

    INSERT INTO MGMT_REPOS_TIME_COEFFICIENT (time_coff)
      VALUES (l_time_coff);                                    

initTimeCoffVerifier(l_time_coff);         

END init;

-- called during out of box to initilise this pkg 
-- check if the pkg has already been initilised by checking 
-- the verifier in mgmt_parameters, if not initilise it
-- this function also initilises the view user password by calling mgmt_view.init_view_user_out_of_box
-- this function is present in this pkg because the oracle wrap util does not
-- obfuscates anonymous plsql blocks

PROCEDURE init_out_of_box IS
l_count INTEGER;
BEGIN
  SELECT count(parameter_name)
  INTO l_count
  FROM MGMT_PARAMETERS
  WHERE parameter_name=TIME_COFF_VERIFIER_PARAM;

  IF l_count = 0 THEN
    MGMT_TIME_SYNC.init;
    MGMT_VIEW_PRIV.init_view_user_out_of_box;
  END IF;
END init_out_of_box;

-- called during em upgrade : get the key from the table and initilise
-- this function is present in this pkg because the oracle wrap util does not
-- obfuscates anonymous plsql blocks


PROCEDURE upgrade IS
l_time_coff RAW(64);
BEGIN
  
  SELECT time_coff INTO l_time_coff FROM MGMT_REPOS_TIME_COEFFICIENT;
  
  initTimeCoffVerifier(l_time_coff);

END upgrade;
 
-- delete the emkey from repository

PROCEDURE lockTimeCoff IS
BEGIN
    DELETE FROM MGMT_REPOS_TIME_COEFFICIENT;    
END lockTimeCoff;


-- dump the emkey back in the repository
PROCEDURE unlockTimeCoff(p_time_coff IN RAW) IS
BEGIN
     setTimeCoff(p_time_coff);
     DELETE FROM MGMT_REPOS_TIME_COEFFICIENT;   
     INSERT INTO MGMT_REPOS_TIME_COEFFICIENT (time_coff) 
       VALUES (p_time_coff);
END unlockTimeCoff;

END MGMT_TIME_SYNC;
/
show errors;