CMS signing with PL/SQL

In order to create CMS (Cryptography message syntax) signed content, we need a private key, usually stored inside a PKCS#12 (.pfx) file container. A hash algorithm must also be specified for the digital signature.

We have to admit that the ORA_RSA.CMS_SIGN methods will include any X.509 certificate contained in the private key file (PKCS#12/pfx file) into the resulting CMS signature. Later on, the signature can be verified without the need to specify a X.509 certificate.

List of examples

CMS signing a CLOB field

This example creates a CMS signature for a CLOB field:

DECLARE
  private_key_file_handle  BFILE;
  private_key  BLOB;
 
  message CLOB;
  signature CLOB;
BEGIN
    -- 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);
 
    message := 'Hello World'; -- initialize the data to be signed
    --
    -- CMS SIGN
    --
    signature := ORA_RSA.CMS_SIGN_CLOB(message => message,
                              private_key => private_key,
                              private_key_password => '4Yvv8RH33D81',
                              hash => ORA_RSA.HASH_SHA512);
 
    -- we can print the signature with:  
    DBMS_OUTPUT.put_line(DBMS_LOB.SUBSTR(signature, 100));
END;

CMS signing a BLOB field

Siging a BLOB field is similar to the example above, but here the ORA_RSA.CMS_SIGN_BLOB method is used:

DECLARE
  private_key_file_handle  BFILE;
  private_key  BLOB;
 
  message BLOB;
  signature BLOB;
BEGIN
    -- 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);
 
    -- load data to be signed
    DBMS_LOB.createtemporary(message, TRUE);
 
    -- CMS SIGN
    signature := ORA_RSA.CMS_SIGN_BLOB(message => message,
                              private_key => private_key,
                              private_key_password => '4Yvv8RH33D81',
                              hash => ORA_RSA.HASH_SHA512);
END;