The functions of the ORA_PGP package devoted to inspecting OpenPGP data can be very useful if we need to implement PL/SQL solutions that handle data encrypted with more than one key or if we need to handle various types of PGP archives.
Below are listed some use cases that illustrate the inspection routines in practice:
- Is the data signed-only?
- Is the data encrypted with a key?
- Is the data encrypted with a password
- Get the encryption key Id
- Get the encryption keys Id’s
- Get signing key
- Get signing key of a signed and encrypted data
- Use case: using the right private key for decryption
Is the data signed only?
To determine if PGP data available as CLOB, BLOB or VARAHCAR2 is PGP signed or clear text signed we can use ORA_PGP.IS_SIGNED:
1 2 3 4 5 6 7 8 | DECLARE pgp_data BLOB; DATA BLOB; BEGIN IF ORA_PGP.IS_SIGNED(pgp_data) THEN DATA:= ORA_PGP.EXTRACT_SIGNED_BLOB(pgp_data) END IF; END |
If the data is signed-only we can verify the signature with the public key of the recipient.
Is the data encrypted with a public key?
The method ORA_PGP.IS_ENCRYPTED will return TRUE for PGP encrypted and signed-and-encrypted messages
1 2 3 4 5 6 7 8 | DECLARE pgp_data BLOB; DATA BLOB; BEGIN IF ORA_PGP.IS_ENCRYPTED(pgp_data) THEN -- do decrypt END IF; END |
Is the data encrypted with a password?
The method ORA_PGP.IS_PASSWORD_ENCRYPTED will return TRUE for PGP data encrypted with a password
1 2 3 4 5 6 7 8 | DECLARE pgp_data BLOB; DATA BLOB; BEGIN IF ORA_PGP.IS_PASSWORD_ENCRYPTED(pgp_data) THEN -- do decrypt END IF; END |
Get the encryption key Id
We can get the ID of the first (and usually only one) encryption key of a PGP encrypted message with ORA_PGP.ENCRYPTION_KEY_ID:
1 2 3 4 5 6 7 8 | DECLARE encrypted_data BLOB; first_encryption_key_id varchar2(255); BEGIN -- load encrypted_data first_encryption_key_id := ORA_PGP.ENCRYPTION_KEY_ID(encrypted_data); dbms_output.put_line('first encryption key ID ' || first_encryption_key); END; |
If a PGP message is encrypted with a password instead of a public key, the method will return the value “SYMKEY”.
Get the encryption keys Id’s
If a message is encrypted for more than one recipient we can get the ID’s of all encryption keys with ORA_PGP.ENCRYPTION_KEYS_IDS:
1 2 3 4 5 6 7 8 9 10 11 | DECLARE encrypted_data BLOB; encryption_keys ENCRYPTION_KEYS_IDS_LIST; BEGIN -- load encrypted_data encryption_keys := ORA_PGP.ENCRYPTION_KEYS_IDS(encrypted_data); -- print all encryption keys FOR i IN encryption_keys.first .. encryption_keys.last loop dbms_output.put_line('key(' || i || ') = ' || encryption_keys(i)); END loop; END; |
Get signing key
For a signed-only or clear text signed message we can get the ID of the signing key with ORA_PGP.SIGNING_KEY_ID:
1 2 3 4 5 6 7 8 | DECLARE signed_data BLOB; signing_key_id varchar2(255); BEGIN -- load signed_data and PRIVATE_KEY signing_key := ORA_PGP.SIGNING_KEY_ID(signed_data); dbms_output.put_line('signing key ID ' || signing_key); END; |
Get signing key of a signed and encrypted data
If we know that an encrypted message is also signed, we can get the signing key ID with an overloaded version of ORA_PGP.SIGNING_KEY_ID where we have to supply a private key and its password for decryption. The private key ID must be matching the encryption key ID.
1 2 3 4 5 6 7 8 9 10 | DECLARE PRIVATE_KEY CLOB; PRIVATE_KEY_PASSWORD VARCHAR2(255) := 'changeit'; signed_and_encrypted_data BLOB; signing_key_id varchar2(255); BEGIN -- load signed_and_encrypted_data and PRIVATE_KEY signing_key := ORA_PGP.SIGNING_KEY_ID(signed_and_encrypted_data, PRIVATE_KEY, PRIVATE_KEY_PASSWORD); dbms_output.put_line('signing key ID ' || signing_key); END; |
1. Use case: Using the right private key for decryption.
In this imaginary use case, we have multiple private keys stored in a database.
table secretmessages(id PLS_INTEGER, encrypted_data BLOB)
and
table private_keys(key_data CLOB, 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 CLOB; KEY_PASSWORD VARCHAR2(200); v_Return BLOB; BEGIN MESSAGE := NULL; PRIVATE_KEY := NULL; KEY_PASSWORD := NULL; SELECT encrypted_data INTO MESSAGE FROM secretmessages 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; |
Summary
This chapter was a short introduction to the OpenPGP inspection features of the ORA_PGP package.