Sign and encrypt in PL/SQL

OpenPGP one pass sign and encrypt produces an encrypted with the recipient public key message with an additional digital signature created with our private key also stored encrypted inside.

In this chapter we are going to illustrate how to perform this operation with DidiSoft OraPgp (ORA_PGP) PL/SQL package. The OpenPGP keys needed for the operation can be supplied either like external file system locations or loaded from BLOB fields. For simplicity in the examples here we use keys straightly from the file system. But each method has the same overloaded version which accepts the keys loaded in BLOB fields.

Integrity Protection

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:

1
2
3
DECLARE
  ORA_PGP.SET_INTEGRITY_PROTECT(TRUE);
  ORA_PGP.SIGN_AND_ENCRYPT...

Sign and encrypt VARCHAR2

For VARCHAR2 data we will use the method ORA_PGP.SIGN_AND_ENCRYPT:

DECLARE
  public_key VARCHAR2(500) := 'c:\Projects\PGPKeys\public_key.asc'; -- public key of the recipient
  PRIVATE_KEY VARCHAR2(500) := 'c:\Projects\PGPKeys\private_key.asc'; -- our private key
  PRIVATE_KEY_PASSWORD VARCHAR2(500) := 'changeit';
 
  MESSAGE VARCHAR(2000);
  v_Return VARCHAR(2000);  
BEGIN
  MESSAGE := 'Hello World!';
 
 -----------------------------------------
 -- OpenPGP sign
 -----------------------------------------
  v_Return := ORA_PGP.SIGN_AND_ENCRYPT(
    MESSAGE => MESSAGE,
    PRIVATE_KEY => PRIVATE_KEY,
    PRIVATE_KEY_PASSWORD => PRIVATE_KEY_PASSWORD,
    PUBLIC_KEY => PUBLIC_KEY
  );
 
 DBMS_OUTPUT.PUT_LINE('Signed data = ' || v_Return);
END;
/

Sign and encrypt BLOB

For BLOB data we will use the method ORA_PGP.SIGN_AND_ENCRYPT_BLOB. The last parameter of the method ascii_armor is a BOOLEAN value that indicates shall the output be in OpenPGP ASCII armored format or binary format. This is useful to be taken into account if you need to export the data in one of those formats outside of the Oracle database later.

DECLARE
  public_key VARCHAR2(500) := 'c:\Projects\PGPKeys\public_key.asc';
  PRIVATE_KEY VARCHAR2(500) := 'c:\Projects\PGPKeys\private_key.asc';
  PRIVATE_KEY_PASSWORD VARCHAR2(500) := 'changeit';
 
  ascii_armor_output BOOLEAN := FALSE;  
 
  message_file_handle  BFILE;
  MESSAGE BLOB;
  v_Return BLOB;  
BEGIN
    -- initialize the message storage
    DBMS_LOB.createtemporary(message, TRUE);
 
    message_file_handle := BFILENAME('DATA_FILE_DIR', 'olp_v3.sql'); 
 
    -- load the data into a BLOB
    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);
 
 -----------------------------------------
 -- OpenPGP sign and encrypt
 -----------------------------------------
  v_Return := ORA_PGP.SIGN_AND_ENCRYPT_BLOB(
    MESSAGE => MESSAGE,
    PRIVATE_KEY => PRIVATE_KEY,
    PRIVATE_KEY_PASSWORD => PRIVATE_KEY_PASSWORD,
    PUBLIC_KEY => PUBLIC_KEY,
    ascii_armor => ascii_armor_output	
  );
 
END;
/

Sign and encrypt CLOB

For CLOB data we will use the method ORA_PGP.SIGN_AND_ENCRYPT_CLOB. The output from it is in OpenPGP ASCII armored format.

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 VARCHAR2(500) := 'c:\Projects\PGPKeys\public_key.asc';
  PRIVATE_KEY VARCHAR2(500) := 'c:\Projects\PGPKeys\private_key.asc';
  PRIVATE_KEY_PASSWORD VARCHAR2(500) := 'changeit';
 
  MESSAGE CLOB;
  v_Return CLOB;  
BEGIN
  MESSAGE := 'Hello World!';
 
 -----------------------------------------
 -- OpenPGP sign and encrypt
 -----------------------------------------
 v_Return := ORA_PGP.SIGN_AND_ENCRYPT_CLOB(
    MESSAGE => MESSAGE,
    PRIVATE_KEY => PRIVATE_KEY,
    PRIVATE_KEY_PASSWORD => PRIVATE_KEY_PASSWORD,
    PUBLIC_KEY => PUBLIC_KEY
  );
 
 DBMS_OUTPUT.PUT_LINE('Signed data = ' || v_Return);
 
END;
/

Sign and encrypt RAW


For RAW we must use the method ORA_PGP.SIGN_AND_ENCRYPT_RAW. Here again the last parameter indicates the format of the output.

DECLARE
  public_key VARCHAR2(500) := 'c:\Projects\PGPKeys\public_key.asc';
  PRIVATE_KEY VARCHAR2(500) := 'c:\Projects\PGPKeys\private_key.asc';
  PRIVATE_KEY_PASSWORD VARCHAR2(500) := 'changeit';
 
  MESSAGE VARCHAR(2000);
  DECRYPTED_MESSAGE RAW(32726);
  VERIFICATION_RESULT PLS_INTEGER;
  v_Return RAW(32726);  
 
  ASCII_armor_output BOOLEAN := FALSE;
 
BEGIN
  MESSAGE := 'Hello World!';
 
 -----------------------------------------
 -- OpenPGP sign and encrypt
 -----------------------------------------
  v_Return := ORA_PGP.SIGN_AND_ENCRYPT_RAW(
    MESSAGE => UTL_I18N.STRING_TO_RAW(MESSAGE),
    PRIVATE_KEY => PRIVATE_KEY,
    PRIVATE_KEY_PASSWORD => PRIVATE_KEY_PASSWORD,
    PUBLIC_KEY => PUBLIC_KEY,
    ascii_armor => ASCII_armor_output
  );
 
END;
/

Sign and encrypt for multiple recipients

The method ORA_PGP.SIGN_AND_ENCRYPT_BLOB can also encrypt the data for multiple recipients by placing their public keys as BLOBs in the ORA_PGP_KEY_BLOBS data type.
This example uses two public keys for clarity but you can extend it for more than two keys. If you don’t know how many keys will be used upfront, just place a call to keys.extend() before placing each key in a loop and remove starting keys.extend(2) from this example.

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;  
 
  private_key_handle BFILE;
  private_key BLOB;  
  PRIVATE_KEY_PASSWORD VARCHAR2(500);
 
  DECRYPTED_MESSAGE BLOB;
  VERIFICATION_RESULT PLS_INTEGER;
  v_Return BLOB;  
BEGIN
    -- 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 ..
 
 
    -- initialize the key storage
    DBMS_LOB.createtemporary(PRIVATE_KEY, TRUE);
 
    -- load a key from the flesystem
    -- the direcory name must be created upfront with CREATE DIRECTORY
    -- for example: CREATE DIRECTORY MY_DATA_DIR AS '/demo/schema/my_data_folder';
    private_key_handle := BFILENAME('PGP_KEYS_DIR', 'private_key.asc'); -- directory name must be Upper case
 
    -- load the data into a BLOB
    DBMS_LOB.OPEN(private_key_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LoadFromFile( DEST_LOB => private_key,
                         SRC_LOB  => private_key_handle,
                         AMOUNT   => DBMS_LOB.GETLENGTH(private_key_handle) );
    DBMS_LOB.CLOSE(private_key_handle);
 
    PRIVATE_KEY_PASSWORD := 'changeit';
 
 
DBMS_JAVA.set_output(10000);
 -----------------------------------------
 -- OpenPGP sign and encrypt
 -----------------------------------------
  v_Return := ORA_PGP.SIGN_AND_ENCRYPT_BLOB(
    MESSAGE => message,
    PRIVATE_KEY => private_key,
    PRIVATE_KEY_PASSWORD => private_key_password,
    PUBLIC_KEYS => KEYS,
    ascii_armor => ascii_armor_output  
  );

Summary

This chapter discussed how to OpenPGP sign and encrypt data in one pass inside the Oracle database. You may also like to check the article regarding how to extract and verify data produced this way.

List of methods used:

ORA_PGP.SIGN_AND_ENCRYPT OpenPGP signs and encrypts VARCHAR2 data
ORA_PGP.SIGN_AND_ENCRYPT_BLOB OpenPGP signs and encrypts BLOB data
ORA_PGP.SIGN_AND_ENCRYPT_CLOB OpenPGP signs and encrypts CLOB data
ORA_PGP.SIGN_AND_ENCRYPT_RAW OpenPGP signs and encrypts RAW data