Can you have multiple Oracle ACL files

database-mailoracleoracle-11g-r2

I have an application which needs to send emails through Oracle triggers. I understand that in Oracle 11g you must have an ACL file to grant certain schemas access to the network. My plan was to create a separate ACL file for each user that needs access to the network, and then check that it has been created properly by using the following query:

SELECT * FROM DBA_NETWORK_ACLS;

However, it seems that only one record is ever returned for this query – always the latest ACL that's been created. Does this mean that you can only ever have one ACL? If so, presumably this means that when installing this application on a client's server, I must just add privileges to their existing ACL rather than trying to create a new one?

Thanks!

UPDATE

These are the commands I'm using:

--create ACL for USER1
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(ACL => 'testacl1.xml', 
                                  DESCRIPTION => 'ACL',
                                  PRINCIPAL   => 'USER1',
                                  IS_GRANT    => TRUE,
                                  PRIVILEGE   => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL => 'testacl1.xml',
                                     PRINCIPAL => 'USER1',  
                                     IS_GRANT  => TRUE,  
                                     PRIVILEGE => 'resolve'); 
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(ACL => 'testacl1.xml', host => '*'); 

--create ACL for USER2
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(ACL => 'testacl2.xml', 
                                  DESCRIPTION => 'ACL',
                                  PRINCIPAL   => 'USER2',
                                  IS_GRANT    => TRUE,
                                  PRIVILEGE   => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL => 'testacl2.xml',
                                     PRINCIPAL => 'USER2',  
                                     IS_GRANT  => TRUE,  
                                     PRIVILEGE => 'resolve'); 
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(ACL => 'testacl2.xml', host => '*');

The problem is that testacl2 seems to be overwriting testacl1. Does this mean that I can only have one ACL file, or one ACL file per host?

Best Answer

Check the Creating an Access Control List for External Network Services documentation (look at step 2, after the parameter descriptions):

Only one access control list can be assigned to any host computer, domain, or IP subnet, and if specified, the TCP port range.

Your second assignment simply replaces the first. You can only have one ACL file per host/domain.

Related Question