Follow Us on Twitter

Oracle 11g: Access Control List and ORA-24247

by Ian Hoogeboom on March 17, 2010 · 30 comments

From a more DBA point of view, I would like to go in more detail in response to Marcel’s blog about APEX web service references and ACL.

With ACL’s, Oracle offers more fine-grained access control for users to access external network resources.

The packages UTL_MAIL, UTL_SMTP, UTL_HTTP, UTL_TCP etc. allow communication beyond the database server to the outside world, but when granted access, all hosts can be accessed. This can be interpreted as a security flaw as no login is required when using UTL_TCP for example. DBA’s are advised to revoke the execute privileges from public on these kind of packages.

Since Oracle 11g, the Access Control List is introduced. You not only can control who has access to these packages by granting, but now you can also control which resources they can call.
For instance, when a user is granted to send emails using UTL_MAIL, you can also control that he/she is only able to send through a specified mail server.
At first this looks like a obstacle (ORA-24247), but since the Voyager worm struck Oracle databases a year ago, it is introduced as an extra security measurement.

I will use the UTL_MAIL package as an example, please scroll to the end of this blog to enable UTL_MAIL as it is disabled by default.

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

If a user is not allowed to connect to a specific server due to ACL restrictions, the following message will appear: ORA-24247: network access denied by access control list (ACL).

I will walk through the solution by using UTL_MAIL as an example; try to execute the following as SCOTT:

SQL> connect scott/tiger
Connected.
begin
  utl_mail.send(
  sender     => 'scott@tiger.com',
  recipients => 'john@doe.org',
  message    => 'Hello World'
  );
end;
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 SCOTT does not have the privilege to access the mail/smtp server. So it must be added to the ACL.

ACL: Access Control List

The ACL is created as a file and it’s file name is used as the key in the process of adding and removing privileges.

Create ACL and privileges

Now first create an ACL as SYS (or any other user with DBMS_NETWORK_ACL_ADMIN execute granted), this will hold the privileges. You can add as many privileges as you like to this file, but I would recommend to split privileges in the ACL to specific tasks or users. You must create an ACL with at least one privilege, so lets start with the ‘connect’ privilege for user SCOTT, (also a role can be added as principal):

begin
  dbms_network_acl_admin.create_acl (
    acl         => 'utl_mail.xml',
    description => 'Allow mail to be send',
    principal   => 'SCOTT',
    is_grant    => TRUE,
    privilege   => 'connect'
    );
    commit;
end;

Add Privilege

Great, now that the ACL is created, you can add more privileges like the ‘resolve’ privilege:

begin
  dbms_network_acl_admin.add_privilege (
  acl       => 'utl_mail.xml',
  principal => 'SCOTT',
  is_grant  => TRUE,
  privilege => 'resolve'
  );
  commit;
end;

Assign ACL

Cool, you granted SCOTT to connect and resolve, but you have not defined to which resources he is allowed to connect:

begin
  dbms_network_acl_admin.assign_acl(
  acl  => 'utl_mail.xml',
  host => 'smtp server host name or address'
  );
  commit;
end;

Try again

SQL> connect scott/tiger
Connected.
begin
  utl_mail.send(
  sender     => 'scott@tiger.com',
  recipients => 'john@doe.org',
  message    => 'Hello World'
  );
  commit;
end;
PL/SQL procedure successfully completed.

Access to websites and ports

The ACL also allows you to control begin and end ports, begin and end dates.

Run as SCOTT:

SQL> select utl_http.request('http://www.tiger.com') from dual;
select utl_http.request('http://www.tiger.com') from dual
       *
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1

Solution Create ACL, add privileges and assign the ACL with ports

Run as SYS:

begin
  dbms_network_acl_admin.create_acl (
    acl         => 'utl_http.xml',
    description => 'HTTP Access',
    principal   => 'SCOTT',
    is_grant    => TRUE,
    privilege   => 'connect',
    start_date  => null,
    end_date    => null
  );
  dbms_network_acl_admin.add_privilege (
    acl        => 'utl_http.xml',
    principal  => 'SCOTT',
    is_grant   => TRUE,
    privilege  => 'resolve',
    start_date => null,
    end_date   => null
  );
  dbms_network_acl_admin.assign_acl (
    acl        => 'utl_http.xml',
    host       => 'www.tiger.com',
    lower_port => 80,
    upper_port => 80
  );
  commit;
end;

The hosts parameter in dbms_network_acl_admin.assign_acl, can also contain wild cards like ‘*.tiger.com’ or even ‘*’.

Try again

Run as SCOTT:

SQL> select utl_http.request('http://www.tiger.com') from dual;
UTL_HTTP.REQUEST('HTTP://WWW.TIGER.COM')
----------------------------------------
[result here]

Now try to access the same URL, but with another port. You will see this fails, because only port 80 is privileged.

SQL> select utl_http.request('http://www.tiger.com:1234') from dual;
select utl_http.request('http://www.tiger.com:1234') from dual
       *
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1

You can specify lower and upper ports like a range when assigning ACL’s.

dba_network_acls

You can view ACL’s and privileges by querying dba_network_acls.

select host, lower_port, upper_port, acl
  from dba_network_acls
  where ACL='/sys/acls/'utl_http.xml';

Removing ACL and priviliges

Of course , removing ACL’s and privileges is also possible and  self explainable.

Run the following as SYS:

Unassign ACL

begin
  dbms_network_acl_admin.unassign_acl(
    acl        => 'utl_http.xml',
    host       => 'www.tiger.com',
    lower_port => 80,
    upper_port => 80
  );
end;

Delete Privilege

begin
  dbms_network_acl_admin.delete_privilege(
    'utl_http.xml', 'SCOTT', NULL, 'connect'
  );
end;

Drop ACL

begin
  dbms_network_acl_admin.drop_acl(
    'utl_http.xml'
  );
end;

Enabling UTL_MAIL

In these examples I use UTL_MAIL, this package is disabled by default, run the following statements as SYS to enable it:

SQL> @?/rdbms/admin/utlmail.sql
SQL> @?/rdbms/admin/prvtmail.plb
SQL> alter system set smtp_out_server = '<smtp host>' scope=spfile;
SQL> shutdown immediate
SQL> startup
Oracle 11g: Access Control List and ORA-24247, 4.9 out of 5 based on 14 ratings

Ratings:
VN:F [1.9.22_1171]
Rating: 4.9/5 (14 votes cast)

30 comments on “Oracle 11g: Access Control List and ORA-24247

  1. Igor on said:

    Thanks, it’s helped me !

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  2. Thank you for posting this. I was able to get over a huge hurdle with this information!

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  3. Ismail on said:

    Thanks so much. Your posting helped me a lot after moving 11G.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  4. Swetal Gajjar on said:

    Thanks
    The above solution worked fine. Now I am able to send email from my application using UTL_SMTP package.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  5. Asim Jamal on said:

    Good article …could pls give some more details in revoking ACL ..it gives error of “unresolved principal” while revoking ACL.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  6. Kevi Hallas on said:

    Thanks for these examples – they helped a lot.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  7. This article is very useful and now I am able send mails using UTL_SMTP.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  8. Kishor on said:

    Hi, Very informative and useful. I managed to get if working on 11g on windows 2008 32 but version. But on win2008 64 bit, have not been able to fix it. Is there anything different on 64 bit?? db version is 11.2.0.1.0, with apex on HTTP 10g. Any advise?

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  9. Super, thanks. This is the best and simplest ACL example I’ve seen yet.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  10. FromSpain on said:

    Thank you.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  11. Thanks for posting. This has been extremely helpful.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  12. hugo reis on said:

    Thanks for the post

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  13. Hi
    I using utl_http.write_text post message than get 200(status_code),but the URL can’t get any message.
    and in oracle 10G are no program.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  14. Thanks, it’s helped me !

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  15. Thanks a lot it helped a lot in understanding.
    Very straightforward and not withholding information that is Awesome.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  16. Thanks Ian !

    sometimes in an emergency situation, I don’t have time to read the Oracle doc. Your post was very helpful and completely resolved the problem.

    Thanks again!

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  17. Thanks! This helped a lot!!!!
    I’m going to remember you for future assists.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  18. Raj Kumar on said:

    Thanks A lot.

    This is very helpfull doc.

    Raj Kumar

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  19. Hi I GOT ERROR FOR UTL_SMTP:

    Error report:
    ORA-24247: network access denied by access control list (ACL)
    ORA-06512: at “RETAIL.PKG_SIM_DECOUPLING”, line 216
    ORA-06512: at “RETAIL.PKG_SIM_DECOUPLING”, line 447
    ORA-24247: network access denied by access control list (ACL)
    ORA-06512: at line 2

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  20. Hari on said:

    Very Helpful. You are Awesome.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  21. John Peters on said:

    Hello,

    should this also work on XE, release 11.2.0.1.0 using user HR?
    After i created the ACL and granted the privileges i still get the same error.

    Thanks,
    John

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  22. John Peters on said:

    Just installed an EE 11.2.0.1.0 on my laptop, but i don’t get it working there either.
    Has some one an idea what i did wrong. I followed evry step above and seen the entires in the DBA_NETWORK views.

    Gr,
    John

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  23. Did all of this and still continue to get ORA-24247. Interesting tidbits on our environment – we were able to successfully use the dbms_network_acl_admin package to access an ldap server from oracle ldap api’s but cannot use the same method/commands to get the utl_smtp or utl_ftp to access out. Any thoughts/ideas?

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    • Hi Mel,

      did you add the correct hosts and ports (smtp=25 and ftp=21) to the ACL? You can check these through:

      select host, lower_port, upper_port, acl
      from dba_network_acls

      kind regards,
      Tony

      VA:F [1.9.22_1171]
      Rating: 0 (from 0 votes)
  24. Pingback: Whom shall I fear? » Blog Archive » UTL_MAIL ORA-24247

  25. Vaibhav on said:

    Dear All,

    I am getting following error : ORA-29279: SMTP permanent error: 530 authentication required.

    kindly provide the solutions ………….thanx in advance.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  26. James Randall on said:

    Thanks it helped alot.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  27. Amos on said:

    Thank you very much. It helped in my learning to be a better dba. Cheers!

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)

Leave a Reply

Your email address will not be published. Required fields are marked *

*

* Copy This Password *

* Type Or Paste Password Here *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 

Previous post:

Next post:

About Whitehorses
Company profile
Services
Technology

Whitehorses website

Home page
Whitebooks
Jobs

Follow us
Blog post RSS
Comment RSS
Twitter