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

ORA-24247

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;

UTL_INADDR.GET_HOST_NAME()
--------------------------------------------------------------------------------
primary01.example.com

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.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  acl => 'Connect_Access.xml',
  description => 'Connect Network',
  principal => 'HR',
  is_grant => TRUE,
  privilege => 'resolve',
  start_date => NULL,
  end_date => NULL);
END;
/

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.

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

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.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
  acl => 'Connect_Access.xml',
  principal => 'SH',
  is_grant => TRUE,
  privilege => 'connect',
  position => NULL,
  start_date => NULL,
  end_date => NULL);
END;
/

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

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

ACL
------------------------------
/sys/acls/Connect_Access.xml

SQL> column principal format a5;
SQL> column privilege format a10;
SQL> SELECT acl, principal, privilege FROM DBA_NETWORK_ACL_PRIVILEGES;

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.
SQL> select UTL_INADDR.get_host_name() from dual;

UTL_INADDR.GET_HOST_NAME()
--------------------------------------------------------------------------------
primary01.example.com

It succeed to resolve a hostname.

2. Test the privilege connect of user SH.

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.

DECLARE
  c UTL_SMTP.CONNECTION;

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

BEGIN
  c := UTL_SMTP.OPEN_CONNECTION('ms1.hinet.net');
  UTL_SMTP.HELO(c, 'foo.com');
  UTL_SMTP.MAIL(c, '[email protected]');
  UTL_SMTP.RCPT(c, '[email protected]');
  UTL_SMTP.OPEN_DATA(c);
  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.');
  UTL_SMTP.CLOSE_DATA(c);
  UTL_SMTP.QUIT(c);
EXCEPTION
  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
    BEGIN
      UTL_SMTP.QUIT(c);
    EXCEPTION
      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.
    END;
    raise_application_error(-20000,
      'Failed to send mail due to the following error: ' || sqlerrm);
END;
/

It works, and the testing email as:

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.

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

Connecting to the database ORCLPDB.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP('192.168.30.128', '49216')

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

Enable DBMS_DEBUG

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