Simple Permissions Example for ACL and Network Test

Here a simple example for granting the permissions to all hosts and all ports for a user. Then below is a function to test the network connection.

For scanning ports see: Scanning Ports Example

DECLARE
    p_user VARCHAR2 ( 30 ) := 'DCOX'; -- user to receive grants
BEGIN

    DBMS_NETWORK_ACL_ADMIN.create_acl (
                                        acl => 'everything.xml',
                                        description => 'Full Access',
                                        principal => UPPER ( p_user ),
                                        is_grant => TRUE,
                                        privilege => 'connect',
                                        start_date => SYSDATE - 1,
                                        end_date => NULL
                                       );
    DBMS_NETWORK_ACL_ADMIN.add_privilege (
                                           acl => 'everything.xml',
                                           principal => UPPER ( p_user ),
                                           is_grant => TRUE,
                                           privilege => 'resolve',
                                           start_date => SYSDATE - 1,
                                           end_date => NULL
                                          );

    DBMS_NETWORK_ACL_ADMIN.assign_acl (
                                        acl => 'everything.xml',
                                        HOST => '*', -- All hosts and ports
                                        lower_port => 1,
                                        upper_port => 65535
                                       );

    COMMIT;
END;
/

Function to test a single port:

create or replace FUNCTION fn_check_network
(p_remote_host in varchar2, -- host name
 p_port_no in integer default 1521 -- port number
 )
  RETURN  number IS
/*

    Purpose: Check Network Connection to tns port

    MODIFICATION HISTORY
    Person      Date        Comments
    ---------   ------      -------------------------------------------
    dcox        8/19/2013    Initial Build

*/
    v_connection   utl_tcp.connection; -- tcp connection
BEGIN

    v_connection := utl_tcp.open_connection(REMOTE_HOST=>p_remote_host, REMOTE_PORT=>p_port_no, IN_BUFFER_SIZE=>1024, OUT_BUFFER_SIZE=>1024,   TX_TIMEOUT=>5);
    RETURN 0 ;
EXCEPTION
   WHEN others THEN
       return sqlcode ;
END fn_check_network;   -- Function fn_check_network
/

Don’t forget to grant UTL_TCP to the user where you create the function. And if you add the following function grant execute on DBMS_NETWORK_ACL_ADMIN to that user too.

More complicated, but easier to maintain:

CREATE OR REPLACE FUNCTION dcox.fn_grant_acl_permission ( p_user IN VARCHAR2 -- username to grant acl permissions
                                                                             )
    RETURN NUMBER
IS
BEGIN

    DBMS_NETWORK_ACL_ADMIN.create_acl (
                                        acl => p_user || '_everything.xml',
                                        description => 'Full Access',
                                        principal => UPPER ( p_user ),
                                        is_grant => TRUE,
                                        privilege => 'connect',
                                        start_date => SYSDATE - 1,
                                        end_date => NULL
                                       );
    DBMS_NETWORK_ACL_ADMIN.add_privilege (
                                           acl => p_user || '_everything.xml',
                                           principal => UPPER ( p_user ),
                                           is_grant => TRUE,
                                           privilege => 'resolve',
                                           start_date => SYSDATE - 1,
                                           end_date => NULL
                                          );

    DBMS_NETWORK_ACL_ADMIN.assign_acl (
                                        acl => p_user || '_everything.xml',
                                        HOST => '*', -- All hosts and ports
                                        lower_port => 1,
                                        upper_port => 65535
                                       );

    COMMIT;
    RETURN 0; -- success
EXCEPTION
    WHEN OTHERS
    THEN
        RETURN SQLCODE;
END fn_grant_acl_permission; -- Function fn_grant_acl_permission
/


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