Follow Us on Twitter

Oracle 11g: Access Control List and ORA-24247

by Ian Hoogeboom on March 17, 2010 · 29 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.8 out of 5 based on 13 ratings

Ratings:
VN:F [1.9.22_1171]
Rating: 4.8/5 (13 votes cast)

{ 28 comments… read them below or add one }

Igor May 21, 2010 at 4:24 pm

Thanks, it’s helped me !

Reply

Joe June 9, 2010 at 6:57 pm

Thank you for posting this. I was able to get over a huge hurdle with this information!

Reply

Ismail June 30, 2010 at 11:23 pm

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

Reply

Swetal Gajjar July 9, 2010 at 8:00 pm

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

Reply

Asim Jamal October 6, 2010 at 11:13 am

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

Reply

Barry Vrielink October 6, 2010 at 12:48 pm

Always include as much information as possible if you want a problem get resolved (versions, ORA errors etc). I guess you get an ORA-44416 error. This is probably caused by some dangling references to dropped users. If so take a look at the following site: http://download.oracle.com/docs/cd/E10513_01/doc/relnotes.310/e10495/toc.htm (look at section 3.13.3 Troubleshooting an Invalid ACL Error). This will probably get this error resolved.

Reply

Kevi Hallas January 6, 2011 at 1:06 pm

Thanks for these examples – they helped a lot.

Reply

Pandi February 16, 2011 at 7:49 pm

This article is very useful and now I am able send mails using UTL_SMTP.

Reply

Kishor March 11, 2011 at 9:06 am

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?

Reply

Larry Holder May 27, 2011 at 4:53 am

Super, thanks. This is the best and simplest ACL example I’ve seen yet.

Reply

FromSpain June 3, 2011 at 4:36 pm

Thank you.

Reply

Jim July 5, 2011 at 8:43 pm

Thanks for posting. This has been extremely helpful.

Reply

hugo reis September 8, 2011 at 4:13 pm

Thanks for the post

Reply

Derek November 18, 2011 at 12:10 pm

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.

Reply

Nuk January 11, 2012 at 12:25 pm

Thanks, it’s helped me !

Reply

Raj January 13, 2012 at 2:32 am

Thanks a lot it helped a lot in understanding.
Very straightforward and not withholding information that is Awesome.

Reply

Randy January 30, 2012 at 6:43 pm

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!

Reply

Mary February 8, 2012 at 11:34 pm

Thanks! This helped a lot!!!!
I’m going to remember you for future assists.

Reply

Raj Kumar February 16, 2012 at 1:23 pm

Thanks A lot.

This is very helpfull doc.

Raj Kumar

Reply

jyoti February 17, 2012 at 7:45 pm

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

Reply

Hari May 8, 2012 at 1:21 am

Very Helpful. You are Awesome.

Reply

John Peters June 24, 2012 at 11:44 am

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

Reply

John Peters June 24, 2012 at 5:48 pm

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

Reply

Mel July 20, 2012 at 8:39 pm

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?

Reply

Tony August 7, 2012 at 4:07 pm

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

Reply

Vaibhav March 26, 2013 at 8:01 am

Dear All,

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

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

Reply

Tony March 26, 2013 at 4:11 pm

Hi,

add your database server’s IP to the relay server list of the mailserver.

regards,
Tony

Reply

James Randall May 23, 2013 at 10:08 pm

Thanks it helped alot.

Reply

Leave a Comment

 

{ 1 trackback }

Previous post:

Next post:

About Whitehorses
Company profile
Services
Technology

Whitehorses website

Home page
Whitebooks
Jobs

Follow us
Blog post RSS
Comment RSS
Twitter