Oracle UTL_TCP Built-In Package
Versions 8.1.7 - 21c

Security Advisory
This package supports application communications with external TCP/IP-based servers using TCP/IP and can be used when PL/SQL code needs to use Internet protocols and/or e-mail.

A maximum of 16 connections, per session, is allowed. See Oracle Support Note:280838.1: ORA-30678 after executing UTL_TCP.OPEN_CONNECTION for details.

From our standpoint the description by Oracle above should have eliminated any thought of granting EXECUTE to PUBLIC. Unfortunately it didn't. There is not conceivable, rational, reason why someone whose only privilege is CONNECT SESSION should be writing anonymous blocks or PL/SQL objects that allow valuable data to use TCP/IP unless the goal is to enhance the ability to steal data. We advise, in the strongest possible terms, revoking EXECUTE from PUBLIC.
 
Recommended Security Rules

 NEVER
  • install 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_TCP';

GRANTEE   PRIVILEGE
--------  ----------
PUBLIC    EXECUTE
Objects that are dependent upon UTL_TCP, 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_TCP'
  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_tcp TO gsmadmin_internal;
SQL> GRANT execute ON utl_tcp TO mdsys;
SQL> REVOKE execute ON utl_tcp 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.
The Email code at right shows how UTL_TCP can be utilized to create ad hoc connection to any remote host unless a restriction is created and maintained in the database or firewall Access Control List (ACL).

If the functionality is not required the safest and most permanent thing to do is to just revoke the privilege where not required.

Note the connection names and email addresses are not valid ... you need to substitute your own for the code to function.
CREATE OR REPLACE PROCEDURE send_mail(
sender    IN VARCHAR2,
recipient IN VARCHAR2,
message   IN VARCHAR2)
AUTHID DEFINER IS
 mailhost   VARCHAR2(30) := 'smtp.dbsecworx.com';
 smtp_error EXCEPTION;
 mail_conn  utl_tcp.connection;

 -- embedded procedure
 PROCEDURE smtp_command(command IN VARCHAR2, ok IN VARCHAR2 DEFAULT '250') IS
  response VARCHAR2(256);
  len      PLS_INTEGER;
 BEGIN
   len := utl_tcp.write_line(mail_conn, command);
   response := utl_tcp.get_line(mail_conn);
   dbms_output.put_line(response);
   response := SUBSTR(response,1,3);
   IF (response <> ok) THEN
     RAISE smtp_error;
   END IF;
 END smtp_command;
 -- end embedded procedure
BEGIN
  mail_conn := utl_tcp.open_connection(remote_host => mailhost,
  remote_port => 25, charset => 'US7ASCII');
  smtp_command('HELO ' || mailhost);
  smtp_command('MAIL FROM: ' || sender);
  smtp_command('RCPT TO: ' || recipient);
  smtp_command('DATA', '354');
  smtp_command(message);
  smtp_command('QUIT', '221');
  utl_tcp.close_connection(mail_conn);
END send_mail;
/

exec send_mail('testing@dbsecworx.com', 'testing@dbsecworx.com', 'Test');
 
UTL_TCP Package Information
AUTHID CURRENT_USER
Constants
Name Data Type Value
CRLF VARCHAR2(2 CHAR) unistr('\000D\000A')
Data Types TYPE connection IS RECORD (
remote_host VARCHAR2(255),  -- Remote host name
remote_port PLS_INTEGER,    -- Remote port number
local_host  VARCHAR2(255),  -- Local host name
local_port  PLS_INTEGER,    -- Local port number
charset     VARCHAR2(30),   -- Character set for on-the-wire comm.
newline     VARCHAR2(2),    -- Newline character sequence
tx_timeout  PLS_INTEGER,    -- Transfer time-out value (in seconds)
private_sd  PLS_INTEGER)    -- For internal use only
/
Dependencies
DBMS_AQELM UTL_CALL_STACK UTL_SMTP
DBMS_ISCHED UTL_MAIL UTL_TCP_LIB
SCHEDULER$_JOB_EVENT_HANDLER    
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
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/utltcp.sql
Subprograms
 
AVAILABLE
Determines the number of bytes available for reading from a TCP/IP connection utl_tcp.available(
c       IN OUT NOCOPY connection,
timeout IN     PLS_INTEGER DEFAULT 0)
RETURN PLS_INTEGER;
See READ_TEXT Demo Below
 
CLOSE_ALL_CONNECTIONS
Closes all open TCP/IP connections utl_tcp.close_all_connections;
exec utl_tcp.close_all_connections;
 
CLOSE_CONNECTION
Closes a TCP/IP connection utl_tcp.close_connection(c IN OUT NOCOPY connection);
See OPEN_CONNECTION Demo Below
 
FLUSH
Immediately transmits all the output data in the output queue to the connection utl_tcp.flush(c IN OUT NOCOPY connection);
See OPEN_CONNECTION Demo Below
 
GET_LINE
A convenient form of the read functions, which return the data read instead of the amount of data rekad utl_tcp.get_line(
c           IN OUT NOCOPY connection,
remove_crlf IN     BOOLEAN DEFAULT FALSE,
peek        IN     BOOLEAN DEFAULT FALSE)
RETURN VARCHAR2;
See OPEN_CONNECTION Demo Below
 
GET_LINE_NCHAR
A convenient form of the read functions, which return the data read instead of the amount of data read utl_tcp.get_line_nchar(
c           IN OUT NOCOPY connection,
remove_crlf IN     BOOLEAN DEFAULT FALSE,
peek        IN     BOOLEAN DEFAULT FALSE)
RETURN NVARCHAR2;
See OPEN_CONNECTION Demo Below
 
GET_RAW
A convenient form of the read functions, which return the data read instead of the amount of data read utl_tcp.get_raw(
c    IN OUT NOCOPY connection,
len  IN     PLS_INTEGER DEFAULT 1,
peek IN     BOOLEAN DEFAULT FALSE)
RETURN RAW;
See OPEN_CONNECTION Demo Below
 
GET_TEXT
A convenient form of the read functions, which return the data read instead of the amount of data read utl_tcp.get_text(
c    IN OUT NOCOPY connection,
len  IN     PLS_INTEGER DEFAULT 1,
peek IN     BOOLEAN DEFAULT FALSE)
RETURN VARCHAR2;
See OPEN_CONNECTION Demo Below
 
GET_TEXT_NCHAR
A convenient form of the read functions, which return the data read instead of the amount of data read utl_tcp.get_text_nchar(
c    IN OUT NOCOPY connection,
len  IN     PLS_INTEGER DEFAULT 1,
peek IN     BOOLEAN DEFAULT FALSE)
RETURN NVARCHAR2;
See OPEN_CONNECTION Demo Below
 
OPEN_CONNECTION
Opens a connection to a TCP/IP service utl_tcp.open_connection(
remote_host     VARCHAR2,
remote_port     PLS_INTEGER,
local_host      VARCHAR2    DEFAULT NULL,
local_port      PLS_INTEGER DEFAULT NULL,
in_buffer_size  PLS_INTEGER DEFAULT NULL,
out_buffer_size PLS_INTEGER DEFAULT NULL,
charset         VARCHAR2    DEFAULT NULL,
newline         VARCHAR2    DEFAULT CRLF,
tx_timeout      PLS_INTEGER DEFAULT NULL,
wallet_path     VARCHAR2    DEFAULT NULL,
wallet_password VARCHAR2    DEFAULT NULL)
RETURN connection;
set serveroutput on

spool c:\temp\utl_tcp.txt

DECLARE
 c      utl_tcp.connection; -- TCP/IP connection to the web server
 retval PLS_INTEGER;
BEGIN
  c := utl_tcp.open_connection(remote_host => 'www.morganslibrary.org', remote_port => 80, charset => 'US7ASCII');
  utl_tcp.secure_connection(c);

  retval := utl_tcp.write_line(c, 'GET / HTTP/1.0'); -- send request
  retval := utl_tcp.write_line(c);

  BEGIN
    LOOP
      dbms_output.put_line(utl_tcp.get_line(c, TRUE)); -- read result
    END LOOP;
  EXCEPTION
    WHEN utl_tcp.end_of_input THEN
      NULL; -- end of input
    WHEN OTHERS THEN
      NULL;
  END;

  BEGIN
    utl_tcp.flush(c);
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

  utl_tcp.close_connection(c);
END;
/

spool off
A function that ping's ports to determine whether they exist CREATE OR REPLACE FUNCTION ping (pHostName VARCHAR2, pPort NUMBER DEFAULT 1000)
RETURN VARCHAR2 AUTHID DEFINER IS
 tcpCnx utl_tcp.connection;
 cOk    CONSTANT VARCHAR2(2) := 'OK';
 cFail  CONSTANT VARCHAR2(5) := 'ERROR';
BEGIN
  tcpCnx := utl_tcp.open_connection (pHostName, pPort);
  utl_tcp.close_connection(tcpCnx);
  RETURN cOk;
EXCEPTION
  WHEN utl_tcp.network_error THEN
    IF (UPPER(SQLERRM) LIKE '%HOST%') THEN
      RETURN cFail;
    ELSIF (UPPER(SQLERRM) LIKE '%LISTENER%') THEN
      RETURN cOk;
    ELSE
      RAISE;
    END IF;
WHEN OTHERS THEN
  RAISE;
END ping;
/

SELECT ping('usps997lt.usa.morganslibrary.com', 1521)
FROM dual;
 
READ_LINE
Reads a text line from a TCP/IP connection utl_tcp.read_line(
c           IN OUT NOCOPY connection,
data        IN OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
remove_crlf IN            BOOLEAN DEFAULT FALSE,
peek        IN            BOOLEAN DEFAULT FALSE)
RETURN PLS_INTEGER;
TBD
 
READ_RAW
Reads binary data from a TCP/IP connection utl_tcp.read_raw(
c    IN OUT NOCOPY connection,
data IN OUT NOCOPY RAW,
len  IN            PLS_INTEGER DEFAULT 1,
peek IN            BOOLEAN DEFAULT FALSE)
RETURN PLS_INTEGER;
TBD
 
READ_TEXT
Reads text data from a TCP/IP connection utl_tcp.read_text(
c    IN OUT NOCOPY connection,
data IN OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
len  IN            PLS_INTEGER DEFAULT 1,
peek IN            BOOLEAN DEFAULT FALSE)
RETURN PLS_INTEGER;
DECLARE
 c    utl_tcp.connection;
 data VARCHAR2(256);
 len  PLS_INTEGER;
BEGIN
  c := utl_tcp.open_connection(remote_host => 'www.morganslibrary.org', remote_port => 80, charset => 'US7ASCII');
  LOOP
    IF (utl_tcp.available(c) > 0) THEN
      len := utl_tcp.read_text(c, data, 256);
    ELSE
      ---do some other things
      NULL;
    END IF;
  END LOOP;
  utl_tcp.close_connection(c);
END;
/
 
SECURE_CONNECTION
Secures a TCP/IP connection using SSL/TLS. Requires an Oracle Wallet spec. utl_tcp.secure_connection(
c           IN OUT NOCOPY connection
secure_host IN            VARCHAR2 DEFAULT NULL);
See OPEN_CONNECTION Demo Above
 
WRITE_LINE
Writes a text line to a TCP/IP connection utl_tcp.write_line(
c    IN OUT NOCOPY connection,
data IN     VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL)
RETURN PLS_INTEGER;
See OPEN_CONNECTION Demo Above
 
WRITE_RAW
Writes binary data to a TCP/IP connection utl_tcp.write_raw(
c    IN OUT NOCOPY connection,
data IN            RAW,
len  IN            PLS_INTEGER DEFAULT NULL)
RETURN PLS_INTEGER;
TBD
 
WRITE_TEXT
Writes text data to a TCP/IP connection utl_tcp.write_text(
c    IN OUT NOCOPY connection,
data IN     VARCHAR2 CHARACTER SET ANY_CS,
len  IN     PLS_INTEGER DEFAULT NULL)
RETURN PLS_INTEGER;
TBD

Related Topics
DBMS_NETWORK_ACL_ADMIN
Lockdown Profiles
UTL_MAIL
UTL_SMTP