Cleartext signed with PL/SQL

Cleartext signatures are an OpenPGP data format where textual data as is, is followed by a digital signature.

With ORA_PGP we can create output in this format with the CLEAR_SIGN methods on columns of type VARCHAR2, BLOB, CLOB and RAW. Clear text signatures are verified with the same methods used for verifying PGP signatures.

Just like ordinary OpenPGP signatures, a private key is needed for the signature. Also, a hash algorithm must be specified explicitly, with available values:

ORA_PGP.HASH_SHA1
ORA_PGP.HASH_SHA224
ORA_PGP.HASH_SHA256
ORA_PGP.HASH_SHA384
ORA_PGP.HASH_SHA512
ORA_PGP.HASH_RIPEMD160
ORA_PGP.HASH_MD5

The examples below illustrate how to use the ORA_RSA.CLEAR_SIGN methods:

Cleartext signing VARCHAR2

The sample code below will create a clear text OpenPGP signature for a VARCHAR2 column. The private key used will be passed as a BLOB field.

DECLARE
  private_key_handle BFILE;
  private_key BLOB;  
  PRIVATE_KEY_PASSWORD VARCHAR2(500) := 'changeit';
 
  MESSAGE VARCHAR(2000);
  v_Return VARCHAR(2000);  
BEGIN
  MESSAGE := 'Hello World!';
 
    private_key_handle := BFILENAME('PGP_KEYS_DIR', 'private_key.asc'); -- directory name must be Upper case
 
    -- load the private key into a BLOB
    DBMS_LOB.OPEN(private_key_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.createtemporary(private_key, TRUE);
    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);
 
 -----------------------------------------
 -- OpenPGP sign
 -----------------------------------------
  v_Return := ORA_PGP.CLEAR_SIGN(
     MESSAGE => MESSAGE,
     PRIVATE_KEY => private_key,
     PRIVATE_KEY_PASSWORD => PRIVATE_KEY_PASSWORD,
     hash => ORA_PGP.HASH_SHA256
   );
 
 DBMS_OUTPUT.PUT_LINE('Signed data = ' || v_Return);  
END;
/

Cleartext signing CLOB

This sample will create a clear text signature for data located in a CLOB.

DECLARE
  private_key_handle BFILE;
  private_key BLOB;  
  PRIVATE_KEY_PASSWORD VARCHAR2(500) := 'changeit';
 
  MESSAGE CLOB;
  v_Return CLOB;  
BEGIN
  MESSAGE := 'Hello World!';
 
    private_key_handle := BFILENAME('PGP_KEYS_DIR', 'private_key.asc'); 
 
    -- load the private key into a BLOB
    DBMS_LOB.OPEN(private_key_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.createtemporary(private_key, TRUE);
    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);
 
 -----------------------------------------
 -- OpenPGP clear sign
 -----------------------------------------
  v_Return := ORA_PGP.CLEAR_SIGN_CLOB(
    MESSAGE => MESSAGE,
    PRIVATE_KEY => PRIVATE_KEY,
    PRIVATE_KEY_PASSWORD => PRIVATE_KEY_PASSWORD,
    hash => ORA_PGP.HASH_SHA256
  );
 
 DBMS_OUTPUT.PUT_LINE('Signed data = ' || v_Return); 
END;
/

Cleartext signing BLOB and RAW

ORA_PGP provides CLEAR_SIGN methods for BLOB and RAW columns but they are intentionally not illustrated here. The clear text signature format is designed for text data only and we don’t recommend their usage in the general case.