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. Despite authentication being part of the standard at the time, when Oracle introduced the UTL_SMTP package in version 8i, it did not include a built in method to authenticate to an SMTP server. Later UTL_MAIL was introduced in 10g but still lacked authentication and it has not been updated to include it in subsequent versions either.
The lack of authentication is often not a problem within a business setting though. Frequently, the local mail server will allow outgoing traffic from within the company’s private network. The fact that the request is internal is sufficient for most communication and Oracle’s original packages were apparently designed with this assumption in place.
That convenience will not always be the case however. If your company’s internal server requires authentication or if you must use an external service then you will want some mechanism to authenticate to the SMTP server.
The APEX_MAIL package can be used with servers requiring authentication; but there is no API within that package to set up the authentication. It is configured through the APEX builder interface or through the APEX_INSTANCE_ADMIN package as shown in part 2 of this series. Since the settings are at the instance level, all email from APEX authenticates as the same user. This may or may not be a desirable feature.
Starting with 11gR2, the AUTH procedure and function were added to the UTL_SMTP package. SMTP authentication can be of multiple forms, some more secure than others. The most significant step in secure authentication with UTL_SMTP also came in 11.2 with the introduction of TLS encryption of the communication.
TLS Encryption
Assuming you are working with 11gR2 or higher, when opening the connection to the SMTP server you should encrypt the communication by using a wallet containing a certificate for SSL/TLS secure communication. If you have an auto-login wallet that might looks something like this…
DECLARE
v_connection UTL_SMTP.connection;
BEGIN
v_connection :=
UTL_SMTP.open_connection(
host => 'smtp.gmail.com',
port => 587,
wallet_path => 'file:/home/sds/mywallets/gmail',
secure_connection_before_smtp => FALSE);
Or, if your wallet requires a password then it’s similar to above except you’ll need to include the wallet’s password.
DECLARE
v_connection UTL_SMTP.connection;
BEGIN
v_connection :=
UTL_SMTP.open_connection(
host => 'smtp.gmail.com',
port => 587,
wallet_path => 'file:/home/sds/mywallets/gmail',
wallet_password => 'secret',
secure_connection_before_smtp => FALSE);
Most of the parameters above are self-explanatory. The secure_connection_before_smtp parameter is also; but it can raise questions why one would choose to not secure the connection. The reason is the initial connection simply opens the socket but no information is exchanged yet. At this point you could encrypt and then immediately begin the smtp communication, or you could send the initial SMTP commands to let the receiving server know you will be doing SMTP communication. Either way is safe as no authentication information is being sent until after the server and client have established SMTP will be transmitted and the communication is encrypted. As shown in the example above, the gmail servers expect a little bit of SMTP handshaking first, then encrypt before sending the authentication information and the rest of the email data.
If you wish, you can include the tx_timeout parameter to define a number of seconds to limit a read or write operation. By default this value is NULL, meaning no timeout (wait forever.)
12cR2 added the secure_host optional parameter. This value can be used if the certificate in your wallet has a different common name than the host name you are connecting to. This parameter defaults to NULL and is often not needed. You may need it though if you have server aliases or connect to an ip address instead of the host name matching the common name. The secure_host value could also be a domain name instead of a specific server such as ‘*.example.com’.
Once you have the connection open, you will need to identify yourself to the server with EHLO instead of HELO. EHLO begins the handshaking for extensions to SMTP, such as authenticating. Once the handshake begins, you begin the encryption with STARTTLS
UTL_SMTP.ehlo(v_connection, 'mydomain.com');
UTL_SMTP.starttls(v_connection);
If you using secure_connection_before_smtp => TRUE, then calling STARTTLS is not appropriate as the communication will already be encrypted.
Assuming no exceptions have been raised, the connection should be secured with encryption and we can safely send our authentication credentials. Depending on the server you may be able to use the default authentication scheme (CRAM-MD5) or you may need to specify them. In the case of the gmail servers, we can use the PLAIN or LOGIN schemes; either or both may be specified.
UTL_SMTP.auth(c => v_connection,
username => 'sean.stuber@gmail.com',
password => 'secret',
schemes => 'PLAIN LOGIN');
If you specify an invalid authentication scheme the SMTP server will return an exception. The wording can vary but the error code of an invalid authentication type is 504. Which will be a UTL_SMTP.permanent_error exception (ORA-29279.)
Authentication without Encryption
While it is not recommended for public servers, within a private network to a private server it may be safe to support authentication over unsecured communication. No wallet or STARTTLS calls are necessary, but it is still necessary to use EHLO rather than HELO. Then call AUTH as above with whatever schemes are supported.
DECLARE
v_connection UTL_SMTP.connection;
BEGIN
v_connection :=
UTL_SMTP.open_connection(
HOST => 'smtp.gmail.com',
port => 587,
secure_connection_before_smtp => FALSE);
UTL_SMTP.ehlo(v_connection, 'smtp.gmail.com');
UTL_SMTP.auth(c => v_connection,
username => 'sean.stuber@gmail.com',
password => 'secret',
schemes => 'PLAIN LOGIN');
Function APIs
In the examples above I used the procedural APIs of UTL_SMTP. In that form, if there is an error the procedure will raise an exception. If you prefer, you can use the functional APIs which return a UTL_SMTP.reply record instead and check the SMTP reply codes each time and take whatever action is appropriate such as logging an error, retrying, and/or exiting the routine.
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.gmail.com',
port => 587,
secure_connection_before_smtp => FALSE);
v_replies := UTL_SMTP.ehlo(v_connection, 'smtp.gmail.com');
-- check v_replies code value(s)
v_reply := UTL_SMTP.auth(
c => v_connection,
username => 'sean.stuber@gmail.com',
password => 'secret',
schemes => 'PLAIN LOGIN'
);
-- check v_reply code value
The APIs described above should work with most SMTP servers you may encounter. If you are using an older version that does not support these APIs or if the server implements a new authentication mechanism you may need to authenticate manually. Those manual methods will be discussed in the next article in this series.