This has tripped me up a couple of times, so it’s time to document the workaround!

In 10g, you might have written a bit of PL/SQL that invokes the package utl_smtp so that the database sends you an email about something -and, although the code would definitely have ended up quite ugly (utl_smtp is like that!), it would have been entirely functional. Upgrade to 11g, however, and that same code will now error out with an ORA-24247 network access denied by access control list (ACL) message. Utl_smtp is not the only package affected, either: utl_http, utl_tcp, utl_mail and utl_inaddr are all similarly affected. If you wrote code that referenced any of these packages, it would break in 11g having worked fine in 10g.

The reason for the breakages is that 11g introduced tighter security on access to all networking services and this security is enforced by Access Control Lists (ACLs). The ACL-based security works independently of package grants… so although a user might have execute permissions on utl_smtp, if he doesn’t also have an ACL allowing him access to the smtp networking service, he’s not going to be sending emails anywhere.

In the right context, this extra security is a great thing… but in the context I work in, it’s a royal pain in the butt! I just want my old code to work -and I don’t want to have to do battle with another layer of user privilege management to get it to do so.

Here, then, is the “can we please pretend we’re running in 10g again” fix. It blows the whole ACL idea out of the water by simply creating an ACL that says everyone can access every networking service, no questions asked. Run it as SYS:

begin
dbms_network_acl_admin.create_acl (
   acl          => 'networkacl.xml',
   description  => 'Allow Network Connectivity',
   principal    => 'PUBLIC',
   is_grant     => TRUE,
   privilege    => 'connect',
   start_date   => SYSTIMESTAMP,
   end_date     => NULL);

dbms_network_acl_admin.assign_acl (
   acl         => 'networkacl.xml',
   host        => '*',
   lower_port  => NULL,
   upper_port  => NULL);

commit;
end;

The first bit of code simply creates an ACL and grants rights to it to PUBLIC. The second bit says that the ACL just created doesn’t restrict hosts or port ranges (which effectively means it’s restricting stuff-all). Your old 10g code will now run just fine in 11g once more.

Obviously, this isn’t subtle and there are good reasons why Oracle tightened up the security surrounding access to these networking services… which my code simply ignores and pretends aren’t an issue. So this is not exactly “world’s best practice”! But in the right situation (basically, one where you are in a hurry to have things behave as they did in 10g and never mind the implications), then this code will do the trick.