This article describes how to decrypt password encrypted OpenPGP data inside the Oracle(c) DB with the help of OraPGP PL/SQL package.
We will need the encryption password in order to decrypt the data.
1. Decrypting CLOB
2. Decrypting BLOB
3. Exception handling
1 |
Decrypting CLOB
The example below decrypts a previously PGP encrypted with a password CLOB field. The decrypted data is returned as a CLOB field too.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | DECLARE MESSAGE CLOB; PASSWORD VARCHAR2(200); v_Return CLOB; BEGIN MESSAGE := NULL; PASSWORD := 'my password'; SELECT encrypted_data_field INTO MESSAGE FROM my_data_table WHERE my_id = 1000; v_Return := ORA_PGP.DECRYPT_CLOB_PASSWORD( DATA => MESSAGE, password => PASSWORD ); END; |
Decrypting BLOB
Decrypting a BLOB field, which was PGP encrypted with a password, 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 | DECLARE MESSAGE BLOB; PASSWORD VARCHAR2(200); v_Return BLOB; BEGIN MESSAGE := NULL; 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, password => 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 it into CLOB respecting the encoding of the data. A demo conversions routine from BLOB to CLOB can be found on GitHub.
1 |
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 article illustrated how to decrypt previously encrypted with a password PGP data inside the Oracle(c) database with PL/SQL.
You may also check how to verify if the data is indeed encrypted with a password before performing any operations.
List of methods used:
ORA_PGP.DECRYPT_BLOB_PASSWORD | Decrypts BLOB data. |
ORA_PGP.DECRYPT_CLOB_PASSWORD | Decrypts CLOB data |