Decrypt and Verify in PL/SQL

In this article we will show how to decrypt one pass signed and encrypted OpenPGP data and verify the validity of the digital signature contained in it with the help of the ORA_PGP PL/SQL package.

For decrypting such data, we need our OpenPGP private key and of course the sender of the data must have used our public key for preparing/encrypting it.

We also use the public key of the sender in order to verify the digital signature. The package recognizes four possible outcomes when verifying a digital signature, defined as constants inside the package:

SIGNATURE_VERIFIED CONSTANT INTEGER := 1; -- signature is Good
SIGNATURE_BROKEN CONSTANT INTEGER := 0; -- signature is invalid
PUBLIC_KEY_NOT_MATCH CONSTANT INTEGER := -1; -- the public key that we have used to verify the signature doesn't match the key which made the signature
NO_SIGNATURE CONSTANT INTEGER := -2; -- the message is just encrypted and there is no signature inside

Below are short examples that deal with OpenPGP signed and encrypted data located in VARCHAR2, BLOB, CLOB and RAW fields:

Decrypt and verify VARCHAR

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

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);
  SIGNED_AND_ENCRYPTED_DATA VARCHAR(2000);  
BEGIN
  SIGNED_AND_ENCRYPTED_DATA := ... -- obtain such data
 
 -----------------------------------------
 -- Verify OpenPGP signed only message
 -----------------------------------------
 VERIFICATION_RESULT := ORA_PGP.VERIFY_ENCRYPTED(
    MESSAGE => SIGNED_AND_ENCRYPTED_DATA,
    PRIVATE_KEY => PRIVATE_KEY,
    PRIVATE_KEY_PASSWORD => PRIVATE_KEY_PASSWORD,
    PUBLIC_KEY => PUBLIC_KEY,
    DECRYPTED_MESSAGE => DECRYPTED_MESSAGE
  );
 
  -- print the encrypted data
  DBMS_OUTPUT.PUT_LINE('Extracted data: ' || DECRYPTED_MESSAGE);
 
  IF VERIFICATION_RESULT = ORA_PGP.SIGNATURE_VERIFIED THEN
    DBMS_OUTPUT.PUT_LINE('Signature is good.');
  ELSIF VERIFICATION_RESULT = ORA_PGP.SIGNATURE_BROKEN THEN
    DBMS_OUTPUT.PUT_LINE('Signature verification failed!');
  ELSIF VERIFICATION_RESULT = ORA_PGP.PUBLIC_KEY_NOT_MATCH THEN
    DBMS_OUTPUT.PUT_LINE('Supplied public key does not match the signature');
  ELSE   
    DBMS_OUTPUT.PUT_LINE('No signature in message');
  END IF;
END;
/

Decrypt and verify BLOB

For BLOB data we will use the method ORA_PGP.VERIFY_ENCRYPTED_BLOB to check the signature and ORA_PGP.DECRYPT_BLOB to extract the data.

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 BLOB;
  DECRYPTED_MESSAGE BLOB;
  VERIFICATION_RESULT PLS_INTEGER;
BEGIN
 -----------------------------------------
 -- Verify OpenPGP signed and encrypted
 -----------------------------------------
 
 -- initialize MESSAGE with OpenPGP signed and encrypted data
 
 VERIFICATION_RESULT := ORA_PGP.VERIFY_ENCRYPTED_BLOB(
    message => MESSAGE,
    PRIVATE_KEY => PRIVATE_KEY,
    PRIVATE_KEY_PASSWORD => PRIVATE_KEY_PASSWORD,
    PUBLIC_KEY => PUBLIC_KEY
  );
 
  DECRYPTED_MESSAGE := ORA_PGP.DECRYPT_BLOB(
    message => MESSAGE,
    PRIVATE_KEY => PRIVATE_KEY,
    PRIVATE_KEY_PASSWORD => PRIVATE_KEY_PASSWORD);
 
  IF VERIFICATION_RESULT = ORA_PGP.SIGNATURE_VERIFIED THEN
    DBMS_OUTPUT.PUT_LINE('Signature is good.');
  ELSIF VERIFICATION_RESULT = ORA_PGP.SIGNATURE_BROKEN THEN
    DBMS_OUTPUT.PUT_LINE('Signature verification failed!');
  ELSIF VERIFICATION_RESULT = ORA_PGP.PUBLIC_KEY_NOT_MATCH THEN
    DBMS_OUTPUT.PUT_LINE('Supplied public key does not match the signature');
  ELSE   
    DBMS_OUTPUT.PUT_LINE('No signature in message');
  END IF;
 
END;
/

Decrypt and verify CLOB

For CLOB data we will use the method ORA_PGP.VERIFY_ENCRYPTED_CLOB to check the signature and ORA_PGP.DECRYPT_CLOB to extract the data.

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;
  DECRYPTED_MESSAGE CLOB;
  VERIFICATION_RESULT PLS_INTEGER;
BEGIN
  -- initialize MESSAGE with signed and encrypted OpenPGP data
 
 -----------------------------------------
 -- Verify OpenPGP signed and encrypted
 -----------------------------------------
 VERIFICATION_RESULT := ORA_PGP.VERIFY_ENCRYPTED_CLOB(
    message => MESSAGE,
    PRIVATE_KEY => PRIVATE_KEY,
    PRIVATE_KEY_PASSWORD => PRIVATE_KEY_PASSWORD,
    PUBLIC_KEY => PUBLIC_KEY
  );
 
  DECRYPTED_MESSAGE := ORA_PGP.DECRYPT_CLOB(
    message => MESSAGE,
    PRIVATE_KEY => PRIVATE_KEY,
    PRIVATE_KEY_PASSWORD => PRIVATE_KEY_PASSWORD);
 
  IF VERIFICATION_RESULT = ORA_PGP.SIGNATURE_VERIFIED THEN
    DBMS_OUTPUT.PUT_LINE('Signature is good.');
  ELSIF VERIFICATION_RESULT = ORA_PGP.SIGNATURE_BROKEN THEN
    DBMS_OUTPUT.PUT_LINE('Signature verification failed!');
  ELSIF VERIFICATION_RESULT = ORA_PGP.PUBLIC_KEY_NOT_MATCH THEN
    DBMS_OUTPUT.PUT_LINE('Supplied public key does not match the signature');
  ELSE   
    DBMS_OUTPUT.PUT_LINE('No signature in message');
  END IF;
 
END;
/

Decrypt and verify RAW


For RAW we must use the method RA_PGP.VERIFY_ENCRYPTED_RAW to check the signature and ORA_PGP.DECRYPT_RAW to extract the data.

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;
BEGIN
  -- initialize MESSAGE with signed and encrypted data
 
 -----------------------------------------
 -- Verify OpenPGP signed and encrypted
 -----------------------------------------
 VERIFICATION_RESULT := ORA_PGP.VERIFY_ENCRYPTED_RAW(
    message => MESSAGE,
    PRIVATE_KEY => PRIVATE_KEY,
    PRIVATE_KEY_PASSWORD => PRIVATE_KEY_PASSWORD,
    PUBLIC_KEY => PUBLIC_KEY
  );
 
 
  DECRYPTED_MESSAGE := ORA_PGP.DECRYPT_RAW(
    message => MESSAGE,
    PRIVATE_KEY => PRIVATE_KEY,
    PRIVATE_KEY_PASSWORD => PRIVATE_KEY_PASSWORD
  );
 
  -- print the encrypted data
  DBMS_OUTPUT.PUT_LINE('Extracted data: ' || UTL_I18N.RAW_TO_CHAR(DECRYPTED_MESSAGE, 'AL32UTF8'));
 
  IF VERIFICATION_RESULT = ORA_PGP.SIGNATURE_VERIFIED THEN
    DBMS_OUTPUT.PUT_LINE('Signature is good.');
  ELSIF VERIFICATION_RESULT = ORA_PGP.SIGNATURE_BROKEN THEN
    DBMS_OUTPUT.PUT_LINE('Signature verification failed!');
  ELSIF VERIFICATION_RESULT = ORA_PGP.PUBLIC_KEY_NOT_MATCH THEN
    DBMS_OUTPUT.PUT_LINE('Supplied public key does not match the signature');
  ELSE   
    DBMS_OUTPUT.PUT_LINE('No signature in message');
  END IF;
 
END;
/

Summary

This chapter discussed how to decrypt and verify OpenPGP signed and encrypted data inside the Oracle database.