PGP encryption inside the Oracle(c) Database

In this chapter we are going to study how to OpenPGP encrypt data inside Oracle DB with the help of DidiSoft OraPGP PL/SQL paclage.

1. Encrypting VARCHAR2
2. Encrypting CLOB
3. Encrypting BLOB
4. Encrypting RAW
5. Encrypting for multiple recipients
6. Invoking from Java
7. Integrity Protection
8. Exception handling

When we perform OpenPGP encryption we need the public key of the recipient. Of course if we need the data encrypted only for our own needs, we are going to use our own public key. The package accepts public keys as sever file system path location or as ASCII armored string in VARCHAR2 or BLOB fields. Check the  chapter regarding how to use OpenPGP keys with ORA_PGP if you still haven’t. The examples here illustrate for short only using  keys as file system path.

1. Encrypting VARCHAR2

The encryption method expect the public key to be provided as an absolute file path on the server or in ASCII armored format. The example below uses absolute file path. In order the package to be able to access the key the invoking user must have access permissions for it.

The output will be OpenPGP encrypted message in ASCII armored format returned as VARCHAR2 data.

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

DECLARE
  MESSAGE VARCHAR2(2000);
  PUBLIC_KEY VARCHAR2(2000);
  v_Return VARCHAR2(2000); -- we need sufficient space here, or an error will be raised
BEGIN
  MESSAGE := 'Hello World';
  PUBLIC_KEY := 'c:\PGPKeys\recipient_public_key.asc';
 
  v_Return := ORA_PGP.ENCRYPT(
    MESSAGE => MESSAGE,
    PUBLIC_KEY => PUBLIC_KEY
  );
 
  -- print the encrypted data
  DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;
/

2. Encrypting CLOB

We can encrypt short CLOB fields using the same code as with the VARCHAR2 above. In order to avoid CLOB to VARCHAR2 conversion we are going to use the dedicated method ORA_PGP.ENCRYPT_CLOB which produces OpenPGP encrypted data in ASCII armored format as CLOB field. By default the encrypted data is assigned internally an artificial “message.txt” label. You can change it with an overloaded method that accepts an additional data_file_label parameter (like in the BLOB example below) :

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

DECLARE
  MESSAGE CLOB;
  PUBLIC_KEY VARCHAR2(2000);
  v_Return CLOB;
BEGIN
  MESSAGE := 'Hello World';
  PUBLIC_KEY := 'c:\PGPKeys\recipient_public_key.asc';
 
  v_Return := ORA_PGP.ENCRYPT_CLOB(
    MESSAGE => MESSAGE,
    PUBLIC_KEY => PUBLIC_KEY
  );
 
  -- print the encrypted data
  DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;
/

3. Encrypting BLOB

The two method signatures for encrypting BLOB data are
ORA_PGP.ENCRYPT_BLOB(message BLOB, public_key BLOB|VARCHAR2, ascii_armor BOOLEAN) return BLOB
ORA_PGP.ENCRYPT_BLOB(message BLOB, data_file_label VARCHAR2, public_key BLOB|VARCHAR2, ascii_armor BOOLEAN) return BLOB

The message parameter is the source data to be encrypted and the public_key is the encryption key. The ascii_armor parameter decides shall the PGP output format be binary or ASCII.

The mystical data_file_label parameter (available as of version 1.3.1) assigns a file name to the encrypted data. The idea is that if the encrypted BLOB is decrypted to a file outside of the database, that decrypted file name will have the value in data_file_label. 

In the example below we use a sample source table with definition:

table documents
+------------------------+
| document_id varchar(10)|
| file_data blob         |
+------------------------+

The example call below will produce binary OpenPGP 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 BLOB;
  PUBLIC_KEY VARCHAR2(200);
  v_Return BLOB;
  ascii_armor_output BOOLEAN := FALSE;
BEGIN
  MESSAGE := NULL;
  PUBLIC_KEY := 'c:\PGPKeys\public_keys.asc';
 
  SELECT file_data INTO MESSAGE
  FROM documents
  WHERE document_id = 'A103';
 
  v_Return := ORA_PGP.ENCRYPT_BLOB(
    message => MESSAGE,
    data_file_label => 'Report_A103.doc', -- file name assigned to the encrypted data, can be skipped
    public_key => PUBLIC_KEY,
    ascii_armor => ascii_armor_output
  );
 
END;
/

4. Encrypting RAW

For every other case, we can use ORA_PGP.ENCRYPT_RAW by first convering the data into RAW format. Here we also have the option to specify shall the output be ASCII armored or .pgp binary:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

DECLARE
  public_key VARCHAR2(500) := 'c:\Projects\PGPKeys\public_key.asc';
  ascii_armor_output BOOLEAN := TRUE;
  message  CLOB;     
  encrypted_message RAW(32000);
  v_Return RAW(32000); 
BEGIN
    message := 'Hello World';
 
    encrypted_message := ORA_PGP.ENCRYPT_RAW(
      UTL_I18N.STRING_TO_RAW(message),
      PUBLIC_KEY,
      ascii_armor_output);
END;
/

5. Encrypting for multiple recipients

We can encrypt for multiple recipients by putting public keys in the data type ORA_PGP_KEY_BLOBS.

In the example below we will use ORA_PGP.ENCRYPT_BLOB with two keys loaded as BLOB fields. The example can be extended with more than two keys of course:

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

DECLARE
  -- public key file location or the key in ASCII armored base64 encoded format
  -- the key location must be granted access like:
  -- call dbms_java.grant_permission( 'USER', 'SYS:java.io.FilePermission', 'c:\Test\*', 'read' );
  public_key_handle BFILE;
  public_key1 BLOB;  
  public_key2 BLOB;  
 
  KEYS ORA_PGP_KEY_BLOBS := ORA_PGP_KEY_BLOBS();
 
  message_file_handle  BFILE;
  message  BLOB;
 
  ascii_armor_output BOOLEAN := FALSE;  
  encrypted_message BLOB;
  v_Return BLOB; -- decrypted message, will be equal to the original [message]
BEGIN
 
    ------------------------------------------
    -- Load BLOB data from file
    ------------------------------------------       
    -- initialize the message storage
    DBMS_LOB.createtemporary(message, TRUE);
 
    -- load the input message as BLOB data
    message_file_handle := BFILENAME('DATA_DIR', 'plsql1.txt');
    DBMS_LOB.OPEN(message_file_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LoadFromFile( DEST_LOB => message,
                         SRC_LOB  => message_file_handle,
                         AMOUNT   => DBMS_LOB.GETLENGTH(message_file_handle) );
    DBMS_LOB.CLOSE(message_file_handle);
 
 
    -- load a key as BLOB
    public_key_handle := BFILENAME('PGP_KEYS_DIR', 'public_key.asc');
    DBMS_LOB.OPEN(public_key_handle, DBMS_LOB.LOB_READONLY);
    -- initialize the key storage
    DBMS_LOB.createtemporary(public_key1, TRUE);        
    DBMS_LOB.LoadFromFile( DEST_LOB => public_key1,
                        SRC_LOB  => public_key_handle,
                        AMOUNT   => DBMS_LOB.GETLENGTH(public_key_handle) );
    DBMS_LOB.CLOSE(public_key_handle);
 
    -- load a key as BLOB
    public_key_handle := BFILENAME('PGP_KEYS_DIR', 'test2_public.pgp');
    DBMS_LOB.OPEN(public_key_handle, DBMS_LOB.LOB_READONLY);
    -- initialize the key storage
    DBMS_LOB.createtemporary(public_key2, TRUE);    
    DBMS_LOB.LoadFromFile( DEST_LOB => public_key2,
                        SRC_LOB  => public_key_handle,
                        AMOUNT   => DBMS_LOB.GETLENGTH(public_key_handle) );
    DBMS_LOB.CLOSE(public_key_handle);
 
    -- load the keys
    KEYS.extend(2); -- number of keys
    KEYS(1) := public_key1;
    KEYS(2) := public_key2;
    -- more keys be added here ..
 
 
    ------------------------------------------
    -- ENCRYPT_BLOB for multiple recipients
    ------------------------------------------       
    encrypted_message := ORA_PGP.ENCRYPT_BLOB(
      MESSAGE => message,
      PUBLIC_KEYS => KEYS,
      ASCII_ARMOR => ascii_armor_output);
 
    -- store encrypted_message or transfer with ORA_SFTP      
END;

The example above can be found in the file ENCRYPT_BLOB_MULTI_KEYS.sql in the Examples folder of OraPGP.

3. Invoking from Java

The encryption functions can be invoked from Java code just like any other PL/SQL procedure:

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 message = "Hello World";
 
		// ENCRYPT_STRING
		CallableStatement cstmt = conn.prepareCall("{?=call ORA_PGP.ENCRYPT(?,?)}");
		cstmt.registerOutParameter(1, Types.VARCHAR);
		cstmt.setString(2, message);
		cstmt.setString(3, "c:\\Projects\\PGPKeys\\public.key");
		cstmt.executeUpdate();
		String encryptedString = cstmt.getString(1);
 
		System.out.println(encryptedString);
	}
}


Integrity Protection

Note: As of version 1.4 Integrity Protection is turned ON by default.

Integrity protecting OpenPGP data consists of embedding a Modification Detection Code (MDC packet) inside the encrypted data. After the press noise from the EFAIL attack major OpenPGP implementations require integrity protection to be activated (e.g. GnuPG 2.2.8 and all future versions reject messages that are not protected).

In order to turn On integrity protection for the current session, we must activate it before performing any encryption with:

BEGIN
  ORA_PGP.SET_INTEGRITY_PROTECT(TRUE);
  ORA_PGP.ENCRYPT...

Exception handling

Exception handling is available as of version 1.1. Please check the dedicated chapter for Exception handling in OraPGP in order to study the custom mechanism provided by the package.

Summary

This chapter illustrated how to invoke the ORA_PGP encryption PL/SQL functions. You can find complete examples in the \Examples sub folder inside the product ZIP archive.

List of methods used:

ORA_PGP.ENCRYPT Encrypts VARCHAR2 data. The output is ASCII armored
ORA_PGP.ENCRYPT_BLOB Encrypts BLOB data. Allows specification of the output format
ORA_PGP.ENCRYPT_CLOB Encrypts CLOB data and produces CLOB. The output is ASCII armored
ORA_PGP.ENCRYPT_RAW Encrypts RAW data. Allows specification of the output format