Scanning for listening ports on other servers with PL/SQL

Posted: 5/11/12 Modified: 8/19/13

Had a chance to help a colleague with a connection testing tool. I created a similar tool and thought I’d share it. This tool could be used for scanning listening ports like SSH, HTTP, ISCSI, Oracle DB, hadoop etc. Or even if a port is open and then not, an alert could go out requesting a check on that server (maybe it’s offline or a network issue has come up). For example if port 1521 stops listening on the database.

The app I wrote scans a range of ports and could easily be simplified to scan a single port. I had a little bit of a hard time figuring out the permissions, a change for 11g, so I got some help at Zahid Karim’s blog or Don Sieler’s post at Pythian. You should read it if you are unfamiliar with the Oracle network connection security.

I create a procedure to set up the permissions (this could be simpler, see Don’s post mentioned above or Simple ACL Permissions Example):


create or replace PROCEDURE conn_grant_access /* procedure conn_grant_access v1.0.0 dcox 5/14/2012 */
(p_access_file_name varchar2, -- access file name with no extension (eg test_machine_enkitec)
p_FQDN varchar2, -- Fully Qualified Domain Name (eg testMachine.enkitec.com)
p_user varchar2 default user -- username (eg dcox)
)
is

/*
Note: For compiling this procedure
Grant permissions to run this as dcox
grant execute on sys.dbms_network_acl_admin to dcox;

Check with:
select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
select * from dba_network_acl_privileges

Purpose: Grants access permissions for network access

MODIFICATION HISTORY
Version Person Date Comments
------- --------- ------ -------------------------------------------
1.0.0 dcox 5/14/2012 Initial Build

*/

BEGIN

dbms_network_acl_admin.create_acl(acl => p_access_file_name||'.xml',
description => 'Full Access',
principal => Upper(p_user),
is_grant => TRUE,
privilege => 'connect',
start_date => sysdate-1,
end_date => sysdate+720);
dbms_network_acl_admin.add_privilege(acl => p_access_file_name||'.xml',
principal => Upper(p_user),
is_grant => TRUE,
privilege => 'resolve',
start_date => sysdate-1,
end_date => sysdate+720);

dbms_network_acl_admin.assign_acl(acl => p_access_file_name||'.xml',
host => p_FQDN,
lower_port => 1,
upper_port => 65535);

commit;
END;
/

See the notes in the function:
grant execute on sys.dbms_network_acl_admin to dcox;

Let’s try:
procedure conn_grant_access
('test_machine_enkitec',
'testMachine.enkitec.com',
'connect' ,
'DCOX'
)

You can check that the permissions were set up with the query:
sql> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

Now a quick test utility to check SSH port 22 to see if it all worked:

CREATE OR REPLACE
FUNCTION dcox.fn_test_connection
RETURN NUMBER
IS
c UTL_TCP.connection; -- Connection
v_line_no INTEGER := 0; -- debug line no
BEGIN
v_line_no := 100;
c := utl_tcp.open_connection(remote_host=>'testMachine.enkitec.com',remote_port=>22);

v_line_no := 200;
-- Add port to list
DBMS_OUTPUT.put_line (SUBSTR ('Valid Port: ' || TO_CHAR (22), 1, 255));

UTL_TCP.close_all_connections;
RETURN 0;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line(substr('Value of sqlerrm='||to_char(sqlerrm),1,255));
DBMS_OUTPUT.put_line (
SUBSTR ('Value of v_line_no=' || TO_CHAR (v_line_no), 1, 255));

UTL_TCP.close_all_connections; -- continue when fails
RETURN -20001;
END;
/

DBMS Output should show:
Valid Port: 22

and then the package to scan a range of ports:

CREATE OR REPLACE
FUNCTION dcox.fn_scan_open_ports (p_hostname IN VARCHAR2, -- hostname
p_port_start IN PLS_INTEGER, -- port number start
p_port_end IN PLS_INTEGER -- port number end
)
RETURN VARCHAR2
IS
/*

Purpose: Scan open ports to see which ones are listening for connections

MODIFICATION HISTORY
Person Date Comments
--------- ------ -------------------------------------------
dcox 5/10/2012 Initial Build

*/
c UTL_TCP.connection; -- Connection
v_line_no integer :=0; -- debug line no
BEGIN
v_line_no := 100; -- debug line no
FOR nport IN p_port_start .. p_port_end
LOOP
v_line_no := 200; -- debug line no

BEGIN
v_line_no := 300; -- debug line no
c := utl_tcp.open_connection(remote_host=>p_hostname,
remote_port=>nPort);

v_line_no := 400; -- debug line no

UTL_TCP.close_connection (c);
v_line_no := 600; -- debug line no
-- Add port to list
DBMS_OUTPUT.put_line (
SUBSTR ('Valid Port: ' || TO_CHAR (nport), 1, 255));
dbms_output.put_line('------------------------------');
EXCEPTION
WHEN OTHERS
THEN

UTL_TCP.close_all_connections; -- continue when fails
END;

END LOOP; -- end of port loop

RETURN 0;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
SUBSTR ('Value of sqlerrm=' || TO_CHAR (SQLERRM), 1, 255));
dbms_output.put_line(substr('Value of v_line_no='||to_char(v_line_no),1,255));
RETURN -20001;
END;
/

Now try it out:

dcox.fn_scan_open_ports (testMachine.enkitec.com, -- hostname
1, -- port number start
200 -- port number end
);
/

And get:
Valid Port: 22
——————————
Valid Port: 80
——————————
Valid Port: 111
——————————
Crude output, but it works.

You’re Done!

Modified: 5/14/12 – Changed procedure conn_grant_access

One thought on “Scanning for listening ports on other servers with PL/SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s