CMS Decrypting with PL/SQL

In order to decrypt CMS (Cryptography Message Syntax) encrypted data, we need the private key corresponding to the X.509 certificate used for the encryption operation.

In this chapter, we will decrypt CMS encrypted data using Oracle PL/SQL.

Table of contents

Decrypt CMS data from VARCHAR2 column

In the example code block below the PKCS#12 (.pfx) file containing the private key is loaded from the file system into a BLOB field. We can easily modify this code to store and load the private key from a BLOB column from withing our database:

DECLARE
  private_key_file_handle  BFILE;
  private_key  BLOB;
  private_key_password VARCHAR2(2000);
 
  encrypted_data VARCHAR2(2000);
  decrypted_data VARCHAR2(2000);
BEGIN
    -- RSA Encrypt
    encrypted_data := ... -- load the CMS encrypted data
 
    -- load a private key from the file system
    private_key_file_handle := BFILENAME('KEYS_DIR', 'PKCS12_Credential_sales@didisoft.com.pfx'); -- 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
	private_key_password := '4Yvv8RH33D81';
    decrypted_data := ORA_RSA.CMS_DECRYPT_VARCHAR2(encrypted_data, private_key, private_key_password);
 
    -- print the decrypted data  
    DBMS_OUTPUT.put_line('decrypted_data='||decrypted_data);     
END;

Decrypting from CLOB

ORA_RSA.CMS_DECRYPT_CLOB is used to decrypt previously encrypted CMS data, which is assumed to be a textual content.

DECLARE
  private_key_file_handle  BFILE;
  private_key  BLOB;
 
  mydata CLOB;
  encrypted_data CLOB;
  decrypted_data CLOB;
BEGIN
    -- load encrypted data 
    encrypted_data := ...
 
    -- load a private key from the file system
    private_key_file_handle := BFILENAME('KEYS_DIR', 'PKCS12_Credential_sales@didisoft.com.pfx'); 
 
    -- 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.CMS_DECRYPT_CLOB(encrypted_data, private_key, '4Yvv8RH33D81');  
 
    DBMS_OUTPUT.PUT_LINE(decrypted_data);
END;

Decrypting from BLOB

The method ORA_RSA.CMS_DECRYPT_BLOB is more suitable for decrypting CMS encrypted data in DER (binary) format. Afterwards we may need to manually convert the decrypted result to textual format if must be displayed or processed as such.

DECLARE
  private_key_file_handle  BFILE;
  private_key  BLOB;
 
  mydata BLOB;
  encrypted_data BLOB;
  decrypted_data BLOB;
BEGIN
    -- load encrypted data
    encrypted_data := ...
 
    -- load a private key from the filesystem
    private_key_file_handle := BFILENAME('KEYS_DIR', 'PKCS12_Credential_sales@didisoft.com.pfx');
 
    -- 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);
 
    -- CMS Decrypt
    decrypted_data := ORA_RSA.CMS_DECRYPT_BLOB(encrypted_data, private_key, '4Yvv8RH33D81');  
END;

Summary

This chapter discussed how to decrypt CMS encrypted data inside the Oracle® database using the PL/SQL programming language.

CMS encrypted data in PEM format is preferable to be decrypted with ORA_RSA.CMS_DECRYPT_CLOB or ORA_RSA.CMS_DECRYPT_VARCHAR2.

CMS encrypted data in DER format is preferable to be decrypted with ORA_RSA.CMS_DECRYPT_BLOB or ORA_RSA.CMS_DECRYPT_RAW.