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 |