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

Posted in: Oracle, Technical Track

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.

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.
Using instructions found at send-email-smtp-client-command-line.html an openssl command was used to discover what certificates are needed.

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

$ openssl s_client -crlf -connect email-smtp.us-east-1.amazonaws.com:465
 CONNECTED(00000003)
 depth=2 C = US, O = "VeriSign, Inc.", OU = VeriSign Trust Network, OU = "(c) 2006 VeriSign, Inc. - For authorized use only", CN = VeriSign Class 3 Public Primary Certification Authority - G5
 verify return:1
 depth=1 C = US, O = Symantec Corporation, OU = Symantec Trust Network, CN = Symantec Class 3 Secure Server CA - G4
 verify return:1
 depth=0 C = US, ST = Washington, L = Seattle, O = "Amazon.com, Inc.", CN = email-smtp.us-east-1.amazonaws.com
 verify return:1
 ---
 Certificate chain
 0 s:/C=US/ST=Washington/L=Seattle/O=Amazon.com, Inc./CN=email-smtp.us-east-1.amazonaws.com
 i:/C=US/O=Symantec Corporation/OU=Symantec Trust Network/CN=Symantec Class 3 Secure Server CA - G4
 1 s:/C=US/O=Symantec Corporation/OU=Symantec Trust Network/CN=Symantec Class 3 Secure Server CA - G4
 i:/C=US/O=VeriSign, Inc./OU=VeriSign Trust Network/OU=(c) 2006 VeriSign, Inc. - For authorized use only/CN=VeriSign Class 3 Public Primary Certification Authority - G5
 ---
 Server certificate
 -----BEGIN CERTIFICATE-----
 MIIFmzCCBIOgAwIBAgIQNJDTuh3YVKI7lsyeFGe3eDANBgkqhkiG9w0BAQsFADB+
 MQswCQYDVQQGEwJVUzEdMBsGA1UEChMUU3ltYW50ZWMgQ29ycG9yYXRpb24xHzAd
 BgNVBAsTFlN5bWFudGVjIFRydXN0IE5ldHdvcmsxLzAtBgNVBAMTJlN5bWFudGVj
 IENsYXNzIDMgU2VjdXJlIFNlcnZlciBDQSAtIEc0MB4XDTE3MDUwMzAwMDAwMFoX
 DTE4MDUyMTIzNTk1OVowfDELMAkGA1UEBhMCVVMxEzARBgNVBAgMCldhc2hpbmd0
 b24xEDAOBgNVBAcMB1NlYXR0bGUxGTAXBgNVBAoMEEFtYXpvbi5jb20sIEluYy4x
 KzApBgNVBAMMImVtYWlsLXNtdHAudXMtZWFzdC0xLmFtYXpvbmF3cy5jb20wggEi
 MA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDpnFs/tdZEnRFL56f7Nw58jED7
 LfdsrTGeqnrMfK7oRznGB0/SjjV0UDLfkdfODZTORavlqKVMOed7XTDFxxPErmHE
 6l1BHILMBF6wEAznwhHlALuS4kgeBdFZWCG4hw+kmBVYhHhBJlmu4dclm2zM592k
 kwzU13NlwS846JF8iFDVDo6HvabDN5Z8TF+2a15sLVGeZcINwh6pGSLQFx/DmW+h
 2wxm4LUloLtaL1LsEKdoXL0K0Tm3cmXox4j8vW2JERi0Uvp9cuBnCm1QAmL1Vaw2
 YC4ajhCN0AUnXs+9B++cYTc4SppyLKWUUODHyC/p/Z39OHUHYUSRkrrSRy7ZAgMB
 AAGjggIVMIICETAtBgNVHREEJjAkgiJlbWFpbC1zbXRwLnVzLWVhc3QtMS5hbWF6
 b25hd3MuY29tMAkGA1UdEwQCMAAwDgYDVR0PAQH/BAQDAgWgMB0GA1UdJQQWMBQG
 CCsGAQUFBwMBBggrBgEFBQcDAjBhBgNVHSAEWjBYMFYGBmeBDAECAjBMMCMGCCsG
 AQUFBwIBFhdodHRwczovL2Quc3ltY2IuY29tL2NwczAlBggrBgEFBQcCAjAZDBdo
 dHRwczovL2Quc3ltY2IuY29tL3JwYTAfBgNVHSMEGDAWgBRfYM9hkFXfhEMUimAq
 svV69EMY7zArBgNVHR8EJDAiMCCgHqAchhpodHRwOi8vc3Muc3ltY2IuY29tL3Nz
 LmNybDBXBggrBgEFBQcBAQRLMEkwHwYIKwYBBQUHMAGGE2h0dHA6Ly9zcy5zeW1j
 ZC5jb20wJgYIKwYBBQUHMAKGGmh0dHA6Ly9zcy5zeW1jYi5jb20vc3MuY3J0MA8G
 AytlTQQIMAYCAQICAQIwgYoGCisGAQQB1nkCBAIEfAR6AHgAdgCnzkpOYgfgrd7l
 /apLH4Z2h2e10AKlXUcxDn5nCpXqsgAAAVvO5xOXAAAEAwBHMEUCIQDAj8IeToMZ
 2cM+0Vyz3xbqXNQlK4yjTBHmwdTymn1F2gIgdTr534zZSqm5XG3U5Egublm6gMvT
 eR5GK6waYVcL5+MwDQYJKoZIhvcNAQELBQADggEBACOZ6nkhyqZ6Gat48ODSX66A
 V8zVf09/Ev8PKPFLtR1atG5bMKsB/fGR9pv2oSwz9s8uS5kePm4z6YPWc8HtOF83
 X/+pz+FVungbWUHMUz5cy0vlCMpBeGtdO5hvcpA03QQLYhnAZ+mZm7yuCpLzjvSw
 v31uIvTDIohbc2+VhiLm8jM1zybp0r0bZ2/PBrrdjgaJ0nWj+Jf2EHU5DPB5SHQI
 pvNArjCvlH6kFP3r22j/UPrmIXM5MlbYMDqKqb6ICjWtOtpZO97fLvV9xEHjzOwx
 baYnSkAPPxtEfZAQgOc5JWensEPzDqwW1OiN7V2J2qmn9/msWx9BSogUMp+Sg+Q=
 -----END CERTIFICATE-----
 ...

The bits we are interested in are the Certificate chain and the Server Certificate. Save everything from BEGIN CERTIFICATE through END CERTIFICATE to a file, email-smtp.us-east-1.pem in this case.

Using google the Verisign certificate was located at Symantec SHA-2 (under SHA-1 Root) Intermediate CA Certificate

Copy and paste the certificate text and save as Verisign-G4.pem.

Check to ensure that CRLF is not in the file. Fix it with dos2unix or similar if CRLF is found in the file.

Symantec 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, 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.

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 Verisign-G4.pem
orapki wallet add -wallet $ORACLE_HOME/owm/wallets/oracle/aws-ses -trusted_cert -cert email-smtp.us-east-1.pem
orapki wallet display -wallet $ORACLE_HOME/owm/wallets/oracle/aws-ses

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

orapki wallet create -wallet $ORACLE_HOME/owm/wallets/oracle/aws-ses -auto_login

To disable auto login, remove the cwallet file.

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

Send an email

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

Note: I found a couple of references in Oracle Support stating that it would be necessary to configure Oracle ACLs so that UTL_SMTP could be used.
While I have seen this issue occur in the past, I did not configure any ACLs for this test, and everything did work.

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.

I could not find an explanation though of which ports to use under which circumstances. I settled on 587 as it was the only port with which I had any success.

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.

set serveroutput on size unlimited
set term on feed on

DECLARE
  mailhost        VARCHAR2(64) := 'email-smtp.us-east-1.amazonaws.com';
  sender          VARCHAR2(64) := 'jared@somedomain.com';
  recipient       VARCHAR2(64) := 'elmerfud@warnerbros.com';
  user_name       VARCHAR2(64) := 'ADFPI6SDHRED#SDFHQNQ';
  user_pwd        VARCHAR2(64) := 'RFskh4$&42hjkhdfSu$S1ObThsNdkPqEIRsd827dNy3zdTvbp';
  mail_connection utl_smtp.connection;
  wallet_loc      VARCHAR2(100) := 'file:/u01/cdbrac/app/oracle/product/12.1.0.2/db_1/owm/wallets/oracle/aws-ses';
  wallet_pwd      VARCHAR2(20) := 'MyPassord';
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

  mail_connection := utl_smtp.open_connection
  (
    host                          => mailhost,
    -- port must be one of 25, 465 or 587
    port                          => 587,
    wallet_path                   => wallet_loc,
    wallet_password               => wallet_pwd,
    secure_connection_before_smtp => FALSE
  );

  utl_smtp.starttls(mail_connection);
  -- 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

  UTL_SMTP.AUTH(
    c        => mail_connection,
    username => user_name,
    password => user_pwd,
    schemes  => 'LOGIN'
  );

  -- Set up and make the the basic smtp calls to send a test email
  utl_smtp.helo(mail_connection, mailhost);
  utl_smtp.mail(mail_connection, sender);
  utl_smtp.rcpt(mail_connection, recipient);
  utl_smtp.open_data(mail_connection);
  utl_smtp.write_data(mail_connection, 'From: ' || sender || UTL_TCP.CRLF);
  utl_smtp.write_data(mail_connection, 'To: ' || recipient || UTL_TCP.CRLF);
  utl_smtp.write_data(mail_connection, 'Subject: Test email from Amazon SES ' || to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') || UTL_TCP.CRLF);
  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.' || UTL_TCP.CRLF );
  utl_smtp.close_data(mail_connection);
  utl_smtp.quit(mail_connection);
exception when others then
  dbms_output.put_line('Error in the anonymous plsql block: '|| sqlerrm);
end;

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 any case, an email was received, and here it is:

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: <0100015ea1b692b1-4a2640ae-3b9b-44ca-8b9c-3d21c7fc87db-000000@email.amazonses.com>
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

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.

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.

 

email

Interested in working with Jared? Schedule a tech call.

About the Author

Oracle experience: started with Oracle 7.0.13
Programming Experience: Perl, PL/SQL, Shell, SQL Also some other odds and ends that are no longer useful
Systems: Networking, Storage, OS to varying degrees.
Have fond memories of DG/UX

1 Comment. Leave new

It occurred to me the reason it was not necessary to configure an ACL was due to using a DBA account to run the test.
As this was just a prototype to get the email portion, I will not deal with that here. It could be a topic for an article by itself.

For the record, this is what will happen is you try this with a non-DBA account:

09:38:26 – scott@ SQL> @p1
Error in the anonymous plsql block: ORA-24247: network access denied by access control list (ACL)

PL/SQL procedure successfully completed.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *