Datenbanken 9. Übung Aufgabe: Integritäts-Sicherheitsvorkehrungen in Oracle Lösungsschritte. Man unterscheidet: statische Integritätsbedingungen (Column bzw. Table Constraints , Assertions) und dynamische Integritätsbedingungen. 1. Statische Integritätsbedingungen Constraints CREATE TABLE tabelle ( col datentyp [DEFAULT wert] [colConstraint ... colConstraint], . . col datentyp [DEFAULT wert] [colConstraint ... colConstraint], . . [ tableConstraint, ] . . [tableConstraint]); colConstraint: ist eine Bedingung, die nur eine Spalte betrifft tableConstraint: kann mehrere Spalten betreffen. Jedes colConstraint bzw. tableConstraint hat die Form [ constraint name ] bedingung Semantische Integrität. Zur Sicherung der semantischen Integrität können 4 Arten von Constraints1 eingesetzt werden: NOT NULL Mit dieser Constraint wird sichergestellt, daß das Feld nach Neuanlage des Datensatzes auf jeden Fall ein Wert enthält DEFAULT Diese Klausel enthält einen Ausdruck, dessen Wert dem Attribut zugewiesen wird, wenn explizit kein anderer Wert zugewiesen wurde. In Wertzuweisungen der DEFAULT-Klausel dürfen SYSDATE, USER, UID oder USERENV enthalten sein. DEFAULT kann in CREATE TABLE-, ALTER TABLE- und MODIFY-Befehlen verwendet werden. UNIQUE Mit dieser Klausel kann für eine oder mehrere Spalten festgelegt werden, daß die Datensätze einer Tabelle für diese Spalte(n) eindeutige Werte besitzen müssen. Die Spalten dürfen Nullwerte enthalten. Für UNIQUE definierte Spalten wird automatisch ein UNIQUE INDEX angelegt, deshalb können in der UNIQUE-Constraint auch die Bedingungen für die Generierung eines Index wie im CREATE INDEXBefehl enthalten sein. CHECK Mit der CHECK-Constraint können Spaltenwerte dahingehend überprüft werden, ob sie den in der CHECK-Klausel definierten Regeln entsprechen Syntax: [constraint name] check (condition) „condition“ kann sich auf alle Spalten einer Tabelle beziehen. Nur einfache Bedingungen sind zugelassen. So ist es bspw. nicht erlaubt, sich auf andere Tabellen zu beziehen oder Abfragen für Bedingungsprüfungen zu formulieren. Auch SYSDATE und USER können nicht in einer Bedingung verwendet werden. Einfache Vergleiche zu Attributen und logische Verbindungen (and, or, not) sind dagegen erlaubt. Die Bedingungsprüfung schließ ein : „is not null“. 1 vgl. 2.3.2.5, 2. 1 Datenbanken Entitätsintegrität: Mit der PRIMARY KEY –Klausel werden für die Spalten des Primärschlüssels implizit die Constraints NOT NULL und UNIQUE festgelegt. Referentielle Integrität: Sie wird über die FOREIGN KEY –Klausel gesichert. Syntax: [constraint name] foreign key (column(s)) references table [(column(s))] [on delete cascade] Der „foreign key“ muß sich auf den vollständigen „primary key“ der referenzierten Tabelle beziehen. Nicht erlaubt sind Teilmengen der Attribute (Spaltenbezüge), die den „primary key“ bilden. Falls eine Constraint über „create table ...“ definiert bzw. mit „alter table ...“ hinzugefügt wurde, ist diese „constraint“ gesetzt („enabled“). Sie kann zurückgesetzt („disabled“) werden über alter table table disable constraint name | primary key | unique [column(s)] [cascade] Ein „constraint“ bzgl. eines „primary key“ kann nur zurückgesetzt werden, wenn alle "foreign constraints“, die diesen "primary key“ verwenden, zurückgesetzt wurden. Die cascade-Klausel besorgt dies automatisch. Falls eine Tabelle Referenz für einen foreign key ist, kann sie nur über das Kommando drop table tabelle cascade constraints gelöscht werden. Alle anderen Datenbankobjekte, die sich auf diese Tabelle beziehen, bleiben im DBS erhalten (z.B. Trigger), sie sind aber nicht gültig. Informationen über Integritäts-Bedingungen und deren Status (enabled, disabled) sind im „data dictionary“ in den Tabellen USER_CONSTRAINTS und USER_CONS_CONSTRAINTS vorhanden. Bsp.: Erzeugen von Tabellen der Datenbank für das Personalwesen, die die Entitätsintegrität und referentielle Integrität mit Hilfe von Constraints erreichen2. 2. Dynamische Integritätsbedingungen Oracle unterscheidet drei Trigger-Typen: DML-Trigger, Instead-of-Trigger, System-Trigger. DML-Trigger. Anweisungen aus dem DML-Bereich von SQL wie INSERT, UPDATE, DELETE Können einen DML-Trigger auslösen. Dabei kann man bei der Programmierung des Triggers bestimmen, ob die Anweisungen vor oder nach dem auslösenden Ereignis (vor oder nach dem Eintragen, Löschen, Aktualisieren also) ausgelöst werden sollen. Instead-of-Trigger. Auch Instead-of-Trigger reagieren auf DML-Operationen allerdings ausschließlich nur auf Sichten (relationale oder Objekt-Sichten). System-Trigger. Der System-Trigger ist ein Werkzeug zum Abfangen von System-Ereignissen und DDL-Befehle. Systemereignisse können bspw. das Starten und Hochfhren der Datenbank, das Anund Abmelden von Benutzern sowie Fehler sein. DML-Trigger Trigger3 sind einer Tabelle zugeordnete PL/SQL-Module, die bei einer DML-Aktion gegen die Tabelle ausgeführt werden. Es handelt sich um eigenständige, im Data Dictionary unkompiliert abgelegte Objekte. Trigger sind in Oracle PL/SQL-Prozeduren. Eine derartige Prozedur ist mit einer Tabelle assoziiert und wird automatisch vom DBS aufgerufen, wenn eine Modifikation (Ereignis, event) bzgl. dieser Tabelle auftritt. Notwendige Systemberechtigungen. Für das Anlegen eines Trigger auf eine Tabelle, muß entweder die Tabelle dem User gehören oder der User besitzt für die Tabelle die Berechtigung ALTER bzw. ALTER ANY TABLE. Außerdem muß er die Berechtigung CREATE TRIGGER besitzen. Um im Schema eines anderen Benutzers einen Trigger anzulegen, benötigt man die Systemberechtigung CREATE ANY TRIGGER. Die Systemberechtigung CREATE TRIGGER gehört zur Resource-Rolle, die von Oracle zur Verfügung gestellt wird. Eine Trigger-Definition besteht aus - dem Namen des Triggers create [or replace] trigger trigger_name 2 3 Vgl. ueb9.sql vgl. 2.3.2.5 3. 2 Datenbanken - dem "Trigger-Zeitpunkt" before | after - "triggering event(s)" insert or update [of column(s)] or delete on tabelle Außerdem lassen sich diese drei Trigger-auslösenden Ereignisse derart kombinieren, daß ein Trigger immer dann ausgelöst wird, wenn eine INSERT-, UPDATE- oder DELETE-Anweisung ausgeführt wird. - dem Trigger-Typ (optional)4 for each row Wird diese Klausel weggelassen, ist der Trigger befehlsorientiert, andernfalls zeilenorientiert. Ein befehlsorientierter Trigger wird genau einmal vor bzw. nach der Ausführung desjenigen Befehls ausgeführt, der das Ereignis auslöst. Ein zeilenorientierter Trigger wird je einmal pro Datensatz ausgeführt, der durch den Befehl verändert wurde. Nur mit einem befehlsorientierten Trigger ist es möglich, sich auf einen Attributwert vor und nach der Modifikation zu beziehen. Jeder "update"-Trigger kann dann den alten Attributwert mit :old.spalte und den neuen Attributwert mit :new.spalte referenzieren. Ein "insert"-Trigger kann den neuen Attributwert mit :new.spalte, ein "delete"-Trigger den alten Attributwert mit :old.spalte referenzieren. Man kann diese Bezüge auch in Vergleichen verwenden, z.B. "if :old.gehalt < :new.gehalt then ...". In einem "before"-Trigger ist es sogar möglich, sich auf die neuen Werte in der Zeile zu beziehen, z.B. :new.gehalt := :new.gehalt * 1.05 bzw. :new.gehalt := :old.gehalt .Derartige Modifikationen sind in zeilenorientierten "after"Triggern nicht erlaubt.. - der Trigger-Restriktion (nur für zeilenorientierte Trigger): when condition Die WHEN-Klausel kann einfache Bedingungen5 enthalten. Allerdings können auch sysdate und user verwendet werden. Bei Vergleichen zwischen altem und neuen Attributwert darf ":" weggelassen werden, z.B. old.attribut bzw. new.attribut. - dem Trigger-Körper: PL/SQL-Block Der Trigger-Körper besteht aus einem PL/SQL-Block. Alle SQL (ausgenommen commit und rollback) und PL/SQL-Kommandos dürfen in einem PL/SQL Trigger-Block verwendet werden. Die Pl/SQL Kontrollstruktur "if" kann mit den Konstrukten if inserting, if updating [(column)] und if deleting, ausgestattet werden. Auf der Grundlage aller in einer CREATE TRIGGER-Anweisung möglichen Permutationen, kann eine Tabelle bis zu 12 verschiedene Trigger aufweisen: - 6 Trigger auf Zeilenebene für BEFORE DELETE, BEFORE INSERT, BEFORE UPDATE, AFTER DELETE, AFTER INSERT, AFTER UPDATE - 6 Trigger auf Anweisungsebene für BEFORE DELETE, BEFORE INSERT, BEFORE UPDATE, AFTER DELETE, AFTER INSERT, AFTER UPDATE Ein BEFORE-Trigger auf Zeilenebene wird vor Ausführung des Trigger auslösenden Ereignisses ausgelöst. Somit kann ein BEFORE-Trigger auf Zeilenebene zur Änderung von Spaltenwerten einer Zeile eingesetzt werden. Ein AFTER-Trigger auf Zeilenebene wird nach Auftreten des Triggerauslösenden Ereignisses ausgelöst. Die Ausführung eines PL/SQL-Block von einem Trigger bildet eine Transaktion um das TriggeringEvent. So kann bspw. eine "insert"-Anweisung in einem PL/SQL-Block einen anderen Trigger auslösen, evtl. zu einem kaskadierenden Ausführen von Triggern führen. Solche Triggersequenzen terminieren erfolgreich, wenn keine Ausnahme innerhalb eines PL/SQL-Blocks auftritt und keine Integritätsbedingung verletzt wurde. Sobald ein Trigger eine Ausnahme (exception) in einem PL/SQL-Block auslöst, werden alle Modifikationen seit Beginn der Transaktion zurückgesetzt. In einem PL/SQL-Block kann eine "exception" die Anweisung raise_application_error auslösen. In Kombination mit einem zeilenorientierten Trigger kann sich raise_application_error auf alte bzw. neue Attributwerte beziehen. Aufruf von gespeicherten Prozeduren in einem Trigger: Aus dem PL/SQL-Block eines DatenbankTrigger kann eine gespeicherte Prozedur oder eine Funktion, ob eigenständig oder Teil eines Pakets, aus dem PL/SQL-Block eines Datenbank-Trigger aufgerufen werden. Aktivieren und Deaktivieren von Triggern. Deaktivieren: ALTER TRIGGER trigger_name DISABLE Aktivieren: ALTER TRIGGER trigger_name ENABLE Löschen eines Trigger: DROP TRIGGER trigger_name 4 5 row level trigger vgl. Bedingungen der CHECK-Klausel unter CONSTRAINTs 3 Datenbanken Anpassen an Fehlerbedingungen. In einem Trigger können verschiedene Fehlerbedingungen festgelegt sein. Für jede definierte Fehlerbedingung kann eine Fehlermeldung gewählt werden, die beim Anlegen dieses Fehlers angezeigt wird. Die Fehlernummern und Meldungen, die der Benutzer sieht, werden über die Prozedur RAISE_APPLICATION_ERROR eingerichtet, die sich aus jedem Trigger aufrufen lässt. Informationen über Trigger. Im Data Dictionary sind Informationen über Trigger in der Tabelle USER_TRIGGERS gespeichert. Die Informationen umfassen den Trigger-Namen, den Trigger-Typ und den Code für den PL/SQL-Block. Trigger auf Zeilenebene und Trigger auf Anweisungesebene. ROW-Level-Trigger (ZeilenebenenTrigger werden mit der optionalen Klausel FOR EACH ROW angelegt Ohne diese Klausel liegt ein Statement-Level-Trigger (Anweisungsebenen-Trigger) vor, der nur einmal je Anweisungsebene angestoßen wird. So bewirkt update job set gehalt = 1.1 * gehalt where titel = 'Systemplaner'; unter Verwendung der Row-Level-Semantik die Auslösung für jede Zeile einmal, die Statement-LevelSemantik führt zur einmaligen Auslösung je Anweisung. Zeilenorientierte Trigger sind wegen zahlreicher Reaktionen meistens kritisch. Oracle führt einen exklusiven LOCK auf die betroffene Tabelle zu Beginn einer INSERT-, UPDATE- , DELETE- Anweisung aus. Diese Kennzeichnung der Tabellen wird "mutating" genannt. Andere Benutzer sind während dieser Zeit vom Zugriff ausgeschlossen. Die einzige Möglichkeit des Zugriffs während dieser Zeit ist die Verwendung von :old.spalte und :new.spalte in Verbindung mit einem zeilenorientierten Trigger. Instead-of-Trigger Dieser Trigger dient zum Überwachen von Sichten bei INSERT-, DELETE- und UPDATEAnweisungen. Über Insted-of-Trigger wird mitgeteilt, wie die darunterliegenden Tabellen, die Bestandteil einer View sind, zu aktualisieren sind. Instead-of-Trigger lassen sich nur auf Sichten ausführen, die änderbar sind6. Die Sichten dürfen keine Mengenoperationen (UNION, MINUS), Aggregatfunktionen, Gruppierungen oder Verkettungen (GROUP BY oder START WITH) sowie DISTINCT enthalten. Definition: CREATE [OR REPLACE] TRIGGER trigger-name INTEAD OF ereignis [referenzklausel] [WHEN trigger-bedingung] [FOR EACH ROW] anweisungsabschnitt 6 vgl. Ueb7.doc 4