If using an older version of the Oracle database (11gR1 or earlier) the UTL_SMTP package does not have authentication APIs built in. You may also run into a situation where a server implements a mechanism that UTL_SMTP does not support. You still have options though, by implementing some of the authentication means manually.
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 and a framework that could be used to write your own if needed.
Initiating the Communication
DECLARE
v_connection UTL_SMTP.connection;
v_replies UTL_SMTP.replies;
v_reply UTL_SMTP.reply;
BEGIN
v_connection :=
UTL_SMTP.open_connection(host => 'smtp.example.com',
port => 587
);
v_replies := UTL_SMTP.ehlo(v_connection, 'example.com');
FOR i IN 1 .. v_replies.COUNT
LOOP
DBMS_OUTPUT.put_line(
'EHLO Reply: ' || v_replies(i).code || ' ' || v_replies(i).text);
END LOOP;
v_reply := UTL_SMTP.quit(v_connection);
DBMS_OUTPUT.put_line('QUIT Reply: ' || v_reply.code || ' ' || v_reply.text);
END;
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:
EHLO Reply: 250 AUTH LOGIN PLAIN CRAM-MD5 CRAM-SHA1
EHLO Reply: 250 STARTTLS
This would indicate the server supports 4 methods of SMTP authentication as well as encryption through SSL/TLS.
First, let’s look at the SMTP authentication methods. The first 3 of those listed (LOGIN, PLAIN, and CRAM-MD5) are supported by UTL_SMTP in 11gR2 and above. All of these include Base-64 Encoding, as part of their algorithms. It is important to note, Base-64 Encoding is NOT encryption. 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=
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
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);
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.
Which would like this after decoding
AUTH LOGIN
334 Username:
my_user_name
334 Password:
my_secret_password
235 Authed. Go on.
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 of the authentication information 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.
DECLARE
v_connection UTL_SMTP.connection;
v_replies UTL_SMTP.replies;
v_reply UTL_SMTP.reply;
v_username_b64 VARCHAR2(1000);
v_password_b64 VARCHAR2(1000);
BEGIN
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, 587);
v_replies := UTL_SMTP.ehlo(v_connection, 'wowway.com');
FOR i IN 1 .. v_replies.COUNT
LOOP
DBMS_OUTPUT.put_line(
'EHLO Reply: ' || v_replies(i).code || ' ' || v_replies(i).text);
END LOOP;
-- should receive a 334 response, prompting for username
v_reply := UTL_SMTP.command(v_connection, 'AUTH', 'LOGIN');
DBMS_OUTPUT.put_line(
'Command AUTH Reply: ' || v_reply.code || ' ' || v_reply.text);
-- should receive a 334 response, prompting for password
v_reply := UTL_SMTP.command(v_connection, v_username_b64);
DBMS_OUTPUT.put_line(
'COMMAND Username Reply: ' || v_reply.code || ' ' || v_reply.text);
-- should receive a 235 response, you are authenticated
v_reply := UTL_SMTP.command(v_connection, v_password_b64);
DBMS_OUTPUT.put_line(
'COMMAND Password Reply: ' || v_reply.code || ' ' || v_reply.text);
v_reply := UTL_SMTP.quit(v_connection);
DBMS_OUTPUT.put_line('QUIT Reply: ' || v_reply.code || ' ' || v_reply.text);
END;
EHLO Reply: 250 smtp.example.com says EHLO EHLO Reply: 250 STARTTLS EHLO Reply: 250 AUTH LOGIN Command AUTH Reply: 334 bXlfdXNlcl9uYW1l COMMAND Username Reply: 334 bXlfc2VjcmV0X3Bhc3N3b3Jk COMMAND Password Reply: 235 Authed. Go on. QUIT Reply: 221 smtp.example.com closing connection
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);
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 algorithm, 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
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;
example use might look something like this
DECLARE
v_connection UTL_SMTP.connection;
v_reply UTL_SMTP.reply;
v_cram_string VARCHAR2(4000);
BEGIN
v_connection := UTL_SMTP.open_connection( :v_smtp_server, 587);
UTL_SMTP.ehlo(v_connection, 'mydomain.com');
v_reply := UTL_SMTP.command(v_connection, 'AUTH', 'CRAM-MD5');
IF v_reply.code = 334
THEN
v_cram_string := sdscram( :v_username, :v_password, v_reply.text);
v_reply := UTL_SMTP.command(v_connection, v_cram_string);
DBMS_OUTPUT.put_line(
'COMMAND Cram Response Reply: '
|| v_reply.code
|| ' '
|| v_reply.text);
END IF;
v_reply := UTL_SMTP.quit(v_connection);
DBMS_OUTPUT.put_line('QUIT Reply: ' || v_reply.code || ' ' || v_reply.text);
END;If the server supported CRAM-SHA1, the usage would be the same as above except when calling the SDSCRAM function, the optional 4th parameter p_hashmethod would be set to DBMS_CRYPTO.HASH_SH1.
As of Oracle version 11.2.0.2, STARTTLS is supported. If your server supports it, it will generally be better to use it before sending hashes, and definitely before sending plain text or base64-encoded credentials. If however, you are using 11.2.0.2 or higher, you should not only use STARTTLS, but the AUTH command itself will be available within UTL_SMTP, thus negating the need to use the manual steps described above.
If your server implements a new algorithm, such as salted challenge-response mechanism (SCRAM) the general flow will be the same as shown above. Open a connection (with or without TLS,) initiate handshake with EHLO, use the COMMAND routine to send manual AUTH statement and check the replies to pick up your salt and iterations, encode your credentials, and then reply.
Other algorithms will also have similar rules. The EHLO command will list the supported algorithms with the AUTH tag in the response. The specific challenges, responses, encryptions, and encodings will, of course, be dependent on the specified algorithm but the SMTP communication itself will still follow the same general flow.
If you encounter such servers, I hope these examples help. As always, questions and comments are welcome.