APEX_MAIL SMIME encrypted and signed emails

The Oracle® DB APEX_MAIL package provides excellent mail sending API for PL/SQL Developers, but lacks support for S/MIME (Secure Multipurpose Internet Mail Extensions).

Part of OraRsa is a new ORA_SMIME_APEX_MAIL package that provides mail sending API similar to APEX_MAIL, but with additional S/MIME support. In this tutorial you will learn how to use the ORA_SMIME_APEX_MAIL functions inside your Oracle® Database.

Quick glance at the API

The sequence of calls to ORA_SMIME_APEX_MAIL is very similar to the one provide by APEX_MAIL:

  1. ORA_SMIME_APEX_MAIL.SEND – this is the first method. It creates the email body.
  2. ORA_SMIME_APEX_MAIL.ADD_ATTACHMENToptional calls. Can be invoked several times and will attach images and documents to your email.
  3. ORA_SMIME_APEX_MAIL.ENCRYPT and SIGN – this is the additional step. Here you create the S/MIME email.
  4. ORA_SMIME_APEX_MAIL.PUSH_QUEUE – actual sending of the final S/MIME email.

If you are familiar with APEX_MAIL the only difference that you will notice is the additional point 3. where the email is signed and/or encrypted.

Here is a sample code that illustrates a complete S/MIME encrypted email sending sequence:

DECLARE
  l_body CLOB;
  l_email ORA_SMIME_MESSAGE;
  l_file_cert BLOB;
BEGIN
  l_body := 'This is S/MIME encrypted email.';
 
  l_email := ORA_SMIME_APEX_MAIL.send(
   p_to => 'certs@didisoft.com', 
   p_from => 'smime@didisoft.com',
   p_body => l_body, 
   p_subj => 'A demo APEX_MAIL S/MIME encrypted message');
 
  -- table recipient_certs(recipient_id varchar2(255), cert BLOB);
  SELECT cert INTO l_file_cert FROM recipient_certs
  WHERE recipient_id = 'certs@didisoft.com';
 
  ORA_SMIME_APEX_MAIL.ENCRYPT(p_mail => l_email,
   x509_certificate => l_file_cert,
   cipher => ORA_SMIME_APEX_MAIL.CIPHER_AES128);
 
 ORA_SMIME_APEX_MAIL.PUSH_QUEUE(l_email); -- for APEX_MAIL SMTP host details
 -- or ORA_SMIME_APEX_MAIL.PUSH_QUEUE2(l_email); -- for custom SMTP host details
END;

Setup of ORA_SMIME_APEX_MAIL


The setup procedure of the ORA_SMIME_APEX_MAIL package requires the setup of ORA_RSA.

After ORA_RSA has been installed, simply execute the [extraction folder]/SetupFiles/ORA_SMIME_APEX_MAIL.sql file inside the target Oracle database.

Networking permissions

In order to establish SMTP connection a Networking permission must also be assigned to the Oracle DB user/schema that owns the ORA_SMIME_APEX_MAIL package.
Below is a sample permission which you can modify to suit your needs: (replace USER with the Oracle username under which you have installed ORA_SMIME_APEX_MAIL):

BEGIN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
  acl => 'send_mails.xml', -- our made up name of the ACL
  description => 'Allow mail to be send', 
  principal => 'USER', 
  is_grant => TRUE, 
  privilege => 'connect');

 ----Grant the connect and resource privilege as below
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'send_mails.xml', principal => 'USER',is_grant => TRUE, privilege => 'resolve');
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL( acl => 'send_mails.xml', host => '*');
END;
/

 

Afterwards the ORA_SMIME_APEX_MAIL functions should be available for programming.

SEND method (new email)


The first method that you should use in order to create S/MIME emails with ORA_SMIME_APEX_MAIL is SEND. Its usage is very similar to APEX_MAIL.SEND with the only difference being that it returns a variable of type ORA_SMIME_MESSAGE, which you gonna pass to all other methods of the package.

Parameters of the SEND method

p_to Valid email address to which the email is sent (required). For multiple email addresses, use a list separated by ;
p_from Email address from which the email is sent (required).
p_body Body of the email in plain text, not HTML (required). If a value
is passed to `p_body_html`, then this is the only text the recipient
sees. If a value is not passed to `p_body_html`, then this text only
displays for email clients that do not support HTML or have HTML
disabled.
p_body_html Body of the email in HTML format. (optional)
p_subj Subject of the email (required)
p_cc Valid email addresses to which the email is copied (optional). For multiple email addresses, use a list separated by ;
p_bcc Valid email addresses to which the email is blind copied. (optional). For multiple email addresses, use a list separated by ;
p_replyto Address of the Reply-To mail header (required)

 

Example with SEND

This example creates an email message of the custom type ORA_SMIME_MESSAGE with textual and HTML body. The email is not sent until you invoke PUSH_QUEUE.

DECLARE
l_body CLOB;
l_body_html CLOB;
email ORA_SMIME_MESSAGE;
 
BEGIN
 l_body := 'To view the content of this message, please use an HTML enabled mail client.'||utl_tcp.crlf;
 
 l_body_html := '<html>
  <head>
  <style type="text/css">
  body{font-family: Arial, Helvetica, sans-serif;
  font-size:10pt;
  margin:30px;
  background-color:#ffffff;}
 
  span.sig{font-style:italic;
  font-weight:bold;
  color:#811919;}
  </style>
  </head>
<body>'||utl_tcp.crlf;
 l_body_html := l_body_html ||'<p>Thank you for your interest in the <strong>APEX_MAIL</strong> package.</p>'||utl_tcp.crlf;
 l_body_html := l_body_html ||' Sincerely,<br />'||utl_tcp.crlf;
 l_body_html := l_body_html ||' <span class="sig">The APEX Dev Team</span><br /><img src="cid:Nina" />'||utl_tcp.crlf;
 email := ORA_SMIME_APEX_MAIL.send(
  p_to => 'support@nciphers.com', -- change to your email address
  p_from => 'sales@didisoft.com', -- change to a real senders email address
  p_body => l_body,
  p_body_html => l_body_html,
  p_subj => 'APEX_MAIL Package - HTML formatted message');

ADD_ATTACHMENT method

The ADD_ATTACHMENT method appends attachment to the previously created email with the SEND function. It can be called several times in a row in order to attach more than one file. Attachments can be documents and images.

For images there is a specific technique used to include them in the HTML text body that will be illustrated below.

List of parameters for ADD_ATTACHMENT

p_mail Email message of type ORA_SMIME_MESSAGE created with the SEND function
p_attachment A `BLOB` variable containing the binary content to be attached
p_filename A filename to be associated with the email attachment.
p_mime_type MIME type (or Internet media type) for the email attachment.
Common MIME types:

  • image/jpg – JPEG image
  • image/png – PNG image
  • application/pdf – PDF document
  • application/vnd.ms-excel – MS Excel document
  • application/msword – Word document
  • application/vnd.ms-powerpoint – PowerPoint document
  • application/zip – ZIP file
p_content_id An optional identifier for the attachment. If this is non-null then the file will be attached inline. That attachment may then
be referenced in the HTML of the email body by using the `cid:` (see below)

Example how to invoke ADD_ATTACHMENT with image referenced from the HTML email body:
Notice how we reference the attached image with the img tag src attribute value having prefix cid: and then follows the p_content_id value of the attachment.

DECLARE
l_body CLOB;
l_body_html CLOB;
email ORA_SMIME_MESSAGE;
 
file_handle BFILE;
file_blob BLOB;
 
BEGIN
l_body := 'To view the content of this message, please use an HTML enabled mail client.'||utl_tcp.crlf;
 
l_body_html := '<html>
<head>
</head>
<body>'||utl_tcp.crlf;
l_body_html := l_body_html ||'<p>Thank you for your interest in the <strong>APEX_MAIL</strong> package.</p>'||utl_tcp.crlf;
l_body_html := l_body_html ||' Sincerely,<br />'||utl_tcp.crlf;
l_body_html := l_body_html ||' <span class="sig">The APEX Dev Team</span><br /><img src="cid:Nina" />'||utl_tcp.crlf;
email := ORA_SMIME_APEX_MAIL.send(
          p_to => 'acm@nciphers.com', -- change to your email address
          p_from => 'smime@didisoft.com', -- change to a real senders email address
          p_body => l_body,
          p_body_html => l_body_html,
          p_subj => 'APEX_MAIL Package - HTML formatted message');
 
-- initialize the message storage
DBMS_LOB.createtemporary(file_blob, TRUE);
 
file_handle := BFILENAME('APPS_KEYS_DIR', 'Nina.png'); -- directory name must be Upper case
 
-- load the data into a BLOB
DBMS_LOB.OPEN(file_handle, DBMS_LOB.LOB_READONLY);
DBMS_LOB.LoadFromFile( DEST_LOB => file_blob,
                      SRC_LOB => file_handle,
                      AMOUNT => DBMS_LOB.GETLENGTH(file_handle) );
                      DBMS_LOB.CLOSE(file_handle);
 
 ORA_SMIME_APEX_MAIL.add_attachment(p_mail => email,
        p_attachment => file_blob,
        p_filename => 'Nina.png',
        p_mime_type => 'image/png',
        p_content_id => 'Nina');
 
...
 
END;

Example how to attach PDF document
This example is essentially the same, except here we don’t have to specify the p_content_id parameter. Without the p_content_id parameter the result is a plain email attachment.

DECLARE
 l_body CLOB;
 email ORA_SMIME_MESSAGE;
 file_blob BLOB;
BEGIN
 l_body := 'A PDF Invoice is attached!'||utl_tcp.crlf;
 email := ORA_SMIME_APEX_MAIL.send(
          p_to => 'acm@nciphers.com', -- change to your email address
          p_from => 'smime@didisoft.com', -- change to a real senders email address
          p_body => l_body,
          p_subj => 'APEX_MAIL Package - PDF Invoice is attached!');
 
 -- sample table myfile(file_id varchar2(255), data BLOB);
 SELECT DATA INTO file_blob FROM myfiles
 WHERE file_id = 'Invoice.pdf';
 
 ORA_SMIME_APEX_MAIL.add_attachment(p_mail => email,
        p_attachment => file_blob,
        p_filename => 'Invoice.pdf',
        p_mime_type => 'application/pdf');
...
END;

SMIME Encrypted email


The package ORA_SMIME_APEX_MAIL offers S/MIME encrypted emails with RSA, RSA-PSS and RSA-OAEP.

Parameters of procedure ORA_SMIME_APEX_MAIL.ENCRYPT

p_mail Email message of type ORA_SMIME_MESSAGE created with the SEND function
x509_certificate A `BLOB` variable containing a X.509 certificate file contents (.cert, .crt or .pem file)
or a .pfx/.p12 password protected file
private_key_password (optional) VARCHAR2 containing a password for unlocking the .pfx/p12 file if the certificate is in such file
cipher (optional) Symmetric cipher parameter. Default value is CIPHER_AES256.

Example usage of ORA_SMIME_APEX_MAIL.ENCRYPT
In this example we are going to create a plain mail message and then S/MIME encrypt it using the X.509 certificate of the recipient:

DECLARE
 l_body CLOB;
 email ORA_SMIME_MESSAGE;
 file_handle BFILE;
 file_blob BLOB;
BEGIN
 l_body := 'This is S/MIME encrypted email.'||utl_tcp.crlf;
 
 email := ORA_SMIME_APEX_MAIL.send(
    p_to => 'certs@didisoft.com', -- change to your email address
    p_from => 'smime@didisoft.com', -- change to a real senders email address
    p_body => l_body,    
    p_subj => 'APEX_MAIL S/MIME encrypted message');
 
 DBMS_LOB.createtemporary(file_blob, TRUE);
 file_handle := BFILENAME('APPS_KEYS_DIR', 'recipient.crt');
 
 DBMS_LOB.OPEN(file_handle, DBMS_LOB.LOB_READONLY);
 DBMS_LOB.LoadFromFile( DEST_LOB => file_blob,
   SRC_LOB => file_handle,
   AMOUNT => DBMS_LOB.GETLENGTH(file_handle) );
 DBMS_LOB.CLOSE(file_handle);
 
 ORA_SMIME_APEX_MAIL.ENCRYPT(p_mail => email,
    x509_certificate => file_blob,
    cipher => ORA_SMIME_APEX_MAIL.CIPHER_AES128);
 ORA_SMIME_APEX_MAIL.PUSH_QUEUE(p_mail);
END;

RSA-OAEP and RSA-PSS S/MIME encrypted emails


RSA encrypted emails use PKCS#1.5 padding and with combination of a symmetric cipher are considered secure.

But RSA-OAEP padding in combination with RSA-PSS signature scheme which uses hash functions to produce a random signature over the symmetric encryption key is considered way more secure. With ORA_SMIME_APEX_MAIL we can encrypt using both RSA-OAEP and RSA-PSS with the ENCRYPT_RSA_OAEP method as shown below:

DECLARE
 l_body CLOB;
 email ORA_SMIME_MESSAGE;
 file_cert BLOB;
BEGIN
 l_body := 'This is S/MIME encrypted email.'||utl_tcp.crlf;
 
 email := ORA_SMIME_APEX_MAIL.send(
 p_to => 'certs@didisoft.com', -- change to your email address
 p_from => 'smime@didisoft.com', -- change to a real senders email address
 p_body => l_body, 
 p_subj => 'APEX_MAIL S/MIME RSA-OAEP encrypted message');
 
 -- table recipient_certs(recipient_id varchar2(255), cert BLOB);
 SELECT cert INTO file_cert FROM recipient_certs
 WHERE recipient_id = 'certs@didisoft.com';
 
 ORA_SMIME_APEX_MAIL.ENCRYPT_RSA_OAEP(p_mail => email,
   x509_certificate => file_cert,
   hash => ORA_SMIME_APEX_MAIL.RSAPSS_SHA256,
   cipher => ORA_SMIME_APEX_MAIL.CIPHER_AES128);
 ORA_SMIME_APEX_MAIL.PUSH_QUEUE(p_mail);
END;

The RSA-PSS signature schema is specified by the ORA_SMIME_APEX_MAIL.RSAPSS_xx hash function.

S/MIME Signed email


Signed emails come in two formats embedded and detached. In the Embedded format the signature and the message are combined in one MIME entity, whereas in the detached format the signature is in the form of email attachment.

Embedded signature is created with ORA_SMIME_APEX_MAIL.SIGN_EMBEDDED:

Parameters of procedure ORA_SMIME_APEX_MAIL.SIGN_EMBEDDED

p_mail Email message of type ORA_SMIME_MESSAGE created with the SEND function
private_key A `BLOB` variable containing a private key in format .pfx or .pkcs12 or raw key file
private_key_password (optional) Private key password
hash (optional) Signature hash function. Default is HASH_SHA256

Example usage of SIGN_EMBEDDED:

DECLARE
 l_body CLOB;
 email ORA_SMIME_MESSAGE;
 file_handle BFILE;
 file_blob BLOB;
BEGIN
 l_body := 'This is S/MIME encrypted email.'||utl_tcp.crlf;
 
 email := ORA_SMIME_APEX_MAIL.send(
    p_to => 'certs@didisoft.com', -- change to your email address
    p_from => 'smime@didisoft.com', -- change to a real senders email address
    p_body => l_body,    
    p_subj => 'APEX_MAIL S/MIME encrypted message');
 
 DBMS_LOB.createtemporary(file_blob, TRUE);
 file_handle := BFILENAME('APPS_KEYS_DIR', 'mykey.pfx');
 
 DBMS_LOB.OPEN(file_handle, DBMS_LOB.LOB_READONLY);
 DBMS_LOB.LoadFromFile( DEST_LOB => file_blob,
   SRC_LOB => file_handle,
   AMOUNT => DBMS_LOB.GETLENGTH(file_handle) );
 DBMS_LOB.CLOSE(file_handle);
 
 ORA_SMIME_APEX_MAIL.SIGN_EMBEDDED(p_mail => email,
    private_key => file_blob,
    private_key_password => 'my key password');
 ORA_SMIME_APEX_MAIL.PUSH_QUEUE(p_mail);
END;

Detached S/MIME signed emails

Detached signatures are created with ORA_SMIME_APEX_MAIL.SIGN_DETACHED. The list of parameters is the same as with ORA_SMIME_APEX_MAIL.SIGN_EMBEDDED

Here is an example usage of ORA_SMIME_APEX_MAIL.SIGN_DETACHED

DECLARE
 l_body CLOB;
 email ORA_SMIME_MESSAGE;
 file_handle BFILE;
 file_blob BLOB;
BEGIN
 l_body := 'This is S/MIME encrypted email.'||utl_tcp.crlf;
 
 email := ORA_SMIME_APEX_MAIL.send(
    p_to => 'certs@didisoft.com', -- change to your email address
    p_from => 'smime@didisoft.com', -- change to a real senders email address
    p_body => l_body,    
    p_subj => 'APEX_MAIL S/MIME encrypted message');
 
 DBMS_LOB.createtemporary(file_blob, TRUE);
 file_handle := BFILENAME('APPS_KEYS_DIR', 'mykey.pfx');
 
 DBMS_LOB.OPEN(file_handle, DBMS_LOB.LOB_READONLY);
 DBMS_LOB.LoadFromFile( DEST_LOB => file_blob,
   SRC_LOB => file_handle,
   AMOUNT => DBMS_LOB.GETLENGTH(file_handle) );
 DBMS_LOB.CLOSE(file_handle);
 
 ORA_SMIME_APEX_MAIL.SIGN_DETACHED(p_mail => email,
    private_key => file_blob,
    private_key_password => 'my key password');
 ORA_SMIME_APEX_MAIL.PUSH_QUEUE(p_mail);
END;

 

Signed and Encrypted


In the S/MIME specification there is no specific format for signed and encrypted emails. So signed and encrypted can be first signed then encrypted or first encrypted then signed.

In the first case (signed then encrypted), we are going to invoke in this order:

ORA_SMIME_APEX_MAIL.SIGN_EMBEDDED(p_mail => ..
ORA_SMIME_APEX_MAIL.ENCRYPT(p_mail => ..

In the encrypted then signed case, we will do the opposite:

ORA_SMIME_APEX_MAIL.ENCRYPT(p_mail => ..
ORA_SMIME_APEX_MAIL.SIGN_EMBEDDED(p_mail => ..

PUSH_QUEUE (actual sending)

PUSH_QUEUE (using APEX_MAIL parameters)

The PUSH_QUEUE procedure reads the APEX_INSTANCE_ADMIN SMTP host parameters and based on them decides, which SMTP server to use and with what credentials. So in order ORA_SMIME_APEX_MAIL.PUSH_QUEUE to work we have to have APEX_INSTANCE_ADMIN mail parameters configured.

Then the actual mail sending is simply calling PUSH_QUEUE with initialized ORA_SMIME_MESSAGE parameter:

 ORA_SMIME_APEX_MAIL.PUSH_QUEUE(p_mail => email);

PUSH_QUEUE2 (custom SMTP parameters)

If you want to send email with custom SMTP host parameters that are different from the APEX_INSTANCE_ADMIN SMTP parameters, then you can use the PUSH_QUEUE2 method.
In addition to the ORA_SMIME_MESSAGE mail parameter, PUSH_QUEUE2 accepts also:

smtp_host VARCHAR2, SMTP host, default is ‘127.0.0.1’
smtp_port NUMBER, default is 25
smtp_username VARCHAR2, default is NULL
smtp_password VARCHAR2, default is NULL
smtp_wallet VARCHAR2, Wallet filesystem path. Default is NULL
smtp_pwd VARCHAR2, Wallet password. Default is NULL
smtp_secure VARCHAR2, Default is ‘N’. Possible values ‘N’ – plain connection. ‘Y’ for TLS, ‘STARTTLS’ for STARTTLS connection

TLS, STARTLS and the wallet file


In order to use encrypted SMTP sessions the SMTP_TLS_MODE, WALLET_PATH and WALLET_PWD parameters of APEX_INSTANCE_ADMIN must be configured.

Based on then above parameters, ORA_SMIME_APEX_MAIL.PUSH_QUEUE will decide which type of secure SMTP session to use.

The WALLET_PATH APEX_MAIL setting, must point to a folder containing existing wallet file and located on the Oracle DB instance server filesystem. The certificate chain of the sending SMTP server must also be imported in this wallet file.

Example value for WALLET_PATH

APEX_INSTANCE_ADMIN.SET_PARAMETER('WALLET_PATH' , 'file:D:\wallet\');

Wallet access permissions

In addition to the networking permissions, if the APEX_INSTANCE_ADMIN parameter SMTP_TLS_MODE is ‘Y’ or ‘STARTTLS’ these privileges are also required:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
  acl => 'send_mails.xml',
  principal => 'USER',
  is_grant => TRUE, 
  privilege => 'use-client-certificates');

 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
  acl => 'send_mails.xml',
  principal => 'USER',
  is_grant => TRUE,
  privilege => 'use-passwords');
END;
/

-- assign the wallet with all imported certificates:
BEGIN 
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL(
  acl => 'send_mails.xml',
 wallet_path => 'file:D:\wallet\');
END;
/

Log table ORA_SMIME_APEX_MAIL_LOG

As of version 1.3.7.5 an additional log table ORA_SMIME_APEX_MAIL_LOG (similar to APEX_MAIL_LOG) is created by the ORA_SMIME_APEX_MAIL script.

By default ORA_SMIME_APEX_MAIL doesn’t write anything into the log table, so we have to enable log output explicitly with:

ORA_SMIME_APEX_MAIL.MAIL_LOG_ENABLED := TRUE;

Now each invocation of the PUSH_QUEUE and PUSH_QUEUE2 methods will store information in the log table.

The MESSAGE_ID, APP_ID, WORKSPACE_ID, and WORKSPACE_NAME columns stored in the ORA_SMIME_APEX_MAIL_LOG table can be set through the identical properties of the ORA_SMIME_APEX_MAIL package like:

ORA_SMIME_APEX_MAIL.APP_ID := 1;
wwv_flow_api.set_security_group_id(ORA_SMIME_APEX_MAIL.WORKSPACE_ID);

The MESSAGE_ID property can be used to identify the email being sent and will be stored after PUSH_QUEUE in the MAIL_MESSAGE_ID column of ORA_SMIME_APEX_MAIL_LOG :

email := ORA_SMIME_APEX_MAIL.SEND(
   p_to => 'certs@didisoft.com', 
   p_from => 'smime@didisoft.com',
   p_body => 'Hello', 
   p_subj => 'A demo APEX_MAIL S/MIME encrypted message');
ORA_SMIME_APEX_MAIL.MESSAGE_ID := 'msg8ahdacu1a7p@didisoft.com';...

Combined view with APEX_MAIL_LOG

We can create a combined view from APEX_MAIL_LOG and ORA_SMIME_APEX_MAIL_LOG in order to inspect both S/MIME and regular emails from one place like:

CREATE VIEW COMBINED_APEX_MAIL_LOG AS
SELECT * FROM ORA_SMIME_APEX_MAIL_LOG
UNION ALL
SELECT * FROM APEX_MAIL_LOG

Grants to ORA_SMIME_APEX_MAIL_LOG

A common practice among users of DidiSoft OraRSA is to create the packages in one schema and use them from another user/schema.
In such case a proper GRANT statements must be executed in order the ORA_SMIME_APEX_MAIL package when invoked from another user/schema/ to be able to insert into the ORA_SMIME_APEX_MAIL_LOG table:

GRANT SELECT, INSERT on ORA_SMIME_APEX_MAIL_LOG to [user/schema]

Summary

This tutorial illustrates the usage of ORA_SMIME_APEX_MAIL package part of DidiSoft OraRSA.  This PL/SQL package offers API that is familiar to APEX_MAIL developers and extends the capabilities of APEX_MAIL with support for signed and encrypted S/MIME emails.