Monday, March 07, 2011

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

When we migrated to use Oracle Database version >= 11gR1 (from version <= 10gR2 ). We should think about PL/SQL Code. When users attempt to access External Network Services. We have to implement Access Control Lists (ACL) by using DBMS_NETWORK_ACL_ADMIN package. If not you may see: ORA-24247: network access denied by access control list (ACL)
SQL> show user;
USER is "DEMO"

SQL> DECLARE
2 c utl_tcp.connection;
3 n number;
4 BEGIN
5 c := utl_tcp.open_connection('127.0.0.1',80);
6 n := utl_tcp.write_line(c, 'GET / HTTP/1.1');
7 n := utl_tcp.write_line(c);
8 BEGIN
9 LOOP
10 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
11 END LOOP;
12 EXCEPTION
13 WHEN utl_tcp.end_of_input THEN
14 NULL;
15 END;
16 utl_tcp.close_connection(c);
17 END;
18/
DECLARE
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at line 5
Example: Implement ACL
On user - DBA privilege:
SQL> BEGIN
2 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
3 acl => 'http_service.xml',
4 description => 'HTTP ACL',
5 principal => 'DEMO',
6 is_grant => true,
7 privilege => 'connect');
8
9 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
10 acl => 'http_service.xml',
11 principal => 'DEMO',
12 is_grant => true,
13 privilege => 'resolve');
14
15 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
16 acl => 'http_service.xml',
17 host => '127.0.0.1');
18
19 COMMIT;
20 END;
21 /

SQL> SELECT host, acl,DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'DEMO', 'connect'), 1, 'GRANTED', 0,'DENIED', null) privilege FROM dba_network_acls;

HOST ACL PRIVILE
---------- ------------------------------ -------
127.0.0.1 /sys/acls/http_service.xml GRANTED
On ... user:
SQL> DECLARE
2 c utl_tcp.connection;
3 n number;
4 BEGIN
5 c := utl_tcp.open_connection('127.0.0.1',80);
6 n := utl_tcp.write_line(c, 'GET / HTTP/1.1');
7 n := utl_tcp.write_line(c);
8 BEGIN
9 LOOP
10 dbms_output.put_line(utl_tcp.get_line(c, TRUE));
11 END LOOP;
12 EXCEPTION
13 WHEN utl_tcp.end_of_input THEN
14 NULL;
15 END;
16 utl_tcp.close_connection(c);
17 END;
18/
HTTP/1.1 200 OK
read more.. Oracle Document

No comments: