2 Replies Latest reply: Feb 1, 2012 12:44 PM by damorgan RSS

    dbms_crypto - avoid error when using different key in lower environment

    spur230
      Hello Experts,

      We are using Oracle 11.2.0.2. We are planning to implement dbms_crypto to encrypt few columns. We clone the data from production to lower environment ( DEV, QC).

      For the lower environments, we do not want to get the sensitive data from production and do not plan to use same key. Rather than getting an error when using differnt key, is it possible to get a different resultset back.

      In other words, we want the implementation to be same across environments but want to use a diffent key in lower environment and get different result (or garbage).

      Any suggestions would be greatly appreciated.

      While testing this logic, I am getting following error when using differnt key to decrypt. It works fine if I use same key.
      Error at line 1
      ORA-28817: PL/SQL function returned an error.
      ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67
      ORA-06512: at "SYS.DBMS_CRYPTO", line 44
      ORA-06512: at line 19
      DECLARE
        l_credit_card_no    VARCHAR2(19) := '1234 5678 9012 3456';
        l_ccn_raw           RAW(128) := UTL_RAW.cast_to_raw(l_credit_card_no);
       
       l_key               RAW(128) := UTL_RAW.cast_to_raw('abcdefgh');
         l2_key               RAW(128) := UTL_RAW.cast_to_raw('12345678');
      
        l_encrypted_raw     RAW(2048);
        l_decrypted_raw     RAW(2048);
      BEGIN
        DBMS_OUTPUT.put_line('Original  : ' || l_credit_card_no);
      
        l_encrypted_raw := DBMS_CRYPTO.encrypt(src => l_ccn_raw, 
                                               typ => DBMS_CRYPTO.des_cbc_pkcs5, 
                                               key => l_key);
      
        DBMS_OUTPUT.put_line('Encrypted : ' || RAWTOHEX(UTL_RAW.cast_to_raw(l_encrypted_raw)));
      
        l_decrypted_raw := DBMS_CRYPTO.decrypt(src => l_encrypted_raw, 
                                               typ => DBMS_CRYPTO.des_cbc_pkcs5, 
                                               key => l2_key); --**Using different key to decrypt
      
        DBMS_OUTPUT.put_line('Decrypted : ' || UTL_RAW.cast_to_varchar2(l_decrypted_raw));
      END;
      Thank you.
        • 1. Re: dbms_crypto - avoid error when using different key in lower environment
          JustinCave
          In general, you can't get different results in different environments, no.

          You could, of course, write your decrypt routine so that it catches the exception and returns a random RAW string.

          However, this is not normally the way people go about masking sensitive data in lower environments. It would be much more common to use a tool that is designed for this tool. For example, Oracle has a Data Masking Pack for Enterprise Manager that can be used as part of the refresh of lower environments to replace sensitive data with fake but realistic data. There are also third-party data masking tools like Data Masker.

          Justin
          • 2. Re: dbms_crypto - avoid error when using different key in lower environment
            damorgan
            If I understand what you are trying to do ... and I may not ... it is not going to work.
            SQL> DECLARE
              2   l_credit_card_no VARCHAR2(19) := '1612-1791-1809-2605';
              3   l_ccn_raw RAW(128) := utl_raw.cast_to_raw(l_credit_card_no);
              4   l_key1     RAW(128) := utl_raw.cast_to_raw('abcdefgh');
              5   l_key2     RAW(128) := utl_raw.cast_to_raw('zyxwvuts');  -- alternate key used to attempt a different decryption
              6  
              7   l_encrypted_raw RAW(2048);
              8   l_decrypted_raw RAW(2048);
              9  BEGIN
             10    dbms_output.put_line('Original : ' || l_credit_card_no);
             11  
             12    l_encrypted_raw := dbms_crypto.encrypt(l_ccn_raw, dbms_crypto.des_cbc_pkcs5, l_key1);
             13  
             14    dbms_output.put_line('Encrypted : ' || RAWTOHEX(utl_raw.cast_to_raw(l_encrypted_raw)));
             15  
             16    l_decrypted_raw := dbms_crypto.decrypt(src => l_encrypted_raw, typ => dbms_crypto.des_cbc_pkc
            s5, key => l_key1);
             17  
             18    dbms_output.put_line('Key1 : ' || utl_raw.cast_to_varchar2(l_decrypted_raw));
             19  
             20    l_decrypted_raw := dbms_crypto.decrypt(src => l_encrypted_raw, typ => dbms_crypto.des_cbc_pkc
            s5, key => l_key2);
             21  
             22    dbms_output.put_line('Key2 : ' || utl_raw.cast_to_varchar2(l_decrypted_raw));
             23  END;
             24  /
            Original : 1612-1791-1809-2605
            Encrypted : 3534443342333642353141363846384237463732384636373943374630364234323243334539383042323135
            Key1 : 1612-1791-1809-2605
            DECLARE
            *
            ERROR at line 1:
            ORA-28817: PL/SQL function returned an error.
            ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67
            ORA-06512: at "SYS.DBMS_CRYPTO", line 44
            ORA-06512: at line 20