PL/SQL und Rollen, Invoker's Rights vs. Definer's Rights ...

Missverständnisse und Probleme tauchen recht häufig im Bereich PL/SQL und Rollen auf. In diesem Zusammenhang wichtig sind folgende Grundsätze:

• PL/SQL-Prozeduren, -Funktionen und -Packages können mit Definer's Rights oder Invoker's Rights ausgeführt werden.
• Während der Ausführung von Definer's Rights Procedures sind alle Rollen abgeschaltet.

Ein Beispiel: Es wird (als SYS) eine Rolle table_creator mit dem Systemprivileg create table erzeugt. Anschließend wird ein User proc_owner erzeugt und diesem werden die Privilegien create session, create procedure und die soeben erzeugte Rolle table_creator zugewiesen. Die Quota auf dem Tablespace (hier: Users) nicht vergessen!

create role table_creator not identified 
/
grant create table to table_creator
/
 
create user proc_owner identified by proc_owner
/

grant create session, create procedure to proc_owner
/

grant table_creator to proc_owner
/

alter user proc_owner quota 10M on users
/

Nun wird als PROC_OWNER die folgende Prozedur angelegt und gestartet:

create or replace procedure CREATE_TABLE_PROC
is
begin
  execute immediate 'create table test_roles (column1 number(10))';
end;
/

Prozedur wurde erstellt.

create table test_table (column1 number(10))
/

Tabelle wurde erstellt.


begin
  create_table_proc;
end;
/

begin
*
FEHLER in Zeile 1:
ORA-01031: insufficient privileges
ORA-06512: at "PROC_OWNER.CREATE_TABLE_PROC", line 4
ORA-06512: at line 2

Man sieht: Der User table_owner kann Tabellen mit create table direkt erstellen, sobald er dasselbe jedoch über seine Prozedur versucht, schlägt das ganze fehl ... Grund dafür ist, wie oben bereits geschrieben: Während der Ausführung einer Definer's Rights-Prozedur (und das ist der Default bei PL/SQL) sind alle Rollen abgeschaltet. Und wie löst man nun das Problem?

1. Man gibt dem User proc_owner das Systemprivileg create table direkt.
2. Man erzeugt die Prozedur als Invoker's Rights-Prozedur. Dann werden die Rollen nicht abgeschaltet. Aber: Für die Ausführung gelten dann die Rechte des Aufrufers, nicht des Eigentümers!

Hier ist ein Beispiel für eine Invoker's Rights-Prozedur:

create or replace procedure CREATE_TABLE_PROC 
authid current_user is
begin
  execute immediate 'create table test_roles (column1 number(10))';
end;
/

Prozedur wurde erstellt.

begin
  create_table_proc;
end;
/

PL/SQL-Prozedur erfolgreich abgeschlossen.

Das praktische an invoker's rights-Prozeduren ist übrigens, dass man, setzt man das Rechtekonzept richtig auf, das execute-Privileg daran an PUBLIC vergeben kann - was der jeweilige User dann tatsächlich tun kann, hängt von seinen Rechten ab. Sehr viele der PL/SQL-Pakete von Oracle sind mit Invoker's Rights erzeugt. Mehr Information zum Thema findet sich übrigens in der PL/SQL-Dokumentation:
http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#sthref1758

Bedingtes Kompilieren mit PL/SQL

Noch nicht so bekannt ist, dass PL/SQL inwzischen einen Präprozessor hat. Man kann also mit PL/SQL, ähnlich wie mit C oder C++ auch bedingtes Kompilieren durchführen. Wozu das gut ist? Nun, man kann bspw. Code für verschiedene Editionen oder Datenbankversionen erstellen. Ein Beispiel könnten Debugging-Informationen sein:

set serveroutput on 

alter session set  PLSQL_CCFLAGS = 'DEBUG_TABLE:false'
/

begin
  for i in 1..1000 loop
    $if $$DEBUG_TABLE $then
      insert into debug_table (datum, message) values (sysdate, 'Durchlauf ' ||i);
    $else
      dbms_output.put_line(sysdate||': Durchlauf '||i);
    $end
  end loop;
end;
/

01.07.07: Durchlauf 1
01.07.07: Durchlauf 2
01.07.07: Durchlauf 3
01.07.07: Durchlauf 4
01.07.07: Durchlauf 5
:

Lassen Sie diesen Code laufen, ohne die Tabelle DEBUG_TABLE anzulegen. Normalerweise würde man einen Fehler erwarten ... aber der Code läuft durch und die Ausgaben erscheinen auf dem Bildschirm. Ändert man allerdings den ALTER SESSION-Befehl zu Beginn, dann sieht das Ergebnis anders aus:

set serveroutput on 

alter session set  PLSQL_CCFLAGS = 'DEBUG_TABLE:true'
/

begin
  for i in 1..1000 loop
    $if $$DEBUG_TABLE $then
      insert into debug_table (datum, message) values (sysdate, 'Durchlauf ' ||i);
    $else
      dbms_output.put_line(sysdate||': Durchlauf '||i);
    $end
  end loop;
end;
/

FEHLER in Zeile 4:
ORA-06550: Zeile 4, Spalte 19:
PL/SQL: ORA-00942: Tabelle oder View nicht vorhanden
ORA-06550: Zeile 4, Spalte 7:

Grund für den Erfolg im ersten Fall ist, dass, dass die INSERT-Anweisung erst gar nicht kompiliert wird - der Präprozessor filtert sie vorher aus. Dies kann man auch selbst überprüfen - dazu gibt es das Paket DBMS_PREPROCESSOR:

alter session set  PLSQL_CCFLAGS = 'DEBUG_TABLE:false'
/

begin
  dbms_preprocessor.print_post_processed_source(q'#
   begin
    for i in 1..1000 loop
     $if $$DEBUG $then
      insert into debug_table (datum, message) values (sysdate, 'Durchlauf ' ||i);
     $else
      dbms_output.put_line(sysdate||': Durchlauf '||i);
     $end
    end loop;
   end;#'
  );
end;
/

begin
 for i in 1..1000 loop
  dbms_output.put_line(sysdate||': Durchlauf '||i);
 end loop;
end;

Sie sehen so den tatsächlich kompilierten Code. Wenn ein Zweig also durch Präprozessor-Anweisungen ausgefiltert wurde, kann dieser durchaus ungültigen Code enthalten. Somit wird es möglich, ein- und dieselbe PL/SQL-Prozedur auf verschiedenen Umgebungen, sogar Datenbankversionen laufen zu lassen ... Ein Beispiel:

$IF DBMS_DB_VERSION.VER_LE_10_1 $THEN
   /* Code für Versionen kleiner oder gleich 10.1
$ELSIF DBMS_DB_VERSION.VER_LE_10_2 $THEN
   /* Code für Versionen kleiner oder gleich 10.2
$ELSE
   /* In Zukunft dann: Code für 11 und später
$END

OS-Umgebungsvariablen mit PL/SQL abrufen

Wie ruft man den Inhalt einer Betriebssystem-Umgebungsvariable aus PL/SQL heraus ab? Ganz einfach:

SQL> var value varchar2(4000);
SQL> exec dbms_system.get_env('ORACLE_HOME', :value);

PL/SQL procedure successfully completed.

SQL> print

VALUE
--------------------------------------------------------------------------------
/oracle/u01/app/oracle/product/10.2.0