The functions of the ORA_PGP package devoted to inspecting of OpenPGP data can be very useful is we need to implement PL/SQL solutions that handle data encrypted with more than one key.
Below are listed some use cases that illustrate the inspection routines in practice:
1. Using the right private key for decryption.
Let’s imagine that we have a table:
table secrets(id pls_integer, encrypted_data BLOB)
table private_keys(key_data VARCHAR2, key_password VARCHAR2)
Here is how we can use the appropriate key to decrypt a given message:
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
DECLARE MESSAGE BLOB; KEY_ID VARCHAR(8); PRIVATE_KEY VARCHAR2(200); KEY_PASSWORD VARCHAR2(200); v_Return BLOB; BEGIN MESSAGE := NULL; PRIVATE_KEY := NULL; KEY_PASSWORD := NULL; SELECT encrypted_data INTO MESSAGE FROM secrets WHERE id = 100; SELECT ORA_PGP.ENCRYPTION_KEY_ID(MESSAGE) INTO KEY_ID FROM dual; IF KEY_ID = 'ANYKEY' THEN -- the message was encrypted with a wildcard (hidden) key id -- in this case we should try with all possible keys ELSE SELECT key_data, key_password INTO PRIVATE_KEY, KEY_PASSWORD FROM private_keys WHERE ORA_PGP.ENCRYPTION_KEY_ID(key_data) = KEY_ID; END IF; v_Return := ORA_PGP.DECRYPT_BLOB( DATA => MESSAGE, PRIVATE_KEY => PRIVATE_KEY, KEY_PASSWORD => KEY_PASSWORD ); END;
2. Determine should we decrypt or verify a given OpenPGP message.
We can use the methods ORA_PGP.IS_ENCRYPTED and ORA_PGP.IS_SIGNED in order to check how was given OpenPGP message packaged and decide what to do with it.
This chapter was a short introduction to the OpenPGP inspection features of the ORA_PGP package.