Oracle ACL

To avoid "ORA-24247: network access denied by access control list (ACL)" when executing UTL packages (Network related Packages), access has to be granted to user using these packages. Affected packages include:

  • UTL_TCP
  • UTL_SMTP
  • UTL_MAIL
  • UTL_HTTP
  • UTL_INADDR

Create ACL

At its simplest...

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'ACLNAME.xml',
                                     description => 'Description of this ACL',
                                     principal => 'MYUSER',
                                     is_grant => true,
                                     privilege => 'connect',
                                     start_date => null,
                                     end_date => null );
   COMMIT;
END;/
for everything except UTL_INADDR, privilege should be 'connect'. For UTL_INADDR you can also use 'resolve'.

The following example first looks for any ACL currently assigned to host_name. If one is found, then the example grants user_name the CONNECT privilege in the ACL only if that user does not already have it. If no ACL exists for host_name, then the example creates a new ACL called ACL_name, grants the CONNECT privilege to user_name, and assigns the ACL to host_name.

DECLARE
   ACL_PATH VARCHAR2(4000);
   ACL_ID   RAW(16);
BEGIN
   -- Look for the ACL currently assigned to '*' and give MYUSER
   -- the "connect" privilege if MYUSER does not have the privilege yet.
 
   SELECT ACL INTO ACL_PATH 
   FROM   DBA_NETWORK_ACLS
   WHERE  HOST = '*'
   AND    LOWER_PORT IS NULL
   AND    UPPER_PORT IS NULL;
 
   -- Before checking the privilege, make sure that the ACL is valid
   -- (for example, does not contain stale references to dropped users).
   -- If it does, the following exception will be raised:
   --
   -- ORA-44416: Invalid ACL: Unresolved principal 'MYUSER'
   -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
 
   SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
   FROM   XDB.XDB$ACL A,
          PATH_VIEW P
   WHERE  extractValue(P.RES, '/Resource/XMLRef') = REF(A)
   AND    EQUALS_PATH(P.RES, ACL_PATH) = 1;
 
   DBMS_XDBZ.ValidateACL(ACL_ID);
 
   IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl       => ACL_PATH,
                                             user      => 'MYUSER',
                                             privilege => 'connect') IS NULL
   THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => ACL_PATH,
                                           principal => 'MYUSER',
                                           is_grant  => TRUE,
                                           privilege => 'connect');
   END IF;
 
EXCEPTION
   -- When no ACL has been assigned to '*'.
   WHEN NO_DATA_FOUND THEN
      DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'aclname.xml',
                                        description => 'ACL description',
                                        principal   => 'MYUSER',
                                        is_grant    => TRUE,
                                        privilege   => 'connect');
      DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl         => 'aclname.xml',
                                        host        => '*');
END;
/
COMMIT;
aclname.xml => Enter a name for the access control list XML file.ACL description => 'file description',principal => 'user_or_role',is_grant => TRUE|FALSE,privilege => 'connect|resolve',host_name => host name (e.g. * for everything, or something like mail.mydomain.com for that specific host). Can be domain name or IP.

Drop ACL

BEGIN
   DBMS_NETWORK_ACL_ADMIN.DROP_ACL(acl => 'ACLNAME');
END
/

Check

To list ACLs...

SELECT ACL
FROM   DBA_NETWORK_ACLS
/

To see which users have ACLs granted…

COLUMN acl FORMAT a30
COLUMN principal FORMAT a30
SELECT acl, 
       principal, 
       privilege 
FROM   dba_network_acl_privileges
ORDER BY principal
/

To check which ACL domains allow MYUSER to connect to mail.mydomain.com…

COLUMN host FORMAT a10
COLUMN acl  FORMAT a40
SET LINESIZE 132
SELECT  dna.host,
        dna.lower_port,
        dna.upper_port,
        dna.acl,
        DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(dna.aclid, 'MYUSER', 'connect'),
               1, 'GRANTED',
               0, 'DENIED',
               null) privilege
FROM    dba_network_acls dna
WHERE   host IN (SELECT  *
                 FROM    TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('mail.mydomain.com')))
ORDER BY DBMS_NETWORK_ACL_UTILITY.DOMAIN_LEVEL(host) desc,
         lower_port,
         upper_port
/

Useful Queries

Example - Changing Mail Server

If you need to change the your mail server for database outgoing mail...

(e.g. you need to change SMTP_OUT_SERVER from mail.mydomain.com to relay.newdomain.com)

First we need to identify how the ACLs are defined for the existing host...

SET LINESIZE 200
COLUMN host FORMAT a30
COLUMN acl FORMAT a60
SELECT host, acl, lower_port, upper_port 
FROM dba_network_acls;

Any ACLs that explicitly point to the old mail server will need to be associated with the new mail server...

It is possible for an ACL to use a wildcard (e.g. * or *.domain.com). You should be mindful of giving non-privileged users access to wildcard ACLs.
BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (acl => '&ACLNAME', host => '&newhost', lower_port => &lowport, upper_port => &hiport);
   commit;
END;
/

At this point it should be possible to send mails through mail.mydomain.com or relay.newdomain.com dependent on your setting for SMTP_OUT_SERVER. Once the change is considered permanent, then you should remove any redundant ACL assignments...

BEGIN
   DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL (acl => '&ACLNAME', host => '&oldhost', lower_port => &lowport, upper_port => &hiport);
   commit;
END;
/

Example - Revoking an ACL from a user

If you need to stop a user from sending mails using UTL_MAIL via a mail server when that user can currently do so

(e.g. you need to stop USER1 sending mails using UTL_MAIL via relay.newdomain.com, but you don't want to revoke access to UTL_MAIL)
BEGIN
  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(acl       => 'ACLNAME',
                                          principal => 'USER1')
END
/

To view an XML ACL file

SET LONG 4000
SET PAGESIZE 4000
SELECT a.OBJECT_VALUE
FROM   RESOURCE_VIEW r,
       XDB.XDB$ACL a
WHERE  ref(a) = extractValue(r.RES, '/Resource/XMLRef')
AND    equals_path(r.RES, '/sys/acls/aclname.xml') = 1;

Useful Views

  • DBA_NETWORK_ACLS
  • DBA_NETWORK_ACL_PRIVILEGES

Bibliography


https://dba12c.wordpress.com/2015/08/18/understanding-dbms_network_acl_admin-with-example/
Note 453786.1 ORA-24247 When Executing UTL_HTTP UTL_INADDR Packages1080105.1-Understanding DBMS_NETWORK_ACL_ADMIN With Example http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm - Oracle® Database PL/SQL Packages and Types Reference 11g Release 1 (11.1)http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm#BABCJDGChttp://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG40012 - Oracle® Database Security Guide 11g Release 1 (11.1)http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb21sec.htm#ADXDB2400 - Oracle® XML DB Developer's Guide 11g Release 1 (11.1)