CMS encrypting with PL/SQL

Cryptography Message Syntax (CMS) is a cryptography protocol that utilizes X.509 certificates and private keys and is standardized in RFC 5652

This tutorial chapter demonstrates how to encrypt data in CMS format from withing an Oracle® Database using PL/SQL and DidiSoft ORA_RSA package.

In order to encrypt data in CMS format, we need the X.509 certificate of the message recipient who will be able to decrypt it with her private key.

The encrypted data can be in PEM (text) format or DER (binary) format. By default, CMS_ENCRYPT_BLOB and CMS_ENCRYPT_RAW produce DER output and CMS_ENCRYPT_CLOB and CMS_ENCRYPT_VARCHAR2 produce PEM format output.

Table of contents

Encrypting VARCHAR2

CMS encryption over VARCHAR2 field is performed with the methods ORA_RSA.CMS_ENCRYPT_VARCHAR2:

DECLARE
  public_key_file_handle  BFILE;
  public_key  BLOB;
 
  encrypted_data VARCHAR2(2000);
BEGIN
    -- initialize the public key BLOB storage
    DBMS_LOB.createtemporary(public_key, TRUE);
 
    -- load a key from the fle system
    public_key_file_handle := BFILENAME('KEYS_DIR', 'asp@didisoftcom.crt'); -- Note: directory name must be Upper case
 
    -- load the key into a BLOB
    DBMS_LOB.OPEN(public_key_file_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LoadFromFile( DEST_LOB => public_key,
                         SRC_LOB  => public_key_file_handle,
                         AMOUNT   => DBMS_LOB.GETLENGTH(public_key_file_handle) );
    DBMS_LOB.CLOSE(public_key_file_handle);
 
    -- RSA Encrypt
    encrypted_data := ORA_RSA.CMS_ENCRYPT_VARCHAR2(message => 'Hello World',
                                      public_key => public_key);
 
  DBMS_OUTPUT.put_line('encrypted_data='||encrypted_data);
END;

Encrypting CLOB

When encrypting a CLOB column the output is again of CLOB data type. The encrypted data is in PEM (text) format.

DECLARE
  public_key_file_handle  BFILE;
  public_key  BLOB;
 
  mydata CLOB;
  encrypted_data CLOB;
BEGIN
    -- initialize the public key BLOB storage
    DBMS_LOB.createtemporary(public_key, TRUE);
 
    -- load a key from the flesystem
    -- the direcory name must be created upfront with CREATE DIRECTORY
    -- for example: CREATE DIRECTORY KEYS_DIR AS '/demo/schema/my_keys_folder';
    --public_key_file_handle := BFILENAME('KEYS_DIR', 'DidiSoftEood.crt'); -- Note: directory name must be Upper case 
    public_key_file_handle := BFILENAME('KEYS_DIR', 'sales@didisoftcom.crt'); -- Note: directory name must be Upper case
 
    -- load the key into a BLOB
    DBMS_LOB.OPEN(public_key_file_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LoadFromFile( DEST_LOB => public_key,
                         SRC_LOB  => public_key_file_handle,
                         AMOUNT   => DBMS_LOB.GETLENGTH(public_key_file_handle) );
    DBMS_LOB.CLOSE(public_key_file_handle);
 
 
    -- load input data to be encrypted
    mydata := TO_CLOB('this is the data to be encrypted');
 
    -- RSA Encrypt
    encrypted_data := ORA_RSA.CMS_ENCRYPT_CLOB(message => mydata,
                                      public_key => public_key);
 
    DBMS_OUTPUT.PUT_LINE(encrypted_data);
END;

Encrypting BLOB

When encrypting a BLOB column the output will be in DER (binary) format.

DECLARE
  public_key_file_handle  BFILE;
  public_key  BLOB;
 
  mydata BLOB;
  encrypted_data BLOB;
BEGIN
    -- initialize the public key BLOB storage
    DBMS_LOB.createtemporary(public_key, TRUE);
 
    -- load a key from the flesystem
    -- the direcory name must be created upfront with CREATE DIRECTORY
    -- for example: CREATE DIRECTORY KEYS_DIR AS '/demo/schema/my_keys_folder';
    --public_key_file_handle := BFILENAME('KEYS_DIR', 'DidiSoftEood.crt'); -- Note: directory name must be Upper case 
    public_key_file_handle := BFILENAME('KEYS_DIR', 'sales@didisoftcom.crt'); -- Note: directory name must be Upper case
 
    -- load the key into a BLOB
    DBMS_LOB.OPEN(public_key_file_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LoadFromFile( DEST_LOB => public_key,
                         SRC_LOB  => public_key_file_handle,
                         AMOUNT   => DBMS_LOB.GETLENGTH(public_key_file_handle) );
    DBMS_LOB.CLOSE(public_key_file_handle);
 
 
    -- load input data to be encrypted
    DBMS_LOB.createtemporary(mydata, TRUE);
 
    -- CMS Encrypt
    encrypted_data := ORA_RSA.CMS_ENCRYPT_BLOB(message => mydata,
                                      public_key => public_key);                                      
END;

Summary

This chapter illustrates how to create CMS (Cryptography Message Syntax) encrypted data with PL/SQL inside the Oracle® database. You may also like to check how to decrypt such data or how to create digital signatures in CMS format.