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
/


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