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