ORA-24247: Network Access Denied by Access Control List (ACL) - Troubleshooting Guide

When encountering the ORA-24247 error, users may find that they are lacking the necessary network privileges within the Access Control List (ACL) to perform hostname resolution or establish connections with external servers. The ACL contains two primary privileges that allow users to interact with external networks: "resolved," which enables users to resolve hostnames, domain names, and IP addresses, and "connected," which grants users the ability to act as a client and establish connections with external hosts using network protocols such as SMTP.

This error can be frustrating for users who need to access external networks, but fortunately, there are solutions available. By ensuring that the appropriate privileges have been granted to the user in the ACL, users can resolve the ORA-24247 error and continue using external network services without issue.

Key Takeaways

The ORA-24247 error is caused by a lack of necessary network privileges within the Access Control List (ACL).
The ACL contains two primary privileges that allow users to interact with external networks: "resolved" and "connected."
By ensuring that the appropriate privileges have been granted in the ACL, users can resolve the ORA-24247 error and continue using external network services.

Solution to ORA-24247

To solve the ORA-24247 error, the DBA must create an ACL that grants the user the right network privilege in the Access Control List (ACL). Here are the three steps to solve the problem:

Create ACL

The first step is to create an ACL. In this case, the ACL is created with an initial user HR, and the privilege is resolved. The principal is the username who initially asked for the privilege, and it must be in upper case.

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;
/

Assign ACL

The second step is to assign the ACL to a specific network. The widest scope '*' is opened to users.

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

If another user asks for a privilege to connect, you can add a privilege to the ACL for this user.

Add Privilege

The third step is to add another user to this ACL. In this case, user SH is given the privilege of connecting. Position "Null" is the default, and position 1 overrides the same privilege but with the value 'false'. The privileges are chosen from the highest value.

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;
/

Once the ACL is generated, it's essential to verify whether the settings align with our intended configuration.

Position "Null" is the default and position 1 overrides the same privilege but with

the value 'false'.

The privileges are chosen from the highest value

$ 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

After creating the ACL, let's make sure the setting matches our expectations.

$ 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

OR

SET LINESIZE 500;
COLUMN ACL_OWNER format a20;
COLUMN START_DATE format a10;
COLUMN end_date format a10;
column privilege format a10;
column principal format a10;

SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

Finally, this is an alternative select that checks your privilege via an ACLID.

SELECT acl,
host,
DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(aclid, 'DW', 'connect'),
1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM   dba_network_acls;

More information can be found on Oracle Meta Link: Document 1080105.1.

Frequently Asked Questions

What is an Oracle ACL and how does it work?

An Oracle Access Control List (ACL) is a security mechanism that controls network connections from Oracle Database to external network services. The ACL is a list of access control entries (ACEs) that specify which users or roles can connect to which hosts and ports. When a user or application tries to connect to an external network service, the Oracle Database checks the ACL to determine if the connection is allowed.

How to create an ACL in Oracle 19c?

To create an ACL in Oracle 19c, you can use the DBMS_NETWORK_ACL_ADMIN package. The following steps outline the basic process:

Identify the host and port that you want to allow access to.
Create an ACE that specifies the host and port, and the user or role that should be granted access.
Create the ACL and add the ACE to it.
Assign the ACL to the network resource that you want to control access to.

How can I resolve 'Ora-24247 network access denied by access control list ACL' error?

The 'Ora-24247 network access denied by access control list ACL' error occurs when a user or application tries to connect to an external network service, but the ACL does not grant them the necessary privileges. To resolve this error, you need to identify the host and port that the connection is trying to access, and then create an ACE in the ACL that grants the necessary privileges to the user or role. You can use the DBMS_NETWORK_ACL_ADMIN package to create and manage ACLs.

What are some common causes of 'Ora-24247 network access denied by access control list ACL' error?

The most common cause of the 'Ora-24247 network access denied by access control list ACL' error is that the user or role does not have the necessary privileges to access the external network service. Other possible causes include incorrect ACL configuration, incorrect network service configuration, and network connectivity issues.

How to troubleshoot 'Ora-24247 network access denied by access control list ACL' error?

To troubleshoot the 'Ora-24247 network access denied by access control list ACL' error, you should first check the ACL configuration to ensure that the user or role has the necessary privileges. You should also check the network service configuration to ensure that it is correctly configured to accept connections. If the ACL and network service configurations are correct, you should check for network connectivity issues, such as firewalls or network access control (NAC) systems that may be blocking the connection.

What is the significance of 'SELECT * FROM dba_network_acls' in Oracle?

The SELECT * FROM dba_network_acls command is used to query the Oracle Database for information about the ACLs that are currently defined. This command returns a list of all ACLs, along with information about the host, port, and privileges that are granted to each user or role. This command can be useful for troubleshooting ACL configuration issues, or for auditing ACL usage across the database.

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