Encrypting with password PL/SQL

OpenPGP data can also be encrypted with a password. This article illustrates how to perform PGP password-based encryption inside the Oracle(c) DB with the help of DidiSoft OraPGP PL/SQL package.

The data is encrypted with the symmetric encryption algorithm AES-256 and the provided password is used as a symmetric key.
Note: this encryption format conforms to the OpenPGP specification (RFC4880)

Examples
1. Encrypting CLOB
2. Encrypting BLOB
3. Exception handling

1. Encrypting CLOB

The method ORA_PGP.ENCRYPT_CLOB_PASSWORD encrypts a CLOB field with a password. The encrypted data will be assigned internally an artificial “message.txt” label. You can change it with an overloaded method that accepts an additional data_file_label parameter (like in the BLOB example below) :

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

DECLARE
  MESSAGE CLOB;
  ENCRYPTION_PASSWORD VARCHAR2(2000);
  v_Return CLOB;
BEGIN
  MESSAGE := 'Hello World';
  ENCRYPTION_PASSWORD := 'my super password';
 
  v_Return := ORA_PGP.ENCRYPT_CLOB_PASSWORD(
    MESSAGE => MESSAGE,
    PASSWORD => ENCRYPTION_PASSWORD
  );
 
  -- print the encrypted data
  DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;
/

2. Encrypting BLOB

The method ORA_PGP.ENCRYPT_BLOB_PASSWORD is used for encrypting a BLOB field with a password.

In the example below we use a sample source table with such definition:

table documents
+------------------------+
| document_id varchar(10)|
| file_data blob         |
+------------------------+

The example call below will produce binary OpenPGP format:

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

DECLARE
  MESSAGE BLOB;
  PASS VARCHAR2(200);
  v_Return BLOB;
  ascii_armor_output BOOLEAN := FALSE;
BEGIN
  MESSAGE := NULL;
  PASS := 'my password';
 
  SELECT file_data INTO MESSAGE
  FROM documents
  WHERE document_id = 'A103';
 
  v_Return := ORA_PGP.ENCRYPT_BLOB_PASSWORD(
    message => MESSAGE,
    data_file_label => 'Report_A103.doc', -- file name assigned to the encrypted data (optinal)
    password => PASS,
    ascii_armor => ascii_armor_output
  );
 
END;
/

Exception handling

Please check the dedicated chapter for Exception handling in OraPGP.

Summary

This chapter illustrated how to invoke the ORA_PGP encryption PL/SQL functions. You can find complete examples in the \Examples subfolder inside the product ZIP archive.

You may also like to check the chapter for encrypting via password.

List of methods used: