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.
- Sign and encrypt VARCHAR2
- Sign and encrypt BLOB
- Sign and encrypt CLOB
- Sign and encrypt RAW
- Sign and encrypt for multiple recipients
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 |
