Ошибка 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;

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, 'edchenlogic@gmail.com');
  UTL_SMTP.RCPT(c, 'edchenlogic@gmail.com');
  UTL_SMTP.OPEN_DATA(c);
  send_header('From',    '"From Blog Tester" <edchenlogic@gmail.com>');
  send_header('To',      '"To Blog Tester" <edchenlogic@gmail.com>');
  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:



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.

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.

Before Oracle 11g access to network services was controlled by granting privileges on packages such as UTL_HTTP, UTL_TCP, UTL_SMTP, and UTL_MAIL. After 11.1 Oracle introduced Application Control Lists (ACL) as part of their Application Security and has now added Application Control Entry (ACE).

If you run into the ORA-24247: network access denied by access control list (ACL) error you can use one of the following methods to resolve the error.

1. The best solution to the ORA-24247 error is to create an ACE using the DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE procedure to grant access control privileges to a user. According to Oracle documentation, the procedure will append an access control entry with specified privilege to the ACL for the given host. If the ACL does not exist it will create it for you. The syntax for the procedure is listed below.

BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
  host         => 'host_name', 
  lower_port   => null|port_number,
  upper_port   => null|port_number,
  ace          => ace_definition); 
END;

• host: can be either the ip address or the hostname. You can also use a wildcard for a domain or an IP subnet.
  host => ‘mailhost.com’
  host => ‘*.domain.com’

• lower_port and upper_port: these values are used to set the lower and upper port range. This is only used for the connect privilege and can be omitted for the resolve privilege. If set to null then there are no port restrictions.
  lower_port => 80
  upper_port => 3999

• ace: You define the ACE by using the XS$ACE_TYPE constant with the following specifications.
  • privilege_list: this can be one or more of the following, http, http_proxy, smtp, resolve, connect, jdwp. Enclose each privilege with single quotes and separate each with a comma.
  • principal_name: enter either a database user or role.
  • principal_type: enter xs_acl_ptype_db for a database user or role.

In this example, the user Scott is being granted network access to send SMTP to a host, mailhost.com, through the UTL_SMTP and UTL_MAIL packages.

BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
(
HOST => 'mailhost.com',
LOWER_PORT => NULL,
UPPER_PORT => NULL,
ACE => XS$ACE_TYPE(PRIVILEGE_LIST => xs$name_list('smtp'),
    			     PRINCIPAL_NAME => 'Scott',
    		     PRINCIPAL_TYPE => xs_acl.ptype_db
   		   )
);
END;
/

1. The second method for resolving the ORA-24247 error is to grant the user requesting network access the XDBADMIN role.
   SQL> grant XDBADMIN to Scott;

This will grant an extra privilege to the Oracle user and is not recommended.

Recently we have switched from Oracle 10g to 11g, and only now I noticed that my mailing function does not work, I now get an error:

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

So I did a bit of googling and was able to figure out that a new feature in Oracle 11g is now restricting users from using certain packages including utl_smtp. Because I am looking for a quick solution I did not read Oracle documentation, but instead I went looking for easier solutions and came across this tutorial:

https://www.pythian.com/blog/setting-up-network-acls-in-oracle-11g-for-dummies/

I messed around with it a little bit, but because I did not know any better I think I added two seperate configuration .xml files. So first part of my question is — HOW DO I REMOVE IT?

Second question is:

After adding some grants to my user I try to test to see if it worked, but I soon realised it did not:

  SELECT DECODE(
         DBMS_NETWORK_ACL_ADMIN.check_privilege('netacl.xml', 'TEST1', 'connect'),
         1, 'GRANTED', 0, 'DENIED', NULL) privilege 
FROM dual;

Returns:

PRIVILE
-------
DENIED

WHY?(THIS HAS BEEN SORTED)

Third part of the question — after reading it was denied I try to fix it like:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('netacl.xml' ,'TEST1', TRUE, 'connect');
END;

But that gives me an error:

Ora19279 - XQuery dynamic type mismatch.....(more text meaning nothing to me).

WHY?(I FIGURED OUT, THAT ERROR HAPPENS WHEN YOU GRANT SAME PERMISSION TO SAME USER SECOND TIME)

UPDATE

I have followed the suggested answer by kevinsky below and have learned quite a bit in the process, however I still have a problem. I still get the ORA-24247: network access denied by access control list (ACL). Because I did everything else as suggested, I am starting to think that the problem could be that first configuration file which I added, but cannot remove now because I cannot remember its name. If anyone can help me I would appreciate that very much.

RESULTS OF(I was trying out a few different things so):

select * from dba_network_acls;

Returns

*                              | 25 | 25 | /sys/acls/utl_smtp.xml| ACLID...
myservername.com               | 25 | 25 | /sys/acls/utl_smtp.xml| ACLID...
myDBName                       | 25 | 25 | /sys/acls/utl_smtp.xml| ACLID...
mailServerDomainName           | 25 | 25 | /sys/acls/utl_smtp.xml| ACLID...
mailserver.myDomain.local      | 25 | 25 | /sys/acls/utl_smtp.xml| ACLID...

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

ERROR at line 1: ORA-24247: network access denied by access control list (ACL) ORA-06512: at “SYS.UTL_MAIL”, line 654 ORA-06512: at “SYS.UTL_MAIL”, line 671 ORA-06512: at line 1

This is because the user SCOTT has no rights to connect to the mail or SMTP servers. The user must be added to the ACL

Create ACL and privilege

The ACL is created as a file and manages the process of handing out rights and privileges. First we are going to create an ACL as SYS or another user with the right to execute DBMS_NETWORK_ACL_ADMIN . This file hold the rights to . You can add as many rights to this file.