2

I have the below function in package where i am trying to decrpyt the encrypted password but getting the below error message:

ORA-28817: PL/SQL function returned an error.
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", 
ORA-06512: at "SYS.DBMS_CRYPTO", 
ORA-06512: at "RATOR_MONITORING_CONFIGURATION.ENCRYPT_DECRYPT_PASSWORD", line 11    
28817. 00000 -  "PL/SQL function returned an error."
*Cause:    A PL/SQL function returned an error unexpectedly.
*Action:   This is an internal error. Enable tracing to find more
           information. Contact Oracle customer support if needed.
*Document: NO

When i run this package in different server and machine then i am able to execute the package and able to decrypt the password with the same query.

Below is my package:

create or replace PACKAGE BODY "ENCRYPT_DECRYPT_PASSWORD"
AS

  l_key RAW(128) := utl_raw.cast_to_raw('abcdefgh');

 FUNCTION decrypt_val( p_val IN varchar2 ) RETURN VARCHAR2
  IS
    l_decrypted RAW(2048);
    l_val RAW(2048) := utl_raw.cast_to_raw(p_val);
   BEGIN
         l_decrypted := dbms_crypto.decrypt
                ( src =>  l_val,
                  typ => dbms_crypto.des_cbc_pkcs5,
                  key => l_key );

         return utl_raw.cast_to_varchar2(l_decrypted);

  END decrypt_val;


FUNCTION get_decrypted_password( table_name IN varchar2,column_name IN varchar2,table_id IN varchar2,table_id_val IN varchar2 ) RETURN VARCHAR2
  IS
     encrypted_pas varchar2(100);
     decrypted_pas varchar2(100);

   BEGIN
      EXECUTE IMMEDIATE 'select ' || column_name || ' from ' || table_name || ' where ' || table_id || ' = ' || table_id_val
         INTO encrypted_pas;
         Select decrypt_val(encrypted_pas) into decrypted_pas from dual;
          return decrypt_val(encrypted_pas);
  END get_decrypted_password;

END encrypt_decrypt_password;

I also tried giving DBMS_CRYPTO permission to the user and my package has also been compiled but when i tried to run the below command to decrypt the password then i am getting the error.

Select ENCRYPT_DECRYPT_PASSWORD.get_decrypted_password('DB_CONNECTION','PASSWORD','DB_CONNECTION_ID',11) from dual;
Andrew
  • 3,632
  • 24
  • 64
  • 113
  • Looks like an Oracle bug. Enable tracing and contact Oracle support. – Codo Nov 03 '16 at 14:41
  • You'd get that if the stored value for ID 11 isn't encrypted properly; you can demo that by setting the password to say `'xyz'`. Is the stored value the same as in the environment where this works? Can you dump the value to check is actually what you expect? (I'm wondering if you can share the actual value, but I'm guessing that isn't a good idea, even if it can't be decrypted...) – Alex Poole Nov 03 '16 at 14:41
  • 1
    As I understand from OTN, this was a false alarm -everything actually works fine. Please close the thread yourself. –  Nov 03 '16 at 14:41
  • @Alex Poole you are right. I dont know for which reason but previously i got dbms_crypto permission issue. I have given the dbms_crypto permission to the user. And tried the select query to decrypt the password. But i was getting the error which i have mentioned. So i tried to encrypt the password again and tried decryption using the same select query and it works. This looks like Oracle bug to me. – Andrew Nov 03 '16 at 15:11
  • 1
    It isn't a helpful error but I'm not sure it's a bug. It couldn't decrypt whatever was stored in there before - maybe you had an error on encryption (from dbms_crypto privs) and it stored something unexpected instead of telling you? (Are you catching exceptions in the encrypt function, perhaps?) – Alex Poole Nov 03 '16 at 15:40
  • No i am not catching any exception. But as i can see the error i assume that again it was due to DBMS_CRYPTO function because the error does not indicates that the values has not benn encrypted properly. – Andrew Nov 04 '16 at 08:30
  • 1
    If you were initially getting a privilege error on that package while trying to decrypt a value then encrypting wouldn't have been working either at that point. Maybe your encryption routine stored a bogus value when that happened, or maybe it had been encrypted elsewhere - perhaps with a different key. Without the encryption code and the value that was originally stored it's all speculation. I don't see anything suggesting a bug though, just an unhelpful error message. – Alex Poole Nov 04 '16 at 10:46
  • Any help on https://stackoverflow.com/questions/52793182/oracle-dbms-crypto-for-pkcs7 please? – ajmalmhd04 Oct 13 '18 at 13:37
  • Answer from Oracle Support regarding to this exception: `When you are passing the encrypted value to the decrypt function or for storage within database, do not use UTL_I18N.RAW_TO_CHAR instead use RAWTOHEX or UTL_ENCODE.BASE64_ENCODE. Similarly for converting back to raw do not use UTL_I18N.STRING_TO_RAW instead use HEXTORAW or UTL_ENCODE.BASE64_DECODE.` – Naeel Maqsudov Mar 05 '21 at 06:43

0 Answers0