S/MIME signed emails with UTL_SMTP

In this article, we will use the ORA_SMIME PL/SQL package to create and send S/MIME signed emails.

A prerequisite before using UTL_SMTP is assigning Access Control List (ACL).

You can get familiar with ACLs and using UTL_SMTP before starting this tutorial.

ORA_SMIME provides low-level methods like UTL_SMTP.WRITE_DATA and high-level like UTL_MAIL.SEND.

Two types of S/MIME signed-only format

The S/MIME signed-only data format can be created in two different ways:

  • Embedded, where the data, signature and validation key are all in one piece of data. This is the default one provided by ORA_SMIME.
  • Detached, where the original data is divided by a MIME boundary from the signature and validation key.

Embedded signatures are created with ORA_SMIME.SIGN_EMAIL_EMBEDDED or ORA_SMIME.SIGN_EMAIL
Detached signatures are created with ORA_SMIME.SIGN_EMAIL_DETACHED

We have chosen the Embedded flavor to be the default one as some SMTP agents were adding additional information to the Email (like Privacy Disclaimer) which brakes the signature validation at the receiving side. Detached signatures can be created by using ORA_SMIME.SIGN_EMAIL_DETACHED instead of ORA_SMIME.SIGN_EMAIL.

If you need the high-level functions to send also detached signed emails, you can replace the same calls in ORA_SMIME package body and recompile it.

S/MIME signed Email with UTL_SMTP (low level)

Now we are going to modify the above example to produce the same email message but signed with an X.509 certificate located in a private key file PKCS#12 storage (usually with filename extension .pkcs12, .p12, .pfx).

The key difference from the plain email sample code above is that instead of writing the original email structure directly into UTL_SMTP with UTL_SMTP.WRITE_DATA we use a local variable (l_message) and write the original email structure into it with the help of ORA_SMIME.WRITE_DATA (or ORA_SMIME.WRITE_RAW_DATA).

The signature algorithm used is taken from the private key. The newer RSASSA-PSS signature schema is also recognized. If we need to use a different algorithm from the one specified in the private key itself, we can use an overloaded version of ORA_SMIME.SIGN_EMAIL_DETACHED or ORA_SMIME.SIGN_EMAIL_EMBEDDED that accepts a custom Hash algorithm as the last parameter. The hash algorithm values are the same as in ORA_RSA.

The example below will produce a signed email with an embedded body. In order to create a embedded S/MIME signed email ORA_SMIME.SIGN_EMAIL_DETACHED just has to be replaced with ORA_SMIME.SIGN_EMAIL_EMBEDDED.

CREATE OR REPLACE PROCEDURE SEND_SIGNED_MAIL (msg_to VARCHAR2, 
                                       msg_subject VARCHAR2,
                                       msg_text VARCHAR2 )
IS
  c UTL_SMTP.connection;
  rc INTEGER; 
  msg_from VARCHAR2(50) := 'Oracle12'; 
  mailhost VARCHAR2(30) := '127.0.0.1'; -- local database host 
 
  -- email message body    
  l_message CLOB;  
  -- helper variable for printing CLOB
  lv_step   PLS_INTEGER  := 12000;
 
  -- PFX/p12 private key and chain storage
  pfx_blob BLOB;  
  pfx_password VARCHAR2(200); 
  file_handle BFILE;
 
BEGIN
  c := UTL_SMTP.open_connection(mailhost, 25); -- SMTP on port 25 
  UTL_SMTP.helo(c, mailhost);
  UTL_SMTP.mail(c, msg_from);
  UTL_SMTP.rcpt(c, msg_to);
 
  UTL_SMTP.open_data(c)
  UTL_SMTP.write_data(c,'From: Oracle Database' || UTL_TCP.crlf);
  UTL_SMTP.write_data(c,'To: ' || msg_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(c,'Subject: ' || msg_subject);
 
  ORA_SMIME.WRITE_DATA(l_message, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
  ORA_SMIME.WRITE_DATA(l_message, msg_text);
  ORA_SMIME.WRITE_DATA(l_message, UTL_TCP.crlf || UTL_TCP.crlf);
 
  -- load .PFX (key storage) into a BLOB
  file_handle := BFILENAME('KEYS_DIR', 'PKCS12_Credential_support@didisoft.com.pfx');    
  DBMS_LOB.createtemporary(pfx_blob, TRUE);
  DBMS_LOB.OPEN(file_handle, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.LoadFromFile( pfx_blob, file_handle, DBMS_LOB.GETLENGTH(file_handle) );
  DBMS_LOB.CLOSE(file_handle);
  pfx_password := 'Df4NHwcahsEF';
 
  l_message := ORA_SMIME.SIGN_EMAIL_DETACHED(l_message, pfx_blob, pfx_password); 
  -- send Email
  FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(l_message) - 1 )/lv_step) LOOP
      UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.SUBSTR(l_message, lv_step, i * lv_step + 1));
  END LOOP;
 
  UTL_SMTP.close_data(c);
  UTL_SMTP.quit(c);
END;
/

S/MIME signed Email with ORA_SMIME (high level)

The ORA_SMIME package also provides high-level mail sending routines. Instead of writing the above lowe level code we can use one of the ORA_SMIME.SEND_SIGNED_EMAIL methods. They are similar to UTL_MAIL.SEND but produce S/MIME signed-only data.

Let’s see how to do it :

DECLARE 
  -- file read handle
  file_handle BFILE;
  -- PFX/p12 private key and chain storage
  pfx_blob BLOB;  
  pfx_password VARCHAR2(200); 
BEGIN
    -- load .PFX (key storage) into a BLOB
    file_handle := BFILENAME('KEYS_DIR', 'EE.pfx');
    pfx_password := '123456';
    DBMS_LOB.createtemporary(pfx_blob, TRUE);
    DBMS_LOB.OPEN(file_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LoadFromFile( pfx_blob, file_handle, DBMS_LOB.GETLENGTH(file_handle) );
    DBMS_LOB.CLOSE(file_handle);
 
    ORA_SMIME.SEND_SIGNED_EMAIL(smtp_host => 'localhost', 
		smtp_port => 25,
		sender => 'test@didisoft.com',
		recipient => 'smime@didisoft.com',
		subject => 'SMIME signed',
		message => 'Hi, this is a test SMIME signed email from DidiSoft!',
		private_key => pfx_blob,
    	        private_key_password => pfx_password,
                embedded => FALSE
 );    
END;
/

Specifying HASH algorithm

Signatures are created with combination of an asymmetric cipher and hash digest algorithm. All the signing methods of ORA_SMIME have overloaded versions where the hash digest algorithm can be specified explicitly (otherwise it is assumed from the provided private key).
ORA_SMIME provides the following constants that can be used to specify the hash algorithm:

ORA_SMIME.SHA1 -- constant for RSA signature with SHA1 hash
ORA_SMIME.SHA224 -- constant for RSA signature with SHA224 hash
ORA_SMIME.SHA256 - constant for RSA signature with SHA256 hash
ORA_SMIME.SHA384 - constant for RSA signature with SHA384 hash
ORA_SMIME.SHA512 - constant for RSA signature with SHA512 hash
ORA_SMIME.RSAPSS_SHA1 - constant for RSASSA-PSA signature with SHA1 hash
ORA_SMIME.RSAPSS_SHA224 - constant for RSASSA-PSA signature with SHA224 hash
ORA_SMIME.RSAPSS_SHA256 - constant for RSASSA-PSA signature with SHA256 hash
ORA_SMIME.RSAPSS_SHA384 - constant for RSASSA-PSA signature with SHA384 hash
ORA_SMIME.RSAPSS_SHA512 - constant for RSASSA-PSA signature with SHA512 hash

Below is an example of how these constants can be used:

l_message := ORA_SMIME.SIGN_EMAIL_DETACHED(l_message, pfx_blob, pfx_password, ORA_SMIME.SHA256); -- RSA with SHA256
l_message := ORA_SMIME.SIGN_EMAIL_DETACHED(l_message, pfx_blob, pfx_password, ORA_SMIME.SHA1); -- RSA with SHA1

S/MIME signed Email with attachment

The easiest way to send S/MIME signed email with attachment (which must be of type BLOB or CLOB) is by using ORA_SMIME.SEND_SIGNED_EMAIL.

The example code below is similar to the previous one but has additional parameters for the attachment: –

  • attachment data as BLOB,
  • attachment MIME type (check here for an incomplete list of known MIME types)
  • attachment file name label (the name of the attachment file as you would like to appear to the recipient)

Sending S/MIME signed email with an attachment with PL/SQL:

DECLARE 
  -- file read handle
  file_handle BFILE;
  -- input PDF BLOB
  pdf_blob BLOB;  
  -- PFX/p12 private key and chain storage
  pfx_blob BLOB;  
  pfx_password VARCHAR2(200); 
BEGIN
    -- load PDF into a BLOB
    file_handle := BFILENAME('SSH_KEYS_DIR', 'MyReport.pdf'); -- directory name must be Upper case
    DBMS_LOB.createtemporary(pdf_blob, TRUE);
    DBMS_LOB.OPEN(file_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LoadFromFile( pdf_blob, file_handle, DBMS_LOB.GETLENGTH(file_handle) );
    DBMS_LOB.CLOSE(file_handle);
 
    -- load .PFX (key storage) into a BLOB
    file_handle := BFILENAME('KEYS_DIR', 'EE.pfx');
    pfx_password := '123456';
    DBMS_LOB.createtemporary(pfx_blob, TRUE);
    DBMS_LOB.OPEN(file_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LoadFromFile( pfx_blob, file_handle, DBMS_LOB.GETLENGTH(file_handle) );
    DBMS_LOB.CLOSE(file_handle);
 
    ORA_SMIME.SEND_SIGNED_EMAIL(smtp_host => 'localhost', 
		smtp_port => 25,sender => 'test@didisoft.com',
		recipient => 'smime@didisoft.com',
		subject => 'SMIME signed',
		message => 'Hi, this is a test SMIME signed email from DidiSoft!',
		attachment => pdf_blob, 
		att_mime_type => 'application/pdf',
		att_filename => 'Report.pdf',
		private_key => pfx_blob,
    	        private_key_password => pfx_password,
                embedded => FALSE,
                hash => ORA_SMIME.HASH_SHA1
);    
END;
/

Summary

This article illustrated how to create S/MIME signed-data Email in PL/SQL by using ORA_SMIME. ORA_SMIME is an additional PL/SQL package that ships with DidiSoft OraRSA.

You may also consider reading the chapter that demonstrates how to send S/MIME encrypted enveloped emails from PL/SQL.

Methods used in this tutorial:

ORA_SMIME.WRITE_DATA Writes VARCHAR2 data to a local variable of type CLOB
ORA_SMIME.WRITE_RAW_DATA Writes RAW data to a local variable of type CLOB
ORA_SMIME.SIGN_EMAIL Converts the contents of a CLOB variable into S/MIME signed-only format
ORA_SMIME.SIGN_EMAIL_DETACHED Converts the contents of a CLOB variable into S/MIME signed-only detached format
ORA_SMIME.SEND_SIGNED_EMAIL Sends email message but converts it into S/MIME signed-only format beforehand