Neue PL/SQL Features I - ACCESSIBLE BY

Obowhl PL/SQL quasi seit Version 6 Teil der Oracle Datenbank ist und es seit Version 7 die Möglichkeit gibt, PL/SQL Code als Funktionen, Prozeduren, Triggern und Packages zu speichern, entwickelt sich die Sprache doch stetig weiter. Mit diesem Blogeintrag starten wir eine kleine Reihe um Ihnen ein paar interessante neue Features seit 12c vorzustellen. Heute beginnen wir mit einer Funktion, das der Sicherheit dient, dem ACCESSIBLE BY. 

Bisher war es so, das man beliebige PL/SQL-Funktionen aufrufen konnte, wenn man der Besitzer ist oder EXECUTE-Rechte darauf hat. Nehmen wir das Beispiel-Schema HR, dort soll es eine API geben, um das Gehalt von Mitarbeitern zu ermitteln. Bisher würde man das so lösen:

SQL> show user
USER ist "HR"
SQL> create or replace package pkg_confidential_data_api
  2  as
  3
  4    function fnc_get_employee_salary(p_employee_id in employees.employee_id%TYPE) return employees.salary%TYPE;
  5
  6  end;
  7  /

Package wurde erstellt.

SQL> show err
Keine Fehler.
SQL>
SQL> create or replace package body pkg_confidential_data_api
  2  as
  3
  4    function fnc_get_employee_salary(p_employee_id in employees.employee_id%TYPE) return employees.salary%TYPE
  5    as
  6      v_salary employees.salary%TYPE;
  7    begin
  8      select salary into v_salary from employees where employee_id =  p_employee_id;
  9
 10      return v_salary;
 11
 12      exception
 13        when NO_DATA_FOUND then
 14          return null;
 15    end;
 16
 17  end;
 18  /

Package Body wurde erstellt.

SQL> grant execute on pkg_confidential_data_api to marco;

Benutzerzugriff (Grant) wurde erteilt.

Als Nutzer "MARCO" kann man dieses Package und all seine Funktionen nun ohne Einschränkungen nutzen um z.B. das Gehalt des Chefs zu ermitteln:

USER ist "MARCO"
SQL> select hr.pkg_confidential_data_api.fnc_get_employee_salary(100) from dual;

HR.PKG_CONFIDENTIAL_DATA_API.FNC_GET_EMPLOYEE_SALARY(100)
---------------------------------------------------------
                                                    24000

Die neue Syntaxerweiterung erlaubt es nun, den Zugriff zu beschränken. Es kann definiert werden, welche Objekte direkten Zugriff auf ein Package, Prozedur o.ä. haben. Im Prinzip wird eine Whitelist mit zugelassenen Objekten erstellt. Wir ergänzen das Beispiel also um diese Funktionalität und erlauben nur einer einzigen Funktion den Zugriff auf das Package. Innerhalb der Funktion wird dann der Chef herausgefiltert um das Prinzip zu veranschaulichen.

SQL> show user
USER ist "HR"
SQL> create or replace package pkg_confidential_data_api
  2  accessible by ( function fnc_get_salary )
  3  as
  4
  5    function fnc_get_employee_salary(p_employee_id in employees.employee_id%TYPE) return employees.salary%TYPE;
  6
  7  end;
  8  /

Package wurde erstellt.

SQL> show err
Keine Fehler.

SQL> create or replace package body pkg_confidential_data_api
  2  as
  3
  4    function fnc_get_employee_salary(p_employee_id in employees.employee_id%TYPE) return employees.salary%TYPE
  5    as
  6      v_salary employees.salary%TYPE;
  7    begin
  8      select salary into v_salary from employees where employee_id =  p_employee_id;
  9
 10      return v_salary;
 11
 12      exception
 13        when NO_DATA_FOUND then
 14          return null;
 15    end;
 16
 17  end;
 18  /

Package Body wurde erstellt.

SQL> show err
Keine Fehler.

SQL> create or replace function fnc_get_salary(p_employee_id in employees.employee_id%TYPE) return employees.salary%TYPE

  2  as
  3  begin
  4    if p_employee_id = 100 then
  5      return null;
  6    else
  7      return pkg_confidential_data_api.fnc_get_employee_salary(p_employee_id);
  8    end if;
  9  end;
 10  /

Funktion wurde erstellt.

SQL> grant execute on fnc_get_salary to marco;

Benutzerzugriff (Grant) wurde erteilt.

Der Benutzer "MARCO" hat also nun nach wie vor EXECUTE Rechte auf das Package als auch auf die neue Funktion FNC_GET_SALARY. Wenn man nun als MARCO versucht, eine Funktion aus dem Package aufzurufen, dann passiert folgendes:

SQL> show user
USER ist "MARCO"
SQL> select hr.pkg_confidential_data_api.fnc_get_employee_salary(100) from dual;
select hr.pkg_confidential_data_api.fnc_get_employee_salary(100) from dual
       *
FEHLER in Zeile 1:
ORA-06553: PLS-904: Ihre Berechtigungen reichen nicht aus, um auf Objekt
PKG_CONFIDENTIAL_DATA_API zuzugreifen

Der Zugriff wird also verhindert. Die neu erstellte Funktion kann man aber sehr wohl aufrufen, denn diese wiederum darf ja das Package benutzen:

SQL> select hr.fnc_get_salary(100) from dual;

HR.FNC_GET_SALARY(100)
----------------------


SQL> select hr.fnc_get_salary(101) from dual;

HR.FNC_GET_SALARY(101)
----------------------
                 17000

Auch der Besitzer des Package selbst darf nicht mehr uneingeschränkt auf das Package und seine Funktionen zugreifen:

SQL> show user
USER ist "HR"
SQL> select pkg_confidential_data_api.fnc_get_employee_salary(100) from dual;
select pkg_confidential_data_api.fnc_get_employee_salary(100) from dual
       *
FEHLER in Zeile 1:
ORA-06553: PLS-904: Ihre Berechtigungen reichen nicht aus, um auf Objekt
PKG_CONFIDENTIAL_DATA_API zuzugreifen

Das ermöglicht eine viel feiner granulierte Zugriffsteuerung auf PL/SQL-Objekte als das bisher der Fall war. In Version 12.1 konnte man nur das ACCESSIBLE BY nur auf Package-Ebene definieren, seit 12.2 geht das auch dediziert für die einzelnen Funktionen und Prozeduren innerhalb eines Package.

So können beispielsweise sehr große PL/SQL-Packages, die das simultane Bearbeiten durch mehrere Entwickler erschweren und die Zeit zum Kompilieren verschlechtern, in mehrere kleinere Packages aufgeteilt werden. Dazu müssen gegebenenfalls Teile, die vorher privat innerhalb des Package waren, öffentlich machen. Der Zugriff auf diese Teile kannnun durch das Whitelisting per ACCESSIBLE BY sehr genau gesteuert werden. 

Weiterführende Informationen erhalten Sie in der Oracle Dokumentation.