S/MIME encrypted emails (known as enveloped-data) can be created very easily with the ORA_SMIME PL/SQL package.
Note: As of version 1.3.7 sending S/MIME encrypted emails is easier with ORA_SMIME_APEX_MAIL
This API will remain as legacy email sending API
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)
- RSA-OAEP
- S/MIME encrypted Email with ORA_SMIME (high level) like UTL_MAIL
- S/MIME encrypted Email with an attachment
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 |