| 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
- Deploy 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 |