Oracle 11g Acces Control List and ORA-24247

This manuel describes the solution ORA-24247: network access denied by access control list (ACL).

for the ACL’ss offers oracle more fine-grained access control for users who have access to external resources.
The packages UTL_MAIL , UTL_SMTP , UTL_HTTP , UTL_TCP etc. provide the ability to communicate to the outside world. When there are rights, data can be created from the database connection to all hosts. This can be seen as a security hole because there is no login is required . Since Oracle 11g , the Access Control List is introduced. You can not only control who has access to these packages, but you can also determine which resources they may comminceren .
For example, when a user is allowed to send emails through UTL_MAIL , you can also determine which server it should gebeuren.Ik UTL_MAIL will use the package as an example When a user does not have permission to email appears the error message:

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.

Add Privilege

Now that the ACL is created you can add more as the privilege to ‘ resolve ‘ privilege .

Grant ACL

The user SCOTT is granted connect and resolve , but there is not yet indicated to what resources he can connect.


Connection to websites and TCP ports

With the ACL , you can set up TCP ranges start and end dates.

Create ACL, add privileges and TCP ports

Excecute as SYS

In the hosts parameter dbms_network_acl_admin.assign_acl, can you can al so add wild cards ‘*’ or even ‘*’.

Execute as SCOTT:

Now try to connect to the same URL but with a different port .


You can by carrying out the following view the ACL and privileges by reacting a query loose on the dba_network_acls .

Remove ACL and priviliges

Execute asSYS:

Unassign ACL

Remove Privilege

Drop ACL

Activate UTL_MAIL

Please let me know if this manual ‘Oracle 11g Acces Control List and ORA-24247’ was usefull to you. If there are  errors or you have suggestions regarding this manual, please let me know. No rights can be derived from this manual




Maarten Schoonus

Leave a Reply