DBSecWorx: UTL

Oracle UTL_MAIL Built-In Package Versions 2002 - 21c

Security Advisory

This package allows a user to send emails, with attachments, from inside the database. What kind of attachments? Why data queried from database objects, of course.

The good news is that this package is not installed by default and when it is no privileges are granted The bad news is that it doesn't take a lot of system privileges to successfully install it in a user schema.

Our recommendation is that the physical file that installs it, utl_mail.sql be deleted from ${ORACLE_HOME}/rdbms/admin to prevent its installation.

Recommended Security Rules

NEVER

Let any user or schema without documented justification gain access to this package by revoking EXECUTE from PUBLIC
Grant ALTER SYSTEM to any user outside of the members of the DBA team and audit all uses because, as demonstrated on this page a single entry can be the foundation for a substantial compromise

WITH GREAT CARE

Identify legitimate requirements for access to this package and grant EXECUTE explicitly to only justified schemas
Query the data dictionary after EXECUTE has been revoked from PUBLIC to verify the equivalence created is the equivalence approved by IT management and your CISO

CAUTIONS

Some usage may be in the form of dynamic SQL so carefully verify usage requirements in source code as well as in DBA_DEPENDENCIES

How Oracle Works

Why this package is not as big a threat as it might first appear to be The first thing that might lull you into a sense of complacency is that for UTL_MAIL to function the following statement must be executed. ALTER SYSTEM SET smtp_out_server = '<name_of_a_functional_smtp server>' SCOPE=<value>; such as the following: ALTER SYSTEM SET smtp_out_server = 'smtp.drizzle.com' SCOPE=BOTH; It shouldn't. We see statements like the above executed all the time. Sometimes they are not captured in the audit trail and sometimes they are. But the critical pieces of the puzzles ... who is looking at the audit trail and does that person know that this has the potential to be a massive security violation? From our experience they do not. SQL> create user abc identified by abc; User created. SQL> grant create session to abc; Grant succeeded. SQL> conn abc/abc@pdbdev Connected. SQL> @?/rdbms/admin/utlmail.sql CREATE OR REPLACE PACKAGE utl_mail AUTHID CURRENT_USER AS * ERROR at line 1: ORA-01031: insufficient privileges CREATE OR REPLACE PUBLIC SYNONYM utl_mail FOR sys.utl_mail * ERROR at line 1: ORA-01031: insufficient privileges

Why this package is still a huge threat.

With access to this package queries can be run and the results they return emailed to anywhere not blocked by a firewall or access control lists (ACL). Take a closer look at the above attempt to install this package by a non-privileged user. SQL> grant create public synonym to abc; Grant succeeded. SQL> grant create procedure to abc; Grant succeeded. SQL> conn abc/abc@pdbdev Connected. SQL> @?/rdbms/admin/utlmail.sql Package created. Synonym created. SELECT status FROM all_objects WHERE object_name = 'UTL_MAIL'; STATUS ------- VALID VALID It only required two system privileges to create the package in an end-user schema and it is doubtful that any DBA or monitoring tool would understand the depth of the threat. In truth, the PUBLIC SYNONYM is not required so it might only require a single system privilege.

UTL_MAIL Package Information

AUTHID CURRENT_USER

Dependencies

DBMS_OUTPUT	UTL_MAIL_INTERNAL	UTL_TCP
PLITBLM	UTL_RAW	V$VERSION
UTL_ENCODE	UTL_SMTP

Documented Yes

Exceptions

Error Code	Reason
ORA-29261	INVALID_ARGUMENT
ORA-44101	INVALID_PRIORITY

First Available 2002-2003

Initialization Parameter SMTP_OUT_SERVER <port_number>

SMTP_OUT_SERVER = 9090

Mime Types

Value

'text/plain'

'text/plain; charset=us-ascii'
'text/plain; charset=ISO-8859-1'
'text/html; charset=utf-8'
'text/plain; charset=windows-1254'

'application/octet'

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/utlmail.sql
{ORACLE_HOME}/rdbms/admin/prvtmail.plb

SQL> @?/rdbms/admin/utlmail.sql

      

      Package created.

      

      Synonym created.

      

      SQL> @?/rdbms/admin/prvtmail.plb

      

      SP2-0808: Package created with compilation warnings

      

      SP2-0810: Package Body created with compilation warnings

      

      Grant succeeded.

      

      SP2-0810: Package Body created with compilation warnings

      

      Errors for PACKAGE BODY UTL_MAIL:

      

      LINE/COL ERROR

      -------- -----------------------------------------------------------------

      94/24 PLW-07203: parameter 'ADDR_LIST' may benefit from use of the

      NOCOPY compiler hint

      

      126/28 PLW-06010: keyword "DATA" used as a defined name

      137/23 PLW-06010: keyword "DATA" used as a defined name

      145/26 PLW-06010: keyword "DATA" used as a defined name

      305/29 PLW-07203: parameter 'SMTP_SERVERS' may benefit from use of the

      NOCOPY compiler hint

      

      306/29 PLW-07203: parameter 'SMTP_PORTS' may benefit from use of the

      NOCOPY compiler hint

      

      351/10 PLW-06002: Unreachable code

      354/9 PLW-06002: Unreachable code

      356/9 PLW-06002: Unreachable code

      429/14 PLW-06009: procedure "SEND_I" OTHERS handler does not end in

      RAISE or RAISE_APPLICATION_ERROR

Subprograms

Packages an email message into the appropriate format, locates SMTP information, and delivers the message to the SMTP server for forwarding to the recipients utl_mail.send( sender IN VARCHAR2 CHARACTER SET ANY_CS, recipients IN VARCHAR2 CHARACTER SET ANY_CS, cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, message IN VARCHAR2 CHARACTER SET ANY_CS, mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii', priority IN PLS_INTEGER DEFAULT 3, replyto IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);

--the SMTP_OUT_SERVER parameter must be set

conn sys@pdbdev as sysdba

      

      ALTER SYSTEM SET smtp_out_server = 'smtp.drizzle.com' SCOPE=BOTH;

      

      conn uwclass/uwclass@pdbdev

      

      CREATE TABLE emails_sent (

      email_addr VARCHAR2(100));

      

      CREATE OR REPLACE PROCEDURE eblast IS

       CURSOR mcur IS

       SELECT per_h_email

       FROM mlib.person

       WHERE per_ok2_email = 'Y'

       AND per_h_email IS NOT NULL

       AND per_h_email NOT IN (SELECT email_addr FROM emails_sent);

      

       vSender VARCHAR2(30) := 'mailsys@morganslibrary.org';

       vSubj   msg.subject%TYPE;

       vMesg   VARCHAR2(4000);

       vMType  VARCHAR2(30) := 'text/plain; charset=us-ascii';

      BEGIN

        SELECT subject, msgcol

        INTO vSubj, vMesg

        FROM msg;

      

        FOR mrec IN mcur

        LOOP

          utl_mail.send(vSender,  mrec.per_h_email, NULL, NULL, vSubj, vMesg, vMType, NULL);

        END LOOP;

      END eblast;

      /

SEND_ATTACH_RAW

Represents the SEND Procedure overloaded for RAW attachments utl_mail.send_attach_raw ( sender IN VARCHAR2 CHARACTER SET ANY_CS, recipients IN VARCHAR2 CHARACTER SET ANY_CS, cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, message IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii', priority IN PLS_INTEGER DEFAULT 3, attachment IN RAW, att_inline IN BOOLEAN DEFAULT TRUE, att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii', att_filename IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, replyto IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);

--the SMTP_OUT_SERVER parameter must be set

      conn sys@pdbdev as sysdba

      

      ALTER SYSTEM SET smtp_out_server = 'smtp.drizzle.com' SCOPE=BOTH;

      

      conn pm/pm

      

      UPDATE online_media

      SET product_text = 'This is a UTL_MAIL demo';

      COMMIT;

      

      CREATE OR REPLACE PROCEDURE Mail_Attach (fname VARCHAR2) IS

       vInHandle utl_file.file_type;

       rfile     RAW(32767);

       flen      NUMBER;

       bsize     NUMBER;

       ex        BOOLEAN;

      

       vSender   VARCHAR2(30) := 'mailsys@morganslibrary.org';

       vSubj     VARCHAR2(50) := 'April Library News';

       vMesg     VARCHAR2(4000);

       vMType    VARCHAR2(30) := 'text/plain; charset=us-ascii';

      

       CURSOR mcur IS

       SELECT per_h_email

       FROM ml.person

       WHERE per_ok2_email = 'Y'

       AND per_h_email IS NOT NULL;

      BEGIN

        vMesg := 'Please print and complete attachment';

      

        SELECT utl_raw.cast_to_raw(product_text)

        INTO rf

        FROM online_media

        WHERE rownum = 1;

      

        utl_file.fgetattr('ORALOAD', fname, ex, flen, bsize);

        vInHandle := utl_file.fopen('ORALOAD', fname, 'R');

        utl_file.get_raw(l_output, rfile, flen);

        utl_file.fclose(vInHandle);

      

        FOR mrec IN mcur

        LOOP

          utl_mail.send_attach_raw(

            sender => vSender,

            recipients => mrec.per_h_email,

            subject => vSubj,

            message => vMesg,

            attachment => rfile,

            att_inline => FALSE,

            att_filename => fname);

        END LOOP;

      END;

      /

SEND_ATTACH_VARCHAR2

Represents the SEND Procedure overloaded for VARCHAR2 attachments utl_mail.send_attach_varchar2( sender IN VARCHAR2 CHARACTER SET ANY_CS, recipients IN VARCHAR2 CHARACTER SET ANY_CS, cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, message IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii', priority IN PLS_INTEGER DEFAULT 3, attachment IN VARCHAR2 CHARACTER SET ANY_CS, att_inline IN BOOLEAN DEFAULT TRUE, att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii, att_filename IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, replyto IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);

Same as SEND_ATTACH_RAW except that the attachment must be an ASCII text file