How to Authenticate to an SMTP server with Oracle PL/SQL: Oracle Email SMTP Authentication PL/SQL

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

1:
2:

Select all 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=

1:

Select all 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

1:

Select all 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);

1:
2:
3:
4:
5:
6:
7:
8:

Select all 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.

1:
2:
3:
4:
5:
6:

Select all 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.

1:
2:
3:
4:
5:
6:

Select all 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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Select all 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);

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:

Select all 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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

Select all 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;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:

Select all 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;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

Select all 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

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

Oracle Email SMTP Authentication PL/SQL

Oracle Database

About The Author

More Articles From sdstuber

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top Oracle Database Experts