S/MIME enveloped-data (encrypted) emails

S/MIME encrypted emails (known as enveloped-data) can be created very easily with the ORA_SMIME PL/SQL package.

Two approaches are used here just like for signed S/MIME emails.

The first one is a low-level approach, where the email body except the email routing header lines is pushed into a CLOB variable with ORA_SMIME.WRITE_DATA (or WRITE_RAW_DATA) and at the end the same variable containing the email message is encrypted with ORA_SMIME.ENCRYPT_EMAIL and passed to UTL_SMTP.

The second high-level approach is to specify the email body and subject and send them ORA_SMIME.SEND_ENCRYPTED_EMAIL very similar to UTL_MAIL.

The session key algorithm can be specified as the last parameter of the methods with available values (AES256 is now default):

ORA_SMIME.CIPHER_AES128 -- AES128
ORA_SMIME.CIPHER_AES192 -- AES192
ORA_SMIME.CIPHER_AES256 -- AES256
ORA_SMIME.CIPHER_CAMELLIA128 -- CAMELLIA128
ORA_SMIME.CIPHER_CAMELLIA192 -- CAMELLIA192
ORA_SMIME.CIPHER_CAMELLIA256 -- CAMELLIA256

S/MIME signed and encrypted emails are created by first performing the signing and then the encryption.

Example PL/SQL for sending S/MIME encrypted email

The procedures below illustrates how to send an S/MIME encrypted email. The emails are encrypted with an encryption key which is an X.509 certificate (.cer/.crt) file belonging to the message recipient. The recipient must possess the corresponding private key (usually in .pfx/.p12 file) installed in her mail client application in order to be able to decrypt and view the contents of the encrypted email.

Example for:

S/MIME encrypted Email with UTL_SMTP (low level)

The sample code below creates an email structure into a local variable (l_message) with ORA_SMIME.WRITE_DATA and afterward encrypts it with ORA_SMIME.ENCRYPT_EMAIL. 

CREATE OR REPLACE PROCEDURE SEND_ENCRYPTED_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;
 
  -- X.509 certificate
  certificate_blob BLOB;  
  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 X.509 into a BLOB
  file_handle := BFILENAME('KEYS_DIR', 'EE.crt');    
  DBMS_LOB.createtemporary(certificate_blob, TRUE);
  DBMS_LOB.OPEN(file_handle, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.LoadFromFile( certificate_blob, file_handle, DBMS_LOB.GETLENGTH(file_handle) );
  DBMS_LOB.CLOSE(file_handle);
 
  l_message := ORA_SMIME.ENCRYPT_EMAIL(l_message, certificate_blob, ORA_SMIME.CIPHER_AES256);
 
  -- 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;
/

RSA-OAEP encrypted emails

A separate method is available for RSA-OAEP encryption. In that case the MGF1 hash function must also be specified:

...
  l_message CLOB;  
BEGIN
 ...
  l_message := ORA_SMIME.ENCRYPT_EMAIL_RSA_OAEP(l_message, certificate_blob, ORA_SMIME.HASH_SHA256);
  l_message := ORA_SMIME.ENCRYPT_EMAIL_RSA_OAEP(l_message, certificate_blob, ORA_SMIME.HASH_SHA256, ORA_SMIME.CIPHER_AES256);
...

S/MIME encrypted 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_ENCRYPTED_EMAIL methods. They are similar to UTL_MAIL.SEND but produce S/MIME enveloped-data (encrypted) emails.

Let’s see how to do it :

DECLARE 
  -- file read handle
  file_handle BFILE;
  -- X.509 certificate
  certificate_blob BLOB;  
BEGIN
    -- load .PFX (key storage) into a BLOB
    file_handle := BFILENAME('KEYS_DIR', 'EE.crt');
    DBMS_LOB.createtemporary(certificate_blob, TRUE);
    DBMS_LOB.OPEN(file_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LoadFromFile( certificate_blob, file_handle, DBMS_LOB.GETLENGTH(file_handle) );
    DBMS_LOB.CLOSE(file_handle);
 
    ORA_SMIME.SEND_ENCRYPTED_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!',
		x509_certificate => certificate_blob);    
END;
/

S/MIME encrypted Email with attachment

The easiest way to send S/MIME encrypted email with attachment (which must be of type BLOB or CLOB) is by using ORA_SMIME.SEND_ENCRYPTED_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 encrypted (enveloped-data) email with an attachment from PL/SQL:

DECLARE 
  -- file read handle
  file_handle BFILE;
  -- input PDF BLOB
  pdf_blob BLOB;  
  -- X.509
  certificate_blob BLOB;  
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.crt');
    DBMS_LOB.createtemporary(certificate_blob, TRUE);
    DBMS_LOB.OPEN(file_handle, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LoadFromFile( certificate_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',
		x509_certificate => certificate_blob);    
END;
/

Summary

This article illustrated how to create S/MIME enveloped-data (encrypted) 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.ENCRYPT_EMAIL Converts the contents of a CLOB variable into S/MIME encrypted format
ORA_SMIME.SEND_ENCRYPTED_EMAIL Sends email message but converts it into S/MIME encrypted format beforehand