Decrypting pgp data inside the Oracle(c) DB

In this chapter we will show how to perform decrypting of OpenPGP data inside the Oracle(c) database with the help of OraPGP PL/SQL package.

In order to decrypt OpenPGP encrypted data we need a private key corresponding to the public key used for encryption and the unlocking password of the private key. If the password has an empty password, then NULL can be used in the place of the key password parameter.

Keys can be used from the file system, blob fields or inline: see using .pgp keys with Ora_PGP. For simplicity here we use only keys from the file system.

1. Decrypting VERCHAR2
2. Decrypting CLOB
3. Decrypting BLOB
4. Decrypting RAW
5. Invoking from Java
6. Exception handling

Decrypting VERCHAR2

The example below decrypt a previously encrypted VARCHAR2 field. As with the other methods of the ORA_PGP package the key can be provided either as an absolute file path, or as in ASCII armored format.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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;

Decrypting CLOB

The example below decrypt a previously encrypted CLOB field. The decrypted data is returned as CLOB field too.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

DECLARE
  MESSAGE CLOB;
  PRIVATE_KEY VARCHAR2(2000);
  KEY_PASSWORD VARCHAR2(200);
  v_Return CLOB;
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_CLOB(
    DATA => MESSAGE,
    PRIVATE_KEY => PRIVATE_KEY,
    KEY_PASSWORD => KEY_PASSWORD
  );
END;

Decrypting BLOB

Decrypting a BLOB field is very similar to decrypting a VARCHAR2 field:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

DECLARE
  MESSAGE BLOB;
  PRIVATE_KEY VARCHAR2(200);
  KEY_PASSWORD VARCHAR2(200);
  v_Return BLOB;
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_BLOB(
    DATA => MESSAGE,
    PRIVATE_KEY => PRIVATE_KEY,
    KEY_PASSWORD => KEY_PASSWORD
  );
 
  -- v_Return now contains the decrypted data
END;

After the decryption, if we know that the BLOB contains textual data, we have to convert in to CLOB respecting the encoding of the data. A demo conversions routine from BLOB to CLOB can be found on GitHub.

Decrypting RAW

Decrypting a RAW field is very similar to decrypting a BLOB field. The result is a RAW field.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

DECLARE
  MESSAGE RAW;
  PRIVATE_KEY VARCHAR2(2000);
  KEY_PASSWORD VARCHAR2(200);
  v_Return RAW;
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_RAW(
    DATA => MESSAGE,
    PRIVATE_KEY => PRIVATE_KEY,
    KEY_PASSWORD => KEY_PASSWORD
  );
 
  -- v_Return now contains the decrypted data
END;

Invoking from Java

Here is an example how we can call the decryption PL/SQL code from java:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

import java.sql.*;
 
public class OraPGPTests {
 
	private static final String JDBC_ORACLE_CONNECTION = "jdbc:oracle:thin:@localhost:1522:orcl2";
 
	public void testEncryptString() throws SQLException {
		DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
 
		Connection conn = DriverManager.getConnection
				  (JDBC_ORACLE_CONNECTION, "user", "pass");		
 
		String encryptedString = "... pgp encrypted string here ...";
 
		// ENCRYPT_STRING
		CallableStatement cstmt = conn.prepareCall("{?=call ORA_PGP.DECRYPT(?,?,?)}");
		cstmt.registerOutParameter(1, Types.VARCHAR);
		cstmt.setString(2, encryptedString);
		cstmt.setString(3, "c:\\Projects\\PGPKeys\\private.key");
		cstmt.setString(4, "key password");
		cstmt.executeUpdate();
		String decryptedString = cstmt.getString(1);
 
		System.out.println(decryptedString);
	}
}

Exception handling

For simplicity exception handling is not included in the above example. For production systems you shall apply PL/SQL exception handling code as described.

Summary

This chapter illustrated how to decrypt inside the Oracle(c) database an OpenPGP encrypted data.

You may also check how to investigate which public key was used for encrypting given column.

List of methods used:

ORA_PGP.DECRYPT Decrypts VARCHAR2 data
ORA_PGP.DECRYPT_BLOB Decrypts BLOB data.
ORA_PGP.DECRYPT_CLOB Decrypts CLOB data
ORA_PGP.DECRYPT_RAW Decrypts RAW data