DR versus IR: HowTo grant privs?

Veröffentlicht von

0

Aus einem aktuellen Projekt heraus entstand die Fragestellung: Muss man immer alle Rechte einem User direkt zuweisen oder genügt es nicht einfach, eine Prozedur mit Invoker Rights zu definieren?

Vorab ein paar Begriffe:

  • Named PL/SQL Block
    Sammelbegriff für PL/SQL Code, wie z.B. Prozeduren, Funktionen und PL/SQL Packages
  • Definer Rights (DR)
    Named PL/SQL Blöcke, die mit AUTHID DEFINER definiert sind. Dies ist der Default. Wird AUTHID nicht spezifiziert, wird der Block intern so behandelt als wäre er mit AUTHID DEFINER deklariert worden.
    Wird ein Named PL/SQL Block, definiert mit Definer Rights, aufgerufen, gelten während der Ausführung die Rechte des Users, dem dieses Objekt gehört.
  • Invoker Rights (IR)
    Named PL/SQL Blöcke, die mit AUTHID CURRENT_USER definiert sind.
    Wird ein PL/SQL Block, definiert mit Invoker Rights, aufgerufen, gelten die Rechte des Aufrufers während der Ausführung.

Beim Aufruf eines DR – Named PL/SQL Blocks werden zuerst der Name des aktuellen Users wie auch seine aktiven Rollen auf den Stack gelegt. Danach werden die Rollen deaktiviert und es findet ein Wechsel des Users statt. Der neue ist jetzt der Eigentümer des Objekts (siehe oben).
Ab diesem Zeitpunkt sind dann nur noch die Rechte aktiv, die dem Eigentümer des Objekts direkt zugewiesen wurden.

Beim Aufruf eines IR – Named PL/SQL Blocks bleiben die Rollen und der User aktiv.

Soweit so gut.
Aus dieser bisher dargestellten Sicht, erscheint die Verwendung von IR – Named PL/SQL Blöcken zunächst leichter – was die Verwendung von Rollen und die damit verbundene Administration der Rechte angeht.
Wie aber weiter unten dann dargestellt, liefert die kleine Prozedur CNT einen Fehler – und dies schon beim Kompilieren.

Dieses Verhalten lässt sich mit dem Verhalten beim Kompilieren erklären:
Wenn ein Named PL/SQL Block kompiliert wird – unabhängig davon, ob er als IR oder DR definiert wurde – während der Kompilierung betrachtet Oracle diesen immer als DR – Named PL/SQL Block.
Enthält dieser nun ein statisches SQL Statement, so wird dies beim Kompilieren – ohne die Rechte, die der User in den Rollen hat – geparsed. Und genau das ist der Grund, warum die Prozedur CNT fehlschlägt: da zum Zeitpunkt des Kompilierens nicht genügend Rechte vorhanden sind.
Um dieses Problem zu umgehen, bestehen prinzipiell zwei Möglichkeiten:

  1. man weist dem Eigentümer des Objektes alle notwendigen Rechte direkt zu
  2. man nutzt kein statisches SQL

Für den Fall, dass das SQL nicht statisch vorliegt, kann der Kompiler dies auch nicht validieren. Dies passiert erst zum Zeitpunkt der Ausführung. Im Falle eines IR – Named PL/SQL Blocks sind aber zum Zeitpunkt der Ausführung alle Rollen wieder aktiv und die Validierung schlägt nicht fehl. Das ist dann der Grund, warum die Prozedur CNTDYN fehlerfrei funktioniert.

 

Zusammenfassung:

  • für den Fall, dass der Eigentümer des oder der Objekte zu viele Rechte direkt benötigen sollte, es aber schon vorgefertigte Rollen gibt, wie z.B. „SELECT_CATALOG_ROLE“, so ist empfehlenswert diese Rechte in Rollen zu kapseln und den Named PL/SQL Block mit Invoker Rights zu definieren. Zu berücksichtigen ist dann, dass alle SQL Statements dynamisch ausgeführt werden müssen.
  • handelt es sich nur um ein paar Rechte, kann es durchaus Sinn ergeben, einen Named PL/SQL Block mit Definer Rights zu definieren, um dem PL/SQL Parser auch gleich die Möglichkeit zu geben, möglichst viele Überprüfungen zur Laufzeit vorzunehmen.
  • gleiches wie zuvor gilt, wenn es darum handelt, sehr vielen Personen zu erlauben, DML Operationen auf Objekten eines Users durchzuführen. Durch die Nutzung eines DR – Named PL/SQL Blocks hat man eine gute Schnittstelle für die User, die deren Zugriff einschränken kann – je nach Bedarf. Auf der anderen Seite benötigt man dann nicht, jedem User alle Rechte auf die Objekte zu geben. Ein einfache „grant execute on … to public“ genügt.

 

Beispiel:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- as User SYS:
CREATE USER owner IDENTIFIED BY owner;
CREATE USER reader IDENTIFIED BY reader;
GRANT CONNECT,resource TO owner,reader;
 
CREATE TABLE owner.readme(txt varchar2(1000 CHAR));
INSERT INTO owner.readme VALUES('liesmich doch bitte');
commit;
 
CREATE ROLE rol_owner;
GRANT SELECT ON owner.readme TO rol_owner;
GRANT rol_owner TO reader;
 
CONNECT reader/reader
 
-- funktioniert:
--
SELECT * FROM owner.readme;

Das folgende Beispiel liefert Fehler – wegen Parsing (siehe oben):

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE OR REPLACE PROCEDURE CNT 
	authid CURRENT_USER
IS
BEGIN
  dbms_output.put_line('START');
 
  FOR r IN (SELECT txt FROM owner.readme) loop
    dbms_output.put_line('>'||r.txt||'<');
  END loop;
 
  dbms_output.put_line('ENDE');
END;
/
SHOW error

Und als Ergebnis:

1
2
3
4
5
6
7
8
9

Warning: Procedure created with compilation errors.
Errors for PROCEDURE CNT:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/13     PL/SQL: SQL Statement ignored
7/35     PL/SQL: ORA-00942: table or view does not exist
8/5      PL/SQL: Statement ignored
8/31     PLS-00364: loop index variable 'R' use is invalid

Dagegen funktioniert dieses Beispiel mit dynamischen SQL – solange es mit Invoker Rights definiert ist:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

CREATE OR REPLACE PROCEDURE CNTDYN
	authid CURRENT_USER 
IS
  c_sql constant varchar2(1000 CHAR) := 'select txt from owner.readme';
 
  TYPE ref_cursor IS REF CURSOR;
  cur REF_CURSOR;
 
  v_txt  VARCHAR2(2000 CHAR);
BEGIN
  dbms_output.put_line('START');
  EXECUTE immediate c_sql INTO v_txt;
  dbms_output.put_line('>'||v_txt||'<');
  dbms_output.put_line('ENDE');
 
  dbms_output.put_line('START');
  OPEN cur FOR c_sql;
  LOOP
    FETCH cur 
    INTO v_txt;
    EXIT WHEN cur%NOTFOUND;
    dbms_output.put_line('>'||v_txt||'<');
 
  END LOOP;
 
  CLOSE cur;
  dbms_output.put_line('ENDE');
END;
/
SHOW error

Dieses Beispiel mit dynamischen SQL und Definier Rights funktioniert nicht. Es kann zwar kompiliert werden, bei der Ausführung kommt es dann aber zu einem Fehler, da aufgrund der Definer Rights (DR) die Rolle nicht aktiv sind und somit der Eigentümer keine Rechte (in diesem Beispiel) hat:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

CREATE OR REPLACE PROCEDURE CNTDYN
	authid definer 
IS
  c_sql constant varchar2(1000 CHAR) := 'select txt from owner.readme';
 
  TYPE ref_cursor IS REF CURSOR;
  cur REF_CURSOR;
 
  v_txt  VARCHAR2(2000 CHAR);
BEGIN
  dbms_output.put_line('START');
  EXECUTE immediate c_sql INTO v_txt;
  dbms_output.put_line('>'||v_txt||'<');
  dbms_output.put_line('ENDE');
 
  dbms_output.put_line('START');
  OPEN cur FOR c_sql;
  LOOP
    FETCH cur 
    INTO v_txt;
    EXIT WHEN cur%NOTFOUND;
    dbms_output.put_line('>'||v_txt||'<');
 
  END LOOP;
 
  CLOSE cur;
  dbms_output.put_line('ENDE');
END;
/
SHOW error

Als Fehlermeldung bekommt man:

1
2
3
4

ERROR at line 1:
ORA-00942: TABLE OR VIEW does NOT exist
ORA-06512: at "READER.CNTDYN", line 12
ORA-06512: at line 1

Viel Spaß beim Ausprobieren !

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.