19. Februar 2015

Installation von "node-oracledb": Node.js und die Oracle-Datenbank

External calls as a different Unix user: DBMS_SCHEDULER or Java?
Letztes Jahr im Herbst hatte ich bereits ein Blog Posting zum Thema Node.js und die Oracle-Datenbank veröffentlicht. Als Datenbanktreiber hatte ich den Open-Source Treiber von Joe Ferner hergenommen, da es von Oracle noch keinen gab. Das hat sich nun geändert - der "offizielle" node.js-Treiber von Oracle ist Seit Januar auf GitHub verfügbar. Stand heute steht die Early Adopter Version 0.3.1 zum Download bereit.
In diesem Blog Posting beschreibe ich die Installation und ein erstes node.js-Programm - durchaus auch für solche Entwickler, die bislang noch nicht mit node.js garbeitet haben. Während der Installation von node-oracledb wird etwas kompiliert; die dazu nötigen Compiler sind auf einem Linux-System out-of-the-box enthalten; auf Windows muss eine C/C++ Compilerumgebung (etwa Microsoft Visual Studio 2013) installiert sein. Ich habe ein Linux-System verwendet; da dort alles bereits vorhanden ist.

Zutaten

Ihr braucht folgende Software-Downloads
  • Auf eurem Rechner muss eine Python-Version zwischen 2.5 und 3.0 installiert sein. Python wird benötigt, um den Oracle-Treiber node-oracledb zu kompilieren und zu installieren. Zur Installation von Python ist reichlich Material verfügbar; im folgenden wird also davon ausgegangen, dass Python installiert ist.
  • Node.js selbst - Download von www.nodejs.org. Am einfachsten ist es, Ihr ladet euch das tar.gz File herunter; damit gelingt die Installation überall und auch ohne "Root"-Privilegien. Nehmt im Augenblick nicht die jüngste Version 0.12.0 (mit der läuft node-oracledb noch nicht), sondern 0.10.36.
  • node-oracledb - den bekommt Ihr entweder vom OTN Node.js Developer Center oder von Github.
  • node-oracledb setzt einen Oracle-Client voraus. Am einfachsten ist die Installation des Instant Client. Den bekommt Ihr aus dem OTN: Oracle Database Instant Client. Wichtig: Ihr müsst hier zwei Pakete herunterladen: Instantclient-Basic und Instantclient-SDK.

Los geht's: Herunterladen und auspacken

Nach dem Download sollten diese Dateien in eurem Verzeichnis sein,
$ ls -lah
total 67M
-rw-r--r--  1 oracle oinstall  61M Feb 19 12:54 instantclient-basic-linux.x64-12.1.0.2.0.zip
-rw-r--r--  1 oracle oinstall 652K Feb 19 12:54 instantclient-sdk-linux.x64-12.1.0.2.0.zip
-rw-r--r--  1 oracle oinstall 103K Feb 19 12:54 node-oracledb-master.zip
-rw-r--r--  1 oracle oinstall 5.5M Feb 19 12:54 node-v0.10.36-linux-x64.tar.gz
[oracle@sccloud037 node-download]$
Nun alles auspacken.
$ tar -xzf node-v0.10.36-linux-x64.tar.gz
$ unzip -q node-oracledb-master.zip
$ unzip -q instantclient-basic-linux.x64-12.1.0.2.0.zip
$ unzip -q instantclient-sdk-linux.x64-12.1.0.2.0.zip
$ ls -lahd */

drwxr-xr-x  3 oracle oinstall 4.0K Feb 19 12:56 instantclient_12_1
drwxr-xr-x  6 oracle oinstall 4.0K Feb 16 18:32 node-oracledb-master
drwxr-xr-x  6 oracle oinstall 4.0K Jan 26 20:33 node-v0.10.36-linux-x64

Initiale Einrichtung

Zunächst ist es hilfreich, sich einige Umgebnungsvariablen zu setzen, damit die node.js Executables node und npm immer verfügbar sind. Am besten baut Ihr euch ein kleines Skript dazu. Für node.js selbst muss PATH gesetzt werden, für unsere Arbeit mit der Oracle-Datenbank braucht es auch LD_LIBRARY_PATH (achtet darauf, die Pfade an eure Umgebung anzupassen).
#!/bin/sh
export PATH=/path/to/node-v0.10.36-linux-x64/bin:$PATH
export LD_LIBRARY_PATH=/path/to/instantclient_12_1:$LD_LIBRARY_PATH

echo "**** node.js environment set ***"

node -v
Dieses Skript dann einfach wie folgt aufrufen.
$ . sh node-env.sh
**** node.js environment set ***
v0.10.36
Als nächstes braucht es eine Arbeitsumgebung. Das ist ein ganz normales Verzeichnis; nennen wir es work. Darin werden die node.js-Skripte (.js-Dateien) liegen. Unterhalb von work legt Ihr dann noch ein Verzeichnis namens node_modules an. In dieses werden dann später die zusätzlichen node.js Pakete gelegt (der Oracle-Datenbanktreiber ebenfalls).

Oracle-Treiber "node-oracledb" aufsetzen

Nun muss der Oracle-Treiber node-oracledb installiert werden - die folgenden Schritte sind einmalig; zum späteren Ausführen der node.js-Programme sind sie nichr mehr nötig.
  • Setzt die Umgebungsvariablen OCI_LIB_DIR und OCI_INC_DIR
    $ export OCI_LIB_DIR=/path/to/instantclient_12_1
    $ export OCI_INC_DIR=/path/to/instantclient_12_1/sdk/include
    
  • Im Verzeichnis des Oracle Instant Client wird eine Datei unter einem anderen Namen erwartet. Kopiert also die Datei libclntsh.so.12.1 nach libclntsh.so (oder legt einen symbolischen Link an).
    cd /path/to/instantclient_12_1
    ln -s libclntsh.so.12.1 libclntsh.so
    
  • Wechselt nun ins Verzeichnis, in das Ihr den Treiber node-oracledb ausgepackt habt und kompiliert diesen mit npm install.
    $ cd /path/to/node-oracledb-master
    $ npm install
    
    > oracledb@0.3.1 install /path/to/node-oracledb-master
    > node-gyp rebuild
    
    make: Entering directory `/path/to/node-oracledb-master/build'
      CXX(target) Release/obj.target/oracledb/src/njs/src/njsOracle.o
      CXX(target) Release/obj.target/oracledb/src/njs/src/njsPool.o
      CXX(target) Release/obj.target/oracledb/src/njs/src/njsConnection.o
    
      :
    
      COPY Release/oracledb.node
    make: Leaving directory `/path/to/node-oracledb-master/build'
    $
    
  • Nun benennt Ihr das Verzeichnis node-oracledb-master nach oracledb um und verschiebt es in den Ordner node_modules eures bereits angelegten Arbeitsverzeichnisses.
Damit sind wir fertig. Die Verzeichnisstruktur sieht damit wie folgt aus.
Die Datei emp.js ist das erste node.js Programm. Es soll eine Zeile aus der Tabelle EMP lesen und auf der Konsole ausgeben. Der Code ist der folgende.
var oracledb = require('oracledb');

function showEmp(conn) {
    conn.action = "Action EMP";
    conn.module = "Node.js Module EMP";
    console.log("... and here is THE KING");
    conn.execute(
      "SELECT * from EMP where EMPNO = 7839",
      [],
      function(err, result)
      {
        if (err) {
          console.log('%s', err.message);
          return;
        }
        console.log(result.rows);
      });
  }

oracledb.getConnection(
  {
    user          : "scott",
    password      : "tiger",
    connectString : "datenbank-server:1521/service-name"
  },
  function(err, connection)
  {
    if (err) {
      console.error(err.message);
      return;
    }
    showEmp(connection);
  }
);

console.log("Finished. Really????");
Den Connection-String, Usernamen und Passwort in den Zeilen 22 bis 24 müsst Ihr natürlich an eure Umgebung anpassen. Wenn Ihr das Skript dann startet, seht ihr folgende Ausgabe.
$ node emp.js
Finished. Really????
... and here is THE KING
[ [ 7839,
    'KING',
    'PRESIDENT',
    null,
    Tue Nov 17 1981 00:00:00 GMT+0100 (CET),
    5000,
    null,
    10 ] ]
Dass Ihr die Ausgabe Finished ... zuerst sieht, liegt an der asynchronen Natur von node.js - das ist unter anderem hier und an vielen anderen Stellen im Internet beschrieben. Darauf aufbauend, lassen sich nun viele Dinge mit node.js und der Oracle-Datenbank realisieren. Einige Beispiele wurden im Oracle-Webinar am 10. Februar vorgestellt und stehen auf apex.oracle.com/folien zum Download bereit.
Viel Spaß beim Ausprobieren.
In September 2014, I had a blog posting about Node.js and the Oracle database. In that posting, I used the Open Source Oracle Driver authored by Joe Ferner, since Oracle did not provide one. In the meantime, this has changed: There is an "official" node.js driver available from Oracle - it's called node-oracledb and is currently in the Early Adopter Stage. As of today (Feb 19th, 2015), the most recent version is 0.3.1.
In this blog posting I'll describe how to get started with node-oracledb. You need to perform a few steps for installation but it isn't that difficult. During installation, something will be compiled, so a compiler suite must be available on your machine. On a linux box, this is present out-of-the-box - on windows, you need to install a C/C++ build environment (such as Microsoft Visual Studio 2013) beforehand. To create this blog posting, I used a linux box.

Ingredients

You need the following software packages.
  • As a prerequisite, you need a Python installation on your machine. Its version must be at least 2.5, but lower than 3.0. Python is needed for the compilation process of node-oracledb. Since there are plenty of tutorials on installing python on a machine, I'll skip this and assume that python is present.
  • You need Node.js itself - download it from www.nodejs.org. The most easy variant is to download the tar.gz file; then you can install everywhere, and without root privileges. At the moment, don't take the latest version 0.12.0 (node-oracledb does not run on it). Choose 0.10.36 instead.
  • node-oracledb - download it either from OTN Node.js Developer Center or from Github.
  • You'll need Oracle Client libraries on your machine. The most easy approach is to use Instant Client. It is available from OTN: Oracle Database Instant Client. Important: You need to download two packages: Instantclient-Basic and Instantclient-SDK.

Let's get started: Download and extract

After downloading, you should have the following files present.
$ ls -lah
total 67M
-rw-r--r--  1 oracle oinstall  61M Feb 19 12:54 instantclient-basic-linux.x64-12.1.0.2.0.zip
-rw-r--r--  1 oracle oinstall 652K Feb 19 12:54 instantclient-sdk-linux.x64-12.1.0.2.0.zip
-rw-r--r--  1 oracle oinstall 103K Feb 19 12:54 node-oracledb-master.zip
-rw-r--r--  1 oracle oinstall 5.5M Feb 19 12:54 node-v0.10.36-linux-x64.tar.gz
[oracle@sccloud037 node-download]$
Now extract everything.
$ tar -xzf node-v0.10.36-linux-x64.tar.gz
$ unzip -q node-oracledb-master.zip
$ unzip -q instantclient-basic-linux.x64-12.1.0.2.0.zip
$ unzip -q instantclient-sdk-linux.x64-12.1.0.2.0.zip
$ ls -lahd */

drwxr-xr-x  3 oracle oinstall 4.0K Feb 19 12:56 instantclient_12_1
drwxr-xr-x  6 oracle oinstall 4.0K Feb 16 18:32 node-oracledb-master
drwxr-xr-x  6 oracle oinstall 4.0K Jan 26 20:33 node-v0.10.36-linux-x64

Configure your node.js environment

It's useful to set some environment variables: The path to the node.js executables node and npm should be part of your PATH environment variable. Since we will work with the Oracle database, we'll also need to have the Oracle Client libraries within LD_LIBRARY_PATH. The following shell script does the job for you (note to adjust the paths to your environment).
#!/bin/sh
export PATH=/path/to/node-v0.10.36-linux-x64/bin:$PATH
export LD_LIBRARY_PATH=/path/to/instantclient_12_1:$LD_LIBRARY_PATH

echo "**** node.js environment set ***"

node -v
Then call the script as follows.
$ . sh node-env.sh
**** node.js environment set ***
v0.10.36
Next, you'll need a working directory; this is a simple folder called work. In this folder we'll place our node.js scripts. Also within work, you need a subdirectory node_modules. This will hold all additional node.js packages - node-oracledb will be placed there as well. The following image illustrates the folder structure.

Install "node-oracledb"

Now we'll install the node-oracledb driver in our node.js environment. These installation steps are only to be executed once.
  • Create two environment variables: OCI_LIB_DIR and OCI_INC_DIR
    $ export OCI_LIB_DIR=/path/to/instantclient_12_1
    $ export OCI_INC_DIR=/path/to/instantclient_12_1/sdk/include
    
  • The Oracle Instant Client folder contains a file named libclntsh.so.12.1. Node.js expects it under the name libclntsh.so. So create either a copy or a symbolic link.
    cd /path/to/instantclient_12_1
    ln -s libclntsh.so.12.1 libclntsh.so
    
  • Now change to the folder which has been created by extracting the downloaded file node-oracledb-master.zip. Compile the driver using npm install.
    $ cd /path/to/node-oracledb-master
    $ npm install
    
    > oracledb@0.3.1 install /path/to/node-oracledb-master
    > node-gyp rebuild
    
    make: Entering directory `/path/to/node-oracledb-master/build'
      CXX(target) Release/obj.target/oracledb/src/njs/src/njsOracle.o
      CXX(target) Release/obj.target/oracledb/src/njs/src/njsPool.o
      CXX(target) Release/obj.target/oracledb/src/njs/src/njsConnection.o
    
      :
    
      COPY Release/oracledb.node
    make: Leaving directory `/path/to/node-oracledb-master/build'
    $
    
  • After that, rename this folder from node-oracledb-master to oracledb and then move it into the node_modules folder in your already created directory work.
Then you are finished. Your folder structure should look like this:
You now can create an run node.js scripts working with the Oracle Database. The followng file emp.js is our first node.js Program - it is supposed to select one row from the EMP table and to print it to the console.
var oracledb = require('oracledb');

function showEmp(conn) {
    conn.action = "Action EMP";
    conn.module = "Node.js Module EMP";
    console.log("... and here is THE KING");
    conn.execute(
      "SELECT * from EMP where EMPNO = 7839",
      [],
      function(err, result)
      {
        if (err) {
          console.log('%s', err.message);
          return;
        }
        console.log(result.rows);
      });
  }

oracledb.getConnection(
  {
    user          : "scott",
    password      : "tiger",
    connectString : "datenbank-server:1521/service-name"
  },
  function(err, connection)
  {
    if (err) {
      console.error(err.message);
      return;
    }
    showEmp(connection);
  }
);

console.log("Finished. Really????");
Of course, you must adjust the connection string, username and password in lines 22 to 24 to your environment. After that, run the script by typing node emp.js and see its results.
$ node emp.js
Finished. Really????
... and here is THE KING
[ [ 7839,
    'KING',
    'PRESIDENT',
    null,
    Tue Nov 17 1981 00:00:00 GMT+0100 (CET),
    5000,
    null,
    10 ] ]
You'll recognize that the output Finished ... comes first and the table contents afterwards. This is due to the asychronous nature of node.js. I'll not elaborate on this within that blog posting - as always, there are plenty of tutorials which describe this behaviour much better than I am able to. Based on this, you can start building applications or services with Node.js and the Oracle Database. Some examples have been introduced in a german Webinar at Feb 10th, 2015. This sample code can be downloaded from apex.oracle.com/folien.
Have fun!

3. Februar 2015

"node-oracledb"-Treiber für Node.js: Webseminar am 10. Februar

This blog posting is about the upcoming webinar on the Oracle Database and "node.js" and therefore in German only. After the webinar I'll post some code snippets and tutorials in both english and german language.
Letztes Jahr im Herbst hatte ich bereits ein Blog Posting zum Thema Node.js und die Oracle-Datenbank veröffentlicht. Als Datenbanktreiber hatte ich den Open-Source Treiber von Joe Ferner hergenommen, da es von Oracle noch keinen gab.
Das hat sich nun geändert - der node.js-Treiber von Oracle ist auf GitHub als Early Adopter Version (0.2) verfügbar. Diesen stelle ich in einem Webseminar am 10. Februar um 11:00 Uhr vor. Mehr Details zur Einwahl findet Ihr auf der Webseite des Oracle Developer Monthly.
Vorgestellt werden Node.js selbst, der Treiber für die Oracle-Datenbank, dessen Installation und einige Code-Beispiele (Einfacher REST Service, IMAP-Integration mit der Datenbank, HTML5-Websocket-Beispiel).
Nach dem Webcast wird ein weiteres Blog Posting mit mehr Details zur Installation und Nutzung des node.js Treibers geben. Bis dahin (für die, die es nicht erwarten können) hier ein wenig Code: Das folgende Beispiel implementiert einen sehr einfachen REST Webservice (nur GET) für die Tabelle EMP.
var oracledb = require('oracledb');
var express = require('express');

var pool;

//
// Diese Javascript-Funktion behandelt einen HTTP-Request /emp/*
//
function processEmp(req, res) {
  pool.getConnection(function(err, connection){
    connection.execute(
      "select * from emp where (empno=:1 or empno is null)", 
      [req.params[0]],
      function(err, results) {
        connection.release(function (err) {}),
        res.writeHead(200, {'Content-Type': 'application/json'});
        res.end(JSON.stringify(results.rows));
      }
    )
  })
}

//
// Diese Javascript-Funktion startet den Server
//
function startServer () {
  var app =  express();
  app.get ("/emp/*", processEmp);

  var server = app.listen(9000, function () {
    var host = server.address().address
    var port = server.address().port
    console.log('Table EMP REST Service listening at http://%s:%s', host, port);
  });
}

//
// Programmstart. Oracle Connection Pool und bei Erfolg den Webserver starten
//
oracledb.createPool(
  {
    user          : "scott",
    password      : "*****",
    connectString : "dbserver.mycompany.com:1521/orcl",
    poolMin       : 10,
    poolMax       : 20
  },

  function(err, ppool){
    pool = ppool;
    startServer();
  }
);
Ein solcher "Mini-Dienst" lässt sich sehr elegant mit der Visualisierungstechnologie D3js verbinden; ein sehr einfaches Beispiel sieht dann so aus:

15. Januar 2015

INHERIT PRIVILEGES in Oracle12c - Was ist das?

INHERIT PRIVILEGES in Oracle12c - What's that?
In diesem Blog-Posting widme ich mich dem (zwar etwas älteren) Thema Privilegien, welches in Oracle12c aber einige Neuerungen erfahren hat:
  • Neues Systemprivileg INHERIT PRIVILEGES
  • Rollen können an PL/SQL-Objekte vergeben werden
  • BEQUEATH CURRENT_USER-Klausel für Views
  • Code Based Access Control für PL/SQL Objekte: PL/SQL Objekte können nur von anderen PL/SQL-Objekten ausgeführt werden
Heute geht es um das neue Systemprivileg INHERIT PRIVILEGES; welches einen ganz speziellen Anwendungsfall hat und bei welchem man auch ein wenig "um die Ecke" denken muss, um die Absicht dahinter zu verstehen.
INHERIT PRIVILEGES ist wichtig bei PL/SQL-Prozeduren, die mit den Rechten des aufrufenden Nutzers ausgeführt werden (AUTHID CURRENT_USER). Bekanntlich läuft PL/SQL-Code defaultmäßig mit den Rechten des Eigentümers ab - egal wer sie aufruft. Eine Definers Rights (DR) Prozedur, die dem SYS gehört, läuft also stets mit den Rechten von SYS, auch wenn SCOTT sie aufruft (EXECUTE-Privileg natürlich vorausgesetzt). Invokers Rights (IR) Prozeduren laufen dagegen mit den Rechten des aufrufenden Nutzers ab - in diesem Beispiel mag eine Prozedur zwar dem SYS gehören; wenn SCOTT sie dagagen aufruft, läuft sie auch nur mit dessen Rechten ab. Dazu gibt es natürlich auch ein Kapitel in der Dokumentation. So weit - so gut.
Bei einer solchen IR-Prozedur kann man sich nun folgenden Fall denken: Angenommen, ein "normaler" User in der Datenbank baut ein PL/SQL-Paket, das zur späteren Nutzung für den DBA vorgesehen ist - es sähe wie folgt aus.
create or replace package app_dba_pkg authid current_user is
  procedure generate_app_stat;
  procedure purge_temp_tables;
end app_dba_pkg;

create or replace package body app_dba_pkg is
  procedure generate_app_stat is 
  begin
    app_stat_page.generate_stats;
    -- Hier wird es böse
    begin
      execute immediate 'grant dba to SCOTT';
    exception when others then null;
    end;
    -- *************************
    dbms_output.put_line('Application statistics generated.');
  end generate_app_stat;

  procedure purge_temp_tables is 
  begin
    ...
  end purge_temp_tables;
end app_dba_pkg;
Das Package wird mit AUTHID CURRENT_USER angelegt; wenn ein DBA es startet, läuft es also in dessen Rechtekontext ab. Der Programmierer hat, wie man erkennen kann, noch etwas mehr Code eingebaut als er eigentlich sollte. Die GRANT DBA TO SCOTT Anweisung, die er selbstständig nicht ausführen könnte, läuft durch, wenn das Paket durch einen entsprechend privilegierten User (ein DBA) aufgerufen wird. Das ist bis einschließlich 11g so - ein hochprivilegierter User sollte die Prozeduren, die er aufruft, also gut kennen.
Oracle12c setzt an diesem Punkt an: Denn genau diese Vorgang - die Übernahme des Rechtekontext eines anderen Users, ist nun durch ein Systemprivileg geschützt: INHERIT PRIVILEGES. Wenn der User SCOTT das Privileg INHERIT PRIVILEGES ON SYS hat, dann darf SCOTTs Code mit den Rechten von SYS ablaufen; SYS kann die Prozedur dann laufen lassen. Fehlt das Privileg, darf der Code also nicht den Rechtekontext des Aufrufers übernehmen, so gibt es eine Fehlermeldung.
Genau hier ist übrigens der Punkt, an dem man "um die Ecke" denken muss - damit SYS eine Operation durchführen kann, muss der User SCOTT ein Privileg gegrantet bekommen ...
SQL> exec scott.app_dba_pkg.generate_app_stat;
BEGIN scott.app_dba_pkg.generate_app_stat; END;

*
FEHLER in Zeile 1:
ORA-06598: Nicht ausreichende INHERIT PRIVILEGES-Berechtigung
ORA-06512: in "SCOTT.APP_DBA_PKG", Zeile 2
ORA-06512: in Zeile 1
Standardmäßig wird in Oracle12c beim Erstellen eines neuen Users das Privileg INHERIT PRIVILIGE ON {neuer User} an PUBLIC vergeben - das gilt auch für alle eingebauten User - aber mit Ausnahme von SYS. Das bedeutet de-facto, dass IR-Prozeduren prinzipiell laufen wie in 11g - allein für den Aufruf durch SYS ergibt sich ein Unterschied: Hier muss INHERIT PRIVILEGES vorher explizit vergeben werden.
SQL> grant inherit privileges on user sys to scott;

Benutzerzugriff (Grant) wurde erteilt.
Der Aufbau dieses GRANT-Kommandos ist wichtig. Ich habe farblich markiert, wessen Rechtekontext der Code von welchem User übernehmen darf. Anschließend funktioniert das Aufrufen der Prozedur.
SQL> exec scott.app_dba_pkg.generate_app_stat;
Application statistics generated.

PL/SQL-Prozedur erfolgreich abgeschlossen.
Angenommen, es gibt einen weiteren User, dessen Rechtekontext mit diesem Konzept geschützt werden soll - nennen wir ihn YADBAUSER - da dies kein Oracle-User ist, wird beim Erzeugen mit CREATE USER automatisch INHERIT PRIVILEGES ON YADBAUSER an PUBLIC vergeben. Das kann man nicht verhindern, man kann es aber rückgängig machen.
SQL> revoke inherit privileges on user yadbauser from public;

Benutzerzugriff wurde aufgehoben (Revoke).
Ein besonders restriktiver Ansatz wäre ein REVOKE INHERIT PRIVILEGES ON {alle User} FROM PUBLIC; dann könnte niemand den Rechtekontext eines anderen Users übernehmen - de-facto also keinen PL/SQL Code eines anderen Nutzers mit Invokers Rights ausführen.
begin
  for i in (select username from dba_users) loop
    execute immediate 'revoke inherit privileges on '||i.username||' from public';
  end loop;
end;
/
Anschließend kann man einzeln freigeben, welche User welche Rechtekontexte übernehmen können.
grant INHERIT PRIVILEGES ON SCOTT to HUGO;
grant INHERIT PRIVILEGES ON SYS   to YADBAUSER;
:
Bei dieser Ansatz sollte allerdings sehr sorgfältig vorgegangen werden; denn wenn man hier einen oder mehrere User vergisst, führt das dazu, dass Anwendungen nicht mehr laufen. Zunächst sollte man also feststellen, welche Nutzer überhaupt IR-Prozeduren haben; diese User sind dann Kandidaten, denen man INHERIT PRIVILEGES granten könnte.
SQL> select distinct owner from all_procedures where authid='CURRENT_USER';

OWNER
------------------------
APEX_040200
MDSYS
CTXSYS
SCOTT
EMREST
XDB
SYS
Als nächstes muss man feststellen, durch welche Nutzer die PL/SQL Objekte dieser Eigentümer aufgerufen werden; welche "Rechtekontexte also freigegeben werden müssen". Und damit kann man die GRANT INHERIT PRIVILEGES Anweisungen dann formulieren.
Erwähnenswert ist noch, dass es auch ein INHERIT ANY PRIVILEGES gibt; mit diesem Privileg kann der Code des jeweiligen Nutzers die Rechtekontexte aller anderen User übernehmen - der Code kann dann also von jedem Datenbanknutzer ausgeführt werden.
Fazit: Vom neuen Privileg INHERIT PRIVILEGES merkt man nach einem Upgrade auf 12c nur dann etwas, wenn der User SYS PL/SQL Code eines anderen Users mit Invokers Rights aufrufen will - beim Aufruf durch andere User merkt man nichts.
Das neue Privileg bringt aber speziell in Datenbanken mit sehr vielen Applikationen, in denen der DBA nicht mehr jede Anwendung kennt, einen wesentlich höheren Sicherheitsgrad. Mit INHERIT PRIVILEGES kann - in der Datenbank - ausgedrückt werden, welcher User welchem anderen User "vertraut", so dass einer des anderen Rechtekontext übernehmen kann. Und dies kann entweder grob oder auch sehr feingranular gesteuert werden. Und auch dar Abschnitt aus der Dokumentation zu INHERIT PRIVILEGES soll nicht unerwähnt bleiben.
This blog posting will be about Privileges in the database - this is a pretty old topic, but Oracle12c introduces some new things - which are very interesting:
  • New system privilege INHERIT PRIVILEGES
  • Roles can be granted to PL/SQL objects
  • BEQUEATH CURRENT_USER clause for views
  • Code Based Access Control for PL/SQL objects
Today I will write about the new system privilege INHERIT PRIVILEGES; which has a very special usecase, and for which you have to think twice, to get the idea behind it.
INHERIT PRIVILEGES is important for PL/SQL code which runs in the privilege context of the invoking user ( AUTHID CURRENT_USER). By default, PL/SQL code runs with the privileges of its owner, regardsless who actually called it. Such a procedure or function is called Definers Rights (DR). An Invokers Rights (IR) procedure will run with the privilege of the invoking user - such a procedure might be owned by SYS, but when SCOTT runs it, it will execute with SCOTT's privileges. Of couse, SCOTT needs an EXECUTE privilege on that procedure (Documentation). So far, so good - nothing new in 12c.
For an IR procedure, let's imagine the following: We have an application developer who authors a package to be used by the DBA - this might be a procedure for some administrative tasks. So, the procedure is owned by the user SCOTT (or APPUSER01, or whatever) and is intended to being used by a DBA. The code looks as follows:
create or replace package app_dba_pkg authid current_user is
  procedure generate_app_stat;
  procedure purge_temp_tables;
end app_dba_pkg;

create or replace package body app_dba_pkg is
  procedure generate_app_stat is 
  begin
    app_stat_page.generate_stats;
    -- this is EVIL
    begin
      execute immediate 'grant dba to SCOTT';
    exception when others then null;
    end;
    -- *************************
    dbms_output.put_line('Application statistics generated.');
  end generate_app_stat;

  procedure purge_temp_tables is 
  begin
    ...
  end purge_temp_tables;
end app_dba_pkg;
As you can see, that package is being created with the AUTHID CURRENT_USER clause, so we have an IR package. Looking carefully at the code, we can see, that the developer is not such a nice guy, he added a GRANT DBA TO SCOTT command. Since the DBA runs this IR procedure, the statement will execute in a DBA's privilege context and therefore without errors - so: "SCOTT" becomes DBA. DBA's should know the PL/SQL procedures they are about to execute, shouldn't they?
In Oracle12c, execution of IR code is controlled by the INHERIT PRIVILEGES privilege. INHERIT PRIVILEGES ON SYS allows IR code, owned by the grantee, to run in the privilege context of SYS. Without it, execution fails with an error message. So, running the above procedure in Oracle12c leads to the following error message.
SQL> exec scott.app_dba_pkg.generate_app_stat;
BEGIN scott.app_dba_pkg.generate_app_stat; END;

*
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "SCOTT.APP_DBA_PKG", line 2
ORA-06512: at line 1
Upon creating a new user, Oracle12c grants INHERIT PRIVILIGE ON {new user} to PUBLIC automatically - this applies to all users except SYS. That means, that IR procedures in Oracle12c will work as in 11g - with the exception of SYS - when SYS calls another users' IR procedure, he will get the above error message. Therefore we need to grant INHERIT PRIVILEGES ON SYS to the owner of the IR code (SCOTT).
And that is the tricky point: SCOTT needs to be granted a privilege, in order to enable SYS to call PL/SQL. This can be confusing - but it's important to get it right.
SQL> grant inherit privileges on user sys to scott;

Grant succeeded.
It's worth to take one more look at this statement: It mentions two database users - the first (ON USER {user}) is the one, whose privilege context can be inherited by the second (the grantee). After executing this, SYS can run the procedure without errors.
SQL> exec scott.app_dba_pkg.generate_app_stat;
Application statistics generated.

PL/SQL procedure successfully completed.
Let's assume, we have another power user, for which we also want to make sure, that no other user can inherit its privilege context. When we create it (CREATE USER), Oracle automatically grants INHERIT PRIVILEGES on that user to PUBLIC. We cannot prevent this, but we can reverse it by revoking INHERIT PRIVILEGES on that user from PUBLIC.
SQL> revoke inherit privileges on user yadbauser from public;

Revoke succeeded.
We could also revoke INHERIT PRIVILEGES on all users from public; after doing this, nobody could execute another users' IR procedures any more.
begin
  for i in (select username from dba_users) loop
    execute immediate 'revoke inherit privileges on '||i.username||' from public';
  end loop;
end;
/
Afterwards we could execute exactly the GRANT INHERIT PRIVILEGES statements which are needed to make the applications work.
grant INHERIT PRIVILEGES ON SCOTT to HUGO;
grant INHERIT PRIVILEGES ON SYS   to YADBAUSER;
:
But this approach needs to be done carefully - missing INHERIT PRIVILEGES grants would lead to broken applications. So, first, we need to determine the IR proceduces and their owners. These are the grantees for INHERIT PRIVILEGES.
SQL> select distinct owner from all_procedures where authid='CURRENT_USER';

OWNER
------------------------
APEX_040200
MDSYS
CTXSYS
SCOTT
EMREST
XDB
SYS
Next, we must find out, which other users are about to execute the IR procedures. These are the users for the ON USER clause in the GRANT INHERIT PRIVILEGES statement. Having this information, we could build the GRANT statements.
There is also an INHERIT ANY PRIVILEGES system privilege; granting this to a database user allows its code to run within the privilege context of any other database user - expressed otherwise: That users' IR procedures can be executed by any other user.
Summarized: By default, the new system privilege INHERIT PRIVILEGES has only limited effect to an existing (upgraded) Oracle instance. You will encounter the effects when you are about to execute IR procedures, owned by a "normal" database user, as SYS. Calling them as other users works as in previous releases.
The new concept is particularly useful for databases with many applications which are not known to the DBA - in such an environment, a DBA cannot trust PL/SQL code owned by a database user and therefore he cannot execute it. The new INHERIT PRIVILEGES system allows the DBA to express "trust" to a specific code owner - the database only allows calling IR code of "trusted" owners.

10. Dezember 2014

Betriebssystem-Aufrufe mit SQL und PL/SQL - mit anderem Unix-User ...?

External calls as a different Unix user: DBMS_SCHEDULER or Java?
Am 12. und 13. Februar 2015 veranstaltet die DOAG eine SIG Development Veranstaltung. Inhalt ist die Programmierung in der Datenbank - vornehmlich also alles rund um SQL und PL/SQL. Es sollen aber auch die im Datenbankumfeld immer wichtiger werdenden Themen wie XML, JSON, Spatial oder unstrukturierte Daten betrachtet werden. Die DOAG sucht derzeit Vortragende - wenn Ihr also etwas habt, was Ihr mit der Community teilen möchtet, meldet euch bei der DOAG.
Heute geht es um das Thema "Betriebssystem-Kommandos" mit der Datenbank ausführen. Hierzu gibt es zwei grundsätzliche Ansätze:
  • Man nutzt das eingebaute PL/SQL Paket DBMS_SCHEDULER. Damit lässt sich ein Job einrichten, der einen Betriebssystem-Call ausführt. DBMS_SCHEDULER ist Teil aller Datenbankeditionen; man braucht also keinerlei Installation. Nachteil ist, dass man an STDIN, STDOUT und STDERR nicht herankommt; die Konsolen-Ausgabe der Programme sieht man in der Datenbank nicht.
  • Alternativ kann das auf Java in der Datenbank basierende Paket für Dateisystem-Zugriff und Betriebssystem-Kommandos hergenommen werden. Es bietet die Dinge an, die mit den eingebauten Features nicht möglich sind, also Ausführen eines Shell-Kommandos mit Zugriff auf STDIN, STDOUT oder STDERR oder das Abrufen eines Dateisystem-Listings als Tabelle.
Bei beiden Varianten wird der eigentliche Betriebssystem-Aufruf standardmäßig als der OS-User getätigt, unter dem die Oracle-Prozesse laufen. Mit DBMS_SCHEDULER ist es schon länger möglich, diesen User zu ändern, das ist aber nicht überall bekannt. Mit der Java-Variante war es lange Zeit überhaupt nicht möglich, den Betriebsystem-User zu ändern, das geht ab der Version 11.2.0.4. In diesem Blog Posting stelle ich beide Varianten vor - zeige aber die Anwendung in Oracle12c.

Betriebssystem-User für DBMS_SCHEDULER festlegen

Für Betriebssystem-Aufrufe, die mit DBMS_SCHEDULER realisiert werden, steht ab Version 12.1 das neue PL/SQL Paket DBMS_CREDENTIAL bereit. In Version 11 ist die im folgenden verwendete Prozedur CREATE_CREDENTIAL im Paket DBMS_SCHEDULER selbst enthalten (ab 12c deprecated). Mit CREATE_CREDENTIAL kann ein Username-Password-Paar hinterlegt werden. Diese Credentials werden verschlüsselt in der Datenbank abgelegt und zur Ausführung des Betriebssystem-Aufrufs genutzt. Zum Erzeugen eines Credentials wird das Systemprivileg CREATE CREDENTIAL benötigt. Nachdem dieses Privileg eingeräumt wurde, erzeugt ein Datenbankuser (bspw. SCOTT) neue Credentials wie folgt.
begin
  dbms_credential.create_credential(
    credential_name => 'CRED_TESTUSER',
    username        => 'testuser',
    password        => 'oracle', 
    database_role   => null,
    enabled         => true
  );
end;
/
Damit ein Datenbankuser mit DBMS_SCHEDULER überhaupt externe Jobs starten kann, braucht er das Systemprivileg CREATE EXTERNAL JOB. Mit DBMS_SCHEDULER wird das neue Credential dann wie folgt genutzt
begin
  dbms_scheduler.create_job(
    job_name        => 'JOB_TOUCH_FILE',
    job_type        => 'EXTERNAL_SCRIPT',
    job_action      => '/bin/touch /tmp/myfile',
    credential_name => 'CRED_TESTUSER',
    auto_drop       => true,
    enabled         => true,
    start_date      => systimestamp 
  );
end;
/
Dieser Job läuft sofort los und führt das touch Kommando als Unix-Nutzer testuser aus. Schaut man danach ins /tmp Verzeichnis, so ist die Datei mit dem richtigen User angelegt.
$ ls -ltrh

total 28K
:
srwxr-xr-x 1 oracle   oinstall    0 Nov 10 13:42 mapping-oracle
drwx------ 2 oracle   oinstall 4.0K Nov 10 13:42 orbit-oracle
drwxr-xr-x 2 oracle   oinstall 4.0K Nov 13 16:18 hsperfdata_oracle
-rw-rw-rw- 1 testuser testuser    0 Dec  9 11:17 myfile

Betriebssystem-User für Aufrufe mit Java in der Datenbank festlegen

Das Java-Beispiel erläutere ich anhand meines oben erwähnten Java- und PL/SQL-Pakets für Dateisystem-Zugriff und Betriebssystem-Kommandos. Ein Betriebssystem-Kommando führt man mit dem Paket OS_COMMAND aus. Wie bereits erwähnt, hat man hier zusätzlich Zugriff auf die Konsole - das Beispiel mit ls zeigt das.
SQL> select os_command.exec_clob('/bin/ls -ltrh /tmp') from dual;

OS_COMMAND.EXEC_CLOB('/BIN/LS-LTRH/TMP')
--------------------------------------------------------------------------------
total 32K
drwx------ 2 oracle   oinstall 4.0K Jul 22 15:33 keyring-bGJZmr
drwx------ 2 oracle   oinstall 4.0K Aug 18 14:56 keyring-zSeMy3
drwx------ 2 oracle   oinstall 4.0K Nov 10 13:42 ssh-wUZJXQ1701
drwx------ 2 oracle   oinstall 4.0K Nov 10 13:42 keyring-7dHMKl
srwxr-xr-x 1 oracle   oinstall    0 Nov 10 13:42 mapping-oracle
drwx------ 2 oracle   oinstall 4.0K Nov 10 13:42 orbit-oracle
drwxr-xr-x 2 oracle   oinstall 4.0K Nov 13 16:18 hsperfdata_oracle
-rw-rw-rw- 1 testuser testuser    0 Dec  9 11:17 myfile
drwx------ 2 testuser testuser 4.0K Dec  9 11:23 tmpdir
In das Verzeichnis tmpdir kommt man so einfach nicht rein - denn es gehört dem Unix-Nutzer testuser und nur er selbst kann es lesen, schreiben oder hineinwechseln.
SQL> select os_command.exec_clob('/bin/ls -ltrh /tmp/tmpdir') from dual;

OS_COMMAND.EXEC_CLOB('/BIN/LS-LTRH/TMP/TMPDIR')
----------------------------------------------------------------------------
/bin/ls: /tmp/tmpdir: Permission denied
Für Java in der Datenbank steht die Prozedur SET_RUNTIME_EXEC_CREDENTIALS im Package DBMS_JAVA bereit. Auch hier wird ein Username/Password-Paar hinterlegt. Allerdings werden diese nicht, wie bei DBMS_CREDENTIAL, unter einem Namen abgelegt, sondern mit dem Datenbankuser verknüpft - und diese Verknüpfung wird dauerhaft gespeichert. Folgerichtig kann auch nur SYS diese Mappings einrichten. Der folgende Aufruf bewirkt also, dass alle mit Java ausgeführten Betriebssystem-Kommandos des Datenbankusers SCOTT als Unix-User testuser ausgeführt werden. Zuzätzlich braucht der User SCOTT natürlich noch Java-Privilegien, um überhaupt ein Betriebssystem-Kommando ausführen zu können. Beides muss als SYS durchgeführt werden.

begin
  dbms_java.set_runtime_exec_credentials(
    dbuser => 'SCOTT',
    osuser => 'testuser',
    ospass => 'oracle'
  );
end;
/

begin
  dbms_java.grant_permission(
    grantee           => 'SCOTT',
    permission_type   => 'SYS:java.io.FilePermission', 
    permission_name   => '/bin/ls',
    permission_action => 'execute'
  );
end;
/
Nun kann der User SCOTT mit Java in der Datenbank das Betriebssystem-Kommando /bin/ls ausführen. Dieser Aufruf findet dann als Betriebssystem-User testuser statt - ein Listing des auf Unix-Ebene geschützten Verzeichnisses klappt nun also.
SQL> select os_command.exec_clob('/bin/ls -ltrh /tmp/tmpdir') from dual

OS_COMMAND.EXEC_CLOB('/BIN/LS-LTRH/TMP/TMPDIR')
----------------------------------------------------------------------------
total 0
-rw-rw-r-- 1 testuser testuser 0 Dec  9 11:24 f3
-rw-rw-r-- 1 testuser testuser 0 Dec  9 11:24 f2
-rw-rw-r-- 1 testuser testuser 0 Dec  9 11:24 f1

Wie bereits erwähnt, ist das Mapping zwischen dem Datenbankuser SCOTT und dem Betriebsystem-User testuser nun persistent gespeichert. Also auch nach dem Aus- und erneuten Einloggen des Users SCOTT finden dessen Betriebssystem-Aufrufe als Unix-User testuser statt. Das ist ein wesentlicher Unterschied zwischen Java in der Datenbank und DBMS_SCHEDULER - letzterer verwendet, wie oben beschrieben, benamte Credentials, die von einem Datenbankuser unabhängig sind.
Dabei möchte ich es für dieses Mal belassen; ich hoffe, das dieses Posting etwas Licht ins Thema "Betriebssystem-Aufrufe mit der Datenbank und was ist der dabei verwendete Unix-User" bringen konnte.
This blog posting will be about "executing operating system commands" from within the Oracle database. Generally, there are two approaches for this.
  • You can use the built-in package DBMS_SCHEDULER to create a job which launches an operating system executable or a shell script. DBMS_SCHEDULER is built-in, available in all database editions (including XE), so there are no special prerequisites. A disadvantage is, that you cannot access STDIN, STDOUT or STDERR - so there is no "handle" for you to get the console output of your operating system command.
  • As an alternative, you can use the package for filesystem access and operating system commands, which is based on the Java VM inside the database. It allows to do those things, the built-in packages cannot do - like accessing console input and output or retrieving folder contents as a virtual table.
In both variants, the actual operating system call is being executed as the user, which also runs the Oracle processes. DBMS_SCHEDULER allows changing this for a longer time, but this is widely unknown. With the Java-based approach, it was impossible to change the operating system user in the past - but the latest database versions 11.2.0.4 and 12.1 allow this as a new feature. In this blog posting I'll show, how to execute an operating system command as a different user - based on the most current 12.1 release of the Oracle database.

Change the operating system user for the DBMS_SCHEDULER approach

Beginning with version 12.1, the Oracle database contains the new package DBMS_CREDENTIAL. Before Oracle 12.1, the DBMS_CREDENTIAL functions and procedures were part of DBMS_SCHEDULER itself (deprecated with 12.1). With the CREATE_CREDENTIAL procedure, a new credential (a username/password pair) is being created in the database. A database user needs the CREATE CREDENTIAL system privilege for this. The following PL/SQL call creates a credential named CRED_TESTUSER containing username and password for the Unix-User testuser.
begin
  dbms_credential.create_credential(
    credential_name => 'CRED_TESTUSER',
    username        => 'testuser',
    password        => 'oracle', 
    database_role   => null,
    enabled         => true
  );
end;
/
This credential can then be utilized with DBMS_SCHEDULER. But in order to create a job executing an operating system call, the system privilege CREATE EXTERNAL JOB is needed. The simple CREATE JOB is not sufficient. The following PL/SQL code creates the scheduler job JOB_TOUCH_FILE which executes some shell code - as the user testuser.
begin
  dbms_scheduler.create_job(
    job_name        => 'JOB_TOUCH_FILE',
    job_type        => 'EXTERNAL_SCRIPT',
    job_action      => '/bin/touch /tmp/myfile',
    credential_name => 'CRED_TESTUSER',
    auto_drop       => true,
    enabled         => true,
    start_date      => systimestamp 
  );
end;
/
This job starts immediately and executes the touch command. If you look into the /tmp directory on your database server, there should be one new file myfile - owned by testuser. It worked.
$ ls -ltrh

total 28K
:
srwxr-xr-x 1 oracle   oinstall    0 Nov 10 13:42 mapping-oracle
drwx------ 2 oracle   oinstall 4.0K Nov 10 13:42 orbit-oracle
drwxr-xr-x 2 oracle   oinstall 4.0K Nov 13 16:18 hsperfdata_oracle
-rw-rw-rw- 1 testuser testuser    0 Dec  9 11:17 myfile

Change the operating system user for calls done with the database JVM

I'll show the java example based on the above mentioned package for filesystem access and operating system commands. Within that package, OS_COMMAND allows to launch an executable. As already mentioned, this package allows to access STDOUT, STDIN and STDERR - the following example with the ls command illustrates this.
SQL> select os_command.exec_clob('/bin/ls -ltrh /tmp') from dual;

OS_COMMAND.EXEC_CLOB('/BIN/LS-LTRH/TMP')
--------------------------------------------------------------------------------
total 32K
drwx------ 2 oracle   oinstall 4.0K Jul 22 15:33 keyring-bGJZmr
drwx------ 2 oracle   oinstall 4.0K Aug 18 14:56 keyring-zSeMy3
drwx------ 2 oracle   oinstall 4.0K Nov 10 13:42 ssh-wUZJXQ1701
drwx------ 2 oracle   oinstall 4.0K Nov 10 13:42 keyring-7dHMKl
srwxr-xr-x 1 oracle   oinstall    0 Nov 10 13:42 mapping-oracle
drwx------ 2 oracle   oinstall 4.0K Nov 10 13:42 orbit-oracle
drwxr-xr-x 2 oracle   oinstall 4.0K Nov 13 16:18 hsperfdata_oracle
-rw-rw-rw- 1 testuser testuser    0 Dec  9 11:17 myfile
drwx------ 2 testuser testuser 4.0K Dec  9 11:23 tmpdir
As you can see, there is the directory tmpdir - which is owned by testuser and which other Unix users cannot access. By default, an operating system command executed via OS_COMMAND, also cannot access it (it's being run as the owner of the Oracle database processes).
SQL> select os_command.exec_clob('/bin/ls -ltrh /tmp/tmpdir') from dual;

OS_COMMAND.EXEC_CLOB('/BIN/LS-LTRH/TMP/TMPDIR')
----------------------------------------------------------------------------
/bin/ls: /tmp/tmpdir: Permission denied
To change the user, as which the external calls are being made, there is the SET_RUNTIME_EXEC_CREDENTIALS procedure within the DBMS_JAVA package. It also allows to store a username/password pair. But, as opposed to DBMS_CREDENTIAL, this will be linked to the database user and not be stored under its own name. Therefore, only a DBA (SYS) can use that procedure. The following PL/SQL code does two things: First, it maps the database user SCOTT to the Unix user testuser - so all external calls, done by SCOTT with Java in the database, are being executed as testuser. Second, SCOTT needs the privilege to execute an external call at all - the call to DBMS_JAVA.GRANT_PERMISSION does this. Both calls have to be executed as DBA (as SYS).

begin
  dbms_java.set_runtime_exec_credentials(
    dbuser => 'SCOTT',
    osuser => 'testuser',
    ospass => 'oracle'
  );
end;
/

begin
  dbms_java.grant_permission(
    grantee           => 'SCOTT',
    permission_type   => 'SYS:java.io.FilePermission', 
    permission_name   => '/bin/ls',
    permission_action => 'execute'
  );
end;
/
After that, we can use OS_COMMAND as SCOTT to execute the ls command on the protected Unix directory. And now, since we are actually executing as testuser, it works.
SQL> select os_command.exec_clob('/bin/ls -ltrh /tmp/tmpdir') from dual

OS_COMMAND.EXEC_CLOB('/BIN/LS-LTRH/TMP/TMPDIR')
----------------------------------------------------------------------------
total 0
-rw-rw-r-- 1 testuser testuser 0 Dec  9 11:24 f3
-rw-rw-r-- 1 testuser testuser 0 Dec  9 11:24 f2
-rw-rw-r-- 1 testuser testuser 0 Dec  9 11:24 f1

As already pointed out, the mapping between SCOTT and the Unix credentials for testuser is being persited in the database. It will remain even when the session end. So one database user can have only one Unix credential, for external calls by the Java VM, at the same time. That is an important difference between the Java based approach and DBMS_SCHEDULER.

4. November 2014

XML-Strukturen analysieren - mit SQL!

Analyze XML documents ... with SQL!
In diesem Blog Posting schreibe ich etwas über die "Analyse" von XML-Dokumenten mit SQL-Mitteln. Das Extrahieren einzelner Inhalte aus XML mit SQL-Funktionen wie XMLTABLE (oder früher: EXTRACTVALUE) habe ich ja schon oft behandelt. Um damit arbeiten zu können, muss man jedoch die Namen der XML-Tags, für die man sich interessiert, deren Position und ggfs. auch deren Namespace kennen. De-jure gibt es zu jedem XML-Dokument eine Dokumentation oder gar ein XML Schema, aus dem man diese Informationen entnehmen kann, de-facto jedoch ...
Daher gibt es heute einige Beispiele für SQL-Abfragen in XML-Dokumenten, die keine Inhalte extrahieren, sondern Informationen über den Aufbau. Vielleicht kann der eine oder andere von euch das gelegentlich gebrauchen.
Wenn die SQL-Funktionen XMLQUERY oder XMLTABLE (ich selbst verwende eigentlich nur XMLTABLE) verwendet werden, so wird zur Selektion innerhalb des XML-Dokumentes XPath oder XQuery genutzt. Neben der reinen Navigation im XML bietet diese Abfragesprache allerdings auch Funktionen an, mit denen man den Namen des Tags (name()), den Namespace (namespace-uri()) oder andere Dinge erfahren kann. Damit bauen wir ein einfaches Beispiel: Zu einem gegebenen XML-Dokument soll die SQL-Abfrage alle Tags auflisten.
select tag from xmltable(
  '//*'
  passing xmltype(
   '<doc>
     <tag1>
      <tag1a>Text</tag1a>
     </tag1>
     <tag2/>
     <tag2/>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'name()'
)

TAG
----------
doc
tag1
tag1a
tag2
tag2

5 Zeilen ausgewählt.
Die XMLTABLE-Funktion erhält neben dem XML-Dokument (welcher hier als Literal direkt in die Abfrage eingebaut wurde) den XPath-Ausdruck //* übergeben - dieser selektiert alle Tags auf allen Hierarchieebenen. In der COLUMNS-Klausel, welche die Informationen auf die Ergebnisspalten der SELECT-Abfrage abbildet, wird dann die XPath-Funktion name() verwendet; im Ergebnis wird für jedes vorkommende XML-Tag eine Zeile generiert, die dessen Namen enthält. Wie man an tag2 erkennen kann, wird wirklich für jedes Tag eine Zeile gebildet - wenn man nur eindeutige Namen haben möchte, kann man dies mit einem SELECT DISTINCT erreichen; auch das Zählen der Vorkommen ist so natürlich überhaupt kein Problem mehr.
select tag, count(*) from xmltable(
  '//*'
  passing xmltype(
   '<doc>
     <tag1>
      <tag1a>Text</tag1a>
     </tag1>
     <tag2/>
     <tag2/>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'name()'
)
group by tag
 
TAG          COUNT(*)
---------- ----------
doc                 1
tag1                1
tag2                2
tag1a               1

4 Zeilen ausgewählt.
Auch Namespace-Informationen (Blog Posting) lassen sich auf diese Weise extrahieren. Die folgende SQL-Abfrage zeigt die Anwendung der XPath-Funktionen local-name() und namespace-uri().
select 
  fulltag, tag, nsuri
from xmltable(
  '//*'
  passing xmltype(
   '<doc xmlns="http://mein-namespace/a" xmlns:ns1="http://mein-namespace/b">
     <ns1:tag>Text</ns1:tag>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'local-name()',
    fulltag varchar2(10) path 'name()',
    nsuri   varchar2(30) path 'namespace-uri()'
)

FULLTAG    TAG        NSURI
---------- ---------- ------------------------------
doc        doc        http://mein-namespace/a
ns1:tag    tag        http://mein-namespace/b

2 Zeilen ausgewählt.
Das ist insbesondere hilfreich, wenn man die Namespaces nicht genau kennt - die Deklaration derselben erfolgt zwar meist zu Beginn des XML-Dokumentes, das muss aber nicht so sein; es ist nach dem XML-Standard durchaus möglich, dass mitten im Dokument ein Tag mit einer neuen Namespace-Deklaration auftaucht.
Als nächstes wollen wir Informationen über den Kontext eines Tags herausfinden. XPath erlaubt nicht nur die fast immer verwendete Navigation zu den Child-Elementen, sondern unter anderem auch die Navigation zu den Elternelementen. Die folgende SQL-Abfrage zeigt, wie das geht. Hier wird aber nicht mehr mit reinem XPath gearbeitet; vielmehr packen wir unsere Aufgabe in eine XQuery-Abfrage.
select 
  tagname, 
  parentname,
  count(*) occurrences
from xmltable(
  'for $x in //*
   return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
  passing xmltype(
    '<root-tag>
      <level1-tag><level2-tag/></level1-tag>
      <level1-tag><level2-tag/></level1-tag>
      <level1-tag/>
     </root-tag>')
  columns
    tagname    varchar2(15) path '/r/t',
    parentname varchar2(15) path '/r/p'
)
group by tagname, parentname
/

TAGNAME         PARENTNAME      OCCURRENCES
--------------- --------------- -----------
root-tag                                  1
level1-tag      root-tag                  3
level2-tag      level1-tag                2
Wie man sehen kann, wird im XQuery Ausdruck für jedes gefundene XML-Tag ein "neues" XML-Dokument zusammengebaut, und zwar von der Struktur "<r><t>{tag-name}</t><p>{parent-tag-name}</p></r>". Dieses wird dann an die COLUMNS-Klausel weitergegeben; welche logischerweise innerhalb der Tags <r>, <t> and <p> navigiert und deren Inhalte auf die relationalen Ergebnisspalten abbildet. Der XQuery-Ausdruck selbst selektiert für jedes Element dessen Namen, dann navigiert er mit dem Ausdruck parent::* zum Elternknoten und selektiert dessen Namen. Wir erhalten zu jedem XML-Tag sein Parent-Tag. Und da wir in der Oracle-Datenbank sind, wissen wir sofort, was nun zu tun ist ...
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(
      '<root-tag>
        <level1-tag><level2-tag/></level1-tag>
        <level1-tag><level2-tag/></level1-tag>
        <level1-tag/>
       </root-tag>')
    columns
      tagname    varchar2(15) path '/r/t',
      parentname varchar2(15) path '/r/p'
  )
  group by tagname, parentname
)
select 
  lpad(' ', level * 2, ' ') || tagname || ' ('||occurrences||')' as tag
from tags
start with parentname is null
connect by prior tagname = parentname
/

TAG
--------------------------------------------------------------
  root-tag (1)
    level1-tag (3)
      level2-tag (2)

3 Zeilen ausgewählt.
Das ist doch gar nicht übel, oder ...? Eine SQL-Abfrage gibt uns einen Überblick über die Struktur im XML-Dokument. Das wollen wir nun an einem größeren XML-Dokument testen. Am besten nehmen wir eins, was die Datenbank bereits hat. Und zwar speichert die XML DB die Konfiguration der Protokollserver (FTP, HTTP, WebDAV) in einem XML-Dokument ab. Man kann es mit der PL/SQL-Funktion DBMS_XDB.CFG_GET abrufen.
SQL> select dbms_xdb.cfg_get().getclobval() from dual

DBMS_XDB.CFG_GET().GETCLOBVAL()
--------------------------------------------------------------------------------
<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-
Nun wenden wir die obige SQL-Abfrage auf dieses XML-Dokument an.
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(dbms_xdb.cfg_get().getclobval())
    columns
      tagname    varchar2(30) path '/r/t',
      parentname varchar2(30) path '/r/p'
  )
  group by tagname, parentname
  )
select 
  lpad(' ', level * 2, ' ') || tagname || ' ('||occurrences||')' as tag
from tags
start with parentname is null
connect by prior tagname = parentname
/

TAG
------------------------------------------------------------
  xdbconfig (1)
    sysconfig (1)
      acl-cache-size (1)
      acl-evaluation-method (1)
:
      persistent-sessions (1)
      protocolconfig (1)
        common (1)
          extension-mappings (1)
:
            mime-mappings (1)
              mime-mapping (51)
                extension (51)
                mime-type (51)
:
                  servlet-schema (2)
              servlet-mappings (1)
                servlet-mapping (6)
                  servlet-name (6)
                  servlet-pattern (6)
            welcome-file-list (1)
              welcome-file (2)
      resource-view-cache-size (1)
      xdbcore-loadableunit-size (1)
      xdbcore-log-level (1)
      xdbcore-logfile-path (1)
      xdbcore-xobmem-bound (1)

92 Zeilen ausgewählt.
Mit anderen hierarchischen Funktionen kann nun auch problemlos der komplette Pfad zu den XML-Tags ausgegeben werden; und man sieht sehr schön, wo ein bestimmtes XML-Tag steht und wie man es selektieren muss. Diese SQL-Abfrage können wir nun nochmals erweitern und damit gezielt nach bestimmten Tags suchen: Die folgende Abfrage zeigt alle Tags namens session-timeout mitsamt deren vollständigen Navigationspfad im XML-Dokument an. Der komplette Pfad wurde mit der SQL-Funktion SYS_CONNECT_BY_PATH generiert - das hat mit XML nichts zu tun; diese Funktion kann in jeder hierarchischen Abfrage genutzt werden.
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(dbms_xdb.cfg_get().getclobval())
    columns
      tagname    varchar2(30) path '/r/t',
      parentname varchar2(30) path '/r/p'
  )
  group by tagname, parentname
), hierarchie as (
  select 
    tagname,
    sys_connect_by_path(tagname, '/') as pfad
  from tags
  start with parentname is null
  connect by prior tagname = parentname
)
select pfad 
from hierarchie
where tagname = 'session-timeout'
/

PFAD
--------------------------------------------------------------------------------
/xdbconfig/sysconfig/protocolconfig/common/session-timeout
/xdbconfig/sysconfig/protocolconfig/ftpconfig/session-timeout
/xdbconfig/sysconfig/protocolconfig/httpconfig/session-timeout

3 Zeilen ausgewählt.
Das Ergebnis kann direkt verwendet werden, wie die Probe zeigt ...
select timeout
from xmltable (
  xmlnamespaces (DEFAULT 'http://xmlns.oracle.com/xdb/xdbconfig.xsd'), 
  '/xdbconfig/sysconfig/protocolconfig/ftpconfig/session-timeout'
  passing dbms_xdb.cfg_get()
  columns timeout path 'text()'
);

TIMEOUT
-----------------------------------------------------
6000

1 Zeile wurde ausgewählt.
Wie haben wir den Namespace herausgefunden ...? Ach ja ...
select distinct nsuri
from xmltable(
  '//*'
  passing dbms_xdb.cfg_get()
  columns
    nsuri   varchar2(50) path 'namespace-uri()'
)
/ 

NSURI
--------------------------------------------------
http://xmlns.oracle.com/xdb/xdbconfig.xsd

1 Zeile wurde ausgewählt.
Damit soll es genug sein. Beachtet bitte, dass diese Art der XML-Abfragen von der Datenbank nicht besonders optimiert werden können; es findet stets ein XML-Parsing statt. Auf sehr großen Dokumentbeständen oder großen Einzeldokumenten können diese Abfragen also durchaus länger dauern. Aber ansonsten steht der Analyse von XML - mit SQL nichts im Wege.
This blog posting will be about "analysis" of XML documents - in the database, with the SQL language. I have blogged several times about how to extract information from XML documents using the SQL functions XMLTABLE or EXTRACTVALUE (in earlier database versions). But to work with these functions, one must know, where the information of interest is located within the XML document. One must know the name of the XML tag, its position within the hierarchy, its namespace and so on. Of course, there is always good and comprehensive documentation (or an XML Schema) which contains this kind of information ... but I'll proceed anyway ...
So, today you'll see SQL queries on XML documents, which don't extract actual content, but information about the XML structure. Perhaps this is useful for somebody - sometime.
Within the SQL functions XMLQUERY or XMLTABLE (I hardly use XMLQUERY myself), XML tags are being selected. For this selection, either the XQuery or the more simple XPath language can be used. Beyond the pure navigational features, XPath also contains some functions to obtain information about the XML structure: name() retrieves the name of the current XML tag, namespace-uri() gets the full XML namespace information, and there are some more. With this in mind, we can build our first example. A SQL query is supposed to list the names of all XML tags.
select tag from xmltable(
  '//*'
  passing xmltype(
   '<doc>
     <tag1>
      <tag1a>Text</tag1a>
     </tag1>
     <tag2/>
     <tag2/>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'name()'
)

TAG
----------
doc
tag1
tag1a
tag2
tag2

5 rows selected.
For clarity, we pass the XML document directly into the XMLTABLE function as a literal. The XPath expression //* selects all XML tags in all hierarchy levels. This "intermediate XML result" will then go the the COLUMNS clause which maps it to the relational result set of the SQL query. In this case, the COLUMNS clause applies the name() function on each selected node. You'll see a row for each XML tag - including duplicates. To get only distinct tag names, we can utilize SQL features (SELECT DISTINCT); and counting the XML tags is also a very easy task now (these are the things I really like in the Oracle database - combine functionality and do awesome things.)
select tag, count(*) from xmltable(
  '//*'
  passing xmltype(
   '<doc>
     <tag1>
      <tag1a>Text</tag1a>
     </tag1>
     <tag2/>
     <tag2/>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'name()'
)
group by tag
 
TAG          COUNT(*)
---------- ----------
doc                 1
tag1                1
tag2                2
tag1a               1

4 rows selected.
We can also extract XML namespace information (blog posting about that). The following example illustrates the usage of local-name() and namespace-uri().
select 
  fulltag, tag, nsuri
from xmltable(
  '//*'
  passing xmltype(
   '<doc xmlns="http://mein-namespace/a" xmlns:ns1="http://mein-namespace/b">
     <ns1:tag>Text</ns1:tag>
    </doc>'
  )
  columns
    tag     varchar2(10) path 'local-name()',
    fulltag varchar2(10) path 'name()',
    nsuri   varchar2(30) path 'namespace-uri()'
)

FULLTAG    TAG        NSURI
---------- ---------- ------------------------------
doc        doc        http://mein-namespace/a
ns1:tag    tag        http://mein-namespace/b

2 rows selected.
That is very useful, when you don't know all the used namespaces within your XML document. In most cases these are being declared at the beginning, but this is not mandatory. A namespace declaration can occur at any position in your XML document, so perhaps there is an XML tag from an unknown namespace - at the very end of the XML document ... you can't be sure. Of course, in pratice we always have an XML schema or a documentation, so this is all theoretical ...
Next, we'll extract information about the context of an XML tag. XPath does not only allow to navigate from a parent to a child, but also from a child to a parent - or from a tag to its sibling. Whereas the child axis is the default, there are other axes to navigate along. The following SQL query will use the parent axis in order to retrieve the name of each XML tag's parent. Since this is a bit more complex, we don't use pure XPath any more. We'll utilize XQuery syntax instead.
select 
  tagname, 
  parentname,
  count(*) occurrences
from xmltable(
  'for $x in //*
   return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
  passing xmltype(
    '<root-tag>
      <level1-tag><level2-tag/></level1-tag>
      <level1-tag><level2-tag/></level1-tag>
      <level1-tag/>
     </root-tag>')
  columns
    tagname    varchar2(15) path '/r/t',
    parentname varchar2(15) path '/r/p'
)
group by tagname, parentname
/

TAGNAME         PARENTNAME      OCCURRENCES
--------------- --------------- -----------
root-tag                                  1
level1-tag      root-tag                  3
level2-tag      level1-tag                2

3 rows selected.
I don't want to provide a full explanation of XQuery here (there are plenty of tutorials in the web), but you might see how it works. The XQuery expression "builds" another XML document (for each XML tag) with the simple structure "<r><t>{tag-name}</t><p>{parent-tag-name}</p></r>". This is being passed to the COLUMNS clause. The XPath expressions in the COLUMNS clause now selects items from the "intermediate" XML document, so they navigate within the <r>, <t> and <p> tags. Looking at this queries' results and knowing that we are within the Oracle database, we should know, what the next step is ...
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(
      '<root-tag>
        <level1-tag><level2-tag/></level1-tag>
        <level1-tag><level2-tag/></level1-tag>
        <level1-tag/>
       </root-tag>')
    columns
      tagname    varchar2(15) path '/r/t',
      parentname varchar2(15) path '/r/p'
  )
  group by tagname, parentname
)
select 
  lpad(' ', level * 2, ' ') || tagname || ' ('||occurrences||')' as tag
from tags
start with parentname is null
connect by prior tagname = parentname
/

TAG
--------------------------------------------------------------
  root-tag (1)
    level1-tag (3)
      level2-tag (2)

3 rows selected.
Another nice instance of combining technology: A SQL query gives an overview on the structure of an XML document. Other technologies would require you to install tools or to author complex procedural code - and here we are doing all this with query language. Let's test this on more complex XML document - I'll take one which is already present in an Oracle database: The configuration of the XML DB protocol servers (FTP, HTTP, WebDAV) is being stored as XML within the database. We can retrieve it with the PL/SQL function DBMS_XDB.CFG_GET.
SQL> select dbms_xdb.cfg_get().getclobval() from dual

DBMS_XDB.CFG_GET().GETCLOBVAL()
--------------------------------------------------------------------------------
<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-
    :
Now, let's apply the above query on that document ...
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(dbms_xdb.cfg_get().getclobval())
    columns
      tagname    varchar2(30) path '/r/t',
      parentname varchar2(30) path '/r/p'
  )
  group by tagname, parentname
  )
select 
  lpad(' ', level * 2, ' ') || tagname || ' ('||occurrences||')' as tag
from tags
start with parentname is null
connect by prior tagname = parentname
/

TAG
------------------------------------------------------------
  xdbconfig (1)
    sysconfig (1)
      acl-cache-size (1)
      acl-evaluation-method (1)
:
      persistent-sessions (1)
      protocolconfig (1)
        common (1)
          extension-mappings (1)
:
            mime-mappings (1)
              mime-mapping (51)
                extension (51)
                mime-type (51)
:
                  servlet-schema (2)
              servlet-mappings (1)
                servlet-mapping (6)
                  servlet-name (6)
                  servlet-pattern (6)
            welcome-file-list (1)
              welcome-file (2)
      resource-view-cache-size (1)
      xdbcore-loadableunit-size (1)
      xdbcore-log-level (1)
      xdbcore-logfile-path (1)
      xdbcore-xobmem-bound (1)

92 rows selected.
We now get an overview on the hierarchy of the XML DB protocol server configuration; without a tool - just with SQL. Using the SYS_CONNECT_BY_PATH function, we can let the database even generate a full path to each XML tag. And with a WHERE clause we can look for specific tags of interest. The following query shows this: We are interested in the full path to a tag named session-timeout.
with tags as (
  select 
    tagname, 
    parentname,
    count(*) occurrences
  from xmltable(
    'for $x in //*
     return <r><t>{$x/name()}</t><p>{$x/parent::*/name()}</p></r>'
    passing xmltype(dbms_xdb.cfg_get().getclobval())
    columns
      tagname    varchar2(30) path '/r/t',
      parentname varchar2(30) path '/r/p'
  )
  group by tagname, parentname
), hierarchie as (
  select 
    tagname,
    sys_connect_by_path(tagname, '/') as pfad
  from tags
  start with parentname is null
  connect by prior tagname = parentname
)
select path
from hierarchie
where tagname = 'session-timeout'
/

PATH
--------------------------------------------------------------------------------
/xdbconfig/sysconfig/protocolconfig/common/session-timeout
/xdbconfig/sysconfig/protocolconfig/ftpconfig/session-timeout
/xdbconfig/sysconfig/protocolconfig/httpconfig/session-timeout

3 rows selected.
You can actually use this result, as the following test shows you.
select timeout
from xmltable (
  xmlnamespaces (DEFAULT 'http://xmlns.oracle.com/xdb/xdbconfig.xsd'), 
  '/xdbconfig/sysconfig/protocolconfig/ftpconfig/session-timeout'
  passing dbms_xdb.cfg_get()
  columns timeout path 'text()'
);

TIMEOUT
-----------------------------------------------------
6000

1 row selected.
And how did we find out the namespace (which is used in the XMLNAMESPACES clause) ...?
select distinct nsuri
from xmltable(
  '//*'
  passing dbms_xdb.cfg_get()
  columns
    nsuri   varchar2(50) path 'namespace-uri()'
)
/ 

NSURI
--------------------------------------------------
http://xmlns.oracle.com/xdb/xdbconfig.xsd

1 row selected.
OK folks, enough for today. When playing with these features, keep in mind, that the database does not optimize these kinds of XML query for performance - so they might take longer for large XML documents or large document sets. But having this in mind, nothing should prevent you from analyzing your XML documents - with SQL!

Beliebte Postings