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.