Using PGP keys in Oracle DB

In order to perform OpenPGP cryptography, we need .pgp keys. When encrypting we usually use the public key of the intended recipient of the message. When decrypting we use our own private key protected by its password phrase.

If you don’t have pgp keys you can use our free OpenPGP key tool from our home page. OpenPGP keys can be met in two formats: binary and the more common ASCII armored with file name extension .asc. ORA_PGP can utilize both formats equally.

Keys can be created with our Online PGP Key Tool.

Using the keys from PL/SQL

DidiSoft ORA_PGP offers two ways of using OpenPGP key files: from file system locations (assumed on the same machine where the Oracle server is) and loaded within BLOB fields. There is also a third possibility to use keys hard coded inside your PL/SQL code. All three solutions will be shown with explanations down here.

Using keys from the file system

In order to use keys from the file system we must first grant access for the folder where they reside to the Oracle user(schema) that will invoke the code, like:

call dbms_java.grant_permission( 'USER', 'SYS:java.io.FilePermission', 'c:\Projects\PGPKeys\*', 'read' );

Then we ca use the keys directly from their location:

1
2
3
4
5
6
7
8
9
10
11
12

DECLARE
  PUBLIC_KEY  CLOB;  
  message CLOB;  
  encrypted_message CLOB;
BEGIN
  PUBLIC_KEY := 'c:\Projects\PGPKeys\public.key'; 
  encrypted_message := ORA_PGP.ENCRYPT(
    MESSAGE => message,
    PUBLIC_KEY => PUBLIC_KEY);    
END;
/

Using keys from a BLOB field

ORA_PGP accepts keys from BLOB fields or variables. So we can initially do a one time load of keys from the file system into our own table structure and afterwards use them when needed. In this example lets assume that we shall prepare OpenPGP encrypted data for may partners and each partner has their own public key (this is a real world scenario). Having a table with structure like this:

Partners
+----------------+
|PartnerID INT   |
|PublicKey BLOB  |
|...             |
+----------------+

We are going to read keys from the file system and insert them into the table above (this will be a one time job, in contrast to the first example above). First we have to create a DIRECTORY variable like:

CREATE DIRECTORY PGP_KEYS_DIR AS '/demo/schema/my_keys';

Then we ca use load the keys

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

DECLARE
  public_key_handle BFILE;
  public_key BLOB;
BEGIN
    -- initialize the key storage
    DBMS_LOB.createtemporary(public_key, TRUE);
 
    public_key_handle := BFILENAME('PGP_KEYS_DIR', 'public_key.asc'); -- directory name must be Upper case
 
    -- load the data into a BLOB
    DBMS_LOB.OPEN(public_key_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LoadFromFile( DEST_LOB => public_key,
                         SRC_LOB  => public_key_handle,
                         AMOUNT   => DBMS_LOB.GETLENGTH(public_key_handle) );
    DBMS_LOB.CLOSE(public_key_handle);
 
    INSERT INTO Partners(PartherID, PublicKey) VALUES( 1, public_key);END;
/

Now having the key in our table we can use it like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

DECLARE
  public_key  BLOB;    
  encrypted_message CLOB;  
  MESSAGE CLOB;  
BEGIN
  MESSAGE := 'Hello World';
 
  SELECT PublicKey INTO public_key FROM Partners WHERE PartnerID = 1; 
  encrypted_message := ORA_PGP.ENCRYPT_CLOB(
    MESSAGE => MESSAGE,
    PUBLIC_KEY => public_key);      
END;
/

Using inline keys

The last option that we have is to hard code an ASCII armored OpenPGP key inside our PL/SQL code. This can be a practical approach for storing our private key for example:

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
33
34
35
36
37
38

DECLARE
  PUBLIC_KEY CLOB;  
  MESSAGE CLOB;  
  encrypted_message CLOB;
BEGIN
  MESSAGE := 'Hello World';
 
  PUBLIC_KEY := '-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v1.4.9 (MingW32)
 
mQGhBEpbMSIRBADe1ZmlpSRWbLH6HI7Y01nvByU7hn4/RImc8c8Afvh3DlxVSLAs
AAYT3oX9z4AK/KOiHwnGqec/qTBl6+tme9+vivXmFcrbO6ZVbsoKt/sZhzInhspf
DXdu/yJbhR8GNLusysOJRxZY7cQyD2FR/taH6ukIqExuA0VaNLXubP5XXwCgp8SK
2hmF9XjrN2t06p8Rrsj6AQ0D/0NDIGWgubSgb7Zs/OtmgQ5UYSNfejCZXvEEK/Iz
lRCA8vBrZvcIkusFTldx2osRCepUddqWBumVOjpkANpnAePv5AvUDIw7ZGJQINIB
O4GhwMeyeceJp+2cX6RJHdtTDaFVQzlnh6n1xD51q36lww89aeJhbyaHbV3lQ4d4
yQJcA/dtODFz7PO+S/cdwhLk7LDetPBkJP9nSD+q5hv/IwT9zilPWfTuFOrawBTMKrcfYc/X9nGn9gVVs0n5aSJRV4WyDI9jxlhROiq05zPsrDWhnFcLzTBWgz4HAjg0
QdREZpR6zVGDiHvfRS8AXrUGEcC4M9Mc71K5c34BxjnpvJfVtDRSaWNoYXJkIENv
bGxpbnMgKFJpY2hhcmQgQy4pIDxyaWNoYXJkQHN1cGVyc2FmZS5vcmc+iGAEExEC
ACAFAkpbMSICGwMGCwkIBwMCBBUCCAMEFgIDAQIeAQIXgAAKCRB8K5podLsyhpYs
AKCOtki/6BXcV7Uwv8gOa+nH1bQ11ACfUFje4H1VOgvt+fdfOF0Q4+ODAP65AQ0E
SlsxIhAEAIOPVZmiYgd1eq856rDhm9aIbHs6swK9xo2qWssByBvpFMQZUcHIsBDY
MZFdU4BKyw/P0E9YYizL1QqUdb2ebeMz94T/RS5U3jpLeRHuYivIcwcY/3os7FT2
d3Euoq1I0UznHgfMQgQPfIhnt6qqCmD27+nridDZhbOp3IDlIilPAAMFA/kBFoje
egv0tseugDpiG0N00IyPXpSOs2fimAm2iiCeC9HcRYXf1rW2/dx8QWBM6AAM5dSn
4nzXDsZBdP69iLsBVMvM+SHFB9F3cOf2VHpw/8RHhWLS5dWrrRYb32mina3YLdOh
0QzYJ5VaHS9nnU1G4kKCuE7OUAi6RjpwPrSZpIhJBBgRAgAJBQJKWzEiAhsMAAoJ
EHwrmmh0uzKG7GIAoJhwmQMUJzPnz0XAvyPLfFPuLEp3AJ97nCAfYVhPMlfZHPyA
L8m9I8h/eg==
=gjPp
-----END PGP PUBLIC KEY BLOCK-----';
 
  encrypted_message := ORA_PGP.ENCRYPT(
    MESSAGE => MESSAGE,
    PUBLIC_KEY => PUBLIC_KEY);
END;
/

Summary

This chapter described the possible ways of using .pgp keys from DidiSoft ORA_PGP. Observed were the three possible solutions: external keys from the Oracle DB server file system, keys loaded and stored in BLOB fields and inline keys directly in the PL/SQL code.