Search

Top 60 Oracle Blogs

Recent comments

XMLDB

Oracle XBRL Extention – Second Release now available for Download

The XBRL Extention, a no cost add-on extention on XML-DB targeting the Extensible Business Reporting Language area, is now available for download. This second release ( p12388668_112020_Generic.zip ) of the Oracle XBRL Extension can be downloaded from the Oracle Support site (https://support.oracle.com). Once you have downloaded the file, please follow the instructions in the README.txt file to install the software.

Here are what’s new in the new in this release:

  • New routines createStarSchemaFromFact and createStarSchemaFromHC have been added to PL/SQL package DBMS_ORAXBRLV to retrieve the list of table (view) names of a generated star schema.
  • New routine DTS_filelist has been added to PL/SQL package DBMS_ORAXBRL to retrieve a discoverable taxonomy set (DTS).

For more info about the Oracle XBRL Extention see also “Oracle XBRL Extension Ready for Download”.

The Oracle XMLDB “anonymous” user account

Trying here to be as correct as possible, as far as I understand it currently.

ANONYMOUS is an Oracle user account specifically designed for HTTP access. It has only one system privilege, that is “create session” and the account is locked by default. If it is unlocked, it only is used for HTTP access via the XDB Protocol Server, aka PL/SQL Gateway, and can access objects in the XDB Repository that are protected by an ACL (Access Control Lists) mentioning this “principal”.

By default there is no ACL file that grants any privilege to this “user” ANONYMOUS. When APEX is installed then there will be a /sys/acls/ro_anonymous_acl.xml file that grants read access to the /images/ or /i/ directory (depending on the APEX version). If you lock ANONYMOUS or remove the ACL defined privileges then APEX can not show/access those files in that XDB Repository folder (/images, /i) if you would need to access these files. For example when using the APEX listener setup the application images and help doc images are stored locally on the server and not in the database, so in principal there is no need to access those image(s) directories in the database.

Example of an ACL which can used by XDB which grants read properties and read content rights to all objects which are protected by this ACL

#66cc66;"><acl description#66cc66;">=#ff0000;">"File /sys/acl/my_acl.xml"
     xmlns#66cc66;">=#ff0000;">"http://xmlns.oracle.com/xdb/acl.xsd"
     xmlns:dav#66cc66;">=#ff0000;">"DAV:"
     xmlns:xsi#66cc66;">=#ff0000;">"http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation#66cc66;">=#ff0000;">"http://xmlns.oracle.com/xdb/acl.xsd
                         http://xmlns.oracle.com/xdb/acl.xsd"#66cc66;">>
  #66cc66;"><ace#66cc66;">>
    #66cc66;"><principal#66cc66;">>ANONYMOUS#66cc66;">principal#66cc66;">>
    #66cc66;"><grant#66cc66;">>true#66cc66;">grant#66cc66;">>
    #66cc66;"><privilege#66cc66;">>
      #66cc66;"><read #66cc66;">-properties#66cc66;">/>
      #66cc66;"><read #66cc66;">-contents#66cc66;">/>
      #66cc66;"><resolve #66cc66;">/>
    #66cc66;">privilege#66cc66;">>
  #66cc66;">ace#66cc66;">>
#66cc66;">acl#66cc66;">>

By default when a resource (a file or folder) is created by a process it will get the privileges defined in the bootstrap ACL (which is protected by itself). So no privileges will be granted to this ANONYMOUS account by default. And even when unlocked, this user only opens up, by default, to hierarchy enabled, XDB Repository related objects. Mind the mentioning “by default”; Its is possible to opening up and overrule default security ruling in place when you alter the content of ACL defaults (which is, could be considered, a security breach). For example you could alter the contents of the bootstrap_acl.xml file in such a way, if your have maliceious intentions from within the database, but you would need very powerful database account access to start with anyway, to make this happen.

Example of the default content of the bootstrap_acl.xml file:

SQL#66cc66;">> #993333; font-weight: bold;">SELECT xdburitype#66cc66;">(#ff0000;">'/sys/acls/bootstrap_acl.xml'#66cc66;">)#66cc66;">.getCLOB#66cc66;">(#66cc66;">) #993333; font-weight: bold;">FROM dual;
 
#66cc66;"><acl description#66cc66;">=#ff0000;">"Protected:Readable by PUBLIC and all privileges to OWNER" 
     xmlns#66cc66;">=#ff0000;">"http://xmlns.oracle.com/xdb/acl.xsd" 
     xmlns:dav#66cc66;">=#ff0000;">"DAV:" 
     xmlns:xsi#66cc66;">=#ff0000;">"http://www.w3.org/2001/XMLSchema-instance" 
     xsi:schemaLocation#66cc66;">=#ff0000;">"http://xmlns.oracle.com/xdb/acl.xsd 
          http://xmlns.oracle.com/xdb/acl.xsd"#66cc66;">>
  #66cc66;"><ace#66cc66;">>
    #66cc66;"><principal#66cc66;">>dav:owner#66cc66;">principal#66cc66;">>
    #66cc66;"><grant#66cc66;">>true#66cc66;">grant#66cc66;">>
    #66cc66;"><privilege#66cc66;">>
      #66cc66;"><all #66cc66;">/>
    #66cc66;">privilege#66cc66;">>
  #66cc66;">ace#66cc66;">>
  #66cc66;"><ace#66cc66;">>
    #66cc66;"><principal#66cc66;">>XDBADMIN#66cc66;">principal#66cc66;">>
    #66cc66;"><grant#66cc66;">>true#66cc66;">grant#66cc66;">>
    #66cc66;"><privilege#66cc66;">>
      #66cc66;"><all #66cc66;">/>
    #66cc66;">privilege#66cc66;">>
  #66cc66;">ace#66cc66;">>
  #66cc66;"><ace#66cc66;">>
    #66cc66;"><principal#66cc66;">>PUBLIC#66cc66;">principal#66cc66;">>
    #66cc66;"><grant#66cc66;">>true#66cc66;">grant#66cc66;">>
    #66cc66;"><privilege#66cc66;">>
      #66cc66;"><read #66cc66;">-properties#66cc66;">/>
      #66cc66;"><read #66cc66;">-contents#66cc66;">/>
      #66cc66;"><read #66cc66;">-acl#66cc66;">/>
      #66cc66;"><resolve #66cc66;">/>
    #66cc66;">privilege#66cc66;">>
  #66cc66;">ace#66cc66;">>
#66cc66;">acl#66cc66;">>

Be aware that, although the PUBLIC ACE (Access Control Entries) entry sounds dangerous, this only means that from within the database DIRECT access to the objects via database accounts are possible. This is not possible via HTTP (by default). An example to this effect would be that for the APEX /images directory, which is protected only for read only access of the principal ANONYMOUS, this means that PL/SQL packages (owned/executed by users from WITHIN the database) etc, will not have access to these image files.

The “service” provided via the XDB Protocol Server and its access rules are defined in the xdbconfig.xml configuration file. The services defined there (for example APEX’s entries via PL/SQL, that is, via the PL/SQL gateway) in this xdbconfig.xml file links up to the to be used “principal” (ANONYMOUS in the case of APEX) security access owner, role, trusted user or LDAP definition, for that specific service.

Normally an anonymous user is a user whose credentials have not been validated (hence unauthenticated) that is permitted access to only unprotected resources, but by default all created objects in the XDB repository will be protected by the default bootstrap ACL and in normal cases a ACL with a defined ANONYMOUS principal is not created, does not exist in the database. Even if, you would still need entries in the xdbconfig.xml file that link the (unlocked) ANONYMOUS account with a defined service that grants you access or an entry point to the database.

The underlying by Oracle implemented security mechanism is the same as for the database and also it used the advanced security feature VPD. Due to the fact that Oracle itself makes use of this, a extra license is not needed for this advanced security feature, as long as you don’t use it yourself. Oracle XMLDB in itself is a “no cost option” that comes along when you buy the licenses needed for your database software.

This is a backup copy of a XMLDB OTN Forum Thread.

XFILES APEX Community Edition (XACE)

Roel Hartman and me are currently very busy with our Kaleidoscope 2011 presentation “XFiles, the APEX Version: The Truth is in There…“. During this presentation, we will demonstrate the awesome possibilities you can create when you combine the APEX and XMLDB functionality of the APEX database environment. And as you probably also know, two “no cost options” delivered with your Oracle database. Our first presentation/demo will be on the AMIS Kaleidoscope Preview Sessions on the 14th of June, to test our “setup” and combined presentation for the real thing during Kaleidoscope, in Long Beach, USA, this year.

Background

The demo will demonstrate how to setup database build-in versioning capabilities, based on standard XMLDB functionality, that can be used by or for APEX applications, while making use of the file/folder metaphor of the XDB Repository.

XFILES APEX Community EditionClick picture to enlarge

The XDB Repository can, by default, be used to, currently via WebDAV or FTP, drag and drop files into the database. This XMLDB functionality also has default versioning, checkin/out, an repository event mechanism and security functionality / capabilities. So why work, while working with this great development environment called APEX, with version tooling like Subversion, if the XMLDB realm of the database already has these capabilities to provide this by default.

The XFILES demo application is used to demonstrate XMLDB functionality but currently still main AJAX based regarding its GUI. In 2008 an effort was made, for that years Oracle Open World conference, by Carl Backstrom (Oracle APEX) and Mark Drake (Oracle XMLDB) to combine the two environments and switch the XFILES AJAX based GUI for the APEX front-end. Due to circumstances, it didn’t had the proper follow up regarding cleaning up the code and share the ideas…see the XFILES tab on this page for more info on this.

Roel and my efforts, regarding this XFILES APEX version, which is based on current and the work done by Mark en Carl in 2008, is called by us, the XFILES APEX Community Edition (abbreviated to XACE), so we are able to implement some chance management without be mistaken by Mark Drake’s still existing AJAX based XFILES version, currently version 5. This version implements and demonstrates Native Database Web Services via XMLDB in the OTN Development Days Virtualbox environment which can be downloaded via Oracle OTN.

More info needed?

More regarding the XFILES XMLDB application and blog posts can be found via the XFILES menu on this site. Information about Roel and his interests can be found on his personal blog roelhartman.blogspot.com and information about official XFILES Oracle XMLDB application and technology can be found on and via the Oracle XMLDB main page. Last but not least, for interest in Oracle APEX go to the apex.oracle.com site.

Preview

To give you a preview of the work done, hereby some updated pictures

XACE - Home PageClick picture to enlarge
XACE - Browser MenuClick picture to enlarge

Updated XML Content & Paper section

Once per year I try to update the “XML Content” page that, in principle, should contain all my XML relevant thoughts and “how to” posts, so I can find my gained “wisdom” a bit more quickly (getting old and stuff). This year I had only 14 and a bit of such XML related posts, which was afterwards easily explained when I updated my “Paper” page where among others I have a list on “presentations” done… Oops… I have been busy…

Anyway. Enjoy the updated XML content overview reference page, it contains now 100+ posts regarding specific XML(DB) related howto’s, solutions, approaches, ideas, etc.

 

HOWTO: Partition Binary XML based on a Virtual Column

This one is long overdue. There is a partition example of binary xml on this website based on Range, Hash and List partitioning, but this is, seen from a XML view, a incorrect way to do it due to the fact that the partition column is a regular one and not a virtual column as described in the Oracle XMLDB Developers Guide for 11.2. The examples given in that ppost will partition the table on the ID column but does not partition the XML based on a value/element IN the XML document. The following will.

So here, a small example, of how it can be done based on a virtual column. Be aware that you should support these virtual columns with at least an index or XMLIndex structure for performance reasons.

#993333; font-weight: bold;">CREATE #993333; font-weight: bold;">TABLE binary_part_xml #993333; font-weight: bold;">OF XMLType
  XMLTYPE STORE #993333; font-weight: bold;">AS SECUREFILE #993333; font-weight: bold;">BINARY XML
  VIRTUAL #993333; font-weight: bold;">COLUMNS
  #66cc66;">(
    LISTING_TYPE #993333; font-weight: bold;">AS #66cc66;">(XMLCast#66cc66;">(XMLQuery#66cc66;">(#ff0000;">'/LISTING/@TYPE'
         PASSING OBJECT_VALUE RETURNING CONTENT#66cc66;">) #993333; font-weight: bold;">AS VARCHAR2#66cc66;">(#cc66cc;">100#66cc66;">)#66cc66;">)#66cc66;">)
  #66cc66;">)
  PARTITION #993333; font-weight: bold;">BY LIST #66cc66;">(LISTING_TYPE#66cc66;">)
  #66cc66;">(
    PARTITION health #993333; font-weight: bold;">VALUES #66cc66;">(#ff0000;">'Health'#66cc66;">)#66cc66;">,
    PARTITION law_firms #993333; font-weight: bold;">VALUES #66cc66;">(#ff0000;">'Law Firm'#66cc66;">)
  #66cc66;">);

M.

Oracle XBRL Extension Ready for Download

The new ready to download Oracle XBRL Extension is a “no cost option” on top of the latest Oracle Database 11.2.0.2.0 release. With this added functionality you will get a database environment that contains

  • One or more back-end XBRL repositories based on Oracle Database, which provide XBRL storage and query-ability with a set of XBRL-specific services
  • An external XBRL processing engine (XPE)

The XBRL Extension to Oracle XML DB integrates easily with Oracle Business Intelligence Suite Enterprise Edition (OBIEE) for analytics and with interactive development environments (IDEs) and design tools for creating and editing XBRL taxonomies.

Architecture of XBRL Extension to Oracle XML DB

Oracle XBRL Extension

There is a generic “patch” (patch 10411201), p10411201_112020_generic.zip, which can be downloaded from http://support.oracle.com and installed to be used on top of the Oracle 11.2.0.2.0 XML DB. In addition, there are also two platform-specific patches (currently only available for Linux-x86-64 and Solaris-64 platforms):

For the Linux-x86 64 bit platform:

  • p10074437_112020_Linux-x86-64.zip
  • p10232225_112020_Linux-x86-64.zip

For the Solaris 64 bit platform:

  • p10074437_112020_SOLARIS64.zip
  • p10232225_112020_SOLARIS64.zip

Besides a installation readme, also a HTML/PDF document is available in these patches that have extensive info on how to use this XML DB XBRL extension and a demo based on the US GAAP XBRL taxonomy. On the Oracle XBRL Extension main page, you also can see the power of this architecture combined with web enabled application, the standard tools like Microsoft Excel, Word and analyses done on such an taxonomy database architecture via Oracle BI Server.

For more information see the Oracle XBRL Extension main page or the Oracle XML DB discussion forum.

M.

XMLDB Whitepapers and Tooling about Design, Performance and Selectivity

From time to time the main Oracle XML DB page gets updated with new whitepapers, tooling or Oracle By Example/ Hands-on Lab examples. “Lately” some cool and interesting new whitepapers and updated tooling content were created on this main Oracle XML DB page. The following items and content are really worth reading. Small issue, though, is that you need a bit more than basic understanding to put all this “lessons learned from the last one, two years” into context, but its worth it and otherwise a small reprise on the Oracle XML DB Developers Guide is always useful. A bit like re-reading the Oracle Concepts Manual.

The “Ease of Use Tools” (xdbutilities.zip tool set) for handling XMLType Object Relational storage has been updated and is now applicable on Oracle 10.x and 11.x. No specific to be installed versioned tool set needed anymore. This prepacked tool set on PL/SQL packages is installable on both versions. The zip file also contains a whitepaper that describes some of the (performance) lessons learned while using XMLType Object Relational storage.

If You Are Going to MOW…

(just on a side-note)

…and would really like to attend Anjo Kolk and Tommy Pedersen’s presentation: “Accessing the Oracle Database from Google (Apps, App Engine, Spreadsheets)” then do yourself a favor and also attend my presentation on XMLDB based out-of-the-box interfacing (“Boost your environment with Oracle XMLDB“) so you have an idea how it all hooks in…

See the MOW agenda for Friday

;-)

Hotsos 2010 – About swag, the Oscars and other stuff

Its Sunday and its raining outside. The nice weather on Saturday (approx. sunny / 20 degrees Celsius) has gone. After a decent flight on Friday where I actually made it to switch in Houston from the international Continental flight, going through customs and pick the next one, a domestic Continental Express flight, within the boundaries of 1 and 1/2 hour. I was so fast that switching for one flight to the other, that apparently my luggage didn’t manage to travel with the last flight. So after I found out that my luggage was still somewhere in Houston, I got from Dallas Love Field to the Omni Hotel in Las Colinas, Irving, where the Hotsos conference will be held again. The whole area is a bit in shambles because they are rebuilding a lot of the environment. While getting to the Grapevine Mills mall yesterday, I noticed that they also a building a new Irving Convention Center along the highway. Another addition to the already crowded Dallas/Plano/Irving Metroplex.

Part of the Puzzle: Oracle XMLDB NFS Functionality

This story is long overdue and no its NOT about the Oracle Database 11g Database File System (DBFS). Its about an “undocumented” NFS functionality that, maybe someday, will be serviced by the XMLDB XDB Protocol Adapter. This post is “long overdue” because the actual attempts to try to figure it out were done during the bank holidays between X-mas and new year 2009.

So what is it all about. I once discovered in the Oracle 11gR1 documentation a small entry in the xmlconfig.xsd XML Schema regarding NFS elements that look like that they are or will be used for enabling NFS functionality based on the Oracle XMLDB Protocol Server architecture. In those days, when Oracle 11gR1 was just of the shelve, I made a few attempts, based on the xdbconfig.xsd XML Schema to adjust the corresponding xdbconfig.xml file that controls the XDB Protocol Server functionality, to see what would happen. At that time I only was able to get this far (see the picture) and I promised myself that I should look deeper into it trying to figure out if I could get it working and/or what the concepts were that made it tick in the XMLDB architecture but somewhere down the line I just didn’t come to it and it got “forgotten” by me due to my daily DBA workload.

NFS Protocol Server functionality enabled manually

Click picture to enlarge