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

Hadoop HDFS Permissions

Updated 4/10/13

It looks like the good doc on permissions is the HDFS Permissions Guide. The HDFS Permissions Guide link changed or is obsolete, but a google search with “hdfs permissions guide” and you should find a good reference.

My simple view is that all the user permissions work the same as unix, with out all the stickyness (no sticky bits etc.). For the group permissions, the supergroup is shown; and this belongs the the owner of the namenode process.

Seems pretty direct.