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 the one part of APEX_MAIL with the additional S/MIME support. In this article you will learn how to use the ORA_SMIME_APEX_MAIL functions inside your Oracle® Database.

Quick glance at the API
Setup of ORA_SMIME_APEX_MAIL
SEND method (new email)
ADD_ATTACHMENT method
SMIME Encrypted email
- RSA-OAEP and RSA-PSS
SMIME Signed email
- Detached
- Embedded
Signed and Encrypted
PUSH_QUEUE (actual sending)
TLS, STARTLS and the wallet file

Quick glance at the API

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

ORA_SMIME_APEX_MAIL.SEND – this is the first method. It creates the email body.
ORA_SMIME_APEX_MAIL.ADD_ATTACHMENT – optional calls. Can be invoked several times and will attach images and documents to your email.
ORA_SMIME_APEX_MAIL.ENCRYPT or SIGN – this is the additional step. Here you create the S/MIME email.
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 Step.3 where the email is signed or encrypted.

Here is a sample code that illustrates a complete email sending:

DECLARE
  l_body CLOB;
  email ORA_SMIME_MESSAGE;
  file_cert BLOB;
BEGIN
  l_body := 'This is S/MIME encrypted email.';
 
  email := ORA_SMIME_APEX_MAIL.send(
   p_to => 'certs@didisoft.com', 
   p_from => 'smime@didisoft.com',
   p_body => l_body, 
   p_subj => 'APEX_MAIL S/MIME 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(p_mail => email,
   x509_certificate => file_cert,
   cipher => ORA_SMIME_APEX_MAIL.CIPHER_AES128);
 
 ORA_SMIME_APEX_MAIL.PUS_QUEUE(email);
END;

Setup of ORA_SMIME_APEX_MAIL

The setup procedure of ORA_SMIME_APEX_MAIL 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 ORA_SMIME_APEX_MAIL
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 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 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)
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 an email and then create S/MIME encrypted email 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 =&gt; 'certs@didisoft.com', -- change to your email address
 p_from =&gt; 'smime@didisoft.com', -- change to a real senders email address
 p_body =&gt; l_body, 
 p_subj =&gt; '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 =&gt; email,
   x509_certificate =&gt; file_cert,
   hash =&gt; ORA_SMIME_APEX_MAIL.RSAPSS_SHA256,
   cipher =&gt; 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

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, 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 we will do the oposite:

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

PUSH_QUEUE (actual sending)

PUSH_QUEUE

The PUSH_QUEUE procedure reads the APEX_INSTANCE_ADMIN SMTP 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

If you want to send email with 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;
/

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.

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.