What must be in place to validate a XMLTYPE against a schema

access-controloracleoracle-11g-r2xml

I have a procedure that generates an XMLTYPE and I want to validate it against a schema. The problem is that there seems to be a permissions issue running createSchemaBasedXML because when I run the procedure as AUTHID DEFINER it gives the error "ORA-31050: Access denied", but when I run it as AUTHID CURRENT_USER it actually returns a validation specific error (I'll deal with that separately). CURRENT_USER is not an acceptable solution.

My supposition is that CURRENT_USER works because the user has the XMLADMIN role. Granting the permissions the role includes does not resolve the issue, so it must be the roles ability to bypass the ACLs.

The thing is, querying RESOURCE_VIEW for the ACL that protects the resource shows that it is protected by /sys/acls/all_owner_acl.xml. DBMS_XDB.getPrivileges shows that the xsd has all the following permissions:

  <read-properties/>
  <read-contents/>
  <write-config/>
  <link/>
  <unlink/>
  <read-acl/>
  <write-acl-ref/>
  <update-acl/>
  <resolve/>
  <link-to/>
  <unlink-from/>
  <dav:lock/>
  <dav:unlock/>
  <dav:write-properties/>
  <dav:write-content/>
  <dav:execute/>
  <dav:take-ownership/>
  <dav:read-current-user-privilege-set/>

Using DBMS_XDB.getAclDocument shows a principal of dav:owner has all privileges, so that must not be enough to allow the owner of the schema to create schema based XML. With this thought in mind I created a block to run DBMS_XDB.createResource creating a new ACL. I can successfully create the ACL and from SQLDeveloper I can see that it exists in the location I created it in.

There are any number of places I could be going wrong in this process, so the core of what I am looking for is this:

What must be in place to validate a XMLTYPE against a schema?

=== Update 4/3/2013 ===
I can set the acl for my xsd file to /sys/acls/all_all_acl.xml and back to /sys/acls/all_owner_acl.xml. As before, neither of these resolve the permissions issue. I also tried a createResource using the acl definition copied from all_owner_acl.xl and using the same path as those files of /sys/acls/. This at least successfully sets the ACL. I have run it for all my XSDs and queried RESOURCE_VIEW to confirm that they are set to the new ACL. After all this though, createSchemaBasedXML still gives an access denied error. Is my ACL correct or might there be another issue?

<acl xmlns="http://xmlns.oracle.com/xdb/acl.xsd" 
   xmlns:dav="DAV:" 
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"   
   xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd 
      http://xmlns.oracle.com/xdb/acl.xsd" shared="true">
       <ace>
         <grant>true</grant>
         <principal>MY_ORACLE_USER</principal>
         <privilege>
           <all/>
         </privilege>
       </ace>
     </acl>

=== Update 4/9/2013 ===
I have an anonymous block that can successfully validate XML based on a schema. This again points to a permissions issue that allows this to work when roles are enabled, but not when they are not available.

== Update 4/12/2013 ===
Every bit of evidence I get seems to indicate that there is something wrong with my ACL or perhaps more likely with the way I set the ACL. Taking the XDBADMIN role away from the user causes the anonymous block to fail with an access denied error even though I have granted all the permissions the role gives according to dba_tab_privs. My setACL follows this form:

DBMS_XDB.setACL('/sys/schemas/MY_ORACLE_USER/account.xsd', '/sys/acls/acl_acc.xml');

A complete test case can be found on Oracle Communities.

Best Answer

My test case ran fine on multiple other databases and multiple sources (including oracle support) asked if the ANONYMOUS user had been dropped. Although it had not, there were some XML DB Repository changes that were made without a full understanding. To try short cut the problem I re-installed XDB using note 1292089.1. This allowed my test case to work correctly and after dropping the created types and tables and re-registering our XML schema, everything worked correctly.

Related Question