Zugriff aus Stored Procedures und Views auf zentrale

51_57_215790_DOAG_Q3
26.07.2007
12:38 Uhr
Seite 51
Stored Procedures
Best Practice
Zugriff aus Stored Procedures und Views
auf zentrale Objekte via Rollen-Privilegien
Autor: Dr. Kurt Franke, debitel AG
In unserem Data Warehouse haben die Fachbereichs-User eigene, durch Quotas limitierte
Bereiche, in denen sie Tabellen anlegen und
befüllen können, ohne dass die IT daran
beteiligt ist. Der Zugriff auf zentrale DWHObjekte erfolgt grundsätzlich über Rollen-Privilegien. Dabei werden dir Rechte auf Tabellen
und Views jeweils an thematische Rollen
vergeben. Die erforderlichen thematischen
Rollen wiederum gehen an die Benutzergruppen-Rollen. Erst über die Zuteilung dieser
Rollen erhalten die Fachbereichs-User ihre
Zugriffsrechte.
Dieses Prinzip erlaubt eine Rechteverwaltung mit geringstem Aufwand. Neue Fachbereichs-User lassen sich sehr
einfach und nahezu ohne zusätzliche Arbeit anlegen.
Diese Fachbereichs-User können über die Verknüpfung der
Daten aus den zentralen DWH-Objekten mit den eigenen
Daten grundsätzlich auch weitere Informationen aus den
Daten gewinnen.
An dieser Stelle offenbart sich aber das dabei auftretende Problem: Die Fachbereichs-User können keine Views
anlegen, in denen zentrale DWH-Objekte referenziert werden. Auch eine Automatisierung von Auswertungen mit
Zugriff auf zentrale DWH-Objekte in Stored Procedures ist
nicht möglich. Dies liegt daran, dass es eine grundsätzliche
Einschränkung bei über Rollen zugeteilten Rechten gibt:
Diese sind prinzipiell weder in Views noch in DEFINERRIGHTS-Stored-Procedures vorhanden. Dort wirken jeweils
nur direkt zugeteilte Privilegien. Da auf die komfortable
Rechteverwaltung über das mehrstufige Rollensystem
keinesfalls verzichtet werden soll, muss hier eine andere
Möglichkeit für solche Zugriffe gefunden werden.
Mögliche Lösungen
Zumindest bei Stored Procedures denkt man sofort an die
Möglichkeit, INVOKER-RIGHTS zu verwenden, weil beim
Aufruf solcher Stored Procedures keine Rollen-Privilegien
abgeschaltet werden. Es zeigt sich jedoch sehr schnell, dass
sie für das bezeichnete Problem nicht geeignet sind. Zur
Kompilierung sind nämlich so genannte Template-Objekte erforderlich, auf die die Zugriffsrechte direkt vorhanden
sein müssen, weil bei der Kompilierung die über Rollen
zugänglichen Privilegien eben nicht berücksichtigt werden. Da hier auf Objekte in jeweils anderen Schemata
zugegriffen werden muss, ist dies so ohne Weiteres nicht
möglich. Man müsste sonst mit Synonymen arbeiten, die
zur Kompilierung auf andersnamige, aber identisch struk-
www.doag.org
turierte, eigene Objekte zeigen, die allerdings nach der
erfolgreichen Kompilierung zum Zugriff auf die eigentlichen Ziel-Objekte umdefiniert werden könnten. Dies ist
jedoch nicht möglich, ohne ein solches Synonym vorher
zu löschen, was automatisch zur Invalidierung der Stored
Procedures führt.
Es bietet sich jedoch die Möglichkeit, auf zentrale
DWH-Objekte via Loopback-Database-Link zuzugreifen.
Bei diesem Zugriff werden über Rollen zugeteilte Privilegien
grundsätzlich berücksichtigt. Damit sind auch Views möglich, die zentrale DWH-Objekte referenzieren.
Damit Loopback-Database-Links verwendet werden
können, muss der Init.ora-Parameter global_names zumindest für die aktuelle Session während des Zugriffs auf
FALSE gesetzt sein, da sonst der Oracle-Fehler ORA-02085
auftritt. Da dieser Parameter bei uns auf Datenbank-Ebene
auf TRUE gesetzt ist, müssen die Fachbereichs-User diese
Einstellung bei der Selection oder beim Erzeugen entsprechender Views selbst via ALTER-SESSION-Kommando
durchführen. Gleiches gilt bei der Kompilierung von
Stored Procedures mit derartigen Zugriffen und natürlich
auch bei der Erzeugung des Loopback-Database-Links selbst.
ALTER SESSION SET global_names =
FALSE;
CREATE DATABASE LINK
DWH.world.loopback
CONNECT TO scott IDENTIFIED BY tiger
USING ‘DWH.world’;
DWH.world ist die Datenbank, in der dieses Kommando
ausgeführt wird. Einen solchen Loopback-Database-Link
muss jeder Fachbereichs-User in seinem eigenen Schema
mit Connect auf dasselbe erstellen, wenn er solche Zugriffe
anwenden will. Sie erfolgen dann jeweils sowohl über die
ihm zugeteilten wie auch über Rollen zugänglichen
Rechte.
Für die Verwendung in Stored Procedures haben wir
zentral einen Mechanismus bereitgestellt, der einen Aufruf
auch bei Zugriffen über Loopback-Database-Link erlaubt,
ohne zuvor die Sessioneinstellung für global_names zu
ändern. Dazu müssen lediglich am Anfang und Ende die
parameterlosen Prozeduren dwh_access.init und dwh_
access.finish aufgerufen werden.
News Q3-2007
51
51_57_215790_DOAG_Q3
26.07.2007
Best Practice
12:38 Uhr
Seite 52
Stored Procedures
CREATE OR REPLACE PACKAGE dwh_access
AS
PROCEDURE init;
PROCEDURE finish;
END dwh_access;
/
CREATE OR REPLACE PACKAGE BODY
dwh_access
AS
global_names_state NUMBER := NULL;
initialized BOOLEAN := FALSE;
PROCEDURE init
AS
ret NUMBER;
ret_int BINARY_INTEGER;
ret_str VARCHAR2(4000);
BEGIN
IF NOT initialized THEN
ret :=
dbms_utility.get_parameter_
value('global_names',ret_int,ret_str);
IF ret != 0 THEN
BEGIN
raise_application_error(-20000,
'invalid
parameter type returned for
globals_names');
EXCEPTION
WHEN OTHERS THEN RAISE
program_error;
END;
END IF;
global_names_state := ret_int;
IF global_names_state = 1 THEN
EXECUTE IMMEDIATE 'ALTER
SESSION SET global_names = FALSE';
END IF;
initialized := TRUE;
END IF;
END init;
PROCEDURE finish
AS
CURSOR get_closable_open_db_links
IS
SELECT *
FROM v$dblink
WHERE in_transaction = 'NO';
BEGIN
FOR r IN
get_closable_open_db_links LOOP
BEGIN
EXECUTE IMMEDIATE
'ALTER SESSION CLOSE
DATABASE LINK ' || r.db_link;
EXCEPTION
52
News Q3-2007
WHEN OTHERS THEN NULL;
END;
END LOOP;
IF initialized THEN
IF global_names_state = 1 THEN
EXECUTE IMMEDIATE 'ALTER SESSION
SET global_names = TRUE';
END IF;
initialized := FALSE;
END IF;
END finish;
END dwh_access;
/
Das Package mit den beiden Prozeduren ist in einem zentralen Schema mit den erforderlichen Rechten installiert.
In der Prozedur init wird der aktuelle global_names-Wert
abgefragt und wenn erforderlich auf FALSE gesetzt. In der
finish-Prozedur werden zunächst alle nicht aktuell in Verwendung befindlichen Database-Links geschlossen und
dann der global_names-Parameter wieder auf TRUE gesetzt,
falls er zuvor diesen Wert hatte.
Die automatisierte Umstellung in den zentralen Prozeduren ermöglicht auch eine automatisierte Ausführung der
aufrufenden Stored Procedures von einem Job-Queue-Prozess aus, und das ohne weitere Umstellungsmechanismen.
Ansonsten müsste in einem solchen Fall ein Login-Trigger
verwendet werden, weil Scripte wie login.sql in einem JobQueue-Prozess niemals ausgeführt werden.
Fazit
Die beschriebene Methode ermöglicht den Zugriff auf Objekte via Rollenprivilegien aus Views und Stored Procedures
mit relativ geringen Aufwänden. Alternativ besteht noch
die Möglichkeit, in einem AFTER-GRANT-Trigger und
einem AFTER-REVOKE-Trigger jeweils eine Anpassung
aller direkt zugeteilten Objekt-Rechte an die über Rollen zugeteilten Objekt-Rechte vorzunehmen. Wenn man jedoch
hierarchische Rollenmodelle wie das beschriebene dabei
berücksichtigen will, sind die erforderlichen GRANT- bzw.
REVOKE-Kommandos nicht mehr mit einem einfachen
Select-Statement zu ermitteln. Die Berücksichtigung der
An- und Abschaltbarkeit von Rollen ist dabei überhaupt
nicht möglich.
Kontakt:
Dr. Kurt Franke
[email protected]
Neu:
Artikel aus früheren Ausgaben der DOAG News
finden Sie unter
http://email.doag.org/
www.doag.org