This post describes the package DBMS_NETWORK_ACL_ADMIN (new to 11.x) with some examples on how to manually set and check privileges. With ACL’s, Oracle offers more fine-grained access control for users to access external network resources.

The packages UTL_MAIL, UTL_SMTP, UTL_HTTP, UTL_TCP etc. allow communication beyond the database server to the outside world, but when granted access, all hosts can be accessed. This can be interpreted as a security flaw as no login is required when using UTL_TCP for example. DBA’s are advised to revoke the execute privileges from public on these kind of packages.

Since Oracle 11g, the Access Control List is introduced. You not only can control who has access to these packages by granting, but now you can also control which resources they can call.We can control accessibility based on host and port number.

In summary these are the steps:

1. Create an ACL – setting the privilege required for the user.
2. Assign the ACL to a network.
3. Test the UTL_ package.

Create an ACL

The ACL is an XML file which lists the permissions given to user(s). This XML is stored in Oracle XML DB. Ensure this is installed. Login as “SYS AS SYSDBA”. Start by creating a test user

CREATE USER [USER] IDENTIFIED BY [PWD];
GRANT CONNECT TO [USER];

Create an ACL – and give ‘connect’ privilege to [USER] (more about the privileges below). Notice the COMMIT is required to save the changes.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'ACL_TEST.xml',
                                    description => 'ACL FOR TEST PURPOSES',
                                    principal   => [USER],
                                    is_grant    => true,
                                    privilege   => 'connect',
                                    start_date  => null,
                                    end_date    => null );
  COMMIT;
END;
/

Here,
acl – Name of the ACL file (which is stored as an XML file in “/sys/acls” which is defined in XDB). Important this is CASE SENSITIVE.
principal – database user or role you want to grant/deny privilege to.
is_grant – true(grant) or false(deny).
privilege – Either “connect” or “resolve” (this is CASE SENSITIVE). Connect is for external networks and required when using UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_MAIL. Resolve is for UTL_INADDR which resolves hostnames into IP addresses (or vice versa). There are other values, for example when using wallets, “use-passwords” to give the user permission to use passwords in the wallet. “use-client-certificates” to authenticate the user with a client certificate in the wallet.
start_date and end_date are optional.

Verify if ACL is created with correct permissions

Check that the ACL is created and connect permission is granted for your user using “CHECK_PRIVILEGE”.

SELECT DECODE(
      DBMS_NETWORK_ACL_ADMIN.check_privilege('ACL_TEST.xml', [USER], 'connect'),1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dual
/

PRIVILE
-------
GRANTED

Adding additional privileges using ADD_PRIVILEGE (Optional)

ADD_PRIVILEGE can be used to add privileges for other users. It can also be used to add the same permission to the same user. For example:

To Add ‘connect’ privilege to our user [USER] but deny access. You would first add another ACE (Access Control Entity). Note the optional ‘position’ parameter allows you to define additional privileges to a user.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'ACL_TEST.xml',
                                       principal => [USER],
                                       is_grant  => false,
                                       privilege => 'connect',
                                       position  => 1);
  COMMIT;
END;
/

--use check_privilege as show above.

PRIVILE
-------
DENIED

This is correct as a second ACE was added. Position “Null” is the default and position 1 overrides the same privilege but with the value ‘false’. The privileges are chosen from the highest value (for example; 1 in this case).

– Be sure to delete that last ACE just added – to restore the initial privilege (true) that we had on ‘connect’.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(acl       => 'ACL_TEST.xml',
                                          principal => ,
                                          is_grant  => false,
                                          privilege => 'connect');
  COMMIT;
END;
/

Note: Use DELETE_PRIVILEGE with “is_grant” = null to remove all ACE’s for that privilege (as long as this not leave an empty ACL –ora-24246).

Optional: To continue this demo do not drop the ACL. This just shows the syntax should it be required

BEGIN
  DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
               acl         => 'ACL_TEST.xml' );
  COMMIT;
END;
/

Assign the ACL to a Network

The ACL needs to be assigned to a network host. The example below uses host www.abc.com. For UTL_SMTP this would be your mail server host.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
                              acl        => 'ACL_TEST.xml',
                              host       => 'www.abc.com',
                              lower_port => null,
                              upper_port => null);
   COMMIT;
END;
/

Here,
host – any host can only have one ACL assigned- but wildcards can be used for the domain or within IP Addresses. Attempting to assign another ACL to the same host will replace the existing ACL. There is a precedence if you define a host of “www.abc.com” the ACL used will take precedence over any that are assigned to “*.abc.com” which in turn takes precedence over “*” the same is true for IP Addresses. e.g. An ACL for “1.2.3.4” will be used before “1.*” or “*”

Optional – To continue this demo do not unassign the ACL. This just shows the syntax should it be required

BEGIN
  DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL (
             acl        => 'ACL_TEST.xml',
             host       => 'www.abc.com',
             lower_port => null,
             upper_port => null);
   COMMIT;
END;
/

– Check if the ACL has been assigned:

col host format a30
col acl format a30
set pagesize 999

SELECT host, acl, lower_port, upper_port
FROM DBA_NETWORK_ACLS
/

HOST ACL LOWER_PORT
------------------------------ ------------------------------ ----------
UPPER_PORT
----------
www.abc.com                 /sys/acls/ACL_TEST.xml

myproxy.abc.com        /sys/acls/ACL_TEST.xml

– Additionally you can check privileges associated with an ACL using the view “DBA_NETWORK_ACL_PRIVILEGES” or for the user using “USER_NETWORK_ACL_PRIVILEGES”:

column acl format a30
column principal format a20

SELECT acl,
       principal,
       privilege,
       is_grant,
       to_char(start_date) ,
       to_char(end_date)
FROM dba_network_acl_privileges
/

ACL                            PRINCIPAL            PRIVILE IS_GRANT
------------------------------ -------------------- ------- --------------------
TO_CHAR(START_DATE)
----------------------------------------------------------------------
TO_CHAR(END_DATE)
----------------------------------------------------------------------
/sys/acls/ACL_TEST.xml         USER                connect true

Finally, this is an alternative select which checks your privilege via an ACLID:

col acl format a30
col host format a30

SELECT acl,
       host,
       DECODE(
         DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(aclid, , 'connect'),
         1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM   dba_network_acls
/

ACL                            HOST                 PRIVILE
------------------------------ -------------------- -------
/sys/acls/ACL_TEST.xml         www.abc.com          GRANTED
/sys/acls/ACL_TEST.xml         myproxy.abc.com      GRANTED

Testing the ACL

A simple test using UTL_HTTP. Note: change the proxy server to reflect the correct value.

DECLARE
   req UTL_HTTP.REQ;
   resp UTL_HTTP.RESP;
   value VARCHAR2(32200);
BEGIN
   UTL_HTTP.SET_PROXY('myproxy.abc.com','uk.abc.com');
   req := UTL_HTTP.BEGIN_REQUEST('http://www.abc.com');
   resp := UTL_HTTP.GET_RESPONSE(req);
   LOOP
     UTL_HTTP.READ_LINE(resp, value, TRUE);
     DBMS_OUTPUT.PUT_LINE(value);
   END LOOP;
--
   UTL_HTTP.END_RESPONSE(resp);
   EXCEPTION
     WHEN UTL_HTTP.END_OF_BODY THEN
     begin
       DBMS_OUTPUT.PUT_LINE('Finished');
       UTL_HTTP.END_RESPONSE(resp);
     end;
END;
/