PLSQL and the Network

PL/SQL and the Network

The Oracle RDBMS has a plethora of PL/SQL packages that can communicate with the network. These packages are installed by default and the default permissions for all of them are set to allow PUBLIC the execute permission. This means that even the lowest -privileged account can use these packages. To help protect the database server and other systems on the network, the DBA should revoke the execute permission from PUBLIC and assign it to only those accounts that require access as a strict business requirement. More often than not it is application accounts that will need access. Each of the relevant packages are discussed in this section detailing what can be done with them.

UTL_TCP

UTL_TCP is the most basic of PL/SQL packages that can access the network, and because of this it is the most flexible. UTL_TCP can make TCP connections to other servers on the network and send and receive data. Further, there are no restrictions on the format of this data, meaning it can be binary or text-based. While this provides great flexibility to allow the RDBMS to communicate with any kind of server on the network that it needs to communicate with, be it a web server or an RPC server, it can be of great use to an attacker.

The key functions in this package are

OPEN_CONNECTION: Opens a socket to the remote host

READ_RAW: Reads binary data from the socket

WRITE_RAW: Writes binary data to the socket

READ_TEXT: Reads ASCII text from the socket

WRITE_TEXT: Writes ASCII text to the socket

Here's the code for a TCP port scanner, which shows a simple example of using UTL_TCP:

 CREATE OR REPLACE PACKAGE TCP_SCAN IS PROCEDURE SCAN(HOST VARCHAR2,  START_PORT NUMBER,  END_PORT NUMBER,  VERBOSE NUMBER DEFAULT 0); PROCEDURE CHECK_PORT(HOST VARCHAR2, TCP_PORT NUMBER, VERBOSE NUMBER DEFAULT 0); END TCP_SCAN; / SHOW ERRORS     CREATE OR REPLACE PACKAGE BODY TCP_SCAN IS PROCEDURE SCAN(HOST VARCHAR2, START_PORT NUMBER, END_PORT NUMBER, VERBOSE NUMBER DEFAULT 0) AS I NUMBER := START_PORT; BEGIN         FOR I IN START_PORT..END_PORT LOOP                 CHECK_PORT(HOST,I,VERBOSE);         END LOOP;     EXCEPTION WHEN OTHERS THEN         DBMS_OUTPUT.PUT_LINE('An error occured.'); END SCAN;         PROCEDURE CHECK_PORT(HOST VARCHAR2, TCP_PORT NUMBER, VERBOSE NUMBER DEFAULT 0) AS CN SYS.UTL_TCP.CONNECTION; NETWORK_ERROR EXCEPTION; PRAGMA EXCEPTION_INIT(NETWORK_ERROR,-29260); BEGIN         DBMS_OUTPUT.ENABLE(1000000);         CN := UTL_TCP.OPEN_CONNECTION(HOST, TCP_PORT);         DBMS_OUTPUT.PUT_LINE('TCP Port '  TCP_PORT  ' on '  HOST  ' is open.');     EXCEPTION WHEN NETWORK_ERROR THEN         IF VERBOSE !=0 THEN                 DBMS_OUTPUT.PUT_LINE('TCP Port '  TCP_PORT  ' on '  HOST  ' is not open.');         END IF;         WHEN OTHERS THEN                 DBMS_OUTPUT.PUT_LINE('There was an error.');     END CHECK_PORT;     END TCP_SCAN; / SHOW ERRORS 

UTL_HTTP

UTL_HTTP essentially wraps around UTL_TCP and provides a number of procedures to communicate with web servers. UTL_HTTP supports proxy servers, cookies, redirects, authentication, and so on. An attacker can use this package to launch attacks against web servers.

The following code is an example using UTL_HTTP:

 DECLARE      txt VARCHAR2(2000);      request   utl_http.req;      response  utl_http.resp; BEGIN      request := utl_http.begin_request('http://www.ngssoftware.com/');      utl_http.set_header(request, 'User-Agent', 'Mozilla/4.0');      response := utl_http.get_response(request);      LOOP           utl_http.read_line(response, txt, TRUE);           dbms_output.put_line(txt);      END LOOP;      utl_http.end_response(response);      EXCEPTION      WHEN utl_http.end_of_body THEN           utl_http.end_response(response); END; / 

UTL_SMTP

Like UTL_HTTP, UTL_SMTP relies on UTL_TCP and is a wrapper for sending e- mails . To use it, an understanding of the SMTP protocol would be useful. (See RFC 895.)

 DECLARE      c utl_smtp.connection; BEGIN      c := utl_smtp.open_connection('smtp.example.com');      utl_smtp.helo(c, 'ngssoftware.com');      utl_smtp.mail(c, '[e-mail]');      utl_smtp.rcpt(c, '[e-mail]');      utl_smtp.open_data(c);      utl_smtp.write_data(c,'Subject: NGSSQuirreL');      utl_smtp.write_data(c, utl_tcp.CRLF            'I want it for x-mas!');      utl_smtp.close_data(c);      utl_smtp.quit(c); END; /