In this chapter we will show how to perform decrypting of OpenPGP data inside the Oracle(c) database with the help of OraPGP PL/SQL package.
In order to decrypt OpenPGP encrypted data we need a private key corresponding to the public key used for encryption and the unlocking password of the private key. If the password has an empty password, then NULL can be used in the place of the key password parameter.
Keys can be used from the file system, blob fields or inline: see using .pgp keys with Ora_PGP. For simplicity here we use only keys from the file system.
1. Decrypting VERCHAR2
2. Decrypting CLOB
3. Decrypting BLOB
4. Decrypting RAW
5. Invoking from Java
6. Exception handling
Decrypting VERCHAR2
The example below decrypt a previously encrypted VARCHAR2 field. As with the other methods of the ORA_PGP package the key can be provided either as an absolute file path, or as in ASCII armored 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 VARCHAR2(2000); PRIVATE_KEY VARCHAR2(200); KEY_PASSWORD VARCHAR2(200); v_Return VARCHAR2(200); BEGIN MESSAGE := NULL; PRIVATE_KEY := 'c:\PGPKeys\private_key.asc'; KEY_PASSWORD := 'key password'; SELECT encrypted_data_field INTO MESSAGE FROM my_data_table WHERE my_id = 1000; v_Return := ORA_PGP.DECRYPT( MESSAGE => MESSAGE, PRIVATE_KEY => PRIVATE_KEY, KEY_PASSWORD => KEY_PASSWORD ); DBMS_OUTPUT.PUT_LINE('Decrypted data = ' || v_Return); END; |
Decrypting CLOB
The example below decrypt a previously encrypted CLOB field. The decrypted data is returned as CLOB field too.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | DECLARE MESSAGE CLOB; PRIVATE_KEY VARCHAR2(2000); KEY_PASSWORD VARCHAR2(200); v_Return CLOB; BEGIN MESSAGE := NULL; PRIVATE_KEY := 'c:\PGPKeys\private_key.asc'; KEY_PASSWORD := 'key password'; SELECT encrypted_data_field INTO MESSAGE FROM my_data_table WHERE my_id = 1000; v_Return := ORA_PGP.DECRYPT_CLOB( DATA => MESSAGE, PRIVATE_KEY => PRIVATE_KEY, KEY_PASSWORD => KEY_PASSWORD ); END; |
Decrypting BLOB
Decrypting a BLOB field is very similar to decrypting a VARCHAR2 field:
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; PRIVATE_KEY VARCHAR2(200); KEY_PASSWORD VARCHAR2(200); v_Return BLOB; BEGIN MESSAGE := NULL; PRIVATE_KEY := 'c:\PGPKeys\private_key.asc'; KEY_PASSWORD := 'key password'; SELECT encrypted_data_field INTO MESSAGE FROM my_data_table WHERE my_id = 1000; v_Return := ORA_PGP.DECRYPT_BLOB( DATA => MESSAGE, PRIVATE_KEY => PRIVATE_KEY, KEY_PASSWORD => KEY_PASSWORD ); -- v_Return now contains the decrypted data END; |
After the decryption, if we know that the BLOB contains textual data, we have to convert in to CLOB respecting the encoding of the data. A demo conversions routine from BLOB to CLOB can be found on GitHub.
Decrypting RAW
Decrypting a RAW field is very similar to decrypting a BLOB field. The result is a RAW field.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | DECLARE MESSAGE RAW; PRIVATE_KEY VARCHAR2(2000); KEY_PASSWORD VARCHAR2(200); v_Return RAW; BEGIN MESSAGE := NULL; PRIVATE_KEY := 'c:\PGPKeys\private_key.asc'; KEY_PASSWORD := 'key password'; SELECT encrypted_data_field INTO MESSAGE FROM my_data_table WHERE my_id = 1000; v_Return := ORA_PGP.DECRYPT_RAW( DATA => MESSAGE, PRIVATE_KEY => PRIVATE_KEY, KEY_PASSWORD => KEY_PASSWORD ); -- v_Return now contains the decrypted data END; |
Invoking from Java
Here is an example how we can call the decryption PL/SQL code from java:
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 | 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 encryptedString = "... pgp encrypted string here ..."; // ENCRYPT_STRING CallableStatement cstmt = conn.prepareCall("{?=call ORA_PGP.DECRYPT(?,?,?)}"); cstmt.registerOutParameter(1, Types.VARCHAR); cstmt.setString(2, encryptedString); cstmt.setString(3, "c:\\Projects\\PGPKeys\\private.key"); cstmt.setString(4, "key password"); cstmt.executeUpdate(); String decryptedString = cstmt.getString(1); System.out.println(decryptedString); } } |
Exception handling
For simplicity exception handling is not included in the above example. For production systems you shall apply PL/SQL exception handling code as described.
Summary
This chapter illustrated how to decrypt inside the Oracle(c) database an OpenPGP encrypted data.
You may also check how to investigate which public key was used for encrypting given column.
List of methods used:
ORA_PGP.DECRYPT | Decrypts VARCHAR2 data |
ORA_PGP.DECRYPT_BLOB | Decrypts BLOB data. |
ORA_PGP.DECRYPT_CLOB | Decrypts CLOB data |
ORA_PGP.DECRYPT_RAW | Decrypts RAW data |