create or replace package body encrypt_util
as
  --+ ----------------------------------------------------------------------------
  -- thankt to:
  -- see http://www.oracleflash.com/41/Encrypt-or-Decrypt-sensitive-data-using-PLSQL---DBMS_CRYPTO.html
  --+ --------------------------------------------------------------------------
  g_crypt_clear_key         varchar2 (256) := '';
  g_encryption_key          raw (32) := '';


  --+ ----------------------------------------------------------------------------
  -- ENCRYPT_DES is the encryption algorithem.
  -- Data Encryption Standard. Block cipher.
  -- Uses key length of 56 bits.
  --
  -- CHAIN_CBC Cipher Block Chaining. Plaintext is XORed with the previous ciphertext
  -- block before it is encrypted.
  --
  -- PAD_PKCS5 Provides padding which complies with the PKCS #5: Password-Based
  -- Cryptography Standard
  --+ ----------------------------------------------------------------------------
  g_encryption_type         pls_integer := dbms_crypto.encrypt_des + dbms_crypto.chain_cbc + dbms_crypto.pad_pkcs5;

  --+ ----------------------------------------------------------------------------
  --  encrypt a text
  --+ ----------------------------------------------------------------------------
  function encrypt (p_plaintext varchar2)
    return raw
    deterministic
  is
    encrypted_raw            raw (2000);
  begin
    encrypted_raw :=
      dbms_crypto.encrypt (src  => utl_raw.cast_to_raw (p_plaintext)
                   , typ  => g_encryption_type
                   , key  => g_encryption_key
                    );
    return encrypted_raw;
  end encrypt;


  --+ ----------------------------------------------------------------------------
  --  decrypt a text
  --+ ----------------------------------------------------------------------------
  function decrypt (p_encryptedtext raw)
    return varchar2
    deterministic
  is
    decrypted_raw            raw (2000);
  begin
    decrypted_raw :=
      dbms_crypto.decrypt (src  => p_encryptedtext
                   , typ  => g_encryption_type
                   , key  => g_encryption_key
                    );
    return (utl_raw.cast_to_varchar2 (decrypted_raw));
  end decrypt;


  --+ ----------------------------------------------------------------------------
  --  clean string from whitespaces to avoid sql injection
  --+ ----------------------------------------------------------------------------
  function cleantext (p_text varchar2)
    return varchar2
  is
    v_text                varchar2 (32000);
  begin
    v_text := replace (p_text, chr (10), '');
    v_text := replace (v_text, chr (13), '');
    v_text := replace (v_text, ' ', '');
    v_text := regexp_replace (v_text , '[[:space:]]' , '');
    return v_text;
  end cleantext;

  --+ ----------------------------------------------------------------------------
  -- get a key from the local db enviroment
  -- The encryption key for DES algorithem, should be 8 bytes or more.
  --+ ----------------------------------------------------------------------------
  function getkeyfromlocal(p_private_key varchar2 default null)
    return varchar2
  is
    v_obj_id1              varchar2 (12) := '00000';
    v_obj_id2              varchar2 (12) := '00000';
    v_key                 varchar2 (2000) := 'PWD';
  begin
    -- get some ids from default objects, that not chaning so often .-)
    select to_char (object_id)
      into v_obj_id1
      from user_objects
     where object_name = 'ENCRYPT_UTIL'
         and object_type = 'PACKAGE BODY';

    select to_char (sum (object_id))
      into v_obj_id2
      from all_objects
     where object_name = 'DBMS_STANDARD'
         and owner in ('SYS', 'PUBLIC');

    v_key :=
        initcap (sys_context ('USERENV','CURRENT_SCHEMA'))
      || '#'
      || lower (sys_context ('USERENV', 'CURRENT_SCHEMAID'))
      || '*'
      || nvl (v_obj_id1, '00000')
      || '$'
      || initcap (sys_context ('USERENV', 'DB_NAME'))
      || '+'
      || nvl (v_obj_id2, '00000');
            
    if    p_private_key is not null then
        v_key := p_private_key||v_key;
    end if;

    return substr(v_key,0,32);
        
  end getkeyfromlocal;


  --+ ----------------------------------------------------------------------------
  -- store passwords in to a object
  -- need create function right
  -- grant create procedure to <package_owner>
  -- grant create type to <package_owner>
    -- debug exec encrypt_util.storepwd('ABCDE',1,'FUNCTION','1234')
  --+ ----------------------------------------------------------------------------

  procedure storepwd (p_pwd varchar2
              , p_slot number
              , p_store varchar2 default 'OBJECT'
                            , p_private_key varchar2 default 'NO'
               )
  is
    cursor c_read_type
    is
      select text
        from user_source
       where name = 'PWD_WALLET'
           and type = 'TYPE BODY';

    v_f_template            varchar2 (2000)
      := 'create or replace function getSecretUserPwd##Slot## return raw is begin return (''##CRYPT_KEY##''); end;';

    v_o_template_s           varchar2 (4000)
      := 'CREATE or replace TYPE pwd_wallet AS OBJECT (
                                                  password varchar2(56)
                                                  ,MEMBER FUNCTION getUserPWD1 RETURN raw
                                                  ,MEMBER FUNCTION getUserPWD2 RETURN raw
                                                  ,MEMBER FUNCTION getUserPWD3 RETURN raw
                                                  ,MEMBER FUNCTION getUserPWD4 RETURN raw
                                                  ,MEMBER FUNCTION getUserPWD5 RETURN raw
                                                )';

    v_o_template_b           varchar2 (4000)
      := 'CREATE or replace  TYPE BODY pwd_wallet AS 
                                        MEMBER 
                                        FUNCTION getUserPWD1 RETURN raw 
                                             IS
                                           BEGIN
                                             RETURN (''##CRYPT_KEY1##''); -- Slot1 not remove 
                                          END;
                                        MEMBER 
                                        FUNCTION getUserPWD2 RETURN raw 
                                             IS
                                           BEGIN
                                             RETURN (''##CRYPT_KEY2##''); -- Slot2 not remove 
                                          END;
                                        MEMBER 
                                        FUNCTION getUserPWD3 RETURN raw 
                                             IS
                                           BEGIN
                                             RETURN (''##CRYPT_KEY3##''); -- Slot3  not remove 
                                          END;
                                        MEMBER 
                                        FUNCTION getUserPWD4 RETURN raw 
                                             IS
                                           BEGIN
                                             RETURN (''##CRYPT_KEY4##''); -- Slot4  not remove 
                                          END;
                                        MEMBER 
                                        FUNCTION getUserPWD5 RETURN raw 
                                             IS
                                           BEGIN
                                             RETURN (''##CRYPT_KEY5##''); -- Slot5  not remove 
                                         END;
                                    end;
                                    ';

    v_template            varchar2 (32000) := '';
    v_key                 varchar2 (2000);
    v_slot                varchar2 (2);
    v_count               pls_integer;
  begin
     
     -- set your private key to harden the safe
    if p_private_key != 'NO' then
        g_crypt_clear_key := getkeyfromlocal(p_private_key);
    else 
       g_crypt_clear_key := getkeyfromlocal;        
    end if;
      
    --dbms_output.put_line ('-- Info :: set New Key ::'||g_crypt_clear_key);
    g_encryption_key := utl_raw.cast_to_raw (g_crypt_clear_key);
      
    -- prevent for SQLInjection
    v_key := cleantext (p_text => p_pwd);
    v_slot := cleantext (p_text => to_char (p_slot));

    dbms_output.put_line ('--Info :: store in Slot : ' || v_slot || ' PWD :: ' || v_key);

    if p_store = 'FUNCTION'
    then
      v_template :=replace (v_f_template , '##CRYPT_KEY##' , (encrypt (v_key)) );

      v_template :=replace (v_template, '##Slot##', v_slot);
    elsif p_store = 'TABLE'
    then
      null;
    -- implement here your setup table to store the pwd
    --
    elsif p_store = 'OBJECT'
    then
      select count (*)
        into v_count
        from user_types
       where type_name = 'PWD_WALLET';

      if v_count < 1
      then
        -- create the object spec
        execute immediate v_o_template_s;

        v_template :=replace (v_o_template_b , '##CRYPT_KEY' || v_slot || '##', (encrypt (v_key)));
      else
        -- get the code of the object
        -- check if object exists
        v_template := 'create or replace ';

        for rec in c_read_type
        loop
          if instr (rec.text
                , 'Slot' || v_slot
                ) > 1
          then
            -- correct line
            v_template :=
                v_template
              || regexp_replace (rec.text
                          , '[RETURN (''].*['');]'
                          , 'RETURN (''' || (encrypt (v_key)) || ''');'
                          );
          else
            v_template := v_template || rec.text;
          end if;
        end loop;
      end if;
    end if;

    --dbms_output.put_line('--Info :: try to execute : '|| v_template);

    execute immediate v_template;
  end storepwd;

  --+ -----------------------------------------------------------------------
  -- get User password
    -- select encrypt_util.getuserpwd(2) from dual;
  --+ -----------------------------------------------------------------------

  function getuserpwd (p_slot number, p_private_key varchar2 default 'NO')
    return varchar2
  is
    v_count                pls_integer;

    v_template_0            varchar2 (2000) := 'declare
        v_pwd pwd_wallet;
        begin
         v_pwd:=pwd_wallet(''xxx'');
         :val:=encrypt_util.decrypt(v_pwd.getUserPWD##Slot##);
        end;
        ';
    v_template_f            varchar2 (2000) := ' begin
          :val:=encrypt_util.decrypt(getSecretUserPwd##Slot##);
        end;
        ';

    v_pwd                 varchar2 (2000);
    v_slot                varchar2 (2);
    v_template              varchar2 (2000);
  begin
    
        -- set your private key to harden the safe      
        if p_private_key != 'NO' then
            g_crypt_clear_key := getkeyfromlocal(p_private_key);
        else 
            g_crypt_clear_key := getkeyfromlocal;   
        end if;
        
        --dbms_output.put_line ('-- Info :: set New Key ::'||g_crypt_clear_key);
         
        g_encryption_key := utl_raw.cast_to_raw (g_crypt_clear_key);
      
    v_slot := cleantext (p_text => to_char (p_slot));

    -- if object exitst use object
    select count (*)
      into v_count
      from user_types
     where type_name = 'PWD_WALLET';

    if v_count > 0
    then
      v_template :=replace (v_template_0 , '##Slot##' , v_slot);
    else
      -- must be a function or a table
      select count (*)
        into v_count
        from user_objects
       where object_name = upper ('getSecretUserPwd' || v_slot);

      if v_count > 0
      then
        v_template :=replace (v_template_f, '##Slot##', v_slot);
      else
        -- implment your table data store here
        raise_application_error (-20001
                        , 'No PWD Function found for the slot::' || v_slot
                        );
      end if;
    end if;

    begin
      execute immediate v_template using out v_pwd;
    exception
      when crypt_key_error then
                 v_pwd := 'NO_PWD_KEY_WRONG_SLOT' || v_slot;
                 dbms_output.put_line ('-- Error :: KEY Error for PWD in Slot ' || v_slot || ' set! Error: '||SQLERRM);
            when crypt_value_error
            then
                v_pwd := 'NO_PWD_SET_SLOT' || v_slot;
                dbms_output.put_line ('-- Error :: No PWD in Slot ' || v_slot || ' set! Error:'||SQLERRM);
            when others
      then
        raise_application_error (-20000, 'Error read pwd from pwd_wallet:: Error' || SQLERRM);
    end;

    return v_pwd;
  end getuserpwd;
begin
   g_crypt_clear_key := getkeyfromlocal;   
   g_encryption_key := utl_raw.cast_to_raw (g_crypt_clear_key);
end;
/