2. April 2015

DOAG DevCamp - und weitere Veranstaltungen im Frühjahr 2015

This blog posting is about some upcoming events in Germany and in german language - and therefore in german only. Makes sense, doesn't it?
Auch wenn ich dieses Jahr wegen anderer Termine nicht teilnehmen kann, so möchte ich dennoch eine Lanze für das DOAG DevCamp, welches am 29. und 30. April in Frankfurt stattfindet, brechen. Dieses Mal steht die Veranstaltung unter dem allgemeinen Thema Upcycling Software - welches uns im Umfeld von Unternehmensdatenbanken und den Systemen drumherum immer wieder betrifft.
Auf dem ersten DevCamp letztes Jahr in München war ich dabei - und von der Art und Weise der Veranstaltung wirklich angetan. Es gibt im Vorfeld keine feste Agenda, zu Beginn kommen alle Teilnehmer zusammen und erstellen die Agenda gemeinsam. Das Format ist unglaublich flexibel und spontan - und auch ohne vorbereitete Vorträge entstehen Diskussionen, man bekommt eine Menge neuer Ideen, Gedanken und Anregungen.
Insofern: Wer noch kein Barcamp besucht hat, dem möchte ich das hiermit wärmstens empfehlen - probiert es einfach mal aus. Und wer schonmal auf einem war ... der weiss ja Bescheid.
Ich selbst werde, wie schon gesagt, dieses Mal nicht dabei sein - dafür findet Ihr mich in April, Mai und Juni auf folgenden DOAG Veranstaltungen.
  • DOAG BI 2015 in München:
    Hier bin ich mit zwei Vorträgen vertreten. Einmal zum Thema D3.js im Unternehmenseinsatz (auf den freue ich mich besonders) und einmal zum Thema Geodaten mit SQL - und ohne Karte.
  • DOAG Oracle Spatial & Geodata Day:
    Hier trifft sich die Oracle Geodaten-Community - zum ersten Mal nach etwas längerer Zeit. An diesem Tag geht es um Geodaten, die Oracle Datenbank und was man damit machen kann. Themen wie Routing mit der Datenbank, Adressvalidierung mit einem Geocoder und OpenStreetMap-Daten mit Oracle machen doch neugierig, oder?
  • DOAG APEX Connect 2015:
    Und natürlich bin ich beim Highlight des Jahres für alle APEX-Entwickler und solche, die es werden wollen, mit dabei. Die APEX Connect ist die erste reine APEX-Konferenz im deutschsprachigen Raum: Zwei Tage lang nur APEX und sonst nix.

17. März 2015

String-Operationen auf CLOBs - richtig schnell machen!

Making string operations on a CLOB really fast
Dieses Blog Posting behandelt das Thema CLOB, Stringoperationen und PL/SQL - es geht um verschiedene Varianten, wie man mit einer PL/SQL Prozedur, per Zeichenverkettung, einen CLOB zusammenbauen kann. Das kommt in der Praxis ja gar nicht so selten vor. Interessant ist, dass die Performance je nach gewähltem Ansatz wirklich völlig verschieden sein kann. In meinem Beispiel braucht - bei gleichen Datenmengen - die langsamste Methode über 4 Minuten, wogegen die schnellste in 0.2 Sekunden fertig ist.
Zunächst zur Aufgabe: Die etwa 920.000 Zeilen der Demotabelle SALES im Schema SH ...
SQL> select * from sh.sales 

PROD_ID CUST_ID TIME_ID             CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
------- ------- ------------------- ---------- -------- ------------- -----------
     13     987 10.01.1998 00:00:00          3      999             1        1232
     13    1660 10.01.1998 00:00:00          3      999             1        1232
     13    1762 10.01.1998 00:00:00          3      999             1        1232
     13    1843 10.01.1998 00:00:00          3      999             1        1232
     13    1948 10.01.1998 00:00:00          3      999             1        1232
     13    2273 10.01.1998 00:00:00          3      999             1        1232
      :       :                   :          :        :             :           : 
... sollen, semikolon-separiert, in einen CLOB geladen werden.
13;987;10.01.1998 00:00:00;3;999;1;1232,16
13;1660;10.01.1998 00:00:00;3;999;1;1232,16
13;1762;10.01.1998 00:00:00;3;999;1;1232,16
13;1843;10.01.1998 00:00:00;3;999;1;1232,16
13;1948;10.01.1998 00:00:00;3;999;1;1232,16
:
Damit die Tests nicht so lange dauern, beschränke ich mich bei meinen Tests auf die ersten 10.000 Zeilen. Der erste Versuch ist der naivste, ganz einfach "herunterprogrammiert": Ein impliziter Cursor wird geöffnet und in der Cursor-Loop wird der CLOB Schritt für Schritt zusammenkonkateniert. Man kann das mit einen CLOB tatsächlich genauso machen wie mit einem VARCHAR2.
declare
  l_clob clob := '';
begin
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
   l_clob := l_clob || to_char(i.PROD_ID) || ';' || 
                       to_char(i.CUST_ID) || ';' ||  
                       to_char(i.TIME_ID) || ';' ||  
                       to_char(i.CHANNEL_ID) || ';' || 
                       to_char(i.PROMO_ID) || ';' || 
                       to_char(i.QUANTITY_SOLD) || ';' || 
                       to_char(i.AMOUNT_SOLD)|| chr(10);
  end loop;
  dbms_output.put_line(length(l_clob));
  dbms_output.put_line(substr(l_clob, 1, 400));
end;
/
sho err
Da wir schon im Vorfeld wissen, dass das Ergebnis größer als 32.767 Byte sein wird, ist die Variable l_clob nicht vom Typ VARCHAR2, sondern CLOB. Trotzdem kann man in PL/SQL, ganz normal, mit dem || zur Zeichenverkettung arbeiten. Allerdings läuft diese Prozedur lange - allein für 10.000 Zeilen braucht sie etwa 4 Minuten. Über die Laufzeit für alle 920.000 Zeilen möchte ich gar nicht nachdenken.
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:04:23.60
Anhand der Data Dictionary-View V$TEMPORARY_LOBS kann man feststellen, dass, während die Prozedur läuft, temporäre LOBs entstehen. Ein temporärer LOB ist ein LOB-Objekt, was nicht in einer Tabelle liegt, sondern nur transient existiert - beispielsweise in einer PL/SQL-Variable. Ein LOB ist immer persistent - in einer Tabelle - oder temporär.
SQL> select * from v$temporary_lobs

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ------------ -------------
        20          0            0             0
        38          0            0             0
       272          6            0             0
Tatsächlich läuft obiger Code nicht optimal. Vor allem das Anhängen der einzelnen Tabellenspalten an den CLOB sorgt dafür, dass sehr viele Konvertierungen von VARCHAR2 nach CLOB stattfinden, und dass sehr viele LOB-Operationen erfolgen. Generell ist das Anhängen eines Strings an einen CLOB wesentlich teurer als an einen VARCHAR2 - hier der Beweis: Zunächst 30.000 Zeichenverkettungen auf einem CLOB ...
declare
  v_result clob;
begin
  for i in 1..30000 loop
    v_result := v_result || '*';
  end loop;
end;
/

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.56
... dann 30.000 Zeichenverkettungen mit einem VARCHAR2 ...
declare
  v_result varchar2(32000);
begin
  for i in 1..30000 loop
    v_result := v_result || '*';
  end loop;
end;
/

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.01
Das scheint zumindest etwa der Faktor 60 zu sein; wenn nicht sogar noch mehr. Der nächste Schritt wäre also, dafür zu sorgen, dass unser Code weniger LOB-Verkettungen und mehr VARCHAR2-Verkettungen macht. Das ist auch ganz einfach: Eine Tabellenzeile wird zuerst in eine Variable vom Typ VARCHAR2 geladen und erst dann an den CLOB angehängt ...
declare
  l_clob clob := '';
  l_str  varchar2(32000);
begin
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    l_clob := l_clob || l_str;
  end loop;
  dbms_output.put_line(length(l_clob));
  dbms_output.put_line(substr(l_clob, 1, 400));
end;
/
sho err
Das Ergebnis kann sich sehen lassen ...
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:01.61
Damit sind wir von 4 Minuten auf 2 Sekunden herunter - das ist stark. Die ganze Datenmenge von 920.000 Zeilen wird dann also etwa 3 Minuten brauchen. Man kann sogar schon nachdenken, hier aufzuhören ... aber wir sind noch nicht fertig.
Wieder schauen wir in die Dictionary View V$TEMPORARY_LOBS, und diesmal zeigt sie nur noch zwei temporäre LOBs an (vorher waren es sechs). Wenn man aber genauer nachdenkt, so müsste einer eigentlich ausreichen. Es braucht einen einzigen temporären LOB (für das Ergebnis); an den wird alles angehängt. Der PL/SQL Code braucht aber zwei. Nun, im PL/SQL-Code haben wir die Syntax für VARCHAR2 verwendet und es der Engine überlassen, das korrekt auf den CLOB anzuwenden. Machen wir die Arbeit doch mal selbst: Alle Zeichenoperationen, die auf dem CLOB stattfinden, sind nun mit dem Paket DBMS_LOB realisiert. Außerdem legen wir unseren temporären LOB (ebenfalls mit DBMS_LOB) selbst an.
declare
  l_clob clob := '';
  l_str  varchar2(32000);
begin
  dbms_lob.createtemporary(l_clob, true, dbms_lob.call);
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    dbms_lob.writeappend(l_clob, length(l_str), l_str);
  end loop;
  dbms_output.put_line(dbms_lob.getlength(l_clob));
  dbms_output.put_line(dbms_lob.substr(l_clob, 400, 1));
end;
/
sho err
Wieder gibt es eine Verbesserung. Der Mehraufwand für die "Übersetzung" der Stringoperationen auf das manchmal umständliche DBMS_LOB hat sich gelohnt: Nochmals Faktor 4.
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.35
Kann man noch mehr rausholen? Ja, es geht noch was: Wir haben festgestellt, dass eine Zeichenverkettung für einen LOB wesentlich teurer ist als für ein VARCHAR2. Derzeit bauen wir eine Tabellenzeile als VARCHAR2 zusammen und hängen sie an den CLOB an. Wir haben also soviele LOB-Operationen, wie es Tabellenzeilen gibt. Nun könnte man aber doch mehrere Tabellenzeilen zu einem VARCHAR2 zusammenfassen und die LOB-Operation erst dann machen, wenn der VARCHAR2 "Puffer" mit 32.000 Bytes "voll" ist. In Code sieht das so aus:
declare
  l_clob  clob := '';
  l_str   varchar2(32000) := '';
  l_block varchar2(32000) := '';
begin
  dbms_lob.createtemporary(l_clob, true, dbms_lob.call);
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    if length(l_block) + length(l_str) > 32000 then
      dbms_lob.writeappend(l_clob, length(l_block), l_block);
      l_block := '';
    end if;
    l_block := l_block || l_str;
  end loop;
  dbms_lob.writeappend(l_clob, length(l_block), l_block);
  dbms_output.put_line(dbms_lob.getlength(l_clob));
  dbms_output.put_line(dbms_lob.substr(l_clob, 400, 1));
end;
/
sho err
Das war Mehraufwand ... aber es hat sich tatsächlich nochmal gelohnt.
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.13
Und damit wäre ich am Optimierungsende angekommen. Es ist schon erstaunlich, was man mit geschicktem Coding herausholen kann. Bei dieser Prozedur traue ich mich nun, die ROWNUM-Beschränkung herauszuholen und den CLOB für die ganze Tabelle generieren zu lassen - der 39MB große CLOB ist in 6 Sekunden erzeugt. Es zeigt sich deutlich, dass, beim Arbeiten mit LOBs, am PL/SQL Paket DBMS_LOB kein Weg vorbeiführt.
Viel Spaß beim Ausprobieren ...
This blog posting is about the CLOB datatype, string operations and PL/SQL. I will elaborate about four different methods to "build" a CLOB from table data with PL/SQL. For 10000 rows, the execution time will vary from 4 Minutes to 0.2 seconds. If you want to know, why the DBMS_LOB package is so important and how to make string operations on CLOB variables really fast, read on.
The exercise: We want to build a CLOB, containing semicolon-separated data, from the well-known demo table SALES within the schema SH. This table contains about 920000 rows.
SQL> select * from sh.sales 

PROD_ID CUST_ID TIME_ID             CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
------- ------- ------------------- ---------- -------- ------------- -----------
     13     987 10.01.1998 00:00:00          3      999             1        1232
     13    1660 10.01.1998 00:00:00          3      999             1        1232
     13    1762 10.01.1998 00:00:00          3      999             1        1232
     13    1843 10.01.1998 00:00:00          3      999             1        1232
     13    1948 10.01.1998 00:00:00          3      999             1        1232
     13    2273 10.01.1998 00:00:00          3      999             1        1232
      :       :                   :          :        :             :           : 
Our result will look like this:
13;987;10.01.1998 00:00:00;3;999;1;1232,16
13;1660;10.01.1998 00:00:00;3;999;1;1232,16
13;1762;10.01.1998 00:00:00;3;999;1;1232,16
13;1843;10.01.1998 00:00:00;3;999;1;1232,16
13;1948;10.01.1998 00:00:00;3;999;1;1232,16
:
To save some time, I will start with focusing on the first 10000 rows. We'll run our procedure for the full dataset, when we have found the most efficient approach. Our first approach: We'll loop using an implicit cursor and use PL/SQL string concatenation operators in order to build the CLOB (as we would do for VARCHAR2). Pretty simple.
declare
  l_clob clob := '';
begin
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
   l_clob := l_clob || to_char(i.PROD_ID) || ';' || 
                       to_char(i.CUST_ID) || ';' ||  
                       to_char(i.TIME_ID) || ';' ||  
                       to_char(i.CHANNEL_ID) || ';' || 
                       to_char(i.PROMO_ID) || ';' || 
                       to_char(i.QUANTITY_SOLD) || ';' || 
                       to_char(i.AMOUNT_SOLD)|| chr(10);
  end loop;
  dbms_output.put_line(length(l_clob));
  dbms_output.put_line(substr(l_clob, 1, 400));
end;
/
sho err
We already know that our result will be larger than 32767 bytes. So we cannot use a VARCHAR2, we must use CLOB as datatype for the variable l_clob. But we can use "normal" PL/SQL string concatenation syntax, though. For 10000 rows, this procedure takes about 4 Minutes - which is long. I don't even want to think about the time needed for the whole table ...
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL procedure successfully completed.

Elapsed: 00:04:23.60
While the procedure runs, it's worthful to have a look into the V$TEMPORARY_LOBS data dictionary view. You'll see that your database session creates some temporary lobs.
SQL> select * from v$temporary_lobs

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS
---------- ---------- ------------ -------------
        20          0            0             0
        38          0            0             0
       272          6            0             0
Let's get this straight: This code is far away from being optimal. All these string concatenation operations on the CLOB variable lead to (too) many temporary lobs being created. And string concatenations on CLOBs are, in general, far more expensive then on VARCHAR2s. Try it out: Here are 30000 string concatenations on a CLOB ...
declare
  v_result clob;
begin
  for i in 1..30000 loop
    v_result := v_result || '*';
  end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.56
... and here is the same on a VARCHAR2:
declare
  v_result varchar2(32000);
begin
  for i in 1..30000 loop
    v_result := v_result || '*';
  end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
That is at least factor 50 to 60. So, our next step must be to eliminate string concatenations on the CLOB. This is more easy than you might think: We can build a VARCHAR2 for a complete table row and finally append this (full row) to the CLOB.
declare
  l_clob clob := '';
  l_str  varchar2(32000);
begin
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    l_clob := l_clob || l_str;
  end loop;
  dbms_output.put_line(length(l_clob));
  dbms_output.put_line(substr(l_clob, 1, 400));
end;
/
sho err
The result is nothing less than impressive ...
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.61
We are down from 4 Minutes to 2 seconds; just with a very tiny code adjustment. For the complete dataset of 920000 rows, we now have to expect an execution time of about 3 minutes. But we are not finished here - the story continues ...
Again, we have a look into V$TEMPORARY_LOBS, while the procedure is running. It shows us that two temporary LOBs are in use. But we really need only one temporary LOB and nothing more. Why? Because our result (which is a CLOB) must be hold as temporary LOB; all other temporary content can be handled as VARCHAR2. Since we used plain PL/SQL string operations, we left it up to the engine, how these operations are to be exeucuted on the CLOB. Thus, in our third approach, we'll do the job ourselves: All string operations are now done with the procedures and functions of the DBMS_LOB package. And ... we'll also create the temporary LOB explicitly and ourselves.
declare
  l_clob clob := '';
  l_str  varchar2(32000);
begin
  dbms_lob.createtemporary(l_clob, true, dbms_lob.call);
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    dbms_lob.writeappend(l_clob, length(l_str), l_str);
  end loop;
  dbms_output.put_line(dbms_lob.getlength(l_clob));
  dbms_output.put_line(dbms_lob.substr(l_clob, 400, 1));
end;
/
sho err
Using this obscure DBMS_LOB package seems to pay off: Performance improvement by factor 4.
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.35
That's all ...? No, we can get even better: We have learned, that a string operation on a CLOB is far more expensive than on a VARCHAR2. Now, we build each table row as VARCHAR2 and append it to the CLOB. So we have as many CLOB concatenations as there are table rows. But we could also aggregate multiple table rows into the VARCHAR2, up to its limit of 32000 bytes. When the VARCHAR buffer is full, we append it to the CLOB and start again. Translated to code, this looks as follows ...
declare
  l_clob  clob := '';
  l_str   varchar2(32000) := '';
  l_block varchar2(32000) := '';
begin
  dbms_lob.createtemporary(l_clob, true, dbms_lob.call);
  for i in (
    select * from sh.sales where rownum <= 10000
  ) loop
    l_str := to_char(i.PROD_ID) || ';' || 
             to_char(i.CUST_ID) || ';' ||  
             to_char(i.TIME_ID) || ';' ||  
             to_char(i.CHANNEL_ID) || ';' || 
             to_char(i.PROMO_ID) || ';' || 
             to_char(i.QUANTITY_SOLD) || ';' || 
             to_char(i.AMOUNT_SOLD)|| chr(10);
    if length(l_block) + length(l_str) > 32000 then
      dbms_lob.writeappend(l_clob, length(l_block), l_block);
      l_block := '';
    end if;
    l_block := l_block || l_str;
  end loop;
  dbms_lob.writeappend(l_clob, length(l_block), l_block);
  dbms_output.put_line(dbms_lob.getlength(l_clob));
  dbms_output.put_line(dbms_lob.substr(l_clob, 400, 1));
end;
/
sho err
It's faster - again.
426038
13;987;10.01.1998 00:00:00;3;999;1;1232,16
:
13;2683;10.01.1998 00:00:00;3;999;1;1232,16
13;2865;10.01.1998 00:00:00;3;999;1;1232,16
13;46

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.13
And this is the end of the optimization. These differences are stunning: We started with 4 minutes and came down to 0.2 seconds; which is a factor of 1200. Now, I'm totally relaxed when running the code for whole table of 920000 rows - the CLOB of 39MB is being build in just 6 seconds. As so often, the first and "most easy" approach isn't the best: When it's about working with CLOBs, there is now way around the (kind of obscure) DBMS_LOB package. Getting familiar with it, is (as we see) absolutely worth the effort ...
As always - habe a lot of fun while playing with this ...

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.

Beliebte Postings