decrypt function

[Roshan]

Hi Erman,

I have a few queries regarding encryption and decryption in JAVA.

a) We are using code base 64 and ASE128 to do the decrypt data
b)  We are calling in DB function in java to do decrypt & encrypt.

Kindly advise whether we should create the functions below in Oracle DB?

Create or replace FUNCTION       DECRYPTPD
(
  SOURCESTR IN VARCHAR2, KEYSTR in VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
  RETURN UTL_RAW.CAST_TO_VARCHAR2(
                DBMS_CRYPTO.DECRYPT
                (
                        SRC => UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(SOURCESTR)),
                        TYP => DBMS_CRYPTO.ENCRYPT_AES128+DBMS_CRYPTO.CHAIN_ECB+DBMS_CRYPTO.PAD_PKCS5,
                        key => UTL_RAW.CAST_TO_RAW(KEYSTR)
                )
        )
;
END DECRYPTPD;
 
create or replace FUNCTION       ENCRYPTPD
(
  SOURCESTR IN VARCHAR2, KEYSTR in VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
  RETURN utl_raw.cast_to_varchar2(utl_encode.base64_encode(
    DBMS_CRYPTO.ENCRYPT
      (SRC => UTL_RAW.CAST_TO_RAW(SOURCESTR),
      TYP => DBMS_CRYPTO.ENCRYPT_AES128+DBMS_CRYPTO.CHAIN_ECB+DBMS_CRYPTO.PAD_PKCS5,
      key => UTL_RAW.CAST_TO_RAW(KEYSTR)
      )
    ));
END ENCRYPTPD;
 
        Here is sample code do encrypt :
 
public PSGCustomerDTO getByIdDocNum(String idDocNum, String idDocType) {
         PSGCustomerDTO result = null;
 
         Session session = this.sessionFactory.getCurrentSession();
         Criteria criteria = session.createCriteria(PSGCustomerDTO.class, "customer");
         criteria.add(Restrictions.eq("id_doc_num", Helper.encryptField(idDocNum)));
         criteria.add(Restrictions.eq("id_doc_type", idDocType));
         //       criteria.add(Restrictions.isNull("id_doc_type"));
         criteria.addOrder(Order.desc("create_date"));
         List<PSGCustomerDTO> list = criteria.list();
         result = (list.size() > 0) ? list.get(0) : null;
         return result;
     }

Kindly advise how to call the decryption functions in the code above?

We are getting error below


Function DECRYPTPD compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
6/3       PL/SQL: Statement ignored
7/17      PLS-00201: identifier 'DBMS_CRYPTO' must be declared
Errors: check compiler log

Thanks,

Roshan

[ErmanArslansOracleBlog]

The issue is related with DBMS_CRYPTO..
Probably, you don't have grant for executing it..
Main cause seems missing grants for DBMS_CRYPTO.
Consider granting Execute ON DBMS_CRYPTO ...

[Roshan]

The full code is found below:

https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_crypto.htm#i1001225

DECLARE
   input_string       VARCHAR2 (200) :=  'Secret Message';
   output_string      VARCHAR2 (200);
   encrypted_raw      RAW (2000);             -- stores encrypted binary text
   decrypted_raw      RAW (2000);             -- stores decrypted binary text
   num_key_bytes      NUMBER := 256/8;        -- key length 256 bits (32 bytes)
   key_bytes_raw      RAW (32);               -- stores 256-bit encryption key
   encryption_type    PLS_INTEGER :=          -- total encryption type
                            DBMS_CRYPTO.ENCRYPT_AES256
                          + DBMS_CRYPTO.CHAIN_CBC
                          + DBMS_CRYPTO.PAD_PKCS5;
BEGIN
   DBMS_OUTPUT.PUT_LINE ( 'Original string: ' || input_string);
   key_bytes_raw := DBMS_CRYPTO.RANDOMBYTES (num_key_bytes);
   encrypted_raw := DBMS_CRYPTO.ENCRYPT
      (
         src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),
         typ => encryption_type,
         key => key_bytes_raw
      );
    -- The encrypted value "encrypted_raw" can be used here
 
   decrypted_raw := DBMS_CRYPTO.DECRYPT
      (
         src => encrypted_raw,
         typ => encryption_type,
         key => key_bytes_raw
      );
   output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');
 
   DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string);
END;

I managed to compile it. How do I decrypt a field from a table using the above function?

Thanks,

Roshan

[ErmanArslansOracleBlog]

Your Question: How do I decrypt a field from a table using the above function?

Answer : You may create a function that decrpyts the encyrpted values and you may use that function while querying your table where you have encrypted columns-data

Here is a little example for you -> http://www.nazmulhuda.info/encryption-decryption-using-dbms_crypto

[Roshan]

Thanks Erman. It is ok now. Permission issue as you correctly mentioned before.

SQL>  grant execute on sys.dbms_crypto to SCOTT;

http://facedba.blogspot.com/2018/09/fix-pls-00201-identifier-dbmscrypto.html