I have had a couple of clients that run some custom code that sends emails or try to access other network services end up with ORA-24247 Network access denied by access control list (ACL) errors after upgrading to Oracle 11g. The same code worked fine at Oracle 10g. This made me think to write a quick blog about Oracle 11g's new feature the ACL (Access Control List)

 

Oracle has been working since Oracle 8i in trying to make the database more secure, in every version they have introduced some items that move them in that direction. Now, overall this is a fine (nay a great) goal to have, but we get tripped up when some features that work in an earlier version do not work in newer version as we expected them to. who remembers "connect" privilege trimmed, unlimited table space cannot be assigned to a role, etc... Well enough about history (and showing my age) let us talk about ACL.

 

Oracle 11g introduced tighter security on access to all networking services packages such as UTL_SMTP, UTL_HTTP, UTL_TCP, UTL_MAIL, UTL_INADDR (to name a few) 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 privileges on these packages, if he/she doesn’t also have an ACL allowing him/her access to the networking service, they end up with ORA-24247.

 

Let work with a very simple example. It always helps me to convey my thoughts. Let us say that user "John" has privilege to execute UTL_SMTP package and he is using this package to send mail (because I cannot stop myself, if it were me, I would use UTL_MAIL package introduced in 10g as it is much easier to work with than UTL_SMTP, but that is a whole different discussion).

In 10g, John had the required privilege and he was good to go. In Oracle 11g in addition to the execute permissions on the package, user "John" needs to have additional permissions to access that particular network service. This allows administrators to have a much fine grained access control, it is no longer a simple on/off switch.

 

Two things need to happen for "John" to enable him to send this mail from this database server (in addition to the privilege to execute this package).

 

1) The DBA needs to create an Access Control List to allow John access to this ACL.

2) Assign the network Access point to the newly created Access Control List.

 

Below is the code for doing the 2 steps.

 

BEGIN
  DBMS_NETWORK_ACL_ADMIN
.create_acl (
  acl 
=> 'mail_acl_for_users.xml',
  description 
=> 'A mail Server ACL for Users',
  principal 
=> 'JOHN',
  is_grant 
=> TRUE,
  privilege 
=> 'connect',
  start_date 
=> SYSTIMESTAMP,
  end_date 
=> NULL);

  COMMIT
;
END;

/


BEGIN
  DBMS_NETWORK_ACL_ADMIN
.assign_acl (
  acl
=> 'mail_acl_for_users.xml',
  host
=> 'mail.mailserver.com',
  lower_port => 25,
  upper_port => NULL);
  COMMIT
;

END;

/


Now John is able to send email as he was able to in 10g. This is a very simple example for a single user wanting a specific access to a single network service, but I hope it conveys the steps that are needed to pull this off. You can add other users, multiple servers, etc and you can find this information on how to do this in Oracle's documentation. One thing to keep in mind is that only one ACL can be assigned to a specific host and port-range combination. If you have more than one ACL against a server, the latest one takes effect. When wildcard usage causes overlapping assignments, the most specific assignment will take precedence. These rules usually give heartburn in a complex ACL environment, so please watch your ACLs closely.


There are some assumptions made here. I will list the main ones. XML DB is required for ACL to work as this is where are the ACL related information is stored. XML DB (or XDB) is installed by default. The user issuing the above commands needs to have DBA or appropriate privileges.


This is the end of the official communication to solve the ORA-24247 error.


Now sometimes I get the question, "I know I am using 11g and this is not a production database, how do I revert back to 10g behavior as far as ACL is concerned? I am not going to do this in production, just want to do a test without running into this fine grained access control in development environment for every user"


Now this is my disclaimer statement... I like the ACL and fine grained access control. It is meant to provide a secure database environment. I strongly advise against disabling it. There is a very good reason why Oracle has put this in place in 11g... Everyone understands that I am not suggesting doing this especially in Production. Please understand the disclaimer statement and DO NOT do this.


Now then as everyone has been forewarned, sometimes in a test or development environment, just for testing you can run the following code to allow everyone access to the networking services.


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 creates an ACL with the user (principal) being public and the second piece assigns this ACL to the all hosts. This is the same as Oracle 10g behavior.