S/MIME signed emails with UTL_SMTP

Sending Email from inside the Oracle database and PL/SQL is performed usually with the UTL_SMTP package.

A flaw of UTL_SMTP is that it is a very low-level tool and PL/SQL developers must construct the email body from scratch. Fortunately, there are many tutorials on the Internet that demonstrates how to send emails of emails with UTL_SMTP.

But when the Email must be signed according to the S/MIME standard, this may be a very complicated task. In this article, we will use the help of the ORA_SMIME package to create S/MIME signed emails. 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.

A simple email with UTL_SMTP

Sending a simple Hello world email with UTL_SMTP requires some low-level email structure knowledge. The example below illustrates this:

CREATE OR REPLACE PROCEDURE SEND_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 
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);
 
  UTL_SMTP.write_data(c, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.write_data(c, msg_text);
  UTL_SMTP.write_data(c, UTL_TCP.crlf || UTL_TCP.crlf);
 
  UTL_SMTP.close_data(c);
  UTL_SMTP.quit(c);
END;
/

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 detached S/MIME signed email ORA_SMIME.SIGN_EMAIL_EMBEDDED just has to be replaced with ORA_SMIME.SIGN_EMAIL_DETACHED.

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(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);    
END;
/

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);    
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.

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