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
• Setup of ORA_SMIME_APEX_MAIL
• SEND method (new email)
• ADD_ATTACHMENT method
• SMIME Encrypted email
  • RSA-OAEP and RSA-PSS
  • AES-GCM
• SMIME Signed email
  • Detached
  • Embedded
• Signed and Encrypted
• PUSH_QUEUE (actual sending)
• TLS, STARTLS and the wallet file
• Log table ORA_SMIME_APEX_MAIL_LOG

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_ATTACHMENT – optional 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.

Permissions

ORA_SMIME_APEX_MAIL is created with AUTHID DEFINER (the default one in Oracle DB) and as such the permissions below must be granted to the Oracle Schema/user/ where ORA_SMIME_APEX_MAIL package resides.

1. 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;
/

2. APEX_ADMINISTRATOR_ROLE (optional)

The functions that actually send the emails are PUSH_QUEUE and PUSH_QUEUE2 with the difference that the first gets the APEX workspace defined SMTP settings and the second one expects them as explicit parameters. So in order to use PUSH_QUEUE the schema must be granted APEX_ADMINISTRATOR_ROLE (this is because PUSH_QUEUE invokes APEX_ADMIN_INSTANCE.GET_PROPERTY; Reference why APEX_ADMINISTRATOR_ROLE is needed here)

GRANT APEX_ADMINISTRATOR_ROLE TO USER;

If fore some reason we cannot give the APEX_ADMINISTRATOR_ROLE , we should use PUSH_QUEUE2 and specify the SMTP parameters explicitly.

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

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

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

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_EMAIL_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.

AES-GCM encrypted emails

In order to encrypt the emails with AES-GCM cypher we have to use the ENCRYPT_EMAIL_RSA_GCM method of the ORA_SMIME package. The invocation is similar to the one above except now we must change the call to ENCRYPT_EMAIL_RSA_OAEP with:

ORA_SMIME_APEX_MAIL.ENCRYPT_RSA_GCM(p_mail => email, 
  x509_certificate => file_cert, 
  cipher => ORA_SMIME_APEX_MAIL.CIPHER_AES128_GCM);

Possible values for the cypher are:

• ORA_SMIME_APEX_MAIL.CIPHER_AES128_GCM
• ORA_SMIME_APEX_MAIL.CIPHER_AES192_GCM
• ORA_SMIME_APEX_MAIL.CIPHER_AES256_GCM

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

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 and the schema where ORA_SMIME_APEX_MAIL is defined must be granted APEX_ADMINISTRATOR_ROLE.

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 we want to send email with custom SMTP host parameters that are different from the APEX_INSTANCE_ADMIN SMTP parameters or for some reason we cannot grant APEX_ADMINISTRATOR_ROLE, then we can use the PUSH_QUEUE2 method.
In addition to the ORA_SMIME_MESSAGE mail parameter, PUSH_QUEUE2 accepts also:

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.