Sending email from Oracle with utl_smtp via Amazon Simple Email Service (SES)

Jared Still

September 21, 2017

Tags: Oracle, Oci, Technical Track, Cloud, Cdb, Paas, Odbcs

Note: This article was updated following Symantec Trust issues earlier in 2018.

Sometimes I want to send email from my own databases for testing purposes.

Lacking a mail server, I opted to use Amazon’s Simple Email Service (SES). SES is a paid product, with a free usage tier.

For my purposes however SES is a bargain at $0.0001 per email. If you want to perform this exercise yourself, you will need the following:
–An Amazon AWS account
–Oracle 11.2.0.4+

My tests were performed on Oracle Linux 6; your mileage may vary on other platforms.

When first configured the only allowed access is via the SES Sandbox. Being in the sandbox requires that you verify all senders and recipients. For testing SES functionality that works out fine however, as I just wanted to successfully send an email from an Oracle database so I would know it could be done, and what was required to make it work.

You can request to be moved out of the Sandbox - this option is available in the SES Console.

Throughout the article I will include some URL’s for AWS, but as AWS tend to change over time these links may not work in the future. If that is the case, just use google or search on the AWS console, as most things are easy to find.

Most everything related to SES can be accessed from the SES Home Page

Verify Email Addresses

The first step is to logon to your AWS account and go to the SES Console page.

If you navigate to the SES Home Page page and click on Getting Started, you will see Send an email using the Amazon SES console (AWS 10-minute tutorial). That article will walk you through verifying email addresses.

All new SES configurations are in the SES Sandbox. Initially you will need to verify both addresses you are sending from and addresses you are sending to. For testing purposes they can be the same addresses.

At this point it is a good idea to keep in mind this test will require Oracle 11.2.0.4+.

Earlier versions of Oracle do not support TLS, and this test simply will not work.
(If someone can prove otherwise, please share the details)

Create an IAM Identity

Next you will need to add an IAM Identity that allows access to the email server. Go back to the SES Console and click on Identity Management.

Follow the instructions to create an identity, and put the information somewhere for safe-keeping.There will be three items created - the IAM Identity, the username and the password.

Username and password will both be long names of random characters; you will not be able to remember them. These can be downloaded as a CSV file at the time of creation.

These AWS pages change from time to time, and will not be documented here.

Create an Oracle Wallet

In order to access the SES email server from Oracle with utl_smtp, an Oracle wallet will be required to enable this.

Digicert has an article on importing certificates into Oracle Wallets: Installation Instructions for Oracle Wallet Manager

As the article refers to using the GUI tool owm (oracle wallet manager) it will not be discussed further here. I found owm to be rather difficult to use. Some owm operations do not work, and the error messages are not useful, and the interface is clunky.

The command line tool orapki is much better, or at least I think so.

The following commands are used to manually create a wallet and import the certificates.

Note: Keep in mind that on RAC these files must be copied to other nodes as needed, or else be on shared storage such as ACFS or NFS.

It is not necessary to create the wallets in Oracle home, I just did so for convenience.

As I just used the default Virginia region for SES setup, the command line shown is somewhat different than that in the aforementioned documentation.

A list of the SES servers is found here SES SMTP Servers

It will be necessary to add server certificates to the Oracle wallet to allow sending email; there are at least a couple of methods to discover the certificates needed for the Oracle Wallet.

This Website can generate the exact certificates needed for the chain What’s My Chain Cert?

The email server name email-smtp.us-east-1.amazonaws.com was plugged into the text box, and the resulting cert saved as email-smtp.us-east-1.amazonaws.com.chain.crt

The following commands were then used to create the Oracle Wallet.

[sourcecode language="bash"] mkdir -p $ORACLE_HOME/owm/wallets/oracle orapki wallet create -wallet $ORACLE_HOME/owm/wallets/oracle/aws-ses orapki wallet add -wallet $ORACLE_HOME/owm/wallets/oracle/aws-ses -trusted_cert -cert email-smtp.us-east-1.amazonaws.com.chain.crt $ orapki wallet display -wallet $ORACLE_HOME/owm/wallets/oracle/aws-ses Oracle PKI Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Requested Certificates: User Certificates: Trusted Certificates: Subject: CN=Amazon,OU=Server CA 1B,O=Amazon,C=US [/sourcecode]

Another method is to get the cert chain with the openssl and s_client programs.

The output is the complete chain of certificates for email-smtp.us-east-1.amazonaws.com.

The output is rather lengthy, and only partially reproduced here:

[sourcecode language="bash"] $ openssl s_client -host email-smtp.us-east-1.amazonaws.com -port 587 -prexit -showcerts -crlf -starttls smtp CONNECTED(00000003) depth=4 C = US, O = "Starfield Technologies, Inc.", OU = Starfield Class 2 Certification Authority verify return:1 depth=3 C = US, ST = Arizona, L = Scottsdale, O = "Starfield Technologies, Inc.", CN = Starfield Services Root Certificate Authority - G2 verify return:1 depth=2 C = US, O = Amazon, CN = Amazon Root CA 1 verify return:1 depth=1 C = US, O = Amazon, OU = Server CA 1B, CN = Amazon verify return:1 depth=0 CN = email-smtp.us-east-1.amazonaws.com verify return:1 --- Certificate chain 0 s:/CN=email-smtp.us-east-1.amazonaws.com i:/C=US/O=Amazon/OU=Server CA 1B/CN=Amazon -----BEGIN CERTIFICATE----- MIIEhTCCA22gAwIBAgIQBJSyqvZcwNT12er++XeOTTANBgkqhkiG9w0BAQsFADBG MQswCQYDVQQGEwJVUzEPMA0GA1UEChMGQW1hem9uMRUwEwYDVQQLEwxTZXJ2ZXIg ... rr/Nhhm2i6y9X6iIBB3dCT8kIYN5wJ6aeb4Dote2VFs4Ih0Q3+Y5vhqgbpZWq5pL nmTKJdIfH3BIe/POr9XE2MUPLQ0dPN8zPtXRg49xwiPeqDku5NiklXfhkzwmcjsA AxedQsGiLnEg -----END CERTIFICATE----- 1 s:/C=US/O=Amazon/OU=Server CA 1B/CN=Amazon i:/C=US/O=Amazon/CN=Amazon Root CA 1 -----BEGIN CERTIFICATE----- MIIESTCCAzGgAwIBAgITBn+UV4WH6Kx33rJTMlu8mYtWDTANBgkqhkiG9w0BAQsF ADA5MQswCQYDVQQGEwJVUzEPMA0GA1UEChMGQW1hem9uMRkwFwYDVQQDExBBbWF6 ... upRyzQ7qDn1X8nn8N8V7YJ6y68AtkHcNSRAnpTitxBKjtKPISLMVCx7i4hncxHZS yLyKQXhw2W2Xs0qLeC1etA+jTGDK4UfLeC0SF7FSi8o5LL21L8IzApar2pR/ -----END CERTIFICATE----- 2 s:/C=US/O=Amazon/CN=Amazon Root CA 1 i:/C=US/ST=Arizona/L=Scottsdale/O=Starfield Technologies, Inc./CN=Starfield Services Root Certificate Authority - G2 -----BEGIN CERTIFICATE----- MIIEkjCCA3qgAwIBAgITBn+USionzfP6wq4rAfkI7rnExjANBgkqhkiG9w0BAQsF ADCBmDELMAkGA1UEBhMCVVMxEDAOBgNVBAgTB0FyaXpvbmExEzARBgNVBAcTClNj ... bRRYh5TmOTFffHPLkIhqhBGWJ6bt2YFGpn6jcgAKUj6DiAdjd4lpFw85hdKrCEVN 0FE6/V1dN2RMfjCyVSRCnTawXZwXgWHxyvkQAiSr6w10kY17RSlQOYiypok1JR4U akcjMS9cmvqtmg5iUaQqqcT5NJ0hGA== -----END CERTIFICATE----- 3 s:/C=US/ST=Arizona/L=Scottsdale/O=Starfield Technologies, Inc./CN=Starfield Services Root Certificate Authority - G2 i:/C=US/O=Starfield Technologies, Inc./OU=Starfield Class 2 Certification Authority -----BEGIN CERTIFICATE----- MIIEdTCCA12gAwIBAgIJAKcOSkw0grd/MA0GCSqGSIb3DQEBCwUAMGgxCzAJBgNV BAYTAlVTMSUwIwYDVQQKExxTdGFyZmllbGQgVGVjaG5vbG9naWVzLCBJbmMuMTIw ... 59vPr5KW7ySaNRB6nJHGDn2Z9j8Z3/VyVOEVqQdZe4O/Ui5GjLIAZHYcSNPYeehu VsyuLAOQ1xk4meTKCRlb/weWsKh/NEnfVqn3sF/tM+2MR7cwA130A4w= -----END CERTIFICATE----- --- Server certificate subject=/CN=email-smtp.us-east-1.amazonaws.com issuer=/C=US/O=Amazon/OU=Server CA 1B/CN=Amazon --- No client certificate CA names sent Peer signing digest: SHA512 Server Temp Key: ECDH, P-256, 256 bits --- SSL handshake has read 5292 bytes and written 466 bytes [/sourcecode]

For some reason the cert chain is shown twice in the output, though it has been truncated here for this article.

If you were to compare the chain displayed by openssl s_client you to the output from What’s My Chain Cert?] you would find that the difference is the openssl command displayed both the Root Certificate and the certificate for the smtp server itself. Neither of these are necessary, but you can choose to include them.

I chose to just copy all the text from the beginning of the first certificate to the end of the last certificate.

There was no need to remove the data between the certs.

[sourcecode language="bash"] Certificate chain 0 s:/CN=email-smtp.us-east-1.amazonaws.com i:/C=US/O=Amazon/OU=Server CA 1B/CN=Amazon -----BEGIN CERTIFICATE----- MIIEhTCCA22gAwIBAgIQBJSyqvZcwNT12er++XeOTTANBgkqhkiG9w0BAQsFADBG MQswCQYDVQQGEwJVUzEPMA0GA1UEChMGQW1hem9uMRUwEwYDVQQLEwxTZXJ2ZXIg ... 59vPr5KW7ySaNRB6nJHGDn2Z9j8Z3/VyVOEVqQdZe4O/Ui5GjLIAZHYcSNPYeehu VsyuLAOQ1xk4meTKCRlb/weWsKh/NEnfVqn3sF/tM+2MR7cwA130A4w= -----END CERTIFICATE----- [/sourcecode]

The file was saved as email-smtp-us-east-1.aws.openssl.crt, and a new Oracle wallet created.

[sourcecode language="bash"] orapki wallet create -wallet $ORACLE_HOME/owm/wallets/oracle/aws-ses-test orapki wallet add -wallet $ORACLE_HOME/owm/wallets/oracle/aws-ses -trusted_cert -cert email-smtp-us-east-1.aws.openssl.crt $ orapki wallet display -wallet $ORACLE_HOME/owm/wallets/oracle/aws-ses-test Oracle PKI Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Requested Certificates: User Certificates: Trusted Certificates: Subject: CN=Amazon,OU=Server CA 1B,O=Amazon,C=US [/sourcecode]

Both wallets were successfully used to send emails in subsequent tests.

Enable auto login on a wallet if you need to. The command is the same as for creating a wallet:

[sourcecode language="bash"] orapki wallet create -wallet $ORACLE_HOME/owm/wallets/oracle/aws-ses -auto_login [/sourcecode]

To disable auto login, remove the cwallet file.

In this case it would be $ORACLE_HOME/owm/wallets/oracle/aws-ses/cwallet.sso.

Network ACLs

It will be necessary to create an Oracle ACL for so we are allowed to access the mail server via UTL_SMTP.

Oracle Note _ 1209644.1_ has directions that may be followed to do create the ACL.

In this case I created a role SMTP, granted the ACL to the role, and granted the SMTP role to the DBA role.
(DBA by default cannot bypass the ACL restrictions)

[sourcecode language="sql"] create role smtp; grant smtp to dba; set serveroutput on begin dbms_network_acl_admin.append_host_ace ( host => 'email-smtp.us-east-1.amazonaws.com', lower_port => null, upper_port => null, ace => xs$ace_type( privilege_list => xs$name_list('smtp'), principal_name => 'SMTP', principal_type => xs_acl.ptype_db ) ); exception when others then dbms_output.put_line('Error while granting ACL :'|| sqlerrm); end; / [/sourcecode]

Send an email

At this time everything should be adequately configured to send an email.

The code

Not having much experience with SMTP administration, some things that might seem obvious to others took me a little time to figure out.

One of the issues was which port to use. SES states there are three ports that may be used; 25, 465 and 587.

As the instructions at https://docs.aws.amazon.com/ses/latest/DeveloperGuide/send-email-smtp-client-command-line.html recommend port 587, that is the port I will use.

Oracle Support has several notes on using utl_smtp; I found the following two notes helpful.

Basics on How to use UTL_SMTP (Doc ID 106513.1)
How to Send an Email Using SMTP over an SSL Connection (Doc ID 1323140.1)

The following code is an amalgamation of the code from these Oracle support notes, and slightly modified. The SES username and password are obfuscated, as are the email addresses.
This code is for prototype purposes only, and not suitable for production use.

Note: All credentials are fabricated. [sourcecode language="sql"] -- prototype for utl_smtp usage with Amazon AWS SES (Simple Email Service) -- Basics on How to use UTL_SMTP (Doc ID 106513.1) -- How to Send an Email Using SMTP over an SSL Connection (Doc ID 1323140.1) -- https://docs.aws.amazon.com/ses/latest/DeveloperGuide/send-using-smtp-programmatically.html set serveroutput on size unlimited set term on feed on DECLARE -- AWS SES Host mailhost VARCHAR2(64) := 'email-smtp.us-east-1.amazonaws.com'; -- email sender and recipient sender VARCHAR2(64) := 'jared@somedomain.com'; recipient VARCHAR2(64) := 'elmerfud@warnerbros.com'; -- SES API Username and Password for AWS SES user_name VARCHAR2(64) := 'ADFPI6SDHRED#SDFHQNQ'; user_pwd VARCHAR2(64) := 'RFskh4$&42hjkhdfSu$S1ObThsNdkPqEIRsd827dNy3zdTvbp'; -- structure/record returned for new connections mail_connection utl_smtp.connection; -- structure/record returned by several of the UTL_SMTP functions smtp_reply utl_smtp.reply; -- wallet created from output of openssl cmd -- openssl s_client -host email-smtp.us-east-1.amazonaws.com -port 587 -prexit -showcerts -crlf -starttls smtp wallet_loc VARCHAR2(100) := 'file:/u01/app/oracledb/product/12.2.0/db1/owm/wallets/oracle/aws-ses-test'; wallet_pwd VARCHAR2(20) := 'MyPassword'; procedure show_reply ( header_in varchar2, reply_rec_in utl_smtp.reply) is begin dbms_output.put_line('============ ' || header_in || ' ===============' ); dbms_output.put_line('Code: ' || to_char(reply_rec_in.code)); dbms_output.put_line('Text: ' || reply_rec_in.text); end; BEGIN -- Make a secure connection using the SSL port configured with your SMTP server -- Note: The sample code here uses the default of 465 but check your SMTP server settings begin mail_connection := utl_smtp.open_connection ( host => mailhost, -- port must be one of 25, 465 or 587 -- I do not yet know the difference between ports -- so far only 587 works with what has been configured port => 587, wallet_path => wallet_loc, wallet_password => wallet_pwd, secure_connection_before_smtp => FALSE ); exception when others then dbms_output.put_line('Error in creating connection: '|| sqlerrm); raise; end; begin show_reply('Starting TLS', utl_smtp.starttls(mail_connection)); exception when others then dbms_output.put_line('Error in starting TLS: '|| sqlerrm); raise; end; -- Call the Auth procedure to authorized a user for access to the mail server -- Schemes should be set appropriatelty for your mail server -- See the UTL_SMTP documentation for a list of constants and meanings begin show_reply ( 'Authorizing', UTL_SMTP.AUTH( c => mail_connection, username => user_name, password => user_pwd, schemes => 'LOGIN' ) ); exception when others then dbms_output.put_line('Error in authentication: '|| sqlerrm); raise; end; begin -- Set up and make the the basic smtp calls to send a test email show_reply( 'Helo', utl_smtp.helo(mail_connection, mailhost)); show_reply( 'Mail', utl_smtp.mail(mail_connection, sender)); show_reply( 'Rcpt', utl_smtp.rcpt(mail_connection, recipient)); show_reply( 'Open Data', utl_smtp.open_data(mail_connection)); utl_smtp.write_data(mail_connection, 'This is a test message using SSL with SMTP.' || chr(13)); utl_smtp.write_data(mail_connection, 'This test requires an Oracle Wallet be properly configured.' || chr(13)); show_reply( 'Close Data', utl_smtp.close_data(mail_connection)); show_reply( 'Quit', utl_smtp.quit(mail_connection)); exception when others then dbms_output.put_line('Error in sending: '|| sqlerrm); raise; end; exception when others then dbms_output.put_line('Error in the anonymous plsql block: '|| sqlerrm); end; / [/sourcecode]

Here is the outout from sending a single email:

[sourcecode language="sql"] ============ Starting TLS =============== Code: 220 Text: Ready to start TLS ============ Authorizing =============== Code: 235 Text: Authentication successful. ============ Helo =============== Code: 250 Text: email-smtp.amazonaws.com ============ Mail =============== Code: 250 Text: Ok ============ Rcpt =============== Code: 250 Text: Ok ============ Open Data =============== Code: 354 Text: End data with <CR><LF>.<CR><LF> ============ Close Data =============== Code: 250 Text: Ok 0100016684a64fdd-d138c4bf-6357-41d7-ad05-12636097c355-000000 ============ Quit =============== Code: 221 Text: Bye [/sourcecode]

The following references are helpful in recognizing a normal SMTP conversation

https://docs.aws.amazon.com/ses/latest/DeveloperGuide/send-email-smtp-client-command-line.html
[The SMTP Protocol]

For some as yet unknown reason, this can be quite slow, with runtimes varying between 1.7 and 30 seconds. This may be due to still running in the SES Sandbox.

In my initial tests a year ago, the emails would appear in my inbox shortly after sending them via this PL/SQL program and AWS SES.

During current testing however I have yet to receive an email.

The AWS console shows that several emails have been sent, and that none are being bounced.

As per the [Delivery Problems] documentation, there are many possible causes of this.

Following is the email received in previous testing.

[sourcecode language="bash"] Test email from Amazon SES 2017-09-20 19:54:10 This is a test message using SSL with SMTP. This test requires an Oracle Wallet be properly configured. Message-ID: &lt;0100015ea1b692b1-4a2640ae-3b9b-44ca-8b9c-3d21c7fc87db-000000@email.amazonses.com&gt; Date: Wed, 20 Sep 2017 23:54:10 +0000 X-SES-Outgoing: 2017.09.20-54.240.8.77 Feedback-ID: 1.us-east-1.8u4uk618KvmcX7PFgcf5RV8XdAP6EJBn2CuNLBx6me4=:AmazonSES [/sourcecode]

Maybe I should print this email, frame it and hang it on the wall, as getting to this point was a bit of a slog.

(Now I think I will hold off on that for an email from the new configuration )

If you have need for sending emails directly from an Oracle database, and do not have an available email server, SES may just be the answer you are looking for.

Insight and analysis of technology and business strategy