Sending secure E-Mails out of the database: SSL/TLS + UTL_SMTP + OpenSSL + ACL + Wallet

Last year I got an interesting task to do – “Sending secure E-Mails out of the database“. Additionally it should be also possible to send attachments larger than 32k! After I spent a couple of days to develop it and lost many hours to troubleshoot some errors, I would like to share some details – hope it will help someone.

Ingredients

So, what do we need for this task:

• Oracle database version should be at least 11.2.0.2 , because UTL_SMTP got STARTTLS and AUTH procedures first in that version
• SMTP Server which supports SSL/TLS
• SSL certificates
• Oracle Wallet
• ACL (Access Control List) privileges

SMTP server and SSL/TLS

Hostname: bsmtp.telekom.at
Port: 587
SSL/TLS: enabled
Login: noreply@oracle.ninja (real e-mail address changed for this article)
Password: dummy123 (this is a real password! )

After I got details about SMTP server, I tried to verify if the login is working and if SSL is enabled/supported. To use a secure SMTP, the credential (login and password) have to be encoded in a base64 encoding. The simplest solution is:

select UTL_RAW.cast_to_varchar2 (
          UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw ('noreply@oracle.ninja'))) as username,
       UTL_RAW.cast_to_varchar2 (
          UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw ('dummy123'))) as password
  from dual;

  USERNAME                  PASSWORD
  ------------------------  ------------
  ZGVqYW5Ab3JhY2xlLm5pbmph  ZHVtbXkxMjM=

I had also to find out which SSL certificates this SMTP server uses and thereafter to import them into Oracle Wallet on the database server.  Basic telnet does not support SSL or TLS, so I had to use openssl or stunnel to verify it.

Here is the output of the openssl session:

[oracle@node1 ~]$ openssl s_client -connect bsmtp.telekom.at:587 -starttls smtp
CONNECTED(00000003)
depth=3 /C=ZA/ST=Western Cape/L=Cape Town/O=Thawte Consulting cc/OU=Certification Services Division/CN=Thawte Premium Server CA/emailAddress=premium-server@thawte.com
verify return:1
depth=2 /C=US/O=thawte, Inc./OU=Certification Services Division/OU=(c) 2006 thawte, Inc. - For authorized use only/CN=thawte Primary Root CA
verify return:1
depth=1 /C=US/O=Thawte, Inc./CN=Thawte SSL CA
verify return:1
depth=0 /C=AT/ST=Ober\xC3\xB6sterreich/L=Linz/O=A1 Telekom Austria AG/OU=ISP/CN=bsmtp.telekom.at
verify return:1
---
Certificate chain
 0 s:/C=AT/ST=Ober\xC3\xB6sterreich/L=Linz/O=A1 Telekom Austria AG/OU=ISP/CN=bsmtp.telekom.at
   i:/C=US/O=Thawte, Inc./CN=Thawte SSL CA
 1 s:/C=US/O=Thawte, Inc./CN=Thawte SSL CA
   i:/C=US/O=thawte, Inc./OU=Certification Services Division/OU=(c) 2006 thawte, Inc. - For authorized use only/CN=thawte Primary Root CA
 2 s:/C=US/O=thawte, Inc./OU=Certification Services Division/OU=(c) 2006 thawte, Inc. - For authorized use only/CN=thawte Primary Root CA
   i:/C=ZA/ST=Western Cape/L=Cape Town/O=Thawte Consulting cc/OU=Certification Services Division/CN=Thawte Premium Server CA/emailAddress=premium-server@thawte.com
 3 s:/C=ZA/ST=Western Cape/L=Cape Town/O=Thawte Consulting cc/OU=Certification Services Division/CN=Thawte Premium Server CA/emailAddress=premium-server@thawte.com
   i:/C=ZA/ST=Western Cape/L=Cape Town/O=Thawte Consulting cc/OU=Certification Services Division/CN=Thawte Premium Server CA/emailAddress=premium-server@thawte.com
---
Server certificate
-----BEGIN CERTIFICATE-----
MIID5jCCAs6gAwIBAgIQHXSLMb2opHvNrSgbp2UMOzANBgkqhkiG9w0BAQUFADA8
MQswCQYDVQQGEwJVUzEVMBMGA1UEChMMVGhhd3RlLCBJbmMuMRYwFAYDVQQDEw1U
aGF3dGUgU1NMIENBMB4XDTEyMDQyNjAwMDAwMFoXDTE0MDUyNjIzNTk1OVowfzEL
MAkGA1UEBhMCQVQxGDAWBgNVBAgMD09iZXLDtnN0ZXJyZWljaDENMAsGA1UEBwwE
TGluejEeMBwGA1UECgwVQTEgVGVsZWtvbSBBdXN0cmlhIEFHMQwwCgYDVQQLDANJ
U1AxGTAXBgNVBAMMEGJzbXRwLnRlbGVrb20uYXQwggEiMA0GCSqGSIb3DQEBAQUA
A4IBDwAwggEKAoIBAQDfygYXq0RXDfZQ67im/wpDz8VJU8F7lHc9HBvs0keG2XS5
BlWAdGu5fePG8dMh4+dwbEnHgcZBgPtnk2jXUjDnl+egU7mvde1XMD0K0mgIBvSY
wdn1b2NelYPpi+7EDw+ffsAccfAQ0gbxUntaJzDYGH+Z2YbvRXLg/dizzq/Aj6cp
afxEneVQ2K/0N8yCdoF0SPgMUIyjtTGbJUvjbspWvuTdCTrb8iBjdOx6vxj2Mu6E
1moU3kYFpTLrE267dvnGK1TirWVasOmdPhr2Qn7e9y6xLByJPjqclzvrk19SB/i5
GkaWTIsjaKgPEi3H67bgpUGUcZjpwLFDyiAHsJVTAgMBAAGjgaAwgZ0wDAYDVR0T
AQH/BAIwADA6BgNVHR8EMzAxMC+gLaArhilodHRwOi8vc3ZyLW92LWNybC50aGF3
dGUuY29tL1RoYXd0ZU9WLmNybDAdBgNVHSUEFjAUBggrBgEFBQcDAQYIKwYBBQUH
AwIwMgYIKwYBBQUHAQEEJjAkMCIGCCsGAQUFBzABhhZodHRwOi8vb2NzcC50aGF3
dGUuY29tMA0GCSqGSIb3DQEBBQUAA4IBAQAFh5lsfUnEePCnN27OHbZZK6mQ7WVw
v6zNw31BcHcJ8COusjhKB6MVwO4yCH9f7txz7JSxtE47IOdDqh/ZTp0GJhkFYOLQ
cBCW7yyORXL9dlNzeNw44XukMNatfxWaiFhbegTEbkHnwgWhhA/Iea+v1iUTGhm9
0olCv72MNykOLEd9dmHeOZ+TalTV0csZ/jjoE7vAgBoPgo3DhRXGTfg8AkqsOxh7
cJWa1N1pDwG8NB7/vHAjqPOuTqWk7qCioYi3vIWStSQf0v2ocUu6ILXvOgfrgys2
zaT3uK2kLYY1KVpXfdyHRHaNEbDHZHPEBouwK21LrMjEMdaLAF9gZFWW
-----END CERTIFICATE-----
subject=/C=AT/ST=Ober\xC3\xB6sterreich/L=Linz/O=A1 Telekom Austria AG/OU=ISP/CN=bsmtp.telekom.at
issuer=/C=US/O=Thawte, Inc./CN=Thawte SSL CA
---
No client certificate CA names sent
---
SSL handshake has read 4922 bytes and written 351 bytes
---
New, TLSv1/SSLv3, Cipher is DHE-RSA-AES256-SHA
Server public key is 2048 bit
Compression: NONE
Expansion: NONE
SSL-Session:
    Protocol  : TLSv1
    Cipher    : DHE-RSA-AES256-SHA
    Session-ID: EE44ACAED8B90763A42ED950830341C5298F4A058D41DF1A1D0E569E0E0BFBE5
    Session-ID-ctx: 
    Master-Key: 080AB13DA54C0E598D475E3DBFC5FCC710364C071E51EA95EF3AD079944647DF417623A9FA3433037F54402186BA83F7
    Key-Arg   : None
    Krb5 Principal: None
    Start Time: 1367960155
    Timeout   : 300 (sec)
    Verify return code: 0 (ok)
---
250 8BITMIME
EHLO Dejan
250-bsmtp.bon.at
250-PIPELINING
250-SIZE 104857600
250-ETRN
250-AUTH PLAIN LOGIN
250-AUTH=PLAIN LOGIN
250-ENHANCEDSTATUSCODES
250 8BITMIME
STARTTLS
554 5.5.1 Error: TLS already active
AUTH LOGIN
334 VXNlcm5hbWU6
ZGVqYW5Ab3JhY2xlLm5pbmph
334 UGFzc3dvcmQ6
ZHVtbXkxMjM=
235 2.0.0 Authentication successful
mail from: noreply@oracle.ninja
250 2.1.0 Ok
rcpt to: shadow@oracle.ninja
250 2.1.5 Ok
data
354 End data with <CR><LF>.<CR><LF>
Subject: dummy message
test message lorem ipsum
.
250 2.0.0 Ok: queued as D44B2A7EB3
quit
221 2.0.0 Bye
read:errno=0

 

SSL certificates & Oracle Wallet

In the output above, there are 4 certificates, which needs to be imported into Oracle Wallet on the database server.

– Download Thawte certificates:

 “Thawte Primary Root CA“:  https://www.thawte.com/roots/thawte_Primary_Root_CA.pem
“Thawte Premium Server CA“: https://www.thawte.com/roots/thawte_Premium_Server_CA.pem
“Thawte Server CA“: https://www.thawte.com/roots/thawte_Premium_Server_CA.pem

– Create a new Wallet or use an existing one:

[oracle@node1 ~]$ owm

– Import all Thawte certificates:

1. start the Oracle Wallet Manager
2. select a wallet
3. go to “Operations” -> “Import Trusted Certificate…“
4. choose the option “Select a file that contains the certificate“
5. select one of the .pem files downloaded
6. repeat the steps 3-5 for other .pem files

– Import the certficate for the SMTP server:

1. start the Oracle Wallet Manager
2. select a wallet
3. go to “Operations” -> “Import Trusted Certificate…“
4. choose the option “Paste the certificate“
5. paste the base64 encoded certificate from the openssl session above (the lines between BEGIN CERTIFICATE and END CERTIFICATE)

Now are all certificates imported into wallet and we can use it to establish a secure connection.

ACL privileges

Of course, if you don’t create an ACL, you’ll get following error:

ORA-24247: network access denied by access control list (ACL)

So you can check the ACLs:

column acl format a30
column host format a20
column principal format a20
column privilege format a10
column is_grant format a8
set lines 1000

select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

If you want, you can drop an existing ACL:

begin
  DBMS_NETWORK_ACL_ADMIN.DROP_ACL('ninjasmtp.xml');
end;
/

Let’s create it:

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'ninjasmtp.xml',
    description  => 'Permissions to access SMTP Server',
    principal    => 'NINJA',
    is_grant     => TRUE,
    privilege    => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl          => 'ninjasmtp.xml',
    principal    => 'NINJA',
    is_grant     => TRUE, 
    privilege    => 'resolve',
    position     => null);

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'ninjasmtp.xml',
    host         => 'bsmtp.telekom.at',
    lower_port    => 1,
    upper_port    => 1024);
   COMMIT;
END;
/

After ACL has been created and connect/resolve privileges granted, the next step is to add two additional privileges to this ACL:

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

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

You’ll maybe (or better to say – for sure!) get this error message:

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

It’s a bug!

[ID 11877463.8] Bug 11877463 – ORA-19279 from DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE of a privilege to an ACL
[ID 1336938.1] Adding a third privilege to an access control entry ACE with two existing privileges in an access control list (ACL) fails with ORA-19279
[ID 1464559.1] DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE failing with an ORA-19279

I lost many hours to solve this problem. This is the solution that worked for me:

-- show current ACL:
set lines 82 long 9999 pages 1000
select XDBUriType('/sys/acls/ninjasmtp.xml').getXML() from dual;

SELECT a.OBJECT_VALUE FROM RESOURCE_VIEW r, XDB.XDB$ACL a
  WHERE ref(a) = extractValue(r.RES, '/Resource/XMLRef')
    AND equals_path(r.RES, '/sys/acls/ninjasmtp.xml') = 1;
    
    
<a:acl description="Permissions to access SMTP Server" xmlns:a="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:plsql="http:/
/xmlns.oracle.com/plsql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.c
om/xdb/acl.xsd                                    http://xmlns.oracle.com/xdb/acl.xsd" shared="true">
  <a:security-class>plsql:network</a:security-class>
  <a:ace>
    <a:grant>true</a:grant>
    <a:principal>NINJA</a:principal>
    <a:privilege>
      <plsql:connect xmlns:plsql="http://xmlns.oracle.com/plsql"/>
      <plsql:resolve xmlns:plsql="http://xmlns.oracle.com/plsql"/>
    </a:privilege>
  </a:ace>
</a:acl>

-- update it with 2 additional privileges:

UPDATE RESOURCE_VIEW r
  SET r.RES =
        updateXML(
          r.RES,
          '/r:Resource/r:Contents/a:acl',
          '<a:acl description="Permissions to access SMTP Server" xmlns:a="http://xmlns.oracle.com/xdb/acl.xsd" 
            xmlns:plsql="http://xmlns.oracle.com/plsql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd http://xmlns.oracle.com/xdb/acl.xsd" shared="true">
              <a:security-class>plsql:network</a:security-class>
              <a:ace>
                <a:grant>true</a:grant>
                <a:principal>NINJA</a:principal>
                <a:privilege>
                  <plsql:connect xmlns:plsql="http://xmlns.oracle.com/plsql"/>
                  <plsql:resolve xmlns:plsql="http://xmlns.oracle.com/plsql"/>
                  <plsql:use-client-certificates xmlns:plsql="http://xmlns.oracle.com/plsql"/>
                  <plsql:use-passwords xmlns:plsql="http://xmlns.oracle.com/plsql"/>
                </a:privilege>
              </a:ace>
            </a:acl>',
          'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"
           xmlns:a="http://xmlns.oracle.com/xdb/acl.xsd"')
  WHERE equals_path(r.RES, '/sys/acls/ninjasmtp.xml') = 1;

commit;

-- assign the wallet with all imported certificates:
BEGIN 
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL(
    acl         => 'ninjasmtp.xml',
    wallet_path => 'file:/oracle/wallets/test_wallet/');
  COMMIT;
END;
/

-- check if it works:
select utl_http.request('https://support.oracle.com/', null, 'file:/oracle/wallets/test_wallet/', 'dummy123') from dual;

select dbms_network_acl_admin.check_privilege('ninjasmtp.xml', 'NINJA', 'use-client-certificates') from dual;

Final test

If everything is ready and configured, this pl/sql block should send a test email:

DECLARE
  c utl_smtp.connection;
  l_mailhost    VARCHAR2 (64) := 'bsmtp.telekom.at';
  l_from        VARCHAR2 (64) := 'noreply@oracle.ninja';
  l_to          VARCHAR2 (64) := 'shadow@oracle.ninja';
  l_subject     VARCHAR2 (64) := 'Test message';
  crlf varchar2(2) := UTL_TCP.CRLF;
BEGIN
  c := utl_smtp.open_connection(
            host => l_mailhost,
            port => 587,
            wallet_path => 'file:/oracle/wallets/test_wallet/',
            wallet_password => 'dummy123',
            secure_connection_before_smtp => FALSE);

  UTL_SMTP.STARTTLS(c);
  UTL_SMTP.EHLO(c, 'oracle.ninja');

  utl_smtp.command( c, 'AUTH LOGIN');
  utl_smtp.command( c, 'ZGVqYW5Ab3JhY2xlLm5pbmph');
  utl_smtp.command( c, 'ZHVtbXkxMjM=');  
 
  UTL_SMTP.mail (c, l_from);
  UTL_SMTP.rcpt (c, l_to);
  UTL_SMTP.open_data (c);
  UTL_SMTP.write_data (c, 'Date: ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || crlf);
  UTL_SMTP.write_data (c, 'From: ' || l_from || crlf);
  UTL_SMTP.write_data (c, 'Subject: ' || l_subject || crlf);
  UTL_SMTP.write_data (c, 'To: ' || l_to || crlf);
  UTL_SMTP.write_data (c, '' || crlf);

  FOR i IN 1 .. 10
  LOOP
    UTL_SMTP.write_data (c, 'This is a test message. Line ' || TO_CHAR (i) || crlf);
  END LOOP;

  UTL_SMTP.close_data (c);
  UTL_SMTP.quit (c); 
END;
/

UTL_MAIL is nice and simple, but not so powerful for this task – it cannot send attachments larger than 32k and does not support TLS. So in this case I decided to create my own solution and to use UTL_SMTP package. Here is a error message you’ll get if you use the UTL_MAIL package:

ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first

I developed also a powerful pl/sql package to send emails out of the database – including the sending of secure emails.

 

Troubleshooting

Error message: ORA-29024: Certificate validation failure
Cause: There is a problem with the Oracle Wallet configuration. Probably some certificate is missing or has been improperly imported into wallet.
Solution: Check the wallet configuration and all certificates. Try to remove and import trusted certificates again.

 

Error message: ORA-19279: XPTY0004 – XQuery dynamic type mismatch: expected singleton sequence – got multi-item sequence
Cause: Oracle bug. See the explanation above.
Solution: See the solution above.

 

Error message: ORA-29278: SMTP transient error: 421 Service not available
Cause: Some ACL privilege is missing.
Solution: Check the ACL privileges, i.e. ‘resolve’, ‘connect’ etc.

 

Useful links

http://oracleblues.blogspot.in/2010/09/11g-release-2-11202-new-utlsmtp.html
http://arkatec.wordpress.com/2011/08/15/sending-email-using-oracle-database-and-google-mail-service/
http://gerardnico.com/wiki/smtp_telnet
http://www.experts-exchange.com/Database/Oracle/A_9074-How-to-use-Network-Access-Control-Lists-in-Oracle.html
http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb21sec.htm