Dieser Tipp widmet sich der Virtual Private Database (VPD) und ihrer Nutzung in APEX-Anwendungen. VPD erlaubt das Einrichten zeilenbasierter Zugriffsrechte auf eine Tabelle - im Gegensatz zu einem normalen GRANT-Kommando, welcher die ganze Tabelle an einen Datenbankuser freigibt, steuert VPD den Zugriff abhängig von den Inhalten der jeweiligen Tabellenzeile. VPD ist somit die perfekte Basistechnologie für mandantenfähige Anwendungen: Alle Daten aller Mandanten liegen in ein- und derselben Tabelle - und die VPD stellt sicher, dass jeder Nutzer nur die Zeilen sehen kann, zu denen er auch die Berechtigung hat. VPD setzt die Zugriffsregeln auf Ebene der Datenbank durch - sie können also nicht durch den Zugriff über eine andere Anwendung oder gar SQL*Plus umgangen werden.

Die hier vorgestellte "Virtual Private Database" ist Teil der Enterprise Edition der Oracle-Datenbank. Wenn Sie auf einer Standard Edition oder auf OracleXE arbeiten, können Sie diesen Tipp nicht nachvollziehen.

Abbildung 1: Funktionsweise der Virtual Private Database

In der deutschsprachigen DBA Community-Seite findet sich ebenfalls ein Tipp zur Virtual Private Database, allerdings basiert dieser auf normalen Datenbank-Userkonten und ist ohne jeden Bezug zu APEX. In diesem Tipp speziell auf die Einrichtung von VPD in APEX-Anwendungen eingegangen. Als Beispiel dienen wieder die bekannten Tabellen EMP und DEPT. Zur Umsetzung einer mandantenfähigen Anwendung werden zunächst die Zugriffsregeln definiert. Die neue Beispielanwendung soll fünf Benutzer kennen

• KING darf alle Informationen sehen.
• SCOTT darf alle Informationen zur Abteilung 10 (ACCOUNTING) sehen.
• MILLER darf alle Informationen zur Abteilung 20 (RESEARCH) sehen.
• BLAKE darf alle Informationen zur Abteilung 30 (SALES) sehen.
• TURNER darf nur seinen eigenen Datensatz sehen.

Diese Privilegien werden in einer neuen Tabelle "MY_USERS" hinterlegt. Nutzen Sie für die folgenden SQL-Skripte im SQL Workshop, mit dem SQL Developer oder SQL*Plus.

create table my_users(
 userid varchar2(50),
  class varchar2(50),
  depts varchar2(50)
);

insert into my_users values ('SCOTT','DEPTADM','10');
insert into my_users values ('BLAKE','DEPTADM','20');
insert into my_users values ('MILLER','DEPTADM','30');
insert into my_users values ('KING','ADMIN','');

Der erste Schritt zur Umsetzung der VPD ist die Einrichtung eines Session Context, welcher beim Aufbau der Datenbanksitzung initialisiert werden muss. Darin für einen eingeloggten Nutzer dessen Name, seine Privilegien und Abteilungen, für die die Privilegien gültig sind, für die Dauer der Datenbanksitzung gespeichert. Die Policy-Funktion, die weiter unten erstellt wird, liest den Session Context aus und erstellt oder erweitert anhand der vorgefundenen Informationen die WHERE-Klausel der abgesetzten SQL-Abfragen.

Der nun folgende Code erstellt zunächst den Session Context my_context und legt zugleich fest, dass dieser nur durch das PL/SQL-Paket my_login_package geändert werden darf. Restriktive Ausführungsrechte an diesem Package vorausgesetzt, wird sichergestellt, dass niemand den Session Context eigenmächtig manipuliert und so die VPD-Policies aushebelt. Beachten Sie, dass zum Erstellen des CONTEXT-Objektes das Privileg CREATE ANY CONTEXT benötigt wird.

create or replace context my_context
using my_login_package
/

create or replace package my_login_package
is
  procedure set_context(p_userid in varchar2);
end my_login_package;
/

create or replace package body my_login_package 
is
  procedure set_context(p_userid in varchar2) is
    v_class  my_users.CLASS%TYPE := 'USER';
    v_deptno my_users.DEPTS%TYPE := '';
  begin
    for cl in (select class,depts from my_users where userid = p_userid) loop
      v_class := cl.class;
      v_deptno := cl.depts;
    end loop;
    dbms_session.set_context('my_context','class', v_class);
    dbms_session.set_context('my_context','deptno', v_deptno);    
 dbms_session.set_context('my_context','userid', p_userid);
  end set_context;
end my_login_package;
/

Der Session Context enthält zwei Attribute. Das Attribut class nimmt die Rolle des Nutzers auf (DEPTADM oder ADMIN). Ist class gleich DEPTADM, so werden die DEPTNOs, für die der Nutzer der Administrator ist, hier abgelegt. Im nächsten Schritt wird die eigentliche Policy-Funktion angelegt. Die Funktion liest den Session Context mit der SQL-Funktion SYS_CONTEXT aus und erstellt dann eine SQL WHERE-Bedingung, die später von der Datenbank automatisch an jede SQL-Abfrage auf die Tabelle EMP angehängt wird.

• Hat der gerade angemeldete Nutzer das ADMIN-Privileg, so wird keine WHERE-Bedingung zurückgegeben. Im Ergebnis kann der Nutzer die ganze Tabelle sehen.
• Wenn das Privileg DEPTADM vergeben ist, so werden die einzelnen DEPTNOs aus dem Attribut depts ausgelesen und die SQL-Bedingung DEPTNO in ([DEPTNO], [DEPTNO]) zurückgegeben.
• Wenn kein Privileg vergeben wurde, so wird eine Einschränkung anhand der Tabellenspalte ENAME (ENAME=[USERID]) vorgenommen.

create or replace function my_policy(
 p_schema varchar2, p_object varchar2
) return varchar2 is
  v_sql varchar2(32767);
begin
  if sys_context('my_context','class') = 'ADMIN' then
    null;
  elsif sys_context('my_context','class') = 'DEPTADM' then
    v_sql := ' DEPTNO in ( '||sys_context('my_context','deptno')||')';
  else
    v_sql := ' ENAME = '''||sys_context('my_context','userid')||'''';
  end if;
  return v_sql;
end;
/

Nun sind alle Grundlagen gelegt: Die Aufgabe der Datenbank ist nun, bei jedem SQL-Kommando vor der Ausführung diese Policy-Funktion aufzurufen, und die zuürckgegebene zusätzliche Bedingung in die WHERE-Klausel aufzunehmen. Ausgeführt wird dann die SQL-Abfrage mit den zusätzlichen Einschränkungen. Damit dies auch wirklich geschieht, muss die Policy-Funktion zum Abschluß mit dem PL/SQL-Paket DBMS_RLS registriert werden. Ersetzen Sie im folgenden Code bitte myschema durch das Datenbankschema, in dem die Tabelle EMP und ihre Policy-Funktion liegen. Das ist typischerweise das Parsing Schema Ihrer APEX-Anwendung.

Abbildung 2: Das Datenbankschema wird im SQL Workshop oben rechts angezeigt

begin
 DBMS_RLS.ADD_POLICY(
    object_schema   => '[myschema]',
    object_name     => 'EMP',
    policy_name     => 'MY_EMP_POLICY',
    function_schema => '[myschema]',
    policy_function => 'MY_POLICY'
  );
end;

Anschließend ist die Virtual Private Database "scharf" geschaltet - ohne die richtigen Privilegien kann man die Tabelle zwar noch selektieren - man sieht jedoch keine Daten mehr.

Abbildung 3: Die VPD-Policy ist aktiv - Abfragen auf die Tabelle EMP liefern keine Daten zurück

Nun geht es daran, die VPD-Policy mit einer APEX-Anwendung konkret zu nutzen. Das Verfahren ist dabei völlig unabhängig vom verwendeten Authentifizierungsschema; Sie können also genauso mit LDAP, SSO oder anderen Login-Verfahren arbeiten; wichtig ist nur, dass die Tabelle my_users die richtigen Usernamen enthält. Der Einfachheit halber arbeiten wir in diesem Tipp aber mit Nutzerkonten im APEX-Workspace. Navigieren Sie also in den Administrationsbereich, dort zu Benutzer und Gruppen verwalten. Legen Sie dort dann die Nutzerkonten BLAKE, KING, MILLER, SCOTT und TURNER an (Abbildung 4).

Abbildung 4: Nutzerkonten für BLAKE, KING, MILLER, SCOTT und TURNER im APEX-Workspace

Legen Sie als nächstes eine neue APEX-Anwendung an. Eine Seite mit einem Bericht auf die Tabelle EMP reicht aus (Abbildung 5). Ob klassisch oder interaktiv, bleibt Ihnen überlassen.

Abbildung 5: Eine neue APEX-Anwendung wird erstellt

Wenn Sie diese neue Anwendung nun starten, werden (wie im SQL Workshop) ebenfalls keine Daten angezeigt, selbst wenn Sie sich als KING angemeldet haben. Der Grund dafür ist, dass der Session Context beim Anmelden an der Anwendung noch nicht initialisiert wurde. Dies geschieht nicht automatisch, es muss vielmehr in der Anwendung selbst konfiguriert werden. Navigieren Sie also zu den Gemeinsamen Komponenten, dann auf der rechten Seite zur Definition bearbeiten und nehmen Sie dann die Reiterkarte Sicherheit. Tragen Sie dort, wie in Abbildung 6 dargestellt, folgenden Code ein.

begin
  my_login_package.set_context(:APP_USER);
end;

Abbildung 6: Setzen des Session Context im Initialisierungsbereich einer APEX-Session

Von nun an ruft APEX automatisch vor jedem Seitenaufbau die Prozedur SET_CONTEXT im Paket MY_LOGIN_PACKAGE auf. Übergeben wird die User-ID, mit der sich der Nutzer an der Anwendung angemeldet hat (:APP_USER). Daraufhin wird der Session Context initialisiert und der Bericht zeigt nun - abhängig vom angemeldeten Nutzer - mehr oder weniger Datensätze an.

Bericht: Angemeldet als KING	Bericht: Angemeldet als BLAKE
Bericht: Angemeldet als SCOTT	Bericht: Angemeldet als TURNER

Damit ist die Umsetzung der Zugriffskontrolle mit der Virtual Private Database fertig. Und das Gute daran ist, dass es völlig losgelöst von der APEX-Anwendung stattfindet. Jede andere Anwendung kann die gleichen Zugriffsregeln nutzen - alles, was zu tun ist, ist dass zu Beginn der Session Context gesetzt wird. Gäbe es beispielsweise die Datenbankschemas SCOTT, TURNER, BLAKE und KING, so könnte der DBA einen Login-Trigger einrichten, welcher den Session Context entsprechend setzt - damit würde sich SQL*Plus genauso verhalten wie die APEX-Anwendung.

Zurück zur Community-Seite