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;


Advertisement