19. Oktober 2015

DOAG2015 und "Modern Application Development" ab Januar 2016

In wenigen Wochen beginnt, wie jedes jahr, die DOAG-Konferenz. Dieses Jahr bin ich mit vier Vorträgen vertreten. Vielleicht ist der eine oder andere interessant für euch.

  • 17.11. 09:00 "Sydney"
    Node.js + Oracle-Datenbank = "node-oracledb" = Cool!
    Wer möchte, kann die Konferenz gleich mit den aktuellen, modernen und coolen Technologien beginnen. Nach einer kurzen Einführung in Node.js geht es in diesem Vortrag um den Oracle-Datenbanktreiber für Node.js, "node-oracledb". Ich werde vorstellen, wie man ein Node.js Programm zum Zugriff auf die Oracle-Datenbank schreibt, und was man mit Node.js und der Oracle-Datenbank alles erreichen kann (Websockets, Mailintegration und einiges mehr).
  • 19.11. 12:00 "Foyer Tokio"
    Das Leben der anderen: Twitter-Analyse mit Oracle12c, JSON und APEX
    Wieder darf ich eines der Demo-Kinos durchführen. Diesmal zeige ich eine "Twitter-Analyse"– mit nichts als den Bordmitteln der Oracle Database 12c. Tweets zum Hashtag #orclapex werden mit PL/SQL abgerufen und als JSON in die Datenbank gespeichert. Mit den Oracle12c SQL/JSON-Funktionen lassen sich dann mächtige Analysen durchführen.
  • 19.11. 14:00 "Istanbul"
    Diagramme – next generation: D3.js im Unternehmen nutzen
    Das freie Visualisierungs-Framework d3js (D3 = DDD = Data Driven Documents) gewinnt mehr und mehr an Popularität. Die Fülle an Diagrammen und Visualisierungen, die sich mit D3 realisieren lassen, grenzt wirklich ans Unglaubliche. Nach einer kurzen Einführung in die Technologie zeigt der Vortrag anhand von Oracle Application Express (APEX), wie man ein D3-Diagramm integrieren und Tabellendaten damit auf moderne Art und Weise visualisieren kann – die Nutzung in anderen Umgebungen ist von dort aus nur ein kleiner Schritt.
  • 17.11. 14:30 "Budapest"
    Anwendungen schnell und einfach – in der Cloud oder On Premise: Oracle Application Express (APEX)
    Der vierte ist kein regulärer DOAH-Vortrag, diesen halte ich im Rahmen der in die DOAG2015 integrierten Veranstaltung Oracle Cloud Day; in 25 Minuten stelle ich die Möglichkeiten von APEX vor - das ist vor allem für die interessant, die APEX noch nicht kennen, aber wissen wollen, was dahinter steckt.

Neben den Vorträgen freue ich mich vor allem auf die Gespräche, Kontakte und zahlreichen Community-Aktivitäten, die neben der DOAG-Konferenz stattfinden. So trifft sich die APEX-Community am Montag abend (also vor der Konferenz) im "Barfüßer" und Mittwochs findet, wie schon im letzten Jahr, eine APEX Open Mic Night statt. Dort ist "das Mikrofon offen", also sind alle eingeladen, in 10 Minuten kurz und knapp vorzustellen, was man mit APEX tolles gebaut hat. Und das ist sicherlich noch nicht alles ...


Im Januar 2016 starten Karin Patenge, Rainer Willems, Beda Hammerschmidt, Kai Donato, Dietmar Aust und ich - in Zusammenarbeit mit der DOAG eine Online-Videoserie zum Thema Modern Application Development - in der Praxis.

In acht Vidoes geht es um JSON, REST-Services, HTML5, Node.js und ähnliche, aktuell heiß diskutierte Entwicklerthemen. Alles wird im Bezug zum Unternehmenseinsatz betrachtet - die Integration in die IT-Landschaft oder mit der Oracle-Datenbank spielt also eine besondere Rolle. Die Teilnahme ist auch hier kostenlos - wenn Ihr auf aktuellem Stand bleiben möchtet, meldet euch gleich an (Klickt oben rechts auf Anmelden). Abgeschlossen wird die Reihe mit einer Online Q & A, bei der Ihr die Gelegenheit habt, mit den Experten zu diskutieren und eure Fragen zu stellen.

Schaut mal rein - sehen wir uns ...?

7. Oktober 2015

Statisches oder Dynamisches SQL? Query oder DML?

Zur in Oracle12c neu eingeführten Möglichkeit, PL/SQL-Code in die WITH-Klausel zu integrieren, hatte ich schon einmal ein Blog-Posting geschrieben. Heute behandle ich das Thema noch einmal, denn es bietet geradezu faszinierende Facetten an ...
Nochmal kurz zum Einstimmen: Die PL/SQL WITH-Klausel wurde mit Oracle12c (12.1.0.1) eingeführt, und sie erlaubt es, PL/SQL Funktionen innerhalb der WITH-Klausel zu definieren und diese in der eigentlichen SQL-Abfrage zu verwenden. Es braucht dazu kein CREATE FUNCTION Privileg; sobald man eine SQL-Query formulieren kann, kann man auch die PL/SQL WITH-Klausel nutzen. Ein einfaches Beispiel.
with function get_sal(p_empno in number) return number as
  l_return number;
 begin
  select sal into l_return from emp where empno = p_empno;
  return l_return;
 end get_sal;
select empno, ename, get_sal(empno) from emp
Das Ergebnis hätte man zugegebermaßen auch ohne PL/SQL-WITH haben können, aber es geht ja auch nur um das Beispiel. Innerhalb der WITH-Klausel wird die Funktion GET_SAL implementiert; diese gilt allein innerhalb der SQL-Abfrage. Allerdings kann man in der WITH-Klausel den vollen Sprachumfang von PL/SQL nutzen - und das nächste Beispiel geht schon etwas weiter: Wir bauen ein EXECUTE IMMEDIATE ein - ist das folgende nun also statisches oder dynamisches SQL ...?
with function get_rowcnt(p_tname in varchar2) return number is
    l_rows number;
  begin
    execute immediate 'select count(*) from ' || 
     dbms_assert.sql_object_name('"'||p_tname||'"') 
    into l_rows;
    return l_rows;
  end get_rowcnt;
select table_name, get_rowcnt(table_name) as row_count 
from user_tables;

TABLE_NAME                      ROW_COUNT
------------------------------ ----------
TAB_ARRAY                          100000
CSV_TABLE                           10000
EMP1                                 8192
EBA_SPATIAL_COUNTRIES                  35
DR$FT_EMP$I                            24
EMP                                    17
VERKAUF                                16
:                                       :
Die PL/SQL Funktion GET_ROWCNT führt nun jedes mal ein EXECUTE IMMEDIATE durch und zählt damit die Zeilen der jeweiligen Tabelle. Verbunden mit einer Query auf die Data Dictionary View USER_TABLES kann man so sehr elegant - und nur mit einer SQL-Abfrage - eigentlich mehrere SQL-Queries auf einmal absetzen. Statisches oder dynamisches SQL - das ist hier die Frage!
Treiben wir das Spiel noch etwas weiter - man könnte ja versuchen, in der Funktion der WITH-Klausel ein SQL UPDATE zu machen. Dazu ändern wir das erste Beispiel mit der Tabelle EMP ein wenig um ...
with function get_sal(p_empno in number) return number as
  l_return number;
 begin
  -- ACHTUNG: Hier passiert was!
  update emp set sal = sal * 2 where empno = p_empno returning sal into l_return;
  commit;
  return l_return;
 end get_sal;
select empno, ename, get_sal(empno) from emp
/
 
FEHLER in Zeile 9:
ORA-14551: DML-Vorgang kann innerhalb einer Abfrage nicht ausgeführt werden
ORA-06512: in Zeile 5
ORA-06512: in Zeile 9
OK, das scheint nicht zu gehen. Oder doch?
with function get_sal(p_empno in number) return number as
  l_return number;
  pragma autonomous_transaction;
 begin
  -- ACHTUNG: Hier passiert was!
  update emp set sal = sal * 2 where empno = p_empno returning sal into l_return;
  commit;
  return l_return;
 end get_sal;
select empno, ename, get_sal(empno) from emp
/

     EMPNO ENAME      GET_SAL(EMPNO)
---------- ---------- --------------
      7369 SMITH                3200
      7499 ALLEN                6400
      7521 WARD                 5000
      7566 JONES               11900
         : :                       :
Das ist schon sehr interessant - eine SQL-Query, die eigentlich ein UPDATE macht. In Oracle12c kann man nur mit einer WITH bzw. SELECT-Abfrage doch einiges erreichen. Doch was macht man, wenn das nicht erwünscht ist? Zunächst wird das SQL in den meisten Fällen ja von der Anwendung kontrolliert - dass der Endanwender freies SQL eingeben kann, ist meist nicht vorgesehen - und so soll es auch sein.
Doch wenn eine Applikation ein Freefrom SQL Window anbietet? Was kann man tun, damit diese Möglichkeiten nicht missbraucht werden können ...? Nun, da gibt es mehrere Ansätze:
  • Der erste und auch wichtigste Ansatz ist, dass die Datenbanksession, mit der die Anwendung verbunden ist, nur die absolut nötigen Privilegien enthalten sollte. Es ist nach wie vor Best Practice, dass man eben nicht als der Eigentümer der Tabellen angemeldet ist, sondern als ein Connection-User. Dieser hat bestenfalls keinen direkten Zugriff auf Tabellen, sondern nur SELECT-Privilegen auf Views und EXECUTE Privilegien auf PL/SQL Packages (welche die Business-Logik beinhalten). In einem solchen Setup ist das UPDATE per SQL WITH nicht mehr möglich:
    with function get_sal(p_empno in number) return number as
      l_return number;
      pragma autonomous_transaction;
     begin
      -- ACHTUNG: Hier passiert was!
      update emp_v set sal = sal * 2 where empno = p_empno returning sal into l_return;
      commit;
      return l_return;
     end get_sal;
    select empno, ename, get_sal(empno) from emp_v
    /
    
    FEHLER in Zeile 1:
    ORA-01031: Nicht ausreichende Berechtigungen
    ORA-06512: in Zeile 6
    ORA-06512: in Zeile 10
    
  • Die PL/SQL WITH-Klausel selbst bietet ebenfalls einen Schutz an: Funktionen in der WITH-Klausel funktionieren generell nur in der Top-Level-Ebene und nicht in einer Subquery. Bietet man in einer Anwendung also ein Freeform SQL Window, so sollte man die Nutzereingabe am besten als Subquery mit einem SELECT * FROM () einrahmen. Schon kann der Anwender SQL eingeben, aber kein PL/SQL mehr nutzen. Rahmt man es dagegen mit einem SELECT /*+ WITH_PLSQL*/ * FROM () ein, geht es dennoch - das sollte aber nur mit Vorsicht - am besten gar nicht - eingesetzt werden.
    select * from (
      with function get_sal(p_empno in number) return number as
        l_return number;
        pragma autonomous_transaction;
       begin
        -- ACHTUNG: Hier passiert was!
        update emp set sal = sal * 2 where empno = p_empno returning sal into l_return;
        commit;
        return l_return;
       end get_sal;
      select empno, ename, get_sal(empno) from emp
    )
    /
    
    with function get_sal(p_empno in number) return number as
    *
    FEHLER in Zeile 2:
    ORA-32034: Nicht unterstützte Benutzung von WITH-Klausel
    
Zusammengefasst kann man sagen, dass die neue PL/SQL WITH-Klausel einfache SQL-Abfragen wesentlich mächtiger macht. Der Unterschied zwischen "statischem" und "dynamischem" SQL verschwimmt allmählich. Man sollte ganz genau überlegen, wem man in einer Applikation die Möglichkeit zum Freeform SQL gibt und wem nicht. Wendet man in seiner Anwendung grundliegende Designkonzepte an, so ist diese mit WITH-Klausel genauso sicher wie ohne.
Wichtig zu wissen ist die Tatsache, dass die WITH-Klausel innerhalb einer Subquery abgeschaltet ist - die Anwendung kann also ganz einfach verhindert werden, indem man die SQL-Abfrage als Subquery ausführt. Das ist übrigens auch der Grund, warum man eine PL/SQL WITH-Klausel in APEX sehr wohl in einem Classic Report anwenden kann, nicht aber in einem Interactive Report.
Some time ago, I already published a blog posting about the new PL/SQL WITH clause which has been introduced in Oracle12c. Today, I'll blog about this again, since this topic has some fascinating aspects ...
Short recap: The PL/SQL WITH clause has been introduced with Oracle12c (12.1.0.1) and it allows to define PL/SQL functions in the WITH clause of a SQL query. These functions only exist during query execution - there are no dictionary objects. As soon as you can execute a SQL query, you can start embedding PL/SQL into that query. Here's a very simple example:
with function get_sal(p_empno in number) return number as
  l_return number;
 begin
  select sal into l_return from emp where empno = p_empno;
  return l_return;
 end get_sal;
select empno, ename, get_sal(empno) from emp
The result of that very query could have been achieved without PL/SQL WITH as well, but it's here as an example: In the WITH clause, a PL/SQL function named GET_SAL is being defined - and used in the main query - the PL/SQL is very simple (and useless in this case as well), but we can start doing other things. Next, I'll start to play with EXECUTE IMMEDIATE.
with function get_rowcnt(p_tname in varchar2) return number is
    l_rows number;
  begin
    execute immediate 'select count(*) from ' || 
     dbms_assert.sql_object_name('"'||p_tname||'"') 
    into l_rows;
    return l_rows;
  end get_rowcnt;
select table_name, get_rowcnt(table_name) as row_count 
from user_tables;

TABLE_NAME                      ROW_COUNT
------------------------------ ----------
TAB_ARRAY                          100000
CSV_TABLE                           10000
EMP1                                 8192
EBA_SPATIAL_COUNTRIES                  35
DR$FT_EMP$I                            24
EMP                                    17
VERKAUF                                16
:                                       :
Again, we define a PL/SQL function named GET_ROWCNT. This function uses EXECUTE IMMEDIATE to execute another, dynamic SQL query. This single query actually executed as much SQL queries as there are tables in the database schema. Is that static or dynamic SQL ...? That is the question.
Let's go further with this. We could try to do a SQL UPDATE as part of our PL/SQL function. Here's the slighly changed first example, based on the table EMP.
with function get_sal(p_empno in number) return number as
  l_return number;
 begin
  -- ATTENTION: It happens here!
  update emp set sal = sal * 2 where empno = p_empno returning sal into l_return;
  commit;
  return l_return;
 end get_sal;
select empno, ename, get_sal(empno) from emp
/
 
ERROR at line 9:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at line 5
ORA-06512: at line 9
OK, does not seem to work. Really?
with function get_sal(p_empno in number) return number as
  l_return number;
  pragma autonomous_transaction;
 begin
  -- ATTENTION: It happens here!
  update emp set sal = sal * 2 where empno = p_empno returning sal into l_return;
  commit;
  return l_return;
 end get_sal;
select empno, ename, get_sal(empno) from emp
/

     EMPNO ENAME      GET_SAL(EMPNO)
---------- ---------- --------------
      7369 SMITH                3200
      7499 ALLEN                6400
      7521 WARD                 5000
      7566 JONES               11900
         : :                       :
That is interesting: a SQL query, which actually does some Data Manipulation. Beginning with Oracle12c, a simple SQL query can do a lot of things. But what, if that behaviour is not wanted ...? In most cases, the SQL is being controlled by the application - users cannot execute any arbitrary SQL - and rightly so.
But as soon as an application offers some kind of freeform SQL window, we need to think about the PL/SQL WITH clause. What can we do, that users can execute SQL queries, but not perform any PL/SQL logic or DML operations ...? We have two approches:
  • First and most important: The database session must have only those privileges which are absolutely required to do its work. It's best practice for years, that the database connection is not done as the table owner, but as a low-privileged connection user. And this user even doesn't have privileges on the tables directly, but only SELECT privileges on some views and EXECUTE privileges on some PL/SQL packages containing the business logic. In such a setup, the PL/SQL WITH clause could be used, but one cannot damage anything.
    with function get_sal(p_empno in number) return number as
      l_return number;
      pragma autonomous_transaction;
     begin
      -- ATTENTION: It happens here!
      update emp_v set sal = sal * 2 where empno = p_empno returning sal into l_return;
      commit;
      return l_return;
     end get_sal;
    select empno, ename, get_sal(empno) from emp_v
    /
    
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at line 6
    ORA-06512: at line 10
    
  • Another level of protection is within the PL/SQL WITH clause itself. You can only add PL/SQL code to your WITH clause when you code the top level query. Within a subquery, PL/SQL code cannot be added to the WITH clause. That means, when an application is about to provide a freeform SQL window to its users, the user input should be embedded by SELECT * FROM () so that it becomes a subquery. The end user now can execute arbitrary SQL queries, but no PL/SQL anymore. Take care: You can add the /*+ WITH_PLSQL*/ hint to the top level query and thus make PL/SQL code in subqueries work again. But this should be used with care - or (even better) not at all.
    select * from (
      with function get_sal(p_empno in number) return number as
        l_return number;
        pragma autonomous_transaction;
       begin
      -- ATTENTION: It happens here!
        update emp set sal = sal * 2 where empno = p_empno returning sal into l_return;
        commit;
        return l_return;
       end get_sal;
      select empno, ename, get_sal(empno) from emp
    )
    /
    
    with function get_sal(p_empno in number) return number as
    *
    ERROR at line 2:
    ORA-32034: unsupported use of WITH clause
    
In summary, the new PL/SQL WITH clause makes SQL queries in Oracle12c much more powerful. The difference between static and dynamic SQL becomes rather blurry and vague. When you are about to add some freeform SQL window to your application, please think twice about which end users to give that privilege and to which not. Of course, it's always best to have the application designed by well known best practices, when the privileges are missing a PL/SQL WITH clause cannot damage that much.
Most important to know about the WITH clause is that it does not work in a subquery. This is not simply a missing feature, but an important aspect of database security. You can actually use this fact in order to make your freeform SQL window more secure: Simply embed the users' input as a subquery. BTW: That is the reason why PL/SQL WITH clauses can work within an APEX "classic" report, but not in an interactive report.

Beliebte Postings