Category: 11gR1, 11gR2
August 14

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:

Read the postOracle 11g Security – XMLDB is here to stay…
August 13

XMLIndex (Part 2) – XMLIndex Path Subsetting

As said in the former post, one of the disadvantages of creating a full blown XMLIndex, indexing on all possible values, is that the size of the XMLIndex is most of the time larger in size then the table itself.

This is one of the reasons that it is possible to make use of what is called “XMLIndex Path Subsetting“. In short, one creates only indexes on XPath locations which are needed. By default, XMLIndex indexes all possible XPath locations in your XML data, this is easy if you have no knowledge of what data will be selected. As said, the disadvantage is that this will use a lot of space.

Path Subsetting

With XMLIndex Path Subsetting one can remove all the index values that one doesn’t need after indexing all Xpath locations OR you create a skeleton structure and add all the index values on the XPath locations you actually need.

As in the relational database world, one should not build an index on every possible table column and concatenated column combinations. So, IMHO, I think it is easier to start from scratch with no index available and build only those indexes on index paths you need.

In the following example, I will demonstrate how you can do this. Given the examples from the “Oracle 11g – XMLIndex (part 1)” post, you can use XMLIndex path subsetting the following way…

Read the postXMLIndex (Part 2) – XMLIndex Path Subsetting
August 10

Oracle 11g – How to enable native WSDL services

Now the first production release is out…

Check it out: WSDL services via the Oracle Protocol Listener. As mentioned in the documentation of the Oracle XMLDB Developers Guide, to enable the WSDL service:

DECLARE
  SERVLET_NAME VARCHAR2(32) := 'orawsv';
BEGIN
  DBMS_XDB.deleteServletMapping(SERVLET_NAME);
  DBMS_XDB.deleteServlet(SERVLET_NAME);
  DBMS_XDB.addServlet(NAME => SERVLET_NAME,
                              LANGUAGE => 'C',
                              DISPNAME => 'Oracle Query Web Service',
                              DESCRIPT => 'Servlet for issuing queries as a Web Service',
                              SCHEMA => 'XDB');
  DBMS_XDB.addServletSecRole(SERVNAME => SERVLET_NAME,
                             ROLENAME => 'XDB_WEBSERVICES',
                             ROLELINK => 'XDB_WEBSERVICES');
  DBMS_XDB.addServletMapping(PATTERN => '/orawsv/*',
                             NAME => SERVLET_NAME);
END;
/

This wil update the xdbconfig.xml file.

To use the WSDL service grant the role XDB_WEBSERVICES to the schema that needs it. This role enables use of Web services over HTTPS; it is required to be able to use Web services.

Read the postOracle 11g – How to enable native WSDL services