26 Accessing the Repository using PL/SQL

This chapter describes the Oracle XML DB resource application program interface (API) for PL/SQL (PL/SQL package DBMS_XDB). It contains these topics:

Overview of PL/SQL Package DBMS_XDB
DBMS_XDB: Resource Management
DBMS_XDB: ACL-Based Security Management
DBMS_XDB: Configuration Management

Overview of PL/SQL Package DBMS_XDB

PL/SQL package DBMS_XDB is the Oracle XML DB resource application program interface (API) for PL/SQL. It is also known as the PL/SQL foldering API. This API provides functions and procedures to access and manage Oracle XML DB Repository resources using PL/SQL. It includes methods for managing resource security and Oracle XML DB configuration.

Oracle XML DB Repository is modeled on XML, and provides a database file system for any data. The repository maps path names (or URLs) onto database objects of XMLType and provides management facilities for these objects.

PL/SQL package DBMS_XDB is an API that you can use to manage all of the following:

Oracle XML DB resources
Oracle XML DB security based on access control lists (ACLs). An ACL is a list of access control entries (ACEs) that determines which principals (users and roles) have access to which resources
Oracle XML DB configuration

See Also:

DBMS_XDB: Resource Management

Table 26-1 describes the DBMS_XDB Oracle XML DB resource management functions and procedures.

Table 26-1 DBMS_XDB Resource Management Functions and Procedures

Function/Procedure	Description
`appendResourceMetadata`	Add user-defined metadata to a resource.
`createFolder`	Create a new folder resource.
`createOIDPath`	Create a virtual path to a resource, based on its object identifier (OID).
`createResource`	Create a new file resource.
`deleteResource`	Delete a resource from the repository.
`deleteResourceMetadata`	Delete specific user-defined metadata from a resource.
`existsResource`	Indicate whether or not a resource exists, given its absolute path.
`getLockToken`	Return a resource lock token for the current user, given a path to the resource.
`getResOID`	Return the object identifier (OID) of a resource, given its absolute path.
`getXDB_tablespace`	Return the current tablespace of database schema (user account) `XDB`.
`link`	Create a link to an existing resource.
lockResource	Obtain a WebDAV-style lock on a resource, given a path to the resource.
`moveXDB_tablespace`	Move user `XDB` to the specified tablespace.
`purgeResourceMetadata`	Delete all user-defined metadata from a resource.
`rebuildHierarchicalIndex`	Rebuild the hierarchical repository index, after import or export operations.
`renameResource`	Rename a resource.
`unlockResource`	Unlock a resource, given its lock token and path.
`updateResourceMetadata`	Modify user-defined resource metadata.

Tip:

For optimal performance of queries on repository resources, gather statistics for the optimizer using procedure DBMS_XDB_ADMIN.gatherRepositoryStats after resource creation. You need the XDBADMIN role with privilege ANALYZE ANY or the DBA role to use gatherRepositoryStats.

The examples in this section illustrate the use of these functions and procedures.

Example 26-1 uses package DBMS_XDB to manage repository resources. It creates the following:

a folder, mydocs, under folder /public
two file resources, emp_selby.xml and emp_david.xml
two links to the file resources, person_selby.xml and person_david.xml

It then deletes each of the newly created resources and links. The folder contents are deleted before the folder itself.

Example 26-1 Managing Resources using DBMS_XDB

DECLARE
  retb BOOLEAN;
BEGIN
  retb := DBMS_XDB.createfolder('/public/mydocs');
  retb := DBMS_XDB.createresource('/public/mydocs/emp_selby.xml',
                                  '<emp_name>selby</emp_name>');
  retb := DBMS_XDB.createresource('/public/mydocs/emp_david.xml',
                                  '<emp_name>david</emp_name>');
END;
/
PL/SQL procedure successfully completed.
 
CALL DBMS_XDB.link('/public/mydocs/emp_selby.xml',
                   '/public/mydocs',
                   'person_selby.xml');
Call completed.
 
CALL DBMS_XDB.link('/public/mydocs/emp_david.xml',
                   '/public/mydocs',
                   'person_david.xml');
Call completed.
 
CALL DBMS_XDB.deleteresource('/public/mydocs/emp_selby.xml');
Call completed.
 
CALL DBMS_XDB.deleteresource('/public/mydocs/person_selby.xml');
Call completed.
 
CALL DBMS_XDB.deleteresource('/public/mydocs/emp_david.xml');
Call completed.
 
CALL DBMS_XDB.deleteresource('/public/mydocs/person_david.xml');
Call completed.
 
CALL DBMS_XDB.deleteresource('/public/mydocs');
Call completed.

See Also:

Chapter 29, "User-Defined Repository Metadata" for examples using appendResourceMetadata and deleteResourceMetadata

DBMS_XDB: ACL-Based Security Management

Table 26-2 lists the DBMS_XDB Oracle XML DB ACL- based security management functions and procedures.

Table 26-2 DBMS_XDB: Security Management Procedures and Functions

Function/Procedure	Description
`ACLCheckPrivileges`	Checks the access privileges granted to the current user by an ACL.
`changePrivileges`	Adds an ACE to a resource ACL.
`checkPrivileges`	Checks the access privileges granted to the current user for a resource.
`getACLDocument`	Retrieves the ACL document that protects a resource, given the path name of the resource.
`getPrivileges`	Returns all privileges granted to the current user for a resource.
`setACL`	Sets the ACL on a resource.

See Also:

The examples in this section illustrate the use of these functions and procedures.

In Example 26-2, database user HR creates two resources: a folder, /public/mydocs, with a file in it, emp_selby.xml. Procedure getACLDocument is called on the file resource, showing that the <principal> user for the document is PUBLIC.

Example 26-2 Using DBMS_XDB.GETACLDOCUMENT

CONNECT hr
Enter password: password

Connected.

DECLARE
  retb BOOLEAN;
BEGIN
  retb := DBMS_XDB.createFolder('/public/mydocs');
  retb := DBMS_XDB.createResource('/public/mydocs/emp_selby.xml',
                                  '<emp_name>selby</emp_name>');
END;
/
PL/SQL procedure successfully completed.
 
SELECT XMLSerialize(DOCUMENT
                    DBMS_XDB.getACLDocument('/public/mydocs/emp_selby.xml')
                    AS CLOB)
  FROM DUAL;
 
XMLSERIALIZE(DOCUMENTDBMS_XDB.GETACLDOCUMENT('/PUBLIC/MYDOCS/EMP_SELBY.XML')ASCL
--------------------------------------------------------------------------------
<acl description="Public:All privileges to PUBLIC" xmlns="http://xmlns.oracle.co
m/xdb/acl.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaL
ocation="http://xmlns.oracle.com/xdb/acl.xsd                           http://xm
lns.oracle.com/xdb/acl.xsd" shared="true">
  <ace>
    <grant>true</grant>
    <principal>PUBLIC</principal>
    <privilege>
      <all/>
    </privilege>
  </ace>
</acl>
 
1 row selected.

In Example 26-3, the system manager connects and uses procedure setACL to give the owner (database schema HR) all privileges on the file resource created in Example 26-2. Procedure getACLDocument then shows that the <principal> user is dav:owner, the owner (HR).

Example 26-3 Using DBMS_XDB.SETACL

CONNECT SYSTEM
Enter password: password

Connected.
 
-- Give all privileges to owner, HR.
CALL DBMS_XDB.setACL('/public/mydocs/emp_selby.xml',
                     '/sys/acls/all_owner_acl.xml');
Call completed.
COMMIT;
Commit complete.

SELECT XMLSerialize(DOCUMENT
                    DBMS_XDB.getACLDocument('/public/mydocs/emp_selby.xml')
                    AS CLOB)
  FROM DUAL;

XMLSERIALIZE(DOCUMENTDBMS_XDB.GETACLDOCUMENT('/PUBLIC/MYDOCS/EMP_SELBY.XML')ASCL
--------------------------------------------------------------------------------
<acl description="Private:All privileges to OWNER only and not accessible to oth
ers" xmlns="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:dav="DAV:" xmlns:xsi="htt
p://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>dav:owner</principal>
    <privilege>
      <all/>
    </privilege>
  </ace>
</acl>
 
1 row selected.

In Example 26-4, user HR connects and uses function changePrivileges to add a new access control entry (ACE) to the ACL, which gives all privileges on resource emp_selby.xml to user oe. Procedure getACLDocument shows that the new ACE was added to the ACL.

Example 26-4 Using DBMS_XDB.CHANGEPRIVILEGES

CONNECT hr
Enter password: password

Connected.

SET SERVEROUTPUT ON

-- Add an ACE giving privileges to user OE
DECLARE
  r        PLS_INTEGER;
  ace      XMLType;
  ace_data VARCHAR2(2000);
BEGIN
  ace_data := '<ace 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">
                 <principal>OE</principal>
                 <grant>true</grant>
                 <privilege><all/></privilege>
               </ace>';
  ace := XMLType.createXML(ace_data);
  r := DBMS_XDB.changePrivileges('/public/mydocs/emp_selby.xml', ace);
END;
/
 
PL/SQL procedure successfully completed.

COMMIT;

SELECT XMLSerialize(DOCUMENT
                    DBMS_XDB.getACLDocument('/public/mydocs/emp_selby.xml')
                    AS CLOB)
  FROM DUAL;
 
XMLSERIALIZE(DOCUMENTDBMS_XDB.GETACLDOCUMENT('/PUBLIC/MYDOCS/EMP_SELBY.XML')ASCL
--------------------------------------------------------------------------------
<acl description="Private:All privileges to OWNER only and not accessible to oth
ers" xmlns="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:dav="DAV:" xmlns:xsi="htt
p://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.
com/xdb/acl.xsd                           http://xmlns.oracle.com/xdb/acl.xsd" s
hared="false">
  <ace>
    <grant>true</grant>
    <principal>dav:owner</principal>
    <privilege>
      <all/>
    </privilege>
  </ace>
  <ace>
    <grant>true</grant>
    <principal>OE</principal>
    <privilege>
      <all/>
    </privilege>
  </ace>
</acl>
 
1 row selected.

In Example 26-5, user oe connects and calls DBMS_XDB.getPrivileges, which shows all of the privileges granted to user oe on resource emp_selby.xml.

Example 26-5 Using DBMS_XDB.GETPRIVILEGES

CONNECT oe
Enter password: password

Connected.

SELECT XMLSerialize(DOCUMENT
                    DBMS_XDB.getPrivileges('/public/mydocs/emp_selby.xml')
                    AS CLOB)
  FROM DUAL;
 
XMLSERIALIZE(DOCUMENTDBMS_XDB.GETPRIVILEGES('/PUBLIC/MYDOCS/EMP_SELBY.XML')ASCLO
--------------------------------------------------------------------------------
<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-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/>
</privilege>
 
1 row selected.

DBMS_XDB: Configuration Management

Table 26-3 lists the DBMS_XDB Oracle XML DB configuration management functions and procedures.

Table 26-3 DBMS_XDB: Configuration Management Functions and Procedures

Function/Procedure	Description
`cfg_get`	Returns the configuration information for the current session.
`cfg_refresh`	Refreshes the session configuration information using the current Oracle XML DB configuration file, `xdbconfig.xml`.
`cfg_update`	Updates the Oracle XML DB configuration information. This writes the configuration file, `xdbconfig.xml`.
`getFTPPort`	Returns the current FTP port number.
`getHTTPPort`	Returns the current HTTP port number.
`setFTPPort`	Sets the Oracle XML DB FTP port to the specified port number.
`setHTTPPort`	Sets the Oracle XML DB HTTP port to the specified port number.

The examples in this section illustrate the use of these functions and procedures.

Example 26-6 uses function cfg_get to retrieve the Oracle XML DB configuration file, xdbconfig.xml.

Example 26-6 Using DBMS_XDB.CFG_GET

CONNECT SYSTEM
Enter password: password

Connected.

SELECT DBMS_XDB.cfg_get() FROM DUAL;
 
DBMS_XDB.CFG_GET()
--------------------------------------------------------------------------------
<xdbconfig xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd" xmlns:xsi="http://w
ww.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/x
db/xdbconfig.xsd                                     http://xmlns.oracle.com/xdb
/xdbconfig.xsd">
  <sysconfig>
    <acl-max-age>19</acl-max-age>
    <acl-cache-size>32</acl-cache-size>
    <invalid-pathname-chars/>
    <case-sensitive>true</case-sensitive>
    <call-timeout>6000</call-timeout>
    <max-link-queue>65536</max-link-queue>
    <max-session-use>100</max-session-use>
    <persistent-sessions>false</persistent-sessions>
    <default-lock-timeout>3600</default-lock-timeout>
    <xdbcore-logfile-path>/sys/log/xdblog.xml</xdbcore-logfile-path>
    <xdbcore-log-level>0</xdbcore-log-level>
    <resource-view-cache-size>1048576</resource-view-cache-size>
    <protocolconfig>
      <common>
        . . .
      </common>
      <ftpconfig>
        . . .
      </ftpconfig>
      <httpconfig>
        <http-port>0</http-port>
        <http-listener>local_listener</http-listener>
        <http-protocol>tcp</http-protocol>
        <max-http-headers>64</max-http-headers>
        <max-header-size>16384</max-header-size>
        <max-request-body>2000000000</max-request-body>
        <session-timeout>6000</session-timeout>
        <server-name>XDB HTTP Server</server-name>
        <logfile-path>/sys/log/httplog.xml</logfile-path>
        <log-level>0</log-level>
        <servlet-realm>Basic realm=&quot;XDB&quot;</servlet-realm>
        <webappconfig>
        . . .
        </webappconfig>
        <authentication>
        . . .
        </authentication>
    </protocolconfig>
    <xdbcore-xobmem-bound>1024</xdbcore-xobmem-bound>
    <xdbcore-loadableunit-size>16</xdbcore-loadableunit-size>
    <acl-evaluation-method>ace-order</acl-evaluation-method>
  </sysconfig>
</xdbconfig>
 
1 row selected.

Example 26-7 illustrates the use of procedure cfg_update. The current configuration is retrieved as an XMLType instance and modified. It is then rewritten using cfg_update.

Example 26-7 Using DBMS_XDB.CFG_UPDATE

DECLARE
  configxml    SYS.XMLType;
  configxml2   SYS.XMLType;
BEGIN
  -- Get the current configuration
  configxml := DBMS_XDB.cfg_get();
 
  -- Modify the configuration
  SELECT updateXML(
           configxml,
           '/xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/text()',
           '8000',
           'xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd"')
    INTO configxml2 FROM DUAL;
 
  -- Update the configuration to use the modified version
  DBMS_XDB.cfg_update(configxml2);
END;
/
 
PL/SQL procedure successfully completed.

SELECT DBMS_XDB.cfg_get() FROM DUAL;
 
DBMS_XDB.CFG_GET()
--------------------------------------------------------------------------------
<xdbconfig xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd" xmlns:xsi="http://w
ww.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/x
db/xdbconfig.xsd http://xmlns.oracle.com/xdb/xdbconfig.xsd">
  <sysconfig>
    <acl-max-age>15</acl-max-age>
    <acl-cache-size>32</acl-cache-size>
    <invalid-pathname-chars/>
    <case-sensitive>true</case-sensitive>
    <call-timeout>6000</call-timeout>
    <max-link-queue>65536</max-link-queue>
    <max-session-use>100</max-session-use>
    <persistent-sessions>false</persistent-sessions>
    <default-lock-timeout>3600</default-lock-timeout>
    <xdbcore-logfile-path>/sys/log/xdblog.xml</xdbcore-logfile-path>
    <resource-view-cache-size>1048576</resource-view-cache-size>
    <protocolconfig>
      <common>
      . . .
      </common>
      <ftpconfig>
      . . .
      </ftpconfig>
      <httpconfig>
        <http-port>8000</http-port>
        . . .
      </httpconfig>
    </protocolconfig>
    <xdbcore-xobmem-bound>1024</xdbcore-xobmem-bound>
    <xdbcore-loadableunit-size>16</xdbcore-loadableunit-size>
    <acl-evaluation-method>ace-order</acl-evaluation-method>
</xdbconfig>
 
1 row selected.