27 Repository Access Control

Principal: A User or Role

In the context of fine-grained database access control, a principal is a user or a role. A user can be any person or application that accesses information in the database. A role is composed of users and possibly other roles, but this recursion cannot be circular. Ultimately, each role, and thus each principal, corresponds to a set of users.

A user is represented for access control purposes by an XML fragment with element user. A role is represented by a fragment with element role.

Oracle Database supports the following as principals:

• Database users and database roles. A database user is also sometimes referred to as a database schema or a user account. When a person or application logs onto the database, it uses a database user (schema) and password. A database role corresponds to a set of database privileges that can be granted to database users, applications, or other database roles — see "Database Roles Map Database Privileges to Users".

• LDAP users and groups of LDAP users. For details on using LDAP principals see "Integrating Oracle XML DB with LDAP".

When a term such as "user" or "role" is used here without qualification, it applies to each type of user or role. When it is important to distinguish the type, the qualifier "database" or "LDAP" is used.

Privilege: A Permission

A privilege is a particular right or permission that can be granted or denied to a principal. A privilege is aggregate or atomic:

• Aggregate privilege – A privilege that includes other privileges.

• Atomic privilege – A privilege that does not include other privileges. It cannot be subdivided.

Aggregate privileges simplify usability when the number of privileges becomes large, and they promote interoperability between ACL clients. See "Privileges".

Aggregate privileges retain their identity: they are not decomposed into the corresponding atomic (leaf) privileges. In WebDAV terms, Oracle Database aggregate privileges are not abstract. This implies that an aggregate privilege acts as a set of pointers to its component privileges, rather than a copy of those components. Thus, an aggregate privilege is always up to date, even if the definition of a component changes.

The set of privileges granted to a principal controls whether that principal can perform a given operation on the data that it protects. For example, if the principal (database user) HR wants to perform the read operation on a given resource, then read privileges must be granted to principal HR prior to the read operation.

Access Control Entry (ACE)

An access control entry (ACE) is an XML element (ace) that is an entry in an access control list (ACL). An ACE either grants or denies access to some repository resource or other database data by a particular principal (user or role). The ACE does not, itself, specify which data to protect. That is done outside the ACE and the ACL, by associating the ACL with target data. One way to make that association is by using PL/SQL procedure DBMS_XDB.setACL.

See "ACL and ACE Evaluation".

An Oracle XML DB ACE either grants or denies privileges for a principal. An ace element has the following:

• Operation grant: either true (to grant) or false (to deny) access.

• Either a valid principal (element principal) or a completed list of principals (element invert).

• Privileges: A set of privileges to be granted or denied for a particular principal (element privilege).

• Principal format (optional): The format of the principal. An LDAP distinguished name (DN), a short name (database user/role or LDAP nickname), or an LDAP GUID. The default value is short name. If the principal name matches both a database user and an LDAP nickname, it is assumed to refer to the LDAP nickname.

• Collection (optional): A BOOLEAN attribute that specifies whether the principal is a collection of users (LDAP group or database role) or a single user (LDAP or database user).

• Start and end date (optional): Attributes that define the time period over which an ACE is valid. See "ACE Validity Time Period".

Example 27-1 shows a simple ACE that grants privilege DAV::all to principal DAV::owner. It thus grants all privileges to the owner of the resource to which its ACL applies.

<ace>
  <grant>true</grant>
  <principal>DAV::owner</principal>
  <privilege>
    <DAV::all/>
  </privilege>
</ace>

Access Control List (ACL)

An access control list (ACL) is a list of access control entries (ACEs). By default, order in the list is relevant (see "ACL and ACE Evaluation"). Example 27-2 shows a simple ACL that contains only the ACE of Example 27-1.

<acl description="myacl"
     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">
  <ace>
    <grant>true</grant>
    <principal>dav:owner</principal>
    <privilege>
      <dav:all/>
    </privilege>
  </ace>
</acl>

Atomic Privileges

Table 27-2 lists the atomic privileges.

Aggregate Privileges

Table 27-3 lists the aggregate privileges and the atomic privileges of which each is composed.

System ACLs

Some ACLs are predefined and supplied with Oracle Database. They are referred to as system ACLs.

There is only one ACL that is self-protected, that is, protected by its own contents. It is the bootstrap ACL, a system ACL that is located in Oracle XML DB Repository at /sys/acls/bootstrap_acl.xml. The bootstrap ACL grants READ privilege to all users. It also grants FULL ACCESS to database roles XDBADMIN (the Oracle XML DB administrator) and DBA. Database role XDBADMIN is particularly useful for users who must register global XML schemas.

Other system ACLs include the following. Each is protected by the bootstrap ACL.

• all_all_acl.xml – Grants all privileges to all users.

• all_owner_acl.xml – Grants all privileges to the owner of the resource.

• ro_all_acl.xml – Grants read privileges to all users.

System ACLs use the file-naming convention <privilege>_<users>_acl.xml, where <privilege> represents the privilege granted, and <users> represents the users that are granted access to the resource. When you define your own ACLs, you can use any names you like.

ACL and ACE Evaluation

Privileges are checked before a principal is allowed to access a repository resource that is protected by one or more ACLs. This check is done by evaluating the protecting ACLs for that principal, in order. For each such ACL, the ACEs in it that apply to the principal are examined, in order.

If one ACE grants a certain privilege to the current user and another ACE denies that privilege to the user, then a conflict arises. There are two possible ways to manage conflicts among ACEs for the same principal.

• The default behavior, termed ace-order, is to use only the first ACE that occurs for a given principal. Additional ACEs for that principal have no effect. In this case, ACE order is relevant.

• You can, however, configure the database to use an alternate behavior, deny-trumps-grant. In this case, any ACE with child deny for a given principal denies permission to that principal, whether or not there are other ACEs for that principal that have a grant child. In this case, deny always takes precedence over grant, and ACE order is irrelevant.

You can configure ACL evaluation behavior by setting configuration parameter acl-evaluation-method, in configuration file xdbconfig.xml, to either ace-order or deny-trumps-grant. The default configuration file specifies ace-method, but the default value for element acl-evaluation-method, used when no method is given, is deny-trumps-grant.

ACL Validation

When an ACL is created, it is validated against the XML schema for ACLs, and some correctness tests are run, such as ensuring that start and end dates for ACEs are in chronological order. There is no complete check at ACL creation time of relations among ACLs. For example, no check is made for the existence and correctness of a parent ACL. (See "ACL Inheritance".)

Such a complete check of ACL correctness is called ACL validity checking, but it is not to be confused with its XML schema validity. For an ACL to be valid (as an ACL), it must also be XML schema-valid, but the converse does not hold.

A full ACL validity check is made at run time, whenever an ACL is evaluated to check whether a principal has the proper privileges for some operation. If this check finds that the ACL is invalid, then all privileges that the ACL would grant are denied to the specified principals.

ACL validity can also be checked independently of its run-time use to check privileges, by invoking PL/SQL procedure DBMS_XDBZ.validateACL. You can do this ahead of time, to avoid run-time errors or privilege denial due to ACL invalidity.

ACL Inheritance

An ACL can inherit grants, that is, associations of principals with privileges, from another ACL. Inheritance provides flexibility of definition and promotes reuse of access control policies.

Grants are defined in ACEs, so inheritance of grants involves traversing ACL inheritance chains and the associated ACES. But ACL inheritance, and therefore grant inheritance, can be recursive. If ACL A1 inherits from ACL A2, a grant defined by A1 is not necessarily present in an ACE of A2. It might instead be in an ACE in ACL A3, where A2 inherits (directly or indirectly) from A3.

ACL inheritance is simple, not multiple, inheritance: an ACL inherits from at most one other ACL. Cycles are not permitted in an inheritance chain: an ACL that inherits directly or indirectly from itself is invalid. An ACL that inherits from an ACL that does not exist is also invalid.

The grants declared in an ACL are those explicitly defined by its ACES. The grants defined for an ACL are those defined by its ACES plus those inherited by it.

There are two kinds of ACL inheritance, extending inheritance and constraining inheritance, specified using element extends-from or element constrained-with, respectively. Both elements reference the ACL being inherited from. An ACL can have at most one extends-from or constrained-with element. Example 27-3 shows an extends-from element. Example 27-4 shows a constrained-with element.

<extends-from type="simple" href="/sys/acls/extend_acl.xml"/>

<constrained-with type="simple" href="/sys/acls/constrain_acl.xml"/>

Extending inheritance extends the grants that are declared in the inheriting ACL (the child ACL) by some grants that are defined for the ACL it is inheriting from (the parent ACL). For example, if ACL A1 declares that it extends from ACL A2, then A1 can include grants defined for A2.

Constraining inheritance restricts the grants that are declared in the inheriting ACL to grants that are also defined for the ACL it is inheriting from. For example, if ACL A1 declares that it inherits from ACL A2 by constraining, then all grants defined for A1 must also be defined for A2.

Extending inheritance is a set union operation, and constraining inheritance is a set intersection operation. When ACL A1 extends from ACL A2, the grants in both can be combined to determine whether a given principal is granted a given privilege. When ACL A1 is constrained with ACL A2, only the grants that are common to both A1 and A2 are used to determine a grant.

More precisely, when ACL A1 inherits from ACL A2, and A1 is checked to see if a given principal has been granted a given set of privileges, determination proceeds as follows:

• If A1 extends from A2 – The ACEs that are declared in A1 are examined first. If they do not grant all or deny any of the privileges in question to the principal, then the ACEs defined in the extending-from parent of A1 are examined. This in turn means that if the ACEs that are explicitly declared in A2 do not grant all or deny any of the privileges in question to the principal, and if A2 extends from A3, then A3 is examined. And so on.

• If A1 is constrained with A2 – The ACEs that are explicitly declared in A1 and those defined for A2 are each examined separately to ensure that they both grant all of the privileges in question to the specified principal. The check for A2 proceeds the same way if it is constrained by ACL A3, and so on.

Put another way, extending inheritance accumulates granted privileges and constraining inheritance accumulates denied privileges. In extending inheritance, if either the child or the parent ACL grants a privilege to the principal, then the privilege is granted. In constraining inheritance, if either the child or the parent ACL denies a privilege, then it is denied.

Complementing the Principals in an ACE: Element invert

It is sometimes more convenient to define a set of principals by complementing another set of principals — that is the purpose of ACE element invert. Instead of listing each of the principals that you want to include, wrap the list of principals that you want to exclude with element invert.

In Example 27-5, the first ACE denies privilege privilege1 to all principals except IntranetUsers. Because (by default) ACEs are considered in the order they appear, all subsequent ACEs are overridden by the first ACE, so principal NonIntraNetUser is denied privilege privilege1 in spite of the explicit grant.

<acl description="invert 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">
  <extends-from type="simple" href="/sys/acls/parent_acl.xml"/>
  <ace>
    <grant>false</grant>
    <invert><principal>dav:owner</principal></invert>
    <privilege><read-contents/></privilege>
  </ace>
  <ace>
    <grant>true</grant>
    <principal>GERONIMO</principal>
    <privilege><read-contents/></privilege>
  </ace>
</acl>

ACE Validity Time Period

You can use optional attributes start_date and end_date (of XML Schema type dateTime) to define the time period over which an ACE is valid. If start_date is specified, then the ACE is valid on and after that date. If end_date is specified, then the ACS is invalid after that date. The end_date value must follow the start_date chronologically or else be the same value. Otherwise, the ACE and its ACL are invalid. If no time zone is specified in an XML Schema dateTime value, then GMT (UTC) is assumed. Example 27-6 shows an ACE with start and end dates.

<ace start_date="2008-02-12T00:00:00Z" end_date="2008-12-31T00:00:00Z">
  <grant>true</grant>
  <principal>GERONIMO</principal>
  <privilege><read-contents/></privilege>
</ace>

Creating an ACL using DBMS_XDB.CREATERESOURCE

Example 27-7 creates an ACL as file resource /TESTUSER/acl1.xml. If applied to a resource, this ACL grants all privileges to the owner of the resource.

DECLARE
  b BOOLEAN;
BEGIN
  b := DBMS_XDB.createFolder('/TESTUSER');
  b := DBMS_XDB.createResource(
         '/TESTUSER/acl1.xml', 
         '<acl description="myacl"
               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">
            <ace>
              <grant>true</grant>
              <principal>dav:owner</principal>
              <privilege>
                <dav:all/>
              </privilege>
            </ace>
          </acl>',
         'http://xmlns.oracle.com/xdb/acl.xsd',
         'acl');
END;

Retrieving an ACL Document, Given its Repository Path

Example 27-8 shows how to retrieve an ACL document, given its location in Oracle XML DB Repository.

SELECT a.OBJECT_VALUE FROM RESOURCE_VIEW rv, XDB.XDB$ACL a
  WHERE ref(a)
        = XMLCast(XMLQuery('declare default element namespace
                            "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
                            fn:data(/Resource/XMLRef)'
                           PASSING rv.RES RETURNING CONTENT)
                  AS REF XMLType)
    AND equals_path(rv.RES, '/TESTUSER/acl1.xml') = 1;

OBJECT_VALUE
--------------------------------------------------------------------------------
<acl description="myacl" 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://xm
lns.oracle.com/xdb/acl.xsd" shared="true">
  <ace>
    <grant>true</grant>
    <principal>dav:owner</principal>
    <privilege>
      <dav:all/>
    </privilege>
  </ace>
</acl>

Setting the ACL of a Resource

Example 27-9 creates resource /TESTUSER/po1.xml and sets its ACL to /TESTUSER/acl1.xml using PL/SQL procedure DBMS_XDB.setACL.

DECLARE
  b BOOLEAN;
BEGIN
  b := DBMS_XDB.createResource('/TESTUSER/po1.xml', 'Hello');
END;
/
 
CALL DBMS_XDB.setACL('/TESTUSER/po1.xml', '/TESTUSER/acl1.xml');

Deleting an ACL

Example 27-10 illustrates how to delete an ACL using procedure DBMS_XDB.deleteResource. It deletes the ACL created in Example 27-7.

CALL DBMS_XDB.deleteResource('/TESTUSER/acl1.xml');

If a resource is being protected by an ACL that you want to delete, change the ACL of that resource before deleting the ACL.

Updating an ACL

You can update an ACL using any of the standard ways of updating resources. In particular, since an ACL is an XML document, you can use Oracle SQL function updateXML and related XML-updating functions to manipulate ACLs. You must COMMIT after making any ACL changes.

Oracle XML DB ACLs are cached, for fast evaluation. When a transaction that updates an ACL is committed, the modified ACL is picked up by existing database sessions, after the timeout specified in the Oracle XML DB configuration file, /xdbconfig.xml. The XPath location for this timeout parameter is /xdbconfig/sysconfig/acl-max-age. The value is expressed in seconds. Sessions initiated after the ACL is modified use the new ACL without any delay.

If an ACL resource is updated with non-ACL content, the same rules apply as for deletion. Thus, if any resource is being protected by an ACL that is being updated, you must first change the ACL.

You can use FTP or WebDAV to update an ACL. For more details on how to use these protocols, see Chapter 28, "Accessing the Repository using Protocols". You can update an ACL or an access control entry (ACE) using RESOURCE_VIEW.

Example 27-11 uses Oracle SQL function updateXML to update the ACL /TESTUSER/acl1.xml by replacing it entirely. The effect is to replace the principal value DAV::owner by TESTUSER, because the rest of the replacement ACL is the same as it was before.

UPDATE RESOURCE_VIEW r
  SET r.RES =
        updateXML(
          r.RES,
          '/r:Resource/r:Contents/a:acl',
          '<acl description="myacl"
                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">
             <ace>
               <grant>true</grant>
               <principal>TESTUSER</principal>
               <privilege>
                 <dav:all/>
               </privilege>
             </ace>
           </acl>',
          'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"
           xmlns:a="http://xmlns.oracle.com/xdb/acl.xsd"')
  WHERE equals_path(r.RES, '/TESTUSER/acl1.xml') = 1;

Example 27-12 uses Oracle SQL function appendChildXML to append an ACE to an existing ACL. The ACE gives privileges read-properties and read-contents to user HR.

UPDATE RESOURCE_VIEW r
  SET r.RES =
        appendChildXML(
          r.RES,
          '/r:Resource/r:Contents/a:acl',
          XMLType('<ace xmlns="http://xmlns.oracle.com/xdb/acl.xsd">
                     <grant>true</grant>
                     <principal>HR</principal>
                     <privilege>
                       <read-properties/>
                       <read-contents/>
                     </privilege>
                   </ace>'),
                  'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"
                   xmlns:a="http://xmlns.oracle.com/xdb/acl.xsd"')
  WHERE equals_path(r.RES, '/TESTUSER/acl1.xml') = 1;

Example 27-13 uses Oracle SQL function deleteXML to delete an ACE from an ACL. The first ACE is deleted.

UPDATE RESOURCE_VIEW r
  SET r.RES =
        deleteXML(r.RES,
                  '/r:Resource/r:Contents/a:acl/a:ace[1]',
                  'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"
                   xmlns:a="http://xmlns.oracle.com/xdb/acl.xsd"')
  WHERE equals_path(r.RES, '/TESTUSER/acl1.xml') = 1;

Retrieving the ACL Document that Protects a Given Resource

Example 26-2 illustrates how to use function DBMS_XDB.getACLDocument to retrieve the ACL document that protects a given resource.

SELECT XMLSerialize(DOCUMENT DBMS_XDB.getACLDocument('/TESTUSER/po1.xml')
                    AS CLOB)
  FROM DUAL;
 
XMLSERIALIZE(DOCUMENTDBMS_XDB.GETACLDOCUMENT('/TESTUSER/PO1.XML')ASCLOB)
--------------------------------------------------------------------------------
<acl description="myacl" 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://x
mlns.oracle.com/xdb/acl.xsd">
  <ace>
    <grant>true</grant>
    <principal>TESTUSER</principal>
    <privilege>
      <dav:all/>
    </privilege>
  </ace>
  <ace xmlns="http://xmlns.oracle.com/xdb/acl.xsd">
    <grant>true</grant>
    <principal>HR</principal>
    <privilege>
      <read-properties/>
      <read-contents/>
    </privilege>
  </ace>
</acl>
 
1 row selected.

Retrieving Privileges Granted to the Current User for a Particular Resource

Example 27-15 illustrates how to retrieve privileges granted to the current user using function DBMS_XDB.getPrivileges.

SELECT XMLSerialize(DOCUMENT DBMS_XDB.getPrivileges('/TESTUSER/po1.xml')
                    AS CLOB)
  FROM DUAL;
 
XMLSERIALIZE(DOCUMENTDBMS_XDB.GETPRIVILEGES('/TESTUSER/PO1.XML')ASCLOB)
--------------------------------------------------------------------------------
<privilege xmlns="http://xmlns.oracle.com/xdb/acl.xsd" 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 DAV: http://xmlns.oracle.com/xdb/dav.xs
d" xmlns:xdbacl="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:dav="DAV:">
  <read-acl/>
  <dav:execute/>
  <read-contents/>
  <update-acl/>
  <dav:write-content/>
  <dav:read-current-user-privilege-set/>
  <link-to/>
  <resolve/>
  <dav:lock/>
  <unlink-from/>
  <write-config/>
  <dav:write-properties/>
  <dav:unlock/>
  <link/>
  <write-acl-ref/>
  <read-properties/>
  <dav:take-ownership/>
  <unlink/>
</privilege>

1 row selected.

Checking Whether the Current User Has Privileges on a Resource

Example 27-16 illustrates how to use function DBMS_XDB.checkPrivileges to check whether the current user has a given set of privileges on a resource. This function returns a nonzero value if the user has the privileges.

SELECT DBMS_XDB.checkPrivileges(

'/TESTUSER/po1.xml',
         XMLType('<privilege
                      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">
                    <read-contents/>
                    <read-properties/>
                  </privilege>'))
  FROM DUAL;
 
DBMS_XDB.CHECKPRIVILEGES('/TESTUSER/PO1.XML',
---------------------------------------------
                                            1
 
1 row selected.

Example 27-16 checks to see if the access privileges read-contents and read-properties have been granted to the current user on resource /TESTUSER/po1.xml. The positive-integer return value shows that they have.

Checking Whether a User Has Privileges using the ACL and Resource Owner

Function DBMS_XDB.ACLCheckPrivileges is typically used by applications that must perform ACL evaluation on their own, before allowing a user to perform an operation.

Example 27-17 checks whether the ACL /TESTUSER/acl1.xml grants the privileges read-contents and read-properties to the current user, sh. The second argument, TESTUSER, is the user that is substituted for DAV::owner in the ACL when checking. Since user sh does not match any of the users granted the specified privileges, the return value is zero.

CONNECT sh
Enter password: <password>

Connected.

SELECT DBMS_XDB.ACLCheckPrivileges(
         '/TESTUSER/acl1.xml',
         'TESTUSER',
         XMLType('<privilege
                      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">
                    <read-contents/>
                    <read-properties/>
                  </privilege>'))
  FROM DUAL;
 
DBMS_XDB.ACLCHECKPRIVILEGES('/TESTUSER/ACL1.XML','TESTUSER',
------------------------------------------------------------
                                                           0
 
1 row selected.

Retrieving the Path of the ACL that Protects a Given Resource

Example 27-18 retrieves the path of the ACL that protects a given resource, by using a RESOURCE_VIEW query. The query uses the fact that the XMLRef and ACLOID elements of the resource form the link between an ACL and a resource.

SELECT rv1.ANY_PATH
  FROM RESOURCE_VIEW rv1
  WHERE
    XMLCast(XMLQuery('declare default element namespace
                      "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
                      fn:data(/Resource/XMLRef)'
                     PASSING rv1.RES RETURNING CONTENT)
            AS REF XMLType)
    = make_ref(XDB.XDB$ACL,
               (SELECT XMLCast(XMLQuery('declare default element namespace
                                         "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
                                         fn:data(/Resource/ACLOID)'
                                        PASSING rv2.RES RETURNING CONTENT)
                               AS REF XMLType)
                  FROM RESOURCE_VIEW rv2
                  WHERE equals_path(rv2.RES, '/TESTUSER/po1.xml') = 1));

ANY_PATH
------------------
/TESTUSER/acl1.xml

Example 27-18 retrieves the path to an ACL, given a resource protected by the ACL. The ACLOID of a protected resource (r) stores the OID of the ACL resource (a) that protects it. The REF of the ACL resource is the same as that of the object identified by the protected-resource ACLOID.

The REF of the resource ACLOID can be obtained using Oracle SQL function make_ref, which returns a REF to an object-table row with a given OID.

In this example, make_ref returns a REF to the row of table XDB$ACL whose OID is the /Resource/ACLOID for the resource /TESTUSER/po1.xml. The inner query returns the ACLOID of the resource. The outer query returns the path to the corresponding ACL.

Retrieving the Paths of All Resources Protected by a Given ACL

Example 27-19 retrieves the paths of all resources protected by a given ACL.

SELECT rv1.ANY_PATH
  FROM RESOURCE_VIEW rv1
  WHERE make_ref(XDB.XDB$ACL, 
                 XMLCast(XMLQuery('declare default element namespace
                                   "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
                                   fn:data(/Resource/ACLOID)'
                                  PASSING rv1.RES RETURNING CONTENT)
                         AS REF XMLType))
        = (SELECT XMLCast(XMLQuery('declare default element namespace
                                    "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
                                    fn:data(/Resource/XMLRef)'
                                   PASSING rv2.RES RETURNING CONTENT)
                          AS REF XMLType)
             FROM RESOURCE_VIEW rv2
             WHERE equals_path(rv2.RES, '/TESTUSER/acl1.xml') = 1);

ANY_PATH
-----------------
/TESTUSER/po1.xml
 
1 row selected.

Example 27-19 retrieves the paths to the resources whose ACLOID REF matches the REF of the ACL resource whose path is /TESTUSER/acl1.xml. Function make_ref returns the resource ACLOID REF.

The inner query retrieves the REF of the specified ACL. The outer query selects the paths of the resources whose ACLOID REF matches the REF of the specified ACL.

Optimization: Do not enforce acl-based security if you do not need it

ACL-based security provides control of access to XML content document-by-document, rather than just table-by-table. When you call PL/SQL procedure DBMS_XMLSCHEMA.register_chema, the tables it creates have ACL-based security enabled, by default.

One effect of this is that when the XML content of such a table is accessed using a SQL statement, a call to sys_checkACL is automatically added to the query WHERE clause, to ensure that the ACL security that was defined is enforced at the SQL level.

Enforcing ACL-based security adds overhead to the SQL query, however. If ACL-based security is not required, then use procedure disable_hierarchy in package DBMS_XDBZ to turn off ACL checking.

When ACL-based security is enabled for an XMLType table, the execution plan output for a query of that table contains a filter similar to the following:

3 - filter(SYS_CHECKACL("ACLOID","OWNERID",xmltype(''<privilege
              xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
              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
              DAV:http://xmlns.oracle.com/xdb/dav.xsd">
                        <read-properties/><read-contents/></privilege>''))=1)

In this example, the filter checks that the user performing the SQL query has read-contents privilege on each of the documents to be accessed.

After calling DBMX_XDBZ.disable_hierarchy, an execution plan of the same query does not show SYS_CHECKACL in the filter.