Oracle 11g Security – XMLDB is here to stay…

If you like it or not, but XMLDB will be an important component of your new Oracle 11g database. I didn’t really looked into the new dependencies regarding other features of the database. I tested mainly on Oracle 11g XMLDB storage functionality, but today, fiddling around with the new WSDL web service feature, I encountered serious security issue. Eh, improvement that is.

XMLDB functionality controls a lot of security with it’s build-in ACL (Access Control List) security. Today I discovered that it is NOT ONLY XMLDB functionality, but also OTHER database features are affected by it.

Yesterday I installed Oracle 11.1.0.6.0 on Oracle Enterprise Linux V5. Doing so I was able to get the WSDL functionality working. Mark Drake (Sr. Product Manager Oracle, XML Technologies), as always, replied on my question on the XMLDB OTN forum with a great example of how one could enable and / or use the WSDL feature. This mentioned also the use of a package called DBMS_NETWORK_ACL_ADMIN

When I looked further into the package DBMS_NETWORK_ACL_ADMIN I read that this is part of Oracle’s more tightened security methods also referred to as “Fine-Grained Access to External Network Services”. Packages that create access to the outside database world are controlled via XMLDB ACL methods.

Packages now controlled via ACL’s are: UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR

The Oracle 11g Upgrade manual describes it as follows:

Configure Fine-Grained Access to External Network Services

Oracle Database 11g Release 1 (11.1) includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XML DB. If you have applications that use one of these packages, you must install Oracle XML DB if it is not already installed. You must also configure network access control lists (ACLs) in the database before these packages can work as they did in prior releases.

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);
BEGIN
  SELECT acl INTO acl_path FROM dba_network_acls
   WHERE host = 'host_name' AND lower_port IS NULL AND upper_port IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,
                                         'user_name','connect') IS NULL THEN
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,
                                         'user_name', TRUE, 'connect');
END IF;
EXCEPTION
  WHEN no_data_found THEN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('ACL_name.xml',
      'ACL description', 'user_name', TRUE, 'connect');
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('ACL_name.xml','host_name');
END;
COMMIT;

With other words, if you don’t enable it this way, security wise, you will encounter an ORA-24247 error.

ORA-24247: network access denied by access control list (ACL)

Cause: No access control list (ACL) has been assigned to the target host or the privilege necessary to access the target host has not been granted to the user in the access control list.

Action: Ensure that an access control list (ACL) has been assigned to the target host and the privilege necessary to access the target host has been granted to the user.

In a different chapter also the following is mentioned.

Access Control for Network Utility Packages

The default behavior for access control to network utility packages has been changed to disallow network operations to all nonprivileged users. This default behavior is different from, and is incompatible with, previous versions of Oracle Database.

For database users upgrading to Oracle Database 11g Release 1 (11.1), applications that depend on the PL/SQL network utility packages compile without any issues. However, at runtime the applications might receive exceptions when attempting to perform privileged network operations. Although you can restore the compatibility by using a wildcard to grant those privileges to perform any network operations to PUBLIC, Oracle strongly advises that database administrators carefully review each situation on an individual basis and grant privileges only as needed.

Note:

Oracle XML DB is required to properly maintain the access control lists. If Oracle XML DB is not already installed on the system, then you must install it during the upgrade procedure.

Elaborating a little on the example given by Mark on the XMLDB OTN Forum site (Testing Protocol Server WSDL functionality), the via package DBMS_NETWORK_ACL_ADMIN created localhost.xml is protected via the standard XMLDB default ACL methods.

Click picture to enlarge

Given below the ACL protection created on the new localhost.xml file.

SQL> SELECT DBMS_XDB.getPrivileges('/sys/acls/localhost.xml').getCLOBVal() FROM DUAL;

And use of DBMS_NETWORK_ACL_ADMIN can be further investigated via:

SQL> desc dba_network_acls
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 HOST                                      NOT NULL VARCHAR2(1000)
 LOWER_PORT                                         NUMBER(5)
 UPPER_PORT                                         NUMBER(5)
 ACL                                                VARCHAR2(4000)
 ACLID                                     NOT NULL RAW(16)

SQL> select * from dba_network_acls
  2  ;
127.0.0.1

/sys/acls/localhost.xml
37A48352DFFD48DFE0400A0AAF0A1153

The WSDL Web Service Security is also controlled via the xdbconfig.xml file (which on itself is of course also protected via ACL’s).

SQL> SELECT DBMS_XDB.getPrivileges('/xdbconfig.xml').getCLOBVal() FROM DUAL;

SQL> XQUERY declare default element namespace "http://xmlns.oracle.com/xdb/xdbconfig.xsd"; (: :)
  2    (: This path is split over two lines for documentation purposes only.
  3       The path should actually be a single long line. :)
  4    for $doc in fn:doc("/xdbconfig.xml")/xdbconfig/sysconfig/protocolconfig/httpconfig/
  5                       webappconfig/servletconfig/servlet-list/servlet[servlet-name='orawsv']
  6  return $doc
  7  /

  orawsv
  C
  Oracle Query Web Service
  Servlet for issuing queries as a Web Service
  XDB

    XDB_WEBSERVICES
    XDB_WEBSERVICES

In all, I have to study these new security items more carefully; they could be a potential show stopper regarding database upgrades.