Can the Oracle PL/SQL dbms_crypto package decrypt a file encrypted with the Linux gpg command?

Question

I'm new to encryption and am trying to figure out if the Linux gpg command and Oracle's dbms_crypto package can work together. Oracle version is 11R2. I'll be receiving files encrypted by a different system and would like to decrypt them via PL/SQL if possible. I'm running through the thread below as a POC to see if this is possible.

At the Linux prompt I created a key with this command:

gpg --gen-key

Created a public key with this command (using an actual email addr):

gpg --armor --export [email protected] > mypublickey

And encrypted my file like this:

gpg -r [email protected] --output input.txt.encrypted --encrypt input.txt

amongst the stdout was this message:

gpg: encrypted with 2048-bit RSA key

So now I have an encrypted file.

Using blobs/clobs I loaded my encrypted file and my pub key into an Oracle table. I'm attempting to use the dbms_crypto.decrypt function to decrypt the file:

select dbms_crypto.decrypt(
          encrypted_file,
          ?,
          utl_raw.cast_to_raw('public_key'),
          null)
from crypto_test_lobs

At this point I'm unsure what values to pass for the 2nd argument which is the "Stream or block cipher type and modifiers to be used". I've tried several different combinations which result in a variety of exceptions that are probably not worth posting.

So my questions are:

1. Is this possible?
2. How do I figure out that 2nd argument?

Thanks

Answer 1

How do I figure out that 2nd argument?

The second argument is an integer which defines the algorithm to use. See also http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_crypto.htm. Within PL/SQL, you can use constants from the DBMS_CRYPTO package to set these values, for example to use AES-256 with CBC and PKCS5-padding, use something like

encryption_type PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256
                             + DBMS_CRYPTO.CHAIN_CBC
                             + DBMS_CRYPTO.PAD_PKCS5;

Outside PL/SQL, you need to know the actual numbers which the constants define. See http://www.remote-dba.net/t_advanced_dbms_crypto.htm for a corresponding list. To define the same algorithm as above, you would use 8 + 256 + 4096.

Answer 2

Short answer to your first question: no, the large answer, the Oracle function seems to use only symmetric cyphers, while the key you created (RSA) is for use with asymmetric cyphers, this won't work. My recommendation is to take a look at the DES and AES algorithms and how they work so you better understand why.

Answer 3

gpg encrypts with the OpenPGP protocol and you need a PL/SQL package that support OpenPGP.

A commercial PL/SQL package OraPGP supports OpenPGP and below is an example decrypting with it:

DECLARE
  MESSAGE VARCHAR2(2000);
  PRIVATE_KEY VARCHAR2(200);
  KEY_PASSWORD VARCHAR2(200);
  v_Return VARCHAR2(200);
BEGIN
  MESSAGE := NULL;
  PRIVATE_KEY := 'c:\PGPKeys\private_key.asc';
  KEY_PASSWORD := 'key password';
 
  SELECT encrypted_data_field INTO MESSAGE
  FROM my_data_table
  WHERE my_id = 1000;
 
  v_Return := ORA_PGP.DECRYPT(
    MESSAGE => MESSAGE,
    PRIVATE_KEY => PRIVATE_KEY,
    KEY_PASSWORD => KEY_PASSWORD
  );
 
 DBMS_OUTPUT.PUT_LINE('Decrypted data = ' || v_Return);
END;