Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

How to Authenticate to an SMTP server with Oracle PL/SQL

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Published:
How to Authenticate to an SMTP server with Oracle PL/SQL
By Sean D. Stuber
Up to date through Oracle version 11gR2

Part II: How to Send Email Attachments with Oracle

Oracle's UTL_SMTP package introduced in version 8i through version 11.2.0.1 (as of this writing, the latest version available on OTN for most platforms)  offers no built in method to authenticate to a server.  When UTL_MAIL was introduced in 10g the functionality hole was still left unaddressed.  Frequently this is not a problem within a business because your company's internal SMTP server will often let internal connections send outbound traffic without additional authentication; relying on firewalls or other configuration to prevent external connections from abusing the server. The fact that the request is "internal" is authentic enough for many places and Oracle's packages were apparently designed with this assumption in place.

However, this is not always the case.  If your internal server requires authentication or if you must connect directly to your ISP's server or if you must use some other third-party service  then you will need to authenticate your identity to the server in order to send mail.  Herein, I will show how to extend the functionality of UTL_SMTP to allow for common authentication mechanisms.  Alas, UTL_MAIL, due to its over-simplicity is not able to be extended in this way and I suggest converting to UTL_SMTP-based functionality.

Surprisingly, the original SMTP protocol provided no means of authenticating; but with the rise of spam engines and malicious users it quickly became a necessary component and was later added with addendum RFC's.  SMTP authentication can be of multiple forms, some more secure than others.  Discussing the implementations of all possibilities is beyond the scope of this article and some aren't possible to replicate solely within the UTL_SMTP API; but I will show a few of the more common methods.  

Initiating the Communication

Since authentication is an extension to base SMTP functionality, you will initiate communication with the EHLO (extended hello) command rather than the standard HELO command.

When the server reads an EHLO command its response should include the types of authentication supported, if any.
The response might look something like this:

250-AUTH LOGIN PLAIN CRAM-MD5 CRAM-SHA1
                      250-STARTTLS

Open in new window


This would indicate the server supports 4 methods of SMTP authentication as well as the more secure SSL/TLS protocol.

First, let's look at the SMTP authentication methods.   All of these include Base-64 Encoding, as part of their algorithms.
It is important to note, Base-64 Encoding is NOT ecryption. It's simply a text conversion to a subset of ASCII for purposes of ensuring correct processing regardless of character sets.  Encoding is unkeyed and completely reversible.  As such it should be considered "plain text" for purposes of security evaluation of the PLAIN and LOGIN methods.

AUTH PLAIN

With this method the client sends the authenticating id, the user id and the password as a single Base-64-encoded, NULL-delimited (ASCII-0) string.  For most email servers, the authenticating id and the user id will be the same.
If they differ, the details should be provided by your server as to what the required id's are.

With PLAIN, there is no prompting or challenge, you simply send an authentication command and the server will process it.  The syntax is a single line, and might look something like this...

AUTH PLAIN bXlfdXNlcl9uYW1lAG15X3VzZXJfbmFtZQBteV9zZWNyZXRfcGFzc3dvcmQ=

Open in new window


which would look like this after decoding with "|" representing the NULL character - chr(0)

AUTH PLAIN my_user_name|my_user_name|my_secret_password

Open in new window


To replicate this functionality in PL/SQL, you simply send these strings with the UTL_SMTP.COMMAND procedure after connecting.
    v_plain_string :=
                               UTL_RAW.cast_to_varchar2(
                                  UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('my_user_name'||chr(0)||'my_user_name'||chr(0)||'my_secret_password'))
                              );
                          
                          v_connection := UTL_SMTP.open_connection(:v_smtp_server);
                          UTL_SMTP.ehlo(v_connection, 'mydomain.com');    -- Must use EHLO  vs HELO
                          UTL_SMTP.command(v_connection, 'AUTH', 'PLAIN ' || v_plain_string);

Open in new window


AUTH LOGIN

Another authentication method, LOGIN, will prompt you for username and password responses.  The prompts will be Base-64 encoded and the responses are expected to be as well.
SMTP authentication via LOGIN method with a telnet session might look something like this...

AUTH LOGIN
                      334 VXNlcm5hbWU6
                      bXlfdXNlcl9uYW1l
                      334 UGFzc3dvcmQ6
                      bXlfc2VjcmV0X3Bhc3N3b3Jk
                      235 Authed. Go on.

Open in new window


Which would like this after decoding

AUTH LOGIN
                      334 Username:
                      my_user_name
                      334 Password:
                      my_secret_password
                      235 Authed. Go on.

Open in new window


To replicate this functionality in PL/SQL, you simply send these strings with the UTL_SMTP.COMMAND procedure after connecting.
As with AUTH PLAIN you initiate communication with the EHLO command and use UTL_SMTP.COMMAND to send the authentication instructions but rather than putting all the info on one line, the server will prompt you as shown above.
In this example, if the login, user or password messages aren't accepted, an error should be raised from the server which will then be re-raised by UTL_SMTP as an oracle UTL_SMTP exception.

    v_username_b64 :=
                              UTL_RAW.cast_to_varchar2(
                                  UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(:v_username))
                              );
                          v_password_b64 :=
                              UTL_RAW.cast_to_varchar2(
                                  UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(:v_password))
                              );
                          v_connection := UTL_SMTP.open_connection(:v_smtp_server);
                          UTL_SMTP.ehlo(v_connection, 'mydomain.com');    -- Must use EHLO  vs HELO
                          UTL_SMTP.command(v_connection, 'AUTH', 'LOGIN');  -- should receive a 334 response, prompting for username
                          UTL_SMTP.command(v_connection, v_username_b64);   -- should receive a 334 response, prompting for password
                          UTL_SMTP.command(v_connection, v_password_b64);   -- should receive a 235 response, you are authenticated

Open in new window



AUTH CRAM-MD5 (or CRAM-SHA1)

The Challenge-Response Authentication Mechanism (CRAM) with MD5 is defined in RFC2195 ( http://www.ietf.org/rfc/rfc2195.txt ) This is the first authentication method described that truly masks the password.  The details of the mechanism are known as Hash-based Message Authentication Code (HMAC defined in RFC2104) and are fairly convoluted; but the basic idea is the server will send a Base-64 encoded "challenge".  The client will then generate a hash of a shared, secret string (i.e. your password) with some bit manipulations and then rehash the hashed-password with the challenge and then finally append that to the username.  That entire string is then Base-64 encoded and sent to the server.  The server uses the same mechanism to generate the hash and compares to the hash the client sent.  If they match then the user is authenticated.  Note, while hashing is irreversible, it's still not cryptographically impervious.  MD5 is known to be less secure than SHA-1, so some servers may authenticate with CRAM-SHA1 instead of CRAM-MD5.  The process is identical except for the choice of hash algorithm.  RFC4954  ( http://www.ietf.org/rfc/rfc4954.txt ) illustrates use of CRAM-MD5 when applied to SMTP communication.

Using the Sample challenge, username and password from RFC2195 the CRAM-MD5 algorithm might look something like this...

The server sends challenge: "PDE4OTYuNjk3MTcwOTUyQHBvc3RvZmZpY2UucmVzdG9uLm1jaS5uZXQ+"   (quotes not included)
Which is "<1896.697170952@postoffice.reston.mci.net>" when decoded (quotes not included)


SELECT UTL_RAW.cast_to_varchar2(
                                 UTL_ENCODE.base64_encode(
                                     UTL_RAW.cast_to_raw(
                                         'tim '    -- username
                                         || LOWER(
                                                RAWTOHEX(
                                                    DBMS_CRYPTO.hash(
                                                        UTL_RAW.CONCAT(
                                                            UTL_RAW.bit_xor(secret, UTL_RAW.copies(HEXTORAW('5C'), 64)),
                                                            DBMS_CRYPTO.hash(
                                                                UTL_RAW.CONCAT(
                                                                    UTL_RAW.bit_xor(
                                                                        secret,
                                                                        UTL_RAW.copies(HEXTORAW('36'), 64)
                                                                    ),
                                                                    UTL_ENCODE.base64_decode(
                                                                        UTL_RAW.cast_to_raw(   -- challenge
                                                                            'PDE4OTYuNjk3MTcwOTUyQHBvc3RvZmZpY2UucmVzdG9uLm1jaS5uZXQ+'
                                                                        )
                                                                    )
                                                                ),
                                                                2 /* 2= MD5 */
                                                            )
                                                        ),
                                                        2  /* 2= MD5 */
                                                    )
                                                )
                                            )
                                     )
                                 )
                             )
                                 encoded_cram
                        FROM (SELECT UTL_RAW.overlay(
                                         UTL_RAW.cast_to_raw('tanstaaftanstaaf'),   -- password
                                         UTL_RAW.copies(HEXTORAW('00'), 64)
                                     )
                                         secret
                                FROM DUAL);

Open in new window


The above is a bit hard to follow, and it doesn't take into account requirements for handling passwords over 64 bytes.  So, I have provided a function below that will encapsulate the full CRAM algoritm, including support for long passwords as well as the option to switch to CRAM-SHA1.  Using this function makes it much easier to generate the proper response to the challenge string.

SELECT sdscram(
                                 'tim',  --username
                                 'tanstaaftanstaaf',  -- password
                                 'PDE4OTYuNjk3MTcwOTUyQHBvc3RvZmZpY2UucmVzdG9uLm1jaS5uZXQ+' -- challenge from server
                             )
                        FROM DUAL;
                        
                      expected result:
                      
                      dGltIGI5MTNhNjAyYzdlZGE3YTQ5NWI0ZTZlNzMzNGQzODkw

Open in new window


CREATE OR REPLACE FUNCTION sdscram(p_username     IN VARCHAR2,
                                                         p_key          IN VARCHAR2,
                                                         p_challenge    IN VARCHAR2,
                                                         p_hashmethod   IN INTEGER DEFAULT DBMS_CRYPTO.hash_md5
                                                        )
                          RETURN VARCHAR2
                      IS
                          /*
                              Challenge-Response Authentication Mechanism
                              as in RFC 2195 
                      
                              http://www.ietf.org/rfc/rfc2195.txt
                      
                              PL/SQL implementation by Sean D. Stuber
                      
                              example usage with data shown in RFC 2195 doc
                      
                              select
                                  sdscram(
                                      'tim',
                                      'tanstaaftanstaaf',
                                      'PDE4OTYuNjk3MTcwOTUyQHBvc3RvZmZpY2UucmVzdG9uLm1jaS5uZXQ+'
                                  )
                              from dual;
                      
                              expected result:
                              dGltIGI5MTNhNjAyYzdlZGE3YTQ5NWI0ZTZlNzMzNGQzODkw
                      
                      
                      
                          */
                          c_blocksize   CONSTANT INTEGER := 64;
                          c_zeroblock   CONSTANT RAW(64) := UTL_RAW.copies(HEXTORAW('00'), c_blocksize);
                          c_outerpad    CONSTANT RAW(64) := UTL_RAW.copies(HEXTORAW('5C'), c_blocksize);
                          c_innerpad    CONSTANT RAW(64) := UTL_RAW.copies(HEXTORAW('36'), c_blocksize);
                      
                          v_outer                RAW(64);
                          v_inner                RAW(64);
                      
                          v_challenge            VARCHAR2(32767);
                          v_key                  RAW(32767) := UTL_RAW.cast_to_raw(p_key);
                      
                          v_hash                 VARCHAR2(64);
                      BEGIN
                          -- If the key is bigger than the block size (64) then hash it
                          -- which will reduce it to a shorter byte stream
                          IF UTL_RAW.LENGTH(v_key) > c_blocksize
                          THEN
                              v_key  := DBMS_CRYPTO.hash(v_key, p_hashmethod);
                          END IF;
                      
                          -- 0-pad the key to fill a block
                          IF UTL_RAW.LENGTH(v_key) < c_blocksize
                          THEN
                              v_key  := UTL_RAW.overlay(v_key, c_zeroblock);
                          END IF;
                      
                          -- The challenge will be a base64 encoded string
                          v_challenge  := UTL_ENCODE.base64_decode(UTL_RAW.cast_to_raw(p_challenge));
                      
                          v_outer      := UTL_RAW.bit_xor(v_key, c_outerpad);
                          v_inner      := UTL_RAW.bit_xor(v_key, c_innerpad);
                      
                          -- append the challenge to the key and hash it
                          v_inner      := DBMS_CRYPTO.hash(UTL_RAW.CONCAT(v_inner, v_challenge), p_hashmethod);
                      
                          -- append the new inner hash to the outer and hash again
                          -- return results as a string for use in next step
                          v_hash       :=
                              LOWER(RAWTOHEX(DBMS_CRYPTO.hash(UTL_RAW.CONCAT(v_outer, v_inner), p_hashmethod)));
                      
                          -- base64 encode the username with the hash, separated by a space
                          RETURN UTL_RAW.cast_to_varchar2(
                                     UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(p_username || ' ' || v_hash)));
                      END;

Open in new window


example use might look something like this

     v_connection := UTL_SMTP.open_connection(:v_smtp_server);
                           UTL_SMTP.ehlo(v_connection, 'mydomain.com'); -- Must use EHLO  vs HELO
                           v_reply := UTL_SMTP.command(v_connection, 'AUTH', 'CRAM-MD5');
                           IF v_reply.code = 334
                           THEN
                               v_cram_string := sdscram('tim', --username
                                                        'tanstaaftanstaaf', -- password
                                                        v_reply.text -- challenge from server
                                                       );
                             UTL_SMTP.command(v_connection, v_cram_string);
                            END IF;

Open in new window




Your SMTP server may support other means as well, such as POP before SMTP which is essentially a timing authentication.

First you must request to receive email via POP3 which has authentication built in to it and then from the same ip address within a short period ("short" being server defined) you can then send SMTP commands and it will allow access.  The POP3 protocol can't be implemented through UTL_SMTP, but could be implemented via UTL_TCP. Doing so is outside the scope of this article, but I may address it in a future article if there is sufficient interest.  Other authentication includes TLS/SSL which is much more secure and common for public servers such as GMail.  Note however, while TLS authentication is requested by SMTP  it is not actually part of SMTP itself and cannot be implemented with calls to UTL_SMTP.COMMAND as with the methods above.   Other authentication means are also possible but again are outside the scope of this article with implementation and content requirements being determined by the server.  

As of Oracle version 11.2.0.2 (available through Oracle Support, not OTN), the OPEN command was extended to allow use of wallets and introduced the STARTTLS procedure which allows us to implement SSL/TLS.  If using this it is important to remember to send the EHLO command a second time after initiating TLS as the server may respond with different authentication schemes within the encrypted communication.


Even if you don't have the latest versions available, hopefully the above examples of basic SMTP authentication will help.
As always, questions and comments welcome.

Ciao!
Sean D. Stuber
7
46,420 Views
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT

Comments (6)

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Author

Commented:
thanks younghv!

I do plan on adding links between this one and other future SMTP-related articles as I complete them, but my other articles, while (mostly) Oracle related aren't really connected to this one.

I guess I could do some cross-advertising anyway.  Is there a good way to publish an article list?  I know how to search for my own articles but I don't know of a way to create a link for others that could produce the same search results
CERTIFIED EXPERT
Author of the Year 2011
Top Expert 2006

Commented:
Excellent question.
In my Articles, I embed the links using this format:

Rogue-Killer-What-a-great-name

For a sample, take a look here: https://www.experts-exchange.com/A_1958.html (MALWARE - "An Ounce of Prevention...")

In my own Articles, I've taken to including links to other malware-specific Articles that may help the reader.
Sanjeev LabhDatabase Consultant

Commented:
Nicely written. However would have much more helpful if you could provide some more detailed examples.

Also would be really nice if you can post a public mail integration like Gmail.

Great as usual.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Author

Commented:
Thank you for the feedback.

What more detail would you like to see?  I gave examples of all necessary syntax for the authentication methods.   No other commands are needed, so I'm not sure what further detail to provide.

For Gmail, you need TLS authentication which is not supported in 11.2.0.1 (the version of Enterprise Edition available from OTN for anyone to use for learning and testing.)  When 12c is released I expect it to support that functionality.  I plan to post a followup article then.

If you want to explore that functionality now 11.2.0.2 and higher do support it.  11gXE is 11.2.0.2 so you could try it now.
Sanjeev LabhDatabase Consultant

Commented:
Thanks for the information once again.

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.