Decrypting

The ORA_RSA package offers RSA decryption functions reversing its encrypting methods.

In order to decrypt RSA encrypted data, we need the corresponding RSA private key. In this chapter we are going to illustrate decrypting with RSA private keys loaded from files, hard coded inside the PL/SQL code or by providing the RSA algorithm private values (modulus and exponent).

For private keys exported in DER or PEM format without password the ORA_RSA.DECRYPT method expects only two parameters: encrypted data and key bytes. For keys in .PFX/.PKCS12 format an overloaded version is available with a third parameter for the private key password.

Table of contents

1. Decrypt with key from file
2. Decrypt with inline key
3. Decrypt with key modulus and exponent

1. Decrypt with key from file

In order to load a private key from the server file system, we use a BFILE variable to point to the file location.

In the example below we load the encrypted data from a table FBI_Agents and try to decrypt the real name of the agent:

DECLARE
  private_key_file_handle  BFILE;
  private_key  BLOB;
 
  encrypted_data raw(32000);
  decrypted_data raw(32000);
BEGIN
    -- FBI_Agents.RealName is of type VARCHAR2(500)
    SELECT RealName INTO encrypted_data FROM FBI_Agents WHERE Agent_Id = '007';
 
    -- load a private key from the filesystem
    -- the direcory name must be created upfront with CREATE DIRECTORY
    -- for example: CREATE DIRECTORY KEYS_DIR AS '/demo/schema/my_keys_folder';
    private_key_file_handle := BFILENAME('KEYS_DIR', 'didisoft.p12'); -- Note: directory name must be Upper case 
 
    -- initialize the private key BLOB storage
    DBMS_LOB.createtemporary(private_key, TRUE);
 
    -- load the private key into a BLOB
    DBMS_LOB.OPEN(private_key_file_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LoadFromFile( DEST_LOB => private_key,
                         SRC_LOB  => private_key_file_handle,
                         AMOUNT   => DBMS_LOB.GETLENGTH(private_key_file_handle) );
    DBMS_LOB.CLOSE(private_key_file_handle);
 
    -- RSA Decrypt
    decrypted_data := ORA_RSA.DECRYPT(encrypted_data, DBMS_LOB.substr(private_key), 'private key password');
 
    -- print the decrypted data  
	DBMS_OUTPUT.put_line('Agent real name is: ');
    DBMS_OUTPUT.put_line(UTL_I18N.RAW_TO_CHAR(decrypted_data, 'AL32UTF8'));     
END;

2. Decrypt with inline key

This sample is similar to the above one, except that the key is stored inline inside the PL/SQL code in PEM (base 64 escaped text) format without password protection:

DECLARE
  private_key CLOB := '-----BEGIN PRIVATE KEY-----
MIICdwIBADANBgkqhkiG9w0BAQEFAASCAmEwggJdAgEAAoGBAMJOVHMWQpGWTiR7
F5z3WQIIvJYUDubZAPUxzj2NgriomhnowjgiK40Q2UPNxgBq3wlMhMpAp01+Or6R
2mkOctOZ55Qjgm2VzTovW4MuOLWGB43fXm3qDe05/SIdV3K3h2KVwcNqecJTR25T
RNeGHo4r8cce3BnuVzEKjk2DNQOXAgMBAAECgYEAuqIMQaL+++IYWrgU/UMkLmz/
31OS4K9NWTamt77F8eKYagyFCO/hTxUA6zyqU9pTMxZZcf9Z83gsqsFjvYcQSHy6
mRXFuORzh0r/wXKJtyFF0B26KC7WipqtPAuzn7SNGNeMh8g3H1qH8neEjir15Uai
6lR/sDIOZlO9sUJoZBECQQDkLXnXl/YXGoQDdupUQMzrF+ZK/od2U9YjdSOi+k/j
x23usurtzRhYGW/73vJd9Sw6Qc6ijPr+ItSpnl+qaxzvAkEA2f+OVzn1HwmYbc2a
Booo32aT96TJrwN8V4gC7m5hseHoXDDoXmwLZwNm7+w0vu3lk1p9tSqs8oc/nR0E
fHhT2QJAOQslasCSxTPbzQHtkyKgGCXhbN40/1/2KOcgAZ6SWl+BHCuej9S2QVAa
rt0Num+Qnv/UqM6V8PLEN6NgRzqAAQJBALeQYrp+WjKNcOYc97LECdC73qLsBswx
QjWumNFO70LLOE7Q/AnuLtfKXJZwrqWLSwJ+c1XnHoSGcIGK2qk45VkCQA6b1qCv
jGFksgcQ8vff5lwOWfJ2ZxA8Zpgeq5w7EaDTWS/WhtVUYg3bBsadgXb3LxpZScxq
U4Ad7pAZrI6H6Tc=
-----END PRIVATE KEY-----';
 
  encrypted_data raw(32000);
  decrypted_data raw(32000);
BEGIN
  -- FBI_Agents.RealName is of type VARCHAR2(500)  
  SELECT RealName INTO encrypted_data FROM FBI_Agents WHERE Agent_Id = '007';
 
  -- RSA Decrypt
  decrypted_data := ORA_RSA.DECRYPT(encrypted_data, UTL_RAW.CAST_TO_RAW(private_key));
 
  -- we need to convert the RAW datatype into VARCHAR2
  DBMS_OUTPUT.put_line(UTL_I18N.RAW_TO_CHAR(decrypted_data, 'AL32UTF8'));              
END;

3. Decrypt with key modulus and exponent

A separate function is offered for decrypting by just providing the private RSA modulus and exponent parameters:

DECLARE
  private_key_modulus CLOB := 'c24e5473164291964e247b179cf7590208bc96140ee6d900f531ce3d8d82b8a89a19e8c238222b8d10d943cdc6006adf094c84ca40a74d7e3abe91da690e72d399e79423826d95cd3a2f5b832e38b586078ddf5e6dea0ded39fd221d5772b7876295c1c36a79c253476e5344d7861e8e2bf1c71edc19ee57310a8e4d83350397';
  private_key_exponent CLOB := 'baa20c41a2fefbe2185ab814fd43242e6cffdf5392e0af4d5936a6b7bec5f1e2986a0c8508efe14f1500eb3caa53da5333165971ff59f3782caac163bd8710487cba9915c5b8e473874affc17289b72145d01dba282ed68a9aad3c0bb39fb48d18d78c87c8371f5a87f277848e2af5e546a2ea547fb0320e6653bdb142686411';
 
  encrypted_data raw(32000);
  decrypted_data raw(32000);
BEGIN
    -- FBI_Agents.RealName is of type VARCHAR2(500)
    SELECT RealName INTO encrypted_data FROM FBI_Agents WHERE Agent_Id = '007';
 
  -- RSA Decrypt
  decrypted_data := ORA_RSA.ENCRYPT_WITH_MODULUS(encrypted_data, 
                                                 private_key_modulus => DBMS_LOB.substr(private_key_modulus), 
                                                 private_key_exponent => DBMS_LOB.substr(private_key_exponent));
 
 
 
  DBMS_OUTPUT.put_line(UTL_I18N.RAW_TO_CHAR(decrypted_data, 'AL32UTF8'));              
END;

Summary

This chapter introduced RSA decryption using DidiSoft ORA_RSA PL/SQL package. The data is assumed to be encrypted by the function ORA_RSA.ENCRYPT.