Inspecting OpenPGP data inside the Oracle(c) Database

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?

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.