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;
/

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