rasti.hil@hilandco.com +41 79 367-9677

Wednesday, 25 November 2009

ORA-24247: network access denied by access control list (ACL)

This mean that your user can't access to network.
Solution without any description pleas read the following source

create or replace
PROCEDURE CREATE_ACL(IN_USERNAME IN VARCHAR2,
IN_HOST IN VARCHAR2,
IN_PORT_START IN NUMBER DEFAULT NULL,
IN_PORT_END IN NUMBER DEFAULT NULL)
AS
ACL VARCHAR2(4000);
ACL_NAME VARCHAR2(4000);

l_port NUMBER;
u_port NUMBER;
BEGIN
-- create ACL name
acl_name := LOWER(in_host);
acl_name := REPLACE(acl_name, '.', '_');
acl_name := acl_name||'.xml';

-- chech if ACL is existing for a given host
-- if not than create a new ACL list
-- if yes than just add a user to list

FOR C1 IN (SELECT ACL FROM SYS.dba_network_acls where ACL='/sys/acls/'||acl_name)
LOOP
acl := C1.ACL;
END LOOP;

IF acl IS NOT NULL THEN
-- add to an existing list the user with CONNECT privilige
DBMS_NETWORK_ACL_ADMIN.add_privilege (acl => acl_name,
principal => upper(in_username),
is_grant => TRUE,
privilege => 'connect',
position => NULL,
start_date => NULL,
end_date => NULL);
COMMIT;
-- add to an existing list the user with RESOLVE privilige
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => acl_name,
principal => upper(in_username),
is_grant => true,
privilege => 'resolve');
COMMIT;
ELSE
-- create an ACL
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => acl_name,
description => in_host||' connection list',
principal => upper(in_username),
is_grant => true,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
-- assign host to ACL
-- now you can define port number
-- note[1] witn an existing ACL you can't define port numbers
-- if port numbers are NULL's then create a default one
IF in_port_start IS NULL THEN
l_port := 1;
ELSE
l_port := in_port_start;
END IF;
IF in_port_end IS NULL THEN
u_port := 10000;
ELSE
u_port := in_port_end;
END IF;
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => acl_name,
host => in_host,
lower_port => l_port,
upper_port => u_port);
COMMIT;
-- add to newly created list the user with RESOLVE privilige
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => acl_name,
principal => upper(in_username),
is_grant => true,
privilege => 'resolve');

COMMIT;
END IF;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END CREATE_ACL;

Step 2) use it:
note[1] I assume that CREATE_ACL procedure is created under sys user.
SQL> exec CREATE_ACL(IN_USERNAME   => 'scott',
IN_HOST => 'www.google.com',
IN_PORT_START => 80,
IN_PORT_END => 80);
SQL> conn scott/tiger
Connected.
SQL> select utl_http.request('http://www.google.com') from dual;
UTL_HTTP.REQUEST('HTTP://WWW.GOOGLE.COM')
--------------------------------------------
<!doctype html><html><head ...



janos ujvari @ 25th November 2009

No comments: