Decrypt Jasypt encrypted information in Oracle

Question

I need to make a materialized view, based on few of the tables in our database, but the catch is, few columns in the underlying tables are encrypted using Jasypt, and all the columns are required to be plain text in the materialized view.

Is there any implicit function in Oracle which can be used for Jasypt decryption or any suggestions around writing own function to achieve this ?

PS : Encryption used is decrypt-able.

Jasypt has pretty trivial use of AES encryption, so I'm gonna say that it should be fairly easy. — Artjom B., Aug 08 '16 at 18:05
Oracle does support transparent encryption on the database level (though it comes at a price) — NullUserException, Aug 08 '16 at 20:16
we dont need to encrypt the data, its already encrypted using Jasypt, we need to decrypt it, which can happen with the same library only.. — Vivek Gupta, Aug 09 '16 at 09:48
The solution below should work. I'm not sure why this question got downvotes, it is an interesting problem really, that can be solved with java stored procs; however, yes, you'll have to write the functions. — slambeth, Aug 09 '16 at 15:04

slambeth · Accepted Answer · 2016-08-08T20:06:28.763

You could create a java stored procedure in oracle. It's fairly easy - Create the source like so:

create or replace and compile java source named JasyptWrapper as
public class JasyptWrapper
{
  public static String decrypt(String str)
  {
         String decryptedStr = "";
         //code to decrypt
         return decryptedStr;
  }
}

Created the stored proc wrapper like so:

FUNCTION jasypt_decrypt(p_str VARCHAR2) RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'JasyptWrapper.decrypt(java.lang.String) return String';

You can then write java code to utilize jasypt to perform the decryption, but you'll have the ability to call it like any other PL/SQL procedure.

You'll have to load the jasypt libraries into the oracle database using loadjava. You'll likely need assistance from your DBA to perform some of these operations, and they'll also probably need to grant additional access using the dbms_java package.

Then use the function you created in the materialized view query to decrypt the values as it processes.

Decrypt Jasypt encrypted information in Oracle

1 Answers1