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
