Ошибка ora 24247 network access denied by access control list acl

Обновлено: 07.07.2024

I am sending a mail thru OWB 11g ( and using database 11g) after successful or failure of process. My process is completing successfully but am not able to send mail. At the time of sending mail it is giving me error as below

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 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at line 8

I have created ACL using the following code

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

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'oramail.xml',
host => '141.146.46.30');

where 141.146.46.30 is my mail server IP. I am still not able to send mail by OWB. Please let me know if I have missed any steps in this.


If you are trying to use oracle to invoke a web service or to connect to a smtp service and you get the following error:

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

The cause according to oracle is that

Since Oracle 11, oracle introduced a fine grained access to network services using access control lists (ACL).

This new feature gave the DBA a better control on which user can connect to which computer

In order to solve ORA-24247 you will need to:

1) Create an acl (if it is not already created)

2) Add privileges to the user using the network resources

3) Assign the acl to a specific address

1) run the following query to check if an ACL exists

If the computer you are trying to connect to is not listed under host, you will need to create an acl:

principal => 'SCOTT', -- the user name trying to access the network resource

This will create the acl and grant SCOTT the connect privilege.

2) IF the acl exists run the following query to verify the user is granted with the appropriate privilege

If you need to resolve a host name from a host IP you will need the resolve grant as well.

3) The final step is to assign the acl to a specific target

It is important to note that only one ACL can be assigned to any host computer. If you assign a new acl to a target the old acl gets unassigned.

However, the old acl is not dropped. So, this could cause confusion because even if the acl was already assigned, it is possible that a new assignment overrode it.

Answers

Just check the result of

select acl, principal from dba_network_acl_privileges;

you should have made two acl like this

description => 'WWW ACL',

description => 'WWW ACL',

SCHEMANAME (EXAMPLE SCOTT) OR YOUR USER NAME

the result should be

AF
localhost - - /sys/acls/local-access-users.xml 2A4C21EA218455B6E05010AC19006FAF

here are some confusions;

i have 10 different schemas who will be using apex.

so what do you mean by

and should i use the same as you typed localhost, or my server ip?

schemaname means the user for example scott

from which user you are trying to send the mail

if that user have acl privilege

yes you can use localhost

I have a schema named HELPDESK. and i have done the below

Now i have some questions:

1. does the above okay?

2. the user will be able to connect from their pc's to my server if i am using 'localhost'?

3. how to add another user to the above code?

1) Yes to first question

2) Yes to second question

.This acl is for the user HELPDESK to send mails it is like we are giving them access for acl

Resolving the ORA-24247

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 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.

Submit a Comment Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

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

it is almost impossible to help remotely without steps to reproduce and a lot more details.

we are using oracle application express 5.0.3 with oracle database 12c enterprise edition on windows server 2012 with internet explorer.

we have microsoft active directory domain authentication.

the following code in a apex redirect throw an error

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

Читайте также: