Inspecting OpenPGP data inside the Oracle(c) Database

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)
and
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.

Summary

This chapter was a short introduction to the OpenPGP inspection features of the ORA_PGP package.