Skip to content

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


There're two error patterns of ORA-24247 in this post.

  1. Resolve or Connect Privilege
  2. Debug Mode in SQL Developer

Resolve or Connect Privilege

For 10g database users, there could be no issues to communicate with external network using public UTL_INADDR synonym to resolve hostname. But for 11g database users, there is a problem to use UTL_INADDR, the synonym is still public, but you may have no "right" to communicate with outside world.

Let's see a case in 10g database, UTL_INADDR is a public synonym.

$ sqlplus / as sysdba
SQL> SELECT owner, object_type, status FROM dba_objects WHERE object_name='UTL_INADDR';

OWNER                          OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
SYS                            PACKAGE BODY        VALID
SYS                            PACKAGE             VALID
PUBLIC                         SYNONYM             VALID

An user HR in 10g database can resolve the hostname as following.

$ sqlplus hr/hr
SQL> select UTL_INADDR.get_host_name() from dual;


Since 11g database introduces Access Control List (ACL) to control the limited network resource and prevent security leaks, there is no more open like 10g was. Hence, in 11g database, it won't work.

$ sqlplus hr/hr
SQL> select UTL_INADDR.get_host_name() from dual;
select UTL_INADDR.get_host_name() from dual
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 4
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at line 1

ORA-24247 means that the user should have the right network privilege in Access Control List (ACL) to resolve hostname or connect to any external servers.

There are two basic privileges in ACL to allow users to communicate with external network, one is resolve, which has the ability to resolve hostname, domain name and IP address; the other is connect, which has the ability to act as a client to connect an external host through network protocols, i.e. SMTP.

Solution to ORA-24247

If this is the first time that a user ask for specific network function, DBA must creates an ACL first. There're 3 steps to solve our problem.

Create ACL

Create an ACL: In this case, we create an ACL with a initial user HR, and the privilege is resolve.

  acl => 'Connect_Access.xml',
  description => 'Connect Network',
  principal => 'HR',
  is_grant => TRUE,
  privilege => 'resolve',
  start_date => NULL,
  end_date => NULL);

Please note that, principal is the username who initially asked for the privilege, and it must be in upper case.

The ACL document in XML format looks like a local file, but it stores as a CLOB in the database. Beside, you can customize the file name to meet your requirement.

Assign ACL

Assign the ACL to a specific network: We open the widest scope '*' to users.

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'Connect_Access.xml',
  host => '*',
  lower_port => NULL,
  upper_port => NULL);

If there is any other user asks for a privilege to connect, you can add a privilege to the ACL for this user.

Add Privilege

Add another user to this ACL: We give user SH a privilege of connect.

  acl => 'Connect_Access.xml',
  principal => 'SH',
  is_grant => TRUE,
  privilege => 'connect',
  position => NULL,
  start_date => NULL,
  end_date => NULL);

After creating the ACL, let's make sure the setting matched our expectation.

$ sqlplus / as sysdba
SQL> column acl format a30;


SQL> column principal format a5;
SQL> column privilege format a10;

ACL                            PRINC PRIVILEGE
------------------------------ ----- ----------
/sys/acls/Connect_Access.xml   HR    resolve
/sys/acls/Connect_Access.xml   SH    connect

More Tests on ACL

The above result is correct, so we can test the privileges.

  1. Test the privilege resolve of user HR.
  2. SQL> select UTL_INADDR.get_host_name() from dual;


    It succeed to resolve a hostname.

  3. Test the privilege connect of user SH.
  4. Here we use an anonymous PL/SQL block to test the function, which is an example of using UTL_SMTP provided by Oracle 11g Documentation, we just modified it for testing purpose.


      PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
        UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);

      UTL_SMTP.HELO(c, '');
      UTL_SMTP.MAIL(c, '[email protected]');
      UTL_SMTP.RCPT(c, '[email protected]');
      send_header('From',    '"From Blog Tester" <[email protected]>');
      send_header('To',      '"To Blog Tester" <[email protected]>');
      send_header('Subject', 'This is a test for network connection');
      UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'If you receive this mail, you are able to connect external network.');
      WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
            NULL; -- When the SMTP server is down or unavailable, we don't have
                  -- a connection to the server. The QUIT call will raise an
                  -- exception that we can ignore.
          'Failed to send mail due to the following error: ' || sqlerrm);

    It works, and the testing email as:

    ACL Connect Test
    ACL Connect Test

Since HR has only resolve privilege, he will get errors as following if he execute the same block of code.

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 246
ORA-06512: at "SYS.UTL_SMTP", line 127
ORA-06512: at "SYS.UTL_SMTP", line 150
ORA-06512: at line 10

As we can see, HR is prohibited to connect the network, which means ACL is taking effect.

Debug Mode in SQL Developer

To debug a programming unit of PL/SQL in SQL Developer, you can click on the bug icon to enable the function. But later on, you might see ORA-24247.

Oracle SQL Developer - Debug Icon
Oracle SQL Developer - Debug Icon

Unfortunately, most developers met a stack of errors like this:

SQL Developer - ORA-24247 when using DBMS_DEBUG_JDWP
SQL Developer - ORA-24247 when using DBMS_DEBUG_JDWP
Connecting to the database ORCLPDB.

ORA-24247: network access denied by access control list (ACL)
ORA-06512: AT "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: AT line 1
Process exited.
Disconnecting from the database ORCLPDB.

ORA-24247 means that the default debug method in SQL developer is to use DBMS_DEBUG_JDWP in order to communicate with the database, which requires users have the permission to connect back to client's tool through network.

Solution to ORA-24247

Although we can try to add the client's IP address or hostname to the white list of ACL in the database, the solution usually fails.

To avoid ACL problem, we can switch the debug method to the normal DBMS_DEBUG in preferences of SQL developer.

Go to Preferences

SQL Developer - Preferences
SQL Developer - Preferences


SQL Developer - Enable DBMS_DEBUG
SQL Developer - Enable DBMS_DEBUG

Next, I guess you might be interested in changing the user interface language of SQL developer to increase your programming productivity.

12 thoughts on “How to Resolve ORA-24247: network access denied by access control list (ACL)”

  1. I tried so many suggestions on ACL error; however yours is the perfect one and it worked.
    You made no assumptions and provided all the steps needed to get rid of “ORA-24247” error.

    Thank you.

  2. Please help me I am unable to remove ora-24247 on localhost:9090 even after trying all your suggestions.
    I Have created a sample program but I am unable to resolve it
    I tried running the above commands in SQL PLUS they say
    PLSQL procedure successfully completed
    but error is not resolved

    1. Where “Connect_Access.xml” is an user’s ACL file name which contains some privilege’s information in XML format. You name the file when creating it. It looks like a local file, but it stores in the database.

  3. These procedures mentioned as deprecated.

    host => ‘&host_url’,
    ace => xs$ace_type(privilege_list => xs$name_list(‘connect’, ‘resolve’),
    principal_name => upper(‘&username’),
    principal_type => xs_acl.ptype_db));

Leave a Reply

Your email address will not be published. Required fields are marked *