Application Express, Network ACLs and Oracle Database 11gR2

Application Express, Network ACLs and Oracle Database 11gR2

In Oracle Database 11gR1, a new feature was introduced called Fine-Grained Access to External Network Services. Succinctly defined, this feature gives an administrator control over which database users are permitted to access external network services, and on which ports. If an application relied upon the PL/SQL packages UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP or UTL_INADDR, they would now need to be given permission to access the external network service via a Network ACL. An excellent writeup of this feature is on ORACLE-BASE.

There have been two changes in this feature in Database 11gR2 which may impact Application Express users. These aren't necessarily documented so prominently in the release notes or README of Database 11gR2, so I felt it necessary to share them here.

1. In Database 11gR2 11.2.0.1, the precedence order in evaluation of the network ACL entries has been changed to most specific to least specific. More about this below.

2. In Database 11gR2 11.2.0.2, the network ACL now applies to any use of DBMS_LDAP.


In the installation guide for Oracle Application Express, we document this feature in Oracle Database 11gR1 and also how to create a network access control list which permits the APEX engine to access any network service on any network port. The name of the ACL in our example is power_users.xml. It just so happened that someone else on our instance of Application Express needed access to an outbound HTTP proxy, so I created a separate network ACL for this user.

The Network ACL privileges looked like:

view plaincopy to clipboardprint?

1. SQL> column host format a30
2. SQL> column acl format a40
3. SQL> select host, lower_port, upper_port, acl from dba_network_acls;
4.
5. HOST LOWER_PORT UPPER_PORT ACL
6. ------------------------------ ---------- ---------- ------------------------------------
7. proxyserver.domain.com 80 80 /sys/acls/proxy_rule.xml
8. * /sys/acls/power_users.xml
9.
10.
11. SQL> column acl format a40
12. SQL> column principal format a30
13. SQL> select acl, principal from dba_network_acl_privileges;
14.
15. ACL PRINCIPAL
16. ---------------------------------------- ------------------------------
17. /sys/acls/proxy_rule.xml SOME_OTHER_USER
18. /sys/acls/power_users.xml APEX_040000


SQL> column host format a30
SQL> column acl format a40
SQL> select host, lower_port, upper_port, acl from dba_network_acls;

HOST LOWER_PORT UPPER_PORT ACL
------------------------------ ---------- ---------- ------------------------------------
proxyserver.domain.com 80 80 /sys/acls/proxy_rule.xml
* /sys/acls/power_users.xml


SQL> column acl format a40
SQL> column principal format a30
SQL> select acl, principal from dba_network_acl_privileges;

ACL PRINCIPAL
---------------------------------------- ------------------------------
/sys/acls/proxy_rule.xml SOME_OTHER_USER
/sys/acls/power_users.xml APEX_040000


Prior to 11.2.0.1, if APEX_040000 needed to access the outbound HTTP proxy, this would be evaluated in terms of the least specific rule to the most specific rule. The ACL power_users.xml permitted access to all hosts on all ports, and thus, there were no issues. But in 11.2.0.1 and higher, this is now evaluated from most specific to least specific. If APEX_040000 now needs to access the outbound HTTP proxy, it is blocked and we'll encounter the dreaded error message "ORA-24247: network access denied by access control list (ACL)". A match for the host in the outbound HTTP proxy is found in /sys/acls/proxy_rule.xml, APEX_040000 does not have privileges on that ACL, and now the fine-grained access control blocks the request.

To correct this, I had to grant privilege on the proxy ACL to APEX_040000:

view plaincopy to clipboardprint?

1. dbms_network_acl_admin.add_privilege(
2. acl => 'proxy_rule.xml',
3. principal => 'APEX_040000',
4. is_grant => TRUE,
5. privilege => 'connect' );


dbms_network_acl_admin.add_privilege(
acl => 'proxy_rule.xml',
principal => 'APEX_040000',
is_grant => TRUE,
privilege => 'connect' );


To address the second point, in 11.2.0.2, access to the host and port specified by methods in the DBMS_LDAP PL/SQL package are now controlled via this same fine-grained access control to external network services.

I can understand, from a security perspective, why this behavior was changed. However, if you've come to rely upon this behavior, upgrading to Oracle Database 11gR2 may introduce some changed behavior when accessing external network resources.

SOURCE