11gR2 Network ACL-What a Nice Feature

For some reason, in 11G Oracle decided to add a new feature into Oracle, the concept of the network Access Control List (ACL).  Now, the most common usage for this type of network access is for Utl_mail, which is used to send email from the Oracle database.  A reasonable person might think that if you choose to grant execute on the utl_mail package to a user, you would like that user to be able to send email from the database.  However, for whatever reason, Oracle has decided that is not the case.  Not only must you grant execute on the package, but now you must also add the user to a Network ACL, which is a new concept in Oracle 11.

It is true that instructions on this ‘feature’ are pretty good, once you get the ORA-24247 error message, but I found little or no mention of it in the pre-upgrade instructions or scripts. To me, waiting until you get the error message is too late. However, on any case, in order to help me the forgetful DBA, and also making the rash assumption that I really do want anyone granted execute on utl_mail to be able to actually send messages, I wrote a simple system trigger to grant network access to anyone given access to utl_mail.  Note that the trigger below grants full network access to the user, if you need something more restrictive, take a look at this post and modify the trigger below as appropriate:

http://www.oracle-base.com/articles11g/FineGrainedAccessToNetworkServices_11gR1.php

My trigger is below:

TRIGGER capture_grant_mail after grant on database
declare
  sql_text ora_name_list_t;
  grantee_list ora_name_list_t;
  grantee varchar2(30);
  stmt VARCHAR2(2000);
  n NUMBER;
  num_grantees binary_integer;
  added number :=0;
  created number:=0;
begin
   n := ora_sql_txt(sql_text);
   FOR i IN 1..n LOOP
     stmt := stmt || sql_text(i);
   end loop;
   if (Upper(stmt) like '%UTL_MAIL%')      
   then
     num_grantees := ora_grantee(grantee_list);
     select count(1) into created from sys.DBA_NETWORK_ACLS;
     for i in 1..num_grantees
     loop
       grantee := upper(grantee_list(i));
       if created=0 
       then
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl=>'network_service.xml',description=>'UTL_MAIL',principal=>grantee,is_grant=>TRUE,privilege='connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl=>'network_service.xml',host=>'*');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl=>'network_service.xml',principal=>grantee,is_grant=>true,privilege=>'resolve');
       else
         select count(1) into added from dba_network_acl_privileges where principal=grantee and upper(privilege)='RESOLVE';
         if added=0
         then
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl=>'network_service.xml',principal=>grantee,is_grant=>true,privilege=>'resolve');
         end if;
         select count(1) into added from dba_network_acl_privileges where principal=grantee and upper(privilege)='CONNECT';
         if added=0
         then
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl=>'network_service.xml',principal=>grantee,is_grant=>true,privilege=>'connect');
         end if;
       end if;
      end loop;
   end if;
end;

6 Responses to “11gR2 Network ACL-What a Nice Feature”

  1. james Says:

    Hi Andrew,

    Really useful trigger !
    Expecting a lot of database cloning here in the shop. Is there a way to put a bar to the ACL once on a new environment e.g host/instance ?

    Thanks
    J

  2. dbakerber Says:

    I don’t know the answer to that. I expect that the ACL will get created if it doesn’t already exist in the destination. But you can always just revoke privileges on utl_mail or whatever needs to be revoked.

  3. Marko Sutic Says:

    Hi Andrew,

    nice idea to write this trigger.

    When we decided to migrate some databases to 11g version I’ve experienced issues with this “new feature” so now I always must remind myself to configure ACL.

    Trigger is very handy solution😉

    Regards,
    Marko

  4. sedat Says:

    Hi Andrew,
    Thanks for it. Really usefull for me

    Best Regards
    Sedat

  5. Sherri Says:

    I am not sure where you’re getting your info, but great topic.
    I needs to spend some time learning much more or understanding more.
    Thanks for great info I was looking for this information for
    my mission.

  6. menagesurbordeaux.com Says:

    Appreciating the hard work you put into your blog and detailed information you offer.
    It’s great to come across a blog every once in a while that isn’t the
    same out of date rehashed information. Excellent read! I’ve saved your site
    and I’m adding your RSS feeds to my Google account.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: