ORA-24247: network access denied by access control list

Applies to:

PL/SQL - Version 11.1.0.6 and later
Information in this document applies to any platform.
***Checked for relevance on 26-Jun-20136***

Prior to 11gR1 you were able to run PL/SQL code making calls to the following Oracle Supplied Packages without error.

UTL_TCP
UTL_HTTP
UTL_SMTP
UTL_MAIL

After moving to 11gR1 or later you are now experiencing a form of the following error message...

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

In 11gR1 the Oracle Database enhanced the level of security when users attempt to access External Network Services by implementing Access Control Lists (ACL) using the new DBMS_NETWORK_ACL_ADMIN package. The PL/SQL packages listed above were affected. For more information on this change to Oracle Database Security please review the following

Solution

To resolve this error, connect to the database as a user with DBA privileges and issue the following script which will grant a user access to External Network Services. Be sure to replace the name of user SCOTT with the name of the user in which you want to grant access. This value is case-sensitive.

BEGIN

-- Only uncomment the following line if ACL "network_services.xml" has already been created
--DBMS_NETWORK_ACL_ADMIN.DROP_ACL('network_services.xml');

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl => 'network_services.xml',
    description => 'NETWORK ACL',
    principal => 'SCOTT',
    is_grant => true,
    privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl => 'network_services.xml',
    principal => 'SCOTT',
    is_grant => true,
    privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl => 'network_services.xml',
    host => '*');

COMMIT;

END;

Elapsed: 00:00:00.01

11:19:36 sys@warehous>BEGIN

11:20:20 2 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (

11:20:20 3 acl => 'mailserver_acl.xml',

11:20:20 4 description => 'Network connection permission for ACCT_MGR',

11:20:20 5 principal => 'DW001', -- Must be in upper case

11:20:20 6 is_grant => TRUE,

11:20:20 7 privilege => 'connect');

11:20:20 8 END;

11:20:20 9 /

BEGIN

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (

acl => 'mailserver_acl.xml',

host => '*',

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

11:20:20 sys@warehous>11:20:20 sys@warehous>11:20:20 2 11:20:20 3 11:20:20 4 11:20:20 5 lower_port => 25,

11:20:20 6 upper_port => 25);

11:20:20 7 END;

11:20:20 8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09

11:20:26 sys@warehous>

11:20:28 sys@warehous>

11:20:29 sys@warehous>commit;

Commit complete.

15:15:30 sys@warehous>select host, lower_port, upper_port, acl from dba_network_acls;

HOST LOWER_PORT UPPER_PORT ACL

------------------------------ ---------- ---------- --------------------------------------------------

* 25 25 /sys/acls/mailserver_acl.xml

15:17:26 sys@warehous>select acl,

15:17:47 2 principal,

15:17:47 3 privilege,

15:17:47 4 is_grant

15:17:47 5 from dba_network_acl_privileges;

ACL PRINCIPAL PRIVILE IS_GRANT

-------------------------------------------------- ------------------------------ ------- --------------------

/sys/acls/mailserver_acl.xml DW001 connect true