Oracle UTL_SMTP Built-In Package
Versions 8.1.7 - 19.3

Security Advisory
The question you must be prepared to address as you read the security issues enumerated on this page are:
  • Why should an unprivileged user be able to connect and send emails and data from an Oracle Database ... the issue isn't one of granting the privilege to an application ... the issue granting the privilege to everyone with a userid and password?
  • Do you have any applications that require the ability to create a TCP/IP connection to a host not restricted by a Network Access Control List (ACL) ... If you do can they send emails and your data to any TCP/IP connection anywhere on the planet or only to one or a select small number of IP addresses?
UTL_SMTP can be used to create and send emails from inside the database with no default restriction imposed by the database on who can send them, where they can go, and whether they can use those emails to exfiltrate data to on outside location.

From our standpoint the description above, and the description by Oracle in the Types and Packages documentation should have eliminated any thought of granting EXECUTE to PUBLIC. Unfortunately it didn't. There is no conceivable, rational, reason why someone whose only privilege is CONNECT SESSION should be writing able to write an anonymous block and send emails, with attached data anywhere they wish. We advise, in the strongest possible terms, revoking EXECUTE from PUBLIC. And we recommend Oracle Corp. rewrite the UTL_ group of packages that provide communications access, UTL_HTTP, UTL_INADDR, UTL_MAIL, UTL_SMTP, and UTL_TCP with a reasonable amount of thought put into how dangerous they are as they were written for version 8i.

For demos written by Bryn Llewellyn [Click Here].
 
Recommended Security Rules

 NEVER
  • Ddeploy an Oracle database without revoking EXECUTE granted to PUBLIC from this package.
 WITH GREAT CARE
  • Test the revocation in your version and edition of the database, with your application to verify that there is no legitimate for access to the package.
  • If there is a need require a written justification signed off by your organization's CISO.
 CAUTIONS
  • Grant EXECUTE on this package to any user or role because doing so is in no way different from the approval that should be required when punching a hole in the firewall to grant access to the database zone directly from the internet.
  • If you do, be sure that if not already done you create a Network Access Control List in the database (see link at the bottom of this page) to severely restrict what hosts can be contacted by this package.
 
How Oracle Works
When a privilege is granted to PUBLIC that means that any connected user, without regard to their need or privileges can access that object. In the case of a legacy built-in package that means they can use any of the 17 objects it contains.

The code at right will verify that EXECUTE has been granted to PUBLIC.
SQL> SELECT grantee, privilege
  2  FROM dba_tab_privs
  3  WHERE table_name = 'UTL_SMTP';

GRANTEE   PRIVILEGE
--------  ----------
PUBLIC    EXECUTE
Objects that are dependent upon UTL_SMTP, unless constructed with dynamic or embedded SQL, which will be extremely rate, can be found in DBA_DEPENDENCIES.

To revoke EXECUTE from PUBLIC you must be absolutely sure you are not breaking anything in the database or your application.
Only these two objects in an Oracle 18.3 database appear to require a grant of EXECUTE to remain valid.

SQL> SELECT owner, name
  2  FROM dba_dependencies
  3  WHERE referenced_name = 'UTL_SMTP'
  4  AND referenced_owner <> owner
  5  AND referenced_name <> name
  6* ORDER BY 1;

OWNER              NAME
-----------------  --------------------
GSMADMIN_INTERNAL  DBMS_GSM_CLOUDADMIN
MDSYS              SEM_RDFCTX_DR

So try this, and if it work, test extensively before promoting to used environments

SQL> SELECT COUNT(*)
  2  FROM dba_objects
  3  WHERE status = 'INVALID';

If you have invalid objects fix them before proceeding.

SQL>  GRANT execute ON utl_smtp TO gsmadmin_internal;
SQL> GRANT execute ON utl_smtp TO mdsys;
SQL> REVOKE execute ON utl_smtp FROM PUBLIC;

SQL> SELECT COUNT(*)
  2  FROM dba_objects
  3  WHERE status = 'INVALID';

If you have invalid objects identify the root cause and fix before proceeding. If you are unable to determine and address the root cause bring in an Oracle Database security expert to address the issue and teach your team how to do it in the future.
Enable Java In The Database if it is not already enabled. To enable UTL_SMTP in the database java must be enabled run

$ORACLE_HOME/javavm/install/initjvm.sql
$ORACLE_HOME/javavm/install/init_jis.sql
$ORACLE_HOME/rdbms/admin/initplsj.sql
Send Email Demo CREATE OR REPLACE PROCEDURE send_mail (
pSender    VARCHAR2,
pRecipient VARCHAR2,
pSubject   VARCHAR2,
pMessage   VARCHAR2) AUTHID CURRENT_USER IS

mailhost  CONSTANT VARCHAR2(30) := 'smtp01.us.oracle.com';
crlf      CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
mesg      VARCHAR2(1000);
mail_conn utl_smtp.connection;

BEGIN
   mail_conn := utl_smtp.open_connection(mailhost, 25);

   mesg := 'Date: ' ||
        TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||
           'From: <'|| pSender ||'>' || crlf ||
           'Subject: '|| pSubject || crlf ||
           'To: '||pRecipient || crlf || '' || crlf || pMessage;

   utl_smtp.helo(mail_conn, mailhost);
   utl_smtp.mail(mail_conn, pSender);
   utl_smtp.rcpt(mail_conn, pRecipient);
   utl_smtp.data(mail_conn, mesg);
   utl_smtp.quit(mail_conn);
EXCEPTION
  WHEN INVALID_OPERATION THEN
    utl_smtp.close_connection(mail_conn);
  WHEN TRANSIENT_ERROR THEN
    utl_smtp.close_connection(mail_conn);
  WHEN PERMANENT_ERROR THEN
    utl_smtp.close_connection(mail_conn);
  WHEN OTHERS THEN
    utl_smtp.close_connection(mail_conn);
END send_mail;
/
Demo from OTN DECLARE
 c utl_smtp.connection;
PROCEDURE send_header(name VARCHAR2, header VARCHAR2) AUTHID DEFINER AS
BEGIN
  utl_smtp.write_data(c,name ||':'|| header || UTL_TCP.CRLF);
END;

BEGIN
  c := utl_smtp.open_connection('smtp-server.morganslibrary.org');
  utl_smtp.helo(c, 'morganslibrary.org');
  utl_smtp.mail(c, 'mailsys@morganslibrary.com');
  utl_smtp.rcpt(c, 'recipient@oracle.com');
  utl_smtp.open_data(c);
  send_header('From', '"Sender" <sender@morganslibrary.org>');
  send_header('To', '"Recipient" <recipient@oracle.com>');
  send_header('Subject', 'Hello');
  utl_smtp.write_data(c, UTL_TCP.CRLF || 'Hello, world!');
  utl_smtp.close_data(c);
  utl_smtp.quit(c);
EXCEPTION
  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
    BEGIN
      utl_smtp.quit(c);
    EXCEPTION
      WHEN utl_smtp.transient_error
      OR utl_smtp.permanent_error THEN
        NULL;
    END;
    raise_application_error(-20000, SQLERRM);
END;
/
 
UTL_SMTP Package Information
AUTHID CURRENT_USER
Constants
Name Data Type Value
ALL_SCHEMES VARCHAR2(80) 'CRAM-MD5 PLAIN LOGIN'
NON_CLEARTEXT_PASSWORD_SCHEMES VARCHAR2(80) 'CRAM-MD5'
Data Types -- connection
TYPE connection IS RECORD(
host            VARCHAR2(255),  -- remote host name
port            PLS_INTEGER,    -- remote port number
tx_timeout      PLS_INTEGER,    -- Transfer time-out (sec.)
private_tcp_con utl_tcp.connection,
private_state   PLS_INTEGER);
/

-- reply & replies
TYPE reply IS RECORD (
code PLS_INTEGER,  -- 3-digit reply code
text VARCHAR2(508) -- text message);
/

TYPE replies IS TABLE OF reply INDEX BY BINARY_INTEGER;
/
Dependencies
DBMS_AQELM UTL_ENCODE UTL_RAW
DBMS_ISCHED UTL_I18N UTL_SMT_LIB
PLITBLM UTL_MAIL UTL_TCP
Documented Yes
Exceptions
Error Code Reason
ORA-24247 network_access_denied_errcode: Network access denied
ORA-29258 buffer_too_small_errcode: Buffer is too small for I/O
ORA-29259 end_of_input_errcode: End of input from the connection
ORA-29260 network_error_errcode: Network error
ORA-29261 bad_argument_errcode: Bad argument passed in API call
ORA-29275 partial_multibyte_char_errcode: A partial multi-byte character found
ORA-29276 transfer_timeout: Transfer time-out occurred
First Available 8.1.7
Reply Codes
Codes Description
211 System status, or system help reply
214 Help message useful only to the human user
220 <domain> Service ready
221 <domain> Service closing transmission channel
250 Requested mail action okay, completed
251 User not local; will forward to <forward-path>
252 OK, pending messages for node <node> started. Cannot VRFY user but will take message and attempt delivery
253 OK, <messages> pending messages for node <node> started
354 Start mail input; end with <CRLF>.<CRLF>
355 Octet-offset is the transaction offset
421 <domain> service not available, closing transmission channel (may be a reply to any command)
450 Requested mail action not taken: mailbox unavailable [for example, mailbox busy]
451 Requested action terminated: local error in processing
452 Requested action not taken: insufficient system storage
453 You have no mail.
454 TLS temporarily no available. Encryption required for requested authentication mechanism.
458 Unable to queue messages for node <node>
459 Node <node> not allowed: reason
500 synt error, command unrecognized (This may include errors such as command line too long.)
501 synt error in parameters or arguments
502 Command not implemented
503 Bad sequence of commands
504 Command parameter not implemented
521 <Machine> does not accept mail.
530 Must issue a STARTTLS command first. Encryption required for requested authentication.
534 Authentication mechanism is too weak.
538 Encryption required for requested authentication mechanism.
550 Requested action not taken: mailbox unavailable [for mailbox not found, no access]
551 User not local; please try <forward-path>
552 Requested mail action terminated: exceeded storage allocation
553 Requested action not taken: mailbox name not allowed [for example, mailbox synt incorrect]
554 Transaction failed
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/utlsmtp.sql
Subprograms
 
AUTH
Sends AUTH command to authenticate to the SMTP server

Overload 1
utl_smtp.auth(
c        IN OUT NOCOPY connection,
username IN            VARCHAR2,
password IN            VARCHAR2,
schemes  IN            VARCHAR2 DEFAULT NON_CLEARTEXT_PASSWORD_SCHEMES)
RETURN REPLY;
TBD
Overload 2 utl_smtp.auth(
c        IN OUT NOCOPY connection,
username IN            VARCHAR2,
password IN            VARCHAR2,
schemes  IN            VARCHAR2 DEFAULT NON_CLEARTEXT_PASSWORD_SCHEMES);
TBD
 
CLOSE_CONNECTION
Closes the SMTP connection. Use to abort current SMTP operation mid-session: Otherwise use quit. utl_smtp.close_connection(c IN OUT NOCOPY connection);
See SEND EMAIL Demo below
 
CLOSE_DATA
Closes the data session
Overload 1
utl_smtp.close_data(c IN OUT NOCOPY connection)
RETURN reply;
See Demos below
Overload 2 utl_smtp.close_data(c IN OUT NOCOPY connection);
See Demos below
 
COMMAND
Perform generic SMTP commands

Overload 1
utl_smtp.command(c IN connection, cmd IN VARCHAR2, arg IN VARCHAR2 DEFAULT NULL) RETURN reply;
TBD
Overload 2 utl_smtp.command(
c   IN connection,
cmd IN VARCHAR2,
arg IN VARCHAR2 DEFAULT NULL);
PROCEDURE send_email(
 pFromUser IN VARCHAR2,
 pToUser   IN VARCHAR2,
 pSubject  IN VARCHAR2 DEFAULT NULL,
 pBody     IN VARCHAR2 DEFAULT NULL) AUTHID DEFINER IS

 conn          UTL_SMTP.CONNECTION;
 crlf          VARCHAR2(2)    := CHR(13) || CHR(10);
 EmailServer   VARCHAR2(60)   := 'smtp.mlib.org';
 mesg          VARCHAR2(4000) := 'Hello World';
 pwd           VARCHAR2(200)  := 'sEcReT';
 SenderAddress VARCHAR2(200)  := '<sender@domain.com>';
 SenderName    VARCHAR2(50)   := 'damorgan18c';
 pToList       VARCHAR2(4000);
 vToReceivers  VARCHAR2(200);
 uname         VARCHAR2(200);
BEGIN
  conn := utl_smtp.open_connection(EmailServer, 25);
  utl_smtp.ehlo(conn, EmailServer);
  --utl_smtp.helo( conn, EmailServer );

  utl_smtp.command(conn, 'AUTH LOGIN');
  utl_smtp.command(conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode( utl_raw.cast_to_raw(uname))));
  utl_smtp.command(conn, utl_raw.cast_to_varchar2(
    utl_encode.base64_encode( utl_raw.cast_to_raw(pwd))));
  utl_smtp.mail(conn, SenderAddress);
  utl_smtp.rcpt(conn, pToList);

  mesg:= 'Date: '|| TO_CHAR(SYSDATE, 'DD MON RR HH24:MI:SS' )|| crlf ||
         'From: "' || SenderName || '" ' || SenderAddress || crlf ||
         'Subject: ' || pSubject || crlf ||
         'To: '|| pToList || crlf||
         pBody || crlf || crlf;

  utl_smtp.data(conn, mesg);
  utl_smtp.quit(conn);
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001,SQLERRM);
END send_email;
/
 
COMMAND_REPLIES
Perform generic SMTP commands utl_smtp.command_replies(
c   IN connection,
cmd IN VARCHAR2,
arg IN VARCHAR2 DEFAULT NULL) RETURN REPLIES;
TBD
 
DATA
Specifies the body of an e-mail message

Overload 1
utl_smtp.data(
c    IN OUT NOCOPY connection
body IN            VARCHAR2 CHARACTER SET ANY_CS)
RETURN reply;
See Demos Below
Overload 2 utl_smtp.data(
c    IN OUT NOCOPY connection
body IN            VARCHAR2 CHARACTER SET ANY_CS);
See Demos Below
 
EHLO
Performs initial handshaking with SMTP server after connecting

Overload 1
utl_smtp.ehlo(
c      IN OUT NOCOPY connection,
domain IN            VARCHAR2)
RETURN REPLIES
;
DECLARE
 conn utl_smtp.connection;
 rply utl_smtp.reply;
BEGIN
  rply := utl_smtp.open_connection('mail.uw.edu', 25, conn);
  rply := utl_smtp.ehlo(conn, 'UW');
  utl_smtp.quit(conn);
END;
/
Overload 2 utl_smtp.ehlo(
c      IN OUT NOCOPY connection,
domain IN            VARCHAR);
DECLARE
 conn utl_smtp.connection;
 rply utl_smtp.reply;
BEGIN
  rply := utl_smtp.open_connection('mail.uw.edu', 25, conn);
  utl_smtp.ehlo(conn, 'UW');
  utl_smtp.quit(conn);
END;
/
 
HELO
Performs initial handshaking with SMTP server after connecting

Overload 1
utl_smtp.helo(
c      IN OUT NOCOPY connection,
domain IN            VARCHAR2)
RETURN REPLY;
DECLARE
 conn utl_smtp.connection;
 rply utl_smtp.reply;
BEGIN
  rply := utl_smtp.open_connection('mail.uw.edu', 25, conn);
  rply := utl_smtp.helo(conn, 'UW');
  utl_smtp.quit(conn);
END;
/
Overload 2 utl_smtp.helo(
c      IN OUT NOCOPY connection,
domain IN            VARCHAR2);
See Demos Below
 
HELP
Sends HELP Command utl_smtp.help(
c       IN OUT NOCOPY connection,
command IN            VARCHAR2 DEFAULT NULL)
RETURN replies;
DECLARE
 conn  utl_smtp.connection;
 rplys utl_smtp.replies;
BEGIN
  conn := utl_smtp.open_connection('mail.uw.edu', 25, conn);
  rplys := utl_smtp.help(conn, 'AUTH LOGIN');

  FOR i IN rplys.FIRST .. rplys.LAST LOOP
    dbms_output.put_line(rplys);
  END LOOP;

  utl_smtp.quit(conn);
END;
/
 
MAIL
Initiates a mail transaction with the server

Overload 1
utl_smtp.mail(
c          IN OUT NOCOPY connection,
sender     IN            VARCHAR2,
parameters IN            VARCHAR2 DEFAULT NULL)
RETURN reply;
See Demos Below
Overload 2 utl_smtp.mail(
c          IN OUT NOCOPY connection,
sender     IN            VARCHAR2,
parameters IN            VARCHAR2 DEFAULT NULL);
See Demos Below
 
NOOP
No operation. A call that can be used for testing that does precisely nothing.
Overload 1
utl_smtp.noop(c IN OUT NOCOPY connection)
RETURN reply;
DECLARE
 eServer VARCHAR2(60) := 'smtp.mlib.org';
 conn    utl_smtp.connection;
 rply    utl_smtp.reply;
BEGIN
  conn := utl_smtp.open_connection(eServer, 25);
  rply := utl_smtp.noop(conn);
END;
/
Overload 2 utl_smtp.noop(c IN OUT NOCOPY connection);
DECLARE
 eServer VARCHAR2(60) := 'smtp.mlib.org';
 conn    utl_smtp.connection;
BEGIN
  conn := utl_smtp.open_connection(eServer, 25);
  utl_smtp.noop(conn);
END;
/
 
OPEN_CONNECTION
Opens a connection to an SMTP server

Overload 1
utl_smtp.open_connection(
host                          IN  VARCHAR2,
port                          IN  PLS_INTEGER DEFAULT 25,
c                             OUT connection,
tx_timeout                    IN  PLS_INTEGER DEFAULT NULL,
wallet_path                   IN  VARCHAR2    DEFAULT NULL,
wallet_password               IN  VARCHAR2    DEFAULT NULL,
secure_connection_before_smtp IN  BOOLEAN     DEFAULT FALSE)
RETURN reply;
See Demos Below
Overload 2 utl_smtp.open_connection(
host                          IN  VARCHAR2,
port                          IN  PLS_INTEGER DEFAULT 25,
c                             OUT connection,
tx_timeout                    IN  PLS_INTEGER DEFAULT NULL,
wallet_path                   IN  VARCHAR2    DEFAULT NULL,
wallet_password               IN  VARCHAR2    DEFAULT NULL,
secure_connection_before_smtp IN  BOOLEAN     DEFAULT FALSE)
RETURN connection;
See Demos Below
 
OPEN_DATA
Sends the DATA command
Overload 1
utl_smtp.open_data(c IN OUT NOCOPY connection) RETURN reply;
See Demos Below
Overload 2 utl_smtp.open_data(c IN OUT NOCOPY connection);
See Demos Below
 
QUIT
Terminates an SMTP session and disconnects from the server
Overload 1
utl_smtp.quit(c IN OUT NOCOPY connection)
RETURN reply;
See Demos Below
Overload 2 utl_smtp.quit(c IN OUT NOCOPY connection);
See Demos Below
 
RCPT
Specifies the recipient of an e-mail message

Overload 1
utl_smtp.rcpt(
c          IN OUT NOCOPY connection,
recipient  IN            VARCHAR2,
parameters IN            VARCHAR2 DEFAULT NULL)
RETURN reply;
See Demos Below
Overload 2 utl_smtp.rcpt(
c          IN OUT NOCOPY connection
recipient  IN            VARCHAR2,
parameters IN            VARCHAR2 DEFAULT NULL);
See Demos Below
 
RSET
Aborts the current mail transaction
Overload 1
utl_smpt.rset(c IN OUT NOCOPY connection) RETURN reply;
TBD
Overload 2 utl_smpt.rset(c IN OUT NOCOPY connection);
TBD
 
STARTTLS
Sends the STARTTLS command to secure the SMTP connection using SSL/TLS
Overload 1
utl_smtp.starttls(c IN OUT NOCOPY connection) RETURN REPLY;
TBD
Overload 2 utl_smtp.starttls(c IN OUT NOCOPY connection);
TBD
 
VRFY
Verifies the validity of a destination e-mail address utl_smtp.vrfy(
c         IN OUT NOCOPY connection,
recipient IN            VARCHAR2)
RETURN reply;
TBD
 
WRITE_DATA
Writes a portion of the e-mail message utl_smtp.write_data(
c    IN OUT NOCOPY connection,
data IN            VARCHAR2 CHARACTER SET ANY_CS);
See Demos Below
 
WRITE_RAW_DATA
Writes a portion of the e-mail message with RAW data utl_smtp.write_raw_data(
c    IN OUT NOCOPY connection,
data IN            RAW);
See Demos Below

Related Topics
DBMS_NETWORK_ACL_ADMIN
UTL_MAIL
UTL_RAW
UTL_MAIL
UTL_SMTP with SSL
UTL_TCP