In this chapter we are going to study how to OpenPGP encrypt data inside Oracle DB with the help of DidiSoft OraPGP PL/SQL paclage.
1. Encrypting VARCHAR2
2. Encrypting CLOB
3. Encrypting BLOB
4. Encrypting RAW
5. Encrypting for multiple recipients
6. Invoking from Java
7. Integrity Protection
8. Exception handling
When we perform OpenPGP encryption we need the public key of the recipient. Of course if we need the data encrypted only for our own needs, we are going to use our own public key. The package accepts public keys as sever file system path location or as ASCII armored string in VARCHAR2 or BLOB fields. Check the chapter regarding how to use OpenPGP keys with ORA_PGP if you still haven’t. The examples here illustrate for short only using keys as file system path.
1. Encrypting VARCHAR2
The encryption method expect the public key to be provided as an absolute file path on the server or in ASCII armored format. The example below uses absolute file path. In order the package to be able to access the key the invoking user must have access permissions for it.
The output will be OpenPGP encrypted message in ASCII armored format returned as VARCHAR2 data.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | DECLARE MESSAGE VARCHAR2(2000); PUBLIC_KEY VARCHAR2(2000); v_Return VARCHAR2(2000); -- we need sufficient space here, or an error will be raised BEGIN MESSAGE := 'Hello World'; PUBLIC_KEY := 'c:\PGPKeys\recipient_public_key.asc'; v_Return := ORA_PGP.ENCRYPT( MESSAGE => MESSAGE, PUBLIC_KEY => PUBLIC_KEY ); -- print the encrypted data DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return); END; / |
2. Encrypting CLOB
We can encrypt short CLOB fields using the same code as with the VARCHAR2 above. In order to avoid CLOB to VARCHAR2 conversion we are going to use the dedicated method ORA_PGP.ENCRYPT_CLOB which produces OpenPGP encrypted data in ASCII armored format as CLOB field. By default the encrypted data is assigned internally an artificial “message.txt” label. You can change it with an overloaded method that accepts an additional data_file_label parameter (like in the BLOB example below) :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | DECLARE MESSAGE CLOB; PUBLIC_KEY VARCHAR2(2000); v_Return CLOB; BEGIN MESSAGE := 'Hello World'; PUBLIC_KEY := 'c:\PGPKeys\recipient_public_key.asc'; v_Return := ORA_PGP.ENCRYPT_CLOB( MESSAGE => MESSAGE, PUBLIC_KEY => PUBLIC_KEY ); -- print the encrypted data DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return); END; / |
3. Encrypting BLOB
The two method signatures for encrypting BLOB data are
ORA_PGP.ENCRYPT_BLOB(message BLOB, public_key BLOB|VARCHAR2, ascii_armor BOOLEAN) return BLOB
ORA_PGP.ENCRYPT_BLOB(message BLOB, data_file_label VARCHAR2, public_key BLOB|VARCHAR2, ascii_armor BOOLEAN) return BLOB
The message parameter is the source data to be encrypted and the public_key is the encryption key. The ascii_armor parameter decides shall the PGP output format be binary or ASCII.
The mystical data_file_label parameter (available as of version 1.3.1) assigns a file name to the encrypted data. The idea is that if the encrypted BLOB is decrypted to a file outside of the database, that decrypted file name will have the value in data_file_label.
In the example below we use a sample source table with definition:
table documents +------------------------+ | document_id varchar(10)| | file_data blob | +------------------------+
The example call below will produce binary OpenPGP format:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | DECLARE MESSAGE BLOB; PUBLIC_KEY VARCHAR2(200); v_Return BLOB; ascii_armor_output BOOLEAN := FALSE; BEGIN MESSAGE := NULL; PUBLIC_KEY := 'c:\PGPKeys\public_keys.asc'; SELECT file_data INTO MESSAGE FROM documents WHERE document_id = 'A103'; v_Return := ORA_PGP.ENCRYPT_BLOB( message => MESSAGE, data_file_label => 'Report_A103.doc', -- file name assigned to the encrypted data, can be skipped public_key => PUBLIC_KEY, ascii_armor => ascii_armor_output ); END; / |
4. Encrypting RAW
For every other case, we can use ORA_PGP.ENCRYPT_RAW by first convering the data into RAW format. Here we also have the option to specify shall the output be ASCII armored or .pgp binary:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | DECLARE public_key VARCHAR2(500) := 'c:\Projects\PGPKeys\public_key.asc'; ascii_armor_output BOOLEAN := TRUE; message CLOB; encrypted_message RAW(32000); v_Return RAW(32000); BEGIN message := 'Hello World'; encrypted_message := ORA_PGP.ENCRYPT_RAW( UTL_I18N.STRING_TO_RAW(message), PUBLIC_KEY, ascii_armor_output); END; / |
5. Encrypting for multiple recipients
We can encrypt for multiple recipients by putting public keys in the data type ORA_PGP_KEY_BLOBS.
In the example below we will use ORA_PGP.ENCRYPT_BLOB with two keys loaded as BLOB fields. The example can be extended with more than two keys of course:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | 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; encrypted_message BLOB; v_Return BLOB; -- decrypted message, will be equal to the original [message] BEGIN ------------------------------------------ -- Load BLOB data from file ------------------------------------------ -- 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 .. ------------------------------------------ -- ENCRYPT_BLOB for multiple recipients ------------------------------------------ encrypted_message := ORA_PGP.ENCRYPT_BLOB( MESSAGE => message, PUBLIC_KEYS => KEYS, ASCII_ARMOR => ascii_armor_output); -- store encrypted_message or transfer with ORA_SFTP END; |
The example above can be found in the file ENCRYPT_BLOB_MULTI_KEYS.sql in the Examples folder of OraPGP.
3. Invoking from Java
The encryption functions can be invoked from Java code just like any other PL/SQL procedure:
import java.sql.*; public class OraPGPTests { private static final String JDBC_ORACLE_CONNECTION = "jdbc:oracle:thin:@localhost:1522:orcl2"; public void testEncryptString() throws SQLException { DriverManager.registerDriver (new oracle.jdbc.OracleDriver()); Connection conn = DriverManager.getConnection (JDBC_ORACLE_CONNECTION, "user", "pass"); String message = "Hello World"; // ENCRYPT_STRING CallableStatement cstmt = conn.prepareCall("{?=call ORA_PGP.ENCRYPT(?,?)}"); cstmt.registerOutParameter(1, Types.VARCHAR); cstmt.setString(2, message); cstmt.setString(3, "c:\\Projects\\PGPKeys\\public.key"); cstmt.executeUpdate(); String encryptedString = cstmt.getString(1); System.out.println(encryptedString); } } |
Integrity Protection
Note: As of version 1.4 Integrity Protection is turned ON by default.
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:
BEGIN ORA_PGP.SET_INTEGRITY_PROTECT(TRUE); ORA_PGP.ENCRYPT... |
Exception handling
Exception handling is available as of version 1.1. Please check the dedicated chapter for Exception handling in OraPGP in order to study the custom mechanism provided by the package.
Summary
This chapter illustrated how to invoke the ORA_PGP encryption PL/SQL functions. You can find complete examples in the \Examples sub folder inside the product ZIP archive.
List of methods used:
ORA_PGP.ENCRYPT | Encrypts VARCHAR2 data. The output is ASCII armored |
ORA_PGP.ENCRYPT_BLOB | Encrypts BLOB data. Allows specification of the output format |
ORA_PGP.ENCRYPT_CLOB | Encrypts CLOB data and produces CLOB. The output is ASCII armored |
ORA_PGP.ENCRYPT_RAW | Encrypts RAW data. Allows specification of the output format |