Kapitel DB-5: Datenintegrität Semantische Datenintegrität …

Herbstsemester 2013
CS261 Web Data Management
Kapitel DB-5: Datenintegrität
H. Schuldt
Semantische Datenintegrität …
Ziel der semantischen Datenintegrität:
•
Die Datenbank soll zu jedem Zeitpunkt die Zusammenhänge und Regeln der
realen (Geschäfts-) Welt so präzise wie möglich widerspiegeln
•
Die Gewährleistung dieser Datenintegrität soll aus den Anwendungsprogrammen
herausgelöst und durch das Datenbanksystem selbst übernommen werden
T effektivere Kontrolle der Integrität
T einfachere Anwendungsprogrammierung
•
Unterstützung der semantischen Datenintegrität in SQL
– Wertebereichsbeschränkungen (durch Angabe einer Domäne)
– Constraints (Column Constraints und Table Constraints)
– Assertions
– Referentielle Integrität
– Trigger
– Views
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-2
1
Typen von Integritätsbedingungen
Man unterscheidet folgende Arten von semantischen Integritätsbedingungen:
•
Statische Integritätsbedingungen (Prädikate über dem Datenbankzustand).
Diese Integritätsbedingungen müssen zu jedem Zeitpunkt eingehalten werden.
– datenmodellinhärente Integritätsbedingungen
• Primärschlüsselbedingung
• Fremdschlüsselbedingung
– anwendungsspezifische Integritätsbedingungen
• für ein Attribut eines Tupels
• für ein Tupel
• für mehrere Tupel einer Relation
• für mehrere Relationen
•
Dynamische Integritätsbedingungen (Prädikate über Zustandsänderungen).
Die dynamischen Integritätsbedingungen müssen am Ende einer
Zustandsänderung wieder hergestellt sein.
Die logische Widerspruchsfreiheit der spezifizierten Integritätsbedingungen muss
(vom Datenbankdesigner) sichergestellt werden!
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-3
… Semantische Datenintegrität
Die Prüfung de Integritätsbedingungen kann zu zwei unterschiedlichen Zeitpunkten
erfolgen:
–
am Ende einer Datenbankoperation (SQL-Anweisung)
–
am Ende einer Transaktion (beim COMMIT WORK), also nach einer Folge
von zusammengehörenden Datenbankoperationen. Damit können
Integritätsbedingungen temporär verletzt werden.
Bei Integritätsverletzungen sind folgende Reaktionen möglich:
–
Nichtausführung bzw. Rückgängigmachen der Datenbankoperation
–
Abbruch der Transaktion (implizites ROLLBACK WORK), also einer Folge
von Datenbankoperationen
–
Ausführung von Folgeänderungen zur Wiederherstellung der Integrität
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-4
2
Beispiele für statische Integritätsbedingungen
1. Der Rabatt eines Kunden darf nicht über 50 Prozent liegen.
2. Der Rabatt eines ausländischen Kunden darf nicht über 30 Prozent liegen.
(Annahme: Es gibt ein zusätzliches Kundenattribut „Land“ in der Kundenrelation)
3. Der durchschnittliche Rabatt aller Kunden darf 30 Prozent nicht überschreiten.
4. Der Gesamtwert aller Produkte im selben Lager darf 1 Mio. CHF nicht
überschreiten.
5. Es muss mindestens ein Produkt geben.
6. Die Rechnungssumme einer Bestellung ergibt sich aus dem Produkt von Preis und
bestellter Menge des bestellten Produkts abzüglich des Kundenrabatts.
7. Der Saldo eines Kunden ist die (negative) Summe der Rechnungssummen aller
noch nicht bezahlten Bestellungen des Kunden.
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-5
Beispiele für dynamische Integritätsbedingungen
8.
Der Rabatt eines Kunden darf nie reduziert werden.
9.
Von Kunden, deren Saldo unter -100‘000.- CHF liegt, werden keine Bestellungen
mehr angenommen.
10. Der Status einer neuen Bestellung darf sich nur in „geliefert“ ändern, der Status
einer gelieferten Bestellung nur in „bezahlt“. Der Status einer bezahlten
Bestellung darf sich nie mehr ändern.
11. Der Rabatt eines Kunden darf innerhalb eines Jahres um maximal 10 Prozent
angehoben werden.
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-6
3
Integritätsbedingungen mit CREATE TABLE Statement
•
Integritätsbedingungen können zusammen mit einem CREATE TABLE Statement
entweder durch ein Column Constraint (für einzelne Attribute) und/oder ein
Table Constraint (für die gesamte Relation) angegeben werden
– Column Constraints beziehen sich auf einzelne Attribute
– Table Constraints können mehrere Attribute derselben Relation bzw.
eine komplette Relationen umfassen
•
Zulässige Integritätsbedingungen sind alle in der WHERE-Klausel der SELECTAnweisung zulässigen Suchprädikate
– Die Integritätsbedingung wird also durch die Ausführung einer Query
überprüft
•
Bedingungen beim CREATE TABLE gelten für leere Relationen immer als erfüllt
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-7
Integritätsbedingungen mit CREATE TABLE Statement
SQL-Syntaxdiagramme (Ausschnitt; Erweiterung zu 4-7)
column_constraint = [CONSTRAINT constraint_name]
[NOT NULL]
[PRIMARY KEY | UNIQUE]
[REFERENCES [user “.”] table [ “(“ column “)” ]]
[CHECK “(“ condition “)” ]
table_constraint =
[CONSTRAINT constraint_name]
[ (PRIMARY KEY | UNIQUE) “(“ column {“,” column} “)” ]
[ FOREIGN KEY “(“ column {“,” column} “)”
REFERENCES [user “.”] table [ “(“ column {“,” column} “)” ]
[CHECK “(“ condition “)” ]
Die vollständigen SQL-Syntaxdiagramme sind auf der Vorlesungswebseite verfügbar.
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-8
4
Integritätsbedingungen mit CREATE TABLE Statement
Beispiele (Bedingungen 1, 2, 3):
CREATE TABLE Kunden (
KNr
Integer
Primary Key,
Name Varchar2(30),
Stadt
Varchar2(30),
Land
Varchar2(2),
Saldo
Float,
Rabatt Float
CONSTRAINT Rabattbedingung CHECK (Rabatt BETWEEN 0.0 AND 0.5),
CONSTRAINT Auslandsrabatt
CHECK (Land = 'CH' OR Rabatt <= 0.3),
CONSTRAINT Durchschnittsrabatt
CHECK (0.3 >= (SELECT AVG(Rabatt) FROM Kunden)) )
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-9
Integritätsbedingungen mit CREATE TABLE Statement
Bedingung 4 (Der Gesamtwert aller Produkte im selben Lager darf 1 Mio. SFr. nicht
überschreiten):
CREATE TABLE Produkte (
...
CONSTRAINT Lagerwertbedingung CHECK
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-10
5
Assertions …
•
Assertions (Zusicherungen) sind für Integritätsbedingungen vorgesehen, die
relationenübergreifend sind, d.h. die mehrere Relationen betreffen.
•
Assertions werden –im Gegensatz zu den Table_Constraints– nicht im
Zusammenhang mit der Tabellendefinition erstellt sondern sind vielmehr
eigenständige Schemaelemente
•
Zulässige (Such-)Bedingungen (Search_Condition) innerhalb einer Assertion sind
dieselben, die auch innerhalb einer WHERE-Klausel erlaubt sind (also eine
Bedingung, die entweder zu true oder false ausgewertet werden kann)
•
Mit der Deferrability wird der Zeitpunkt der Prüfung festgelegt
Syntaxdiagramm für die Definition von Assertions
AssertionDef =
CREATE ASSERTION Assertion CHECK "("Search_Condition ")" [ Deferrability ]
Deferrability =
[ NOT ] DEFERRABLE | INITIALLY ( DEFFERRED | IMMEDIATE )
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-11
… Assertions …
Mögliche Zeitpunkte für die Prüfung der Integritätsbedingung von Assertions
(Deferrability)
– am Ende jeder SQL-Anweisung (bei DEFERRABLE und INITIALLY
IMMEDIATE) und bei NOT DEFERRABLE
– oder am Ende der Transaktion (bei DEFFERABLE und INITIALLY
DEFERRED),
– oder explizit durch den Programmierer innerhalb einer Transaktion mittels
SET CONSTRAINTS constraint-name IMMEDIATE bzw. ..... DEFFERRED
– Defaults sind NOT DEFERRABLE
bzw. INITIALLY IMMEDIATE bei DEFERRABLE
Reaktion bei Integritätsverletzung:
– Die SQL-Anweisung wird nicht ausgeführt bzw. rückgängig gemacht;
– bei verzögerter Prüfung wird die gesamte Transaktion zurückgesetzt.
– Eine flexiblere Reaktion ist nur für Verletzungen der
T referentiellen Integrität vorgesehen.
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-12
6
Einschub Transaktionsprogrammierung
Kurzer Vorgriff auf Aspekte der Transaktionsverwaltung
BOT: (Begin of Transaction)
C1, C2 seien NOT Deferrable,
C3 sei Deferrable Initially Immediate,
C4, C5, C6 seien Contraints mit der Angabe
Deferrable Initially Deferred
SQL-DML-1;
SET CONSTRAINTS C4 IMMEDIATE;
SET CONSTRAINTS C3 DEFERRED;
SQL-DML-2;
SQL-DML-3;
EOT: End-of-Transaction
RBT: Roll-Back-Transaction
t
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-13
… Assertions …
Bedingung 5 (Es muss mindestens ein Produkt geben):
CREATE ASSERTION Produktexistenzbedingung
CHECK (EXISTS (SELECT * FROM Produkte) )
DEFERRABLE INITIALLY DEFERRED
Bedingung 6 (Die Rechnungssumme einer Bestellung ergibt sich aus dem
Produkt von Preis und bestellter Menge des bestellten Produkts abzüglich
des Kundenrabatts):
CREATE ASSERTION Rechnungssummenbedingung
CHECK ( NOT EXISTS (
SELECT * FROM Bestellungen B, Produkte P, Kunden K
WHERE B.PNr = P.PNr AND B.KNr = K.KNr
AND B.Status = ‘neu’
AND B.Summe <> B.Menge * P.Preis * (1.0 - K.Rabatt)))
DEFERRABLE INITIALLY DEFERRED
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-14
7
… Assertions
Bedingung 7 (Der Saldo eines Kunden ist die (negative) Summe der
Rechnungssummen aller noch nicht bezahlten Bestellungen des Kunden):
CREATE ASSERTION Saldobedingung
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-15
Referentielle Integrität …
Zusammen mit der Definition von Fremdschlüsselbeziehungen lässt sich auch
angeben, wie mit die Reaktion auf Verletzungen der referentiellen Integrität
aussehen soll:
ReferentialIntegrityConstraintDef =
[ CONSTRAINT name ]
FOREIGN KEY "(" ColumnList ")" REFERENCES Table [ "(" ColumnList ")" ]
[ ON DELETE Action ] [ ON UPDATE Action ] Deferrability
Action = NO ACTION | CASCADE | SET NULL | SET DEFAULT .
Bedeutung der „Action“:
• NO ACTION: Zurückweisung der Löschung/Änderung (Default). Es wird also
keine Aktion mit dauerhaftem Ergebnis durchgeführt
• CASCADE: Löschen bzw. Ändern aller Tupel, die den Primärschlüssel des
gelöschten bzw. geänderten Tupels referenzieren
• SET NULL bzw. SET DEFAULT: Setzen des Fremdschlüssels in allen Tupeln,
die den Primärschlüssel des gelöschten bzw. geänderten Tupels referenzieren,
auf NULL bzw. auf den vereinbarten Default-Wert.
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-16
8
… Referentielle Integrität …
Gegeben sei das etwas modifizierte Beispielschema:
Kunden
Produkte
Bestellungen (Fremdschlüssel: KNr)
KNr
…
PNr
…
1
…
1
…
BestNr
1001
Monat
10
Tag
04
KNr
1
Summe
3500,00
Status
bezahlt
2
…
2
…
1002
11
18
2
1800,00
bezahlt
…
…
…
…
1003
11
21
1
9000,00
…
…
…
…
…
bezahlt
…
CREATE TABLE Bestellungen (
... ,
FOREIGN KEY KNr REFERENCES Kunden (KNr) ON DELETE SET NULL )
Was passiert nach dem Löschen von Kunde 1?
Bestellungen 1001 und 1003 erhalten den Nullwert als KNr
KNr
…
PNr
…
BestNr
Monat
Tag
2
…
1
…
1001
10
04
null
3500,00
…
…
2
…
1002
11
18
2
1800,00
bezahlt
…
…
1003
11
21
null
9000,00
bezahlt
…
…
…
…
…
HS 2013
KNr
Summe
Web Data Management (CS261) – Datenintegrität
Status
bezahlt
…
DB-5-17
… Referentielle Integrität …
Bestellposten (Fremdschlüssel: PNr)
BestNr
PNr
Menge
1001
1
4
1002
2
18
1003
1
100
1003
2
21
…
…
…
CREATE TABLE Bestellposten (
... ,
FOREIGN KEY PNr REFERENCES Produkte (PNr)
ON DELETE CASCADE,
FOREIGN KEY BestNr REFERENCES Bestellungen
(BestNr) ON DELETE NO ACTION )
Was passiert nach dem Löschen von Produkt 1?
Die Bestellposten für Produkt 1 werden gelöscht.
PNr
…
BestNr
2
…
1002
PNr
2
Menge
18
…
…
1003
2
21
…
…
…
Was passiert nach dem Löschen von Bestellung 1002?
Keine Änderung, die Löschoperation wird zurückgewiesen.
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-18
9
… Referentielle Integrität
Auch Fremdschlüsselbeziehungen lassen sich verzögert überprüfen. Die Angabe der
Deferrability (Syntax und Semantik) ist dieselbe wie bei den Assertions.
Deferrability =
[ NOT ] DEFERRABLE | INITIALLY ( DEFFERRED | IMMEDIATE )
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-19
Trigger …
Kernidee:
•
Es wird eine Folge von SQL-Anweisungen (ACTION) definiert, die vor oder nach
einer bestimmten Art von Änderungsoperationen (EVENT) und bei Erfüllung einer
spezifizierten Bedingung (CONDITION) automatisch ausgeführt wird.
•
Trigger kombinieren die auszuführende Aktion mit Event und Condition. Das
Ausführen der SQL-Anweisungen eines Triggers wird auch als „Feuern“ des
Triggers bezeichnet.
•
Vorteile von Triggern gegenüber der rein deklarativen Spezifikation von
Integritätsbedingungen:
– Es wird eine flexible Reaktion auf Integritätsverletzungen ermöglicht
(Aktionen um Integritätsverletzungen zu kompensieren)
– Es ist eine sehr spezifische Wahl der Überprüfungszeitpunkte möglich
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-20
10
… Trigger …
Syntax der Triggerdefinition
TriggerDef =
CREATE TRIGGER Trigger
( BEFORE | AFTER ) ( INSERT | DELETE | UPDATE [ OF ColumnList ] )
ON Table
[ REFERENCING OLD AS CorrelationVar NEW AS CorrelationVar ]
[ WHEN "(" SearchCondition ")" ]
"(" StatementSequence ")" .
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-21
… Trigger …
Beispiel: Bedingung 7 (Der Saldo eines Kunden ist die (negative) Summe der
Rechnungssummen aller noch nicht bezahlten Bestellungen des Kunden)
…
CREATE TRIGGER Saldoeintrag
AFTER INSERT ON Bestellungen
WHEN ( Status = 'neu')
( UPDATE Kunden SET Saldo = Saldo - Summe
WHERE Kunden.KNr = Bestellungen.KNr )
…
CREATE TRIGGER Saldoausgleich
AFTER UPDATE OF Status ON Bestellungen
WHEN ( Status = 'bezahlt' )
( UPDATE Kunden SET Saldo = Saldo + Summe
WHERE Kunden.KNr = Bestellungen.KNr )
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-22
11
… Trigger …
Die Reihenfolge, in der die Trigger „feuern“, ist unter Umständen essentiell.
•
Die durch einen Trigger ausgelöste Anweisungsfolge kann selbst wieder eine
Integritätsverletzung hervorrufen und damit andere (oder auch denselben)
Trigger feuern.
•
Trigger sind ein sehr mächtiges Konzept zur Integritätssicherung
– Man kann mit Triggern auch (eingeschränkt) Anwendungsfunktionalität
direkt in der Datenbank umsetzen.
– Man spricht auch von aktiven Datenbanken.
– Die Regeln (Event – Condition – Action) die den aktiven Datenbanken
zugrunde liegen werden auch als Produktionsregeln bezeichnet (Kurzform:
ECA-Regeln)
•
Allerdings sind Triggerspezifikationen sind aber auch potentiell sehr fehleranfällig
(da die Trigger im Datenbanksystem versteckt sind und man bei einer grossen
Anzahl Trigger rasch den Überblick verliert)
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-23
… Trigger …
Bedingung 8 (Der Rabatt eines Kunden darf nie reduziert werden):
CREATE TRIGGER Rabattmonotonie
AFTER UPDATE OF Rabatt ON Kunden
REFERENCING OLD AS KOld NEW AS KNew
WHEN ( KNew.Rabatt < KOld.Rabatt ) ( ROLLBACK WORK )
Bedingung 9 (Von Kunden, deren Saldo unter -100000 CHF liegt, werden keine
Bestellungen mehr angenommen):
CREATE TRIGGER Kundensperrung
AFTER INSERT ON Bestellungen
WHEN ( (SELECT Saldo FROM Kunden
WHERE Kunden.KNr = Bestellungen.KNr)
< -100000.0 )
( <Fehlermeldung ausgeben>; ROLLBACK WORK )
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-24
12
… Trigger
Bedingung 10 (Der Status einer neuen Bestellung darf sich nur in "geliefert" ändern,
der Status einer gelieferten Bestellung nur in "bezahlt". Der Status einer
bezahlten Bestellung darf sich nie mehr ändern):
CREATE TRIGGER Lieferung
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-25
Views (Sichten, Virtuelle Relationen) …
Idee (eine von mehreren Motivationen für das View-Konzept)
•
Die Integritätssicherung wird umso einfacher, je weniger abgeleitete
Daten explizit gespeichert werden. Solche abgeleiteten Daten (z.B. Saldo)
sollen vielmehr nur bei Bedarf berechnet werden.
•
Um die Formulierung der entsprechenden Anfragen so einfach möglich zu
machen, können abgeleitete Daten als "Views" zur Verfügung gestellt werden.
•
Views erscheinen gegenüber dem SQL-Programmierer praktisch wie gespeicherte
Relationen, ohne dass die Tupel der View wirklich gespeichert sind.
Grobsyntax zur Definition von Views:
CREATE VIEW view-name [ "(" column {"," column} ")" ]
AS Query [ WITH CHECK OPTION ]
Query = SelectBlock { [UNION | INTERSECTION | EXCEPT] SelectBlock }
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-26
13
… Views …
Beispiel für View-Definition (Übersicht über Kunden):
CREATE VIEW KundenInfo ( KNr, Name, Umsatz ) AS
SELECT Kunden.KNr, Name, SUM(Summe)
FROM Kunden, Bestellungen
WHERE Kunden.KNr = Bestellungen.KNr
GROUP BY KNr, Name
Abfrage des Umsatzes:
SELECT Umsatz FROM KundenInfo WHERE KNr = 1
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-27
… Views
•
Views können generell zur Vereinfachung von Abfragen definiert werden (analog
zu Zuweisungen in der Relationenalgebra). Auf Views können wiederum weitere
Views definiert werden.
Beispiel:
CREATE VIEW BestellungsInfo (BestNr, Monat, Tag, KNr, Kundenname, Rabatt,
PNr, Produktbez, Menge, Summe, Status)
AS SELECT BestNr, Monat, Tag, Kunden.KNr, Name, Rabatt,
Produkte.PNr, Bez, Menge, Summe, Status
FROM Bestellungen, Kunden, Produkte
WHERE Bestellungen.KNr = Kunden.KNr AND Bestellungen.PNr = Produkte.PNr
•
Die View Bestellungsinfo erlaubt einfache Anfragen über die drei zugrunde
liegenden Tabellen lässt sich damit einfache Anfrage, z.B.:
SELECT Kundenname FROM BestellungsInfo WHERE Produktbez='Platte'
•
Es könne auch neue Views definiert werden, die auf einer View aufbauen:
CREATE VIEW SuperBestellungsInfo AS
SELECT * FROM BestellungsInfo WHERE Summe > 10000.00
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-28
14
Ausführung von Operationen auf Views
•
Anfragen auf Views werden DBS-intern direkt durch Substitution in Anfragen
auf die gespeicherten Relationen transformiert (bzw. transitiv, wenn eine
View-Definition wiederum auf einer View aufbaut)
Beispiel (in Relationenalgebra):
s[Rabatt > 0.3] (SuperBestellungsInfo)
= s[Rabatt > 0.3] (s[Summe > 10000.00] (BestellungsInfo))
= s[Rabatt > 0.3] (s[Summe > 10000.00] (p[BestNr, ...]
(Kunden  Bestellungen  Produkte)))
Beispiel (in Pseudo-SQL)
SELECT * FROM SuperBestellungsInfo WHERE Rabatt > 0.3
= SELECT * FROM (SELECT * FROM BestellungsInfo WHERE Summe > 10000.00)
WHERE Rabatt > 0.3
= SELECT * FROM (SELECT * FROM ( SELECT BestNr, Monat, Tag, Kunden.KNr,
Name, Rabatt, Produkte.PNr, Bez, Menge, Summe, Status
FROM Bestellungen, Kunden, Produkte
WHERE Bestellungen.KNr = Kunden.KNr AND
Bestellungen.PNr = Produkte.PNr )
WHERE Summe > 10000.00)
WHERE Rabatt > 0.3
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-29
Änderungen über Views
•
Änderungen eines Tupels einer View sind nur möglich, wenn sie eindeutig auf
ein Tupel einer gespeicherten Relation abgebildet werden können (Gleiches gilt
für Einfügen und Löschen).
Beispiele:
1. UPDATE BestellungsInfo SET Menge = 10 WHERE BestNr = 5
ist (zumindest theoretisch) erlaubt
2. UPDATE KundenInfo SET Name = 'Baumann' WHERE KNr=1
ist theoretisch zulässig, aber in den meisten DBS nicht erlaubt
(wegen Join in View-Definition)
3. UPDATE KundenInfo SET Umsatz = Umsatz + 1000.0 WHERE KNr=1
ist nicht möglich (berechnetes Attribut)!
4. UPDATE BestellungsInfo SET Produktbez = 'Druckerpapier' WHERE PNr=1
ist theoretisch zulässig, aber in den meisten DBS nicht erlaubt!
5. UPDATE BestellungsInfo SET Produktbez = 'Druckerpapier' WHERE BestNr=1
ist theoretisch möglich, aber nicht erlaubt!
6. UPDATE BestellungsInfo SET Produktbez = 'Druckerpapier' WHERE Monat=12
ist nicht erlaubt!
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-30
15
CHECK Option für Views
•
Einfügung eines Tupels in eine View, das dort nicht sichtbar sein kann bzw.
Änderung eines View-Tupels, die dazu führt, dass das Tupel aus der View
"verschwindet", können durch Spezifikation der CHECK OPTION verboten
werden.
•
Beispiel:
CREATE VIEW SuperKunden AS
SELECT * FROM Kunden WHERE Rabatt > 0.3 WITH CHECK OPTION
INSERT INTO SuperKunden (KNr, Name, Stadt, Rabatt)
VALUES (100, 'Meier', 'Basel', 0.1)
wird daher zurückgewiesen (Kunde Meier ist kein „Superkunde“)
UPDATE SuperKunden SET Rabatt = Rabatt - 0.05 WHERE KNr = 10
wird eventuell zurückgewiesen
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-31
Views zur Datenunabhängigkeit
•
Views können auch als Mittel zur Realisierung der Datenunabhängigkeit bei
Schema-Änderungen verwendet werden
– Das Schema der eigentlichen Relation wird geändert
– Für Anwendungen, die noch das alte Schema verwenden, wird eine
entsprechende View bereit gestellt
•
Beispiel: die Relation Kunden wird in zwei Relationen aufgeteilt, weil ein Kunde
auch in verschiedenen Städten sein kann:
Kundenkonto(KNr, Name, Saldo, Rabatt)
Kundenorte(KNr, Stadt)
•
Anfragen auf die ursprüngliche Relation Kunden können bei Definition der
folgenden View wie bisher gestellt werden:
CREATE VIEW Kunden (KNr, Name, Stadt, Saldo, Rabatt) AS
SELECT K.KNr, Name, Stadt, Saldo, Rabatt
FROM Kundenkonto K, Kundenorte O
WHERE K.KNr = O.KNr
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-32
16
Wiederholung: Architektur eines DBS
•
Drei-Ebenen-Architektur zur Realisierung von
– physischer
– und logischer
Datenunabhängigkeit nach ANSI/SPARC (American National Standards Institute /
Standards Planning and Requirements Commitee)
A1
A2
Ext. Schema1
A3
A4
Ext. Schema2
A5
Anwendungsgruppen
Ext. Schema3
Externe Ebene
Logische Datenunabhängigkeit
Logisches Schema
Konzeptionelle (logische) Ebene
Physische Datenunabhängigkeit
Internes Schema
HS 2013
Interne (physische) Ebene
Web Data Management (CS261) – Datenintegrität
DB-5-33
Datenschutz und Zugriffskontrolle
Datenschutz (engl.: data privacy):
• Einschränkungen bei der Speicherung und Verarbeitung „kritischer“ Daten,
insbesondere personenbezogener Daten (Schutz der Privatsphäre von Personen)
Zugriffskontrolle / Autorisation (engl.: data security, authorization):
• Verhinderung von unbefugten Zugriffen auf gespeicherte Daten
Massnahmen der Zugriffskontrolle
1. Organisatorische Massnahmen (z.B. kontrollierter Zugang zu den Rechnerräumen)
2. Technische Massnahmen (Datenverschlüsselung, etc.)
3. Massnahmen des Betriebssystems
(die der Datenbank zugrunde liegenden Dateien bzw. Platten sind nur für das
DBS zugreifbar, also z.B. nur vom Account „Oracle“ aus.)
4. Authentifizierung des DB-Benutzers
(typischerweise durch Angabe eines Kennworts beim CONNECT)
5. Prüfung der Zugriffsrechte des DB-Benutzers beim Zugriff auf Daten
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-34
17
Prüfung von Zugriffsrechten …
•
•
Die Überprüfung von Zugriffsrechten in Datenbanken basiert zunächst auf
der Vergabe von Rechten auf Objekten (zur Ausführung von Operationen)
an Subjekte.
Die Vergabe von Rechten erfolgt in SQL durch die GRANT-Anweisung.
Grobsyntax:
GRANT ( ALL | privilege {"," privilege} ) ON ( table | view )
TO ( PUBLIC | user {"," user} ) [ WITH GRANT OPTION ]
•
Mögliche Rechte zum Zugriff auf relationale Datenbanken sind:
– SELECT
lesender Zugriff auf eine Relation
– INSERT
Einfügen in eine Relation
– UPDATE
Ändern von Tupeln einer Relation (ggf. nur bestimmte Attribute)
– DELETE
Löschen von Tupeln einer Relation
– CONNECT Verbindung zum DBS aufnehmen (Login-Recht)
– RESOURCE Anlegen neuer Relationen (ggf. mit Limit für den Plattenplatz)
– DBA
Datenbankadministration (z.B. Aufruf von Dienstprogrammen)
– EXECUTE
Ausführung eines Anwendungsprogramms
– IO_LIMIT
Beschränkung des Ressourcenverbrauchs für SQL-Anweisungen
– …
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-35
… Prüfung von Zugriffsrechten …
Beispiele:
1. An Benutzer Bob wird das Recht zur Ausführung von SELECT-Anweisungen
auf der Relation Bestellungen vergeben.
GRANT SELECT ON Bestellungen TO Bob
2. Benutzer Bob erhält das Recht zur Ausführung des Programms Lieferung.
GRANT EXECUTE Lieferung TO Bob
3. Das Programm Lieferung erhält das Recht zur Änderung der Relation
Bestellungen.
GRANT UPDATE Status ON Bestellungen TO Lieferung
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-36
18
… Prüfung von Zugriffsrechten
•
Zugriffsrechte können durch die Definition von Views noch verfeinert werden
(prädikat-orientierte Verfeinerung)
CREATE VIEW KundenBS AS
SELECT * FROM Kunden WHERE Stadt = 'Basel'
Beispiel:
• Benutzer Bob hat nur das Recht zum Lesen der Kundendaten der Stadt Basel.
GRANT SELECT ON KundenBS TO Bob
Die Prädikate zur Verfeinerung der Zugriffsrechte sind also nicht im GrantStatement enthalten sondern in einer entsprechenden View-Definition
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-37
Weitergabe/Rücknahme von Zugriffsrechten …
•
Für jedes Objekt gibt es genau ein Subjekt, den so genannten Eigentümer
(owner), das alle Rechte für das Objekt besitzt.
– Das Subjekt bleibt auch bei der Weitergabe von Zugriffsrechten der
Eigentümer des Objektes.
•
Der Empfänger der Rechte kann diese zunächst nicht weitergeben
– Ausnahme: Bei Angabe der GRANT OPTION darf der Empfänger eines Rechts
dieses selbst wiederum an andere Subjekte weitergeben
•
Weitergegebene Rechte können mit folgender Anweisung wieder zurück
genommen werden
REVOKE privilege FROM user
Beispiel: REVOKE SELECT ON Bestellungen FROM Bob
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-38
19
… Weitergabe/Rücknahme von Zugriffsrechten
Beispiel für transitiv weitergegebene Zugriffsrechte:
• Annahme: Benutzerin Alice sei der Eigentümerin der Relation ATable
Alice:
GRANT SELECT ON ATable TO Bob WITH GRANT OPTION
Bob:
GRANT SELECT ON ATable TO Carol WITH GRANT OPTION
Alice:
REVOKE SELECT ON ATable FROM Bob
Carol:
GRANT SELECT ON ATable TO Bob
… ist Bob jetzt noch berechtigt, auf ATable zuzugreifen?
•
Lösung in relationalen DBMS: REVOKE wirkt transitiv, nimmt also auch die vom
Empfänger eines Rechts an Dritte weitergegebenen Rechte wieder zurück.
T es wird ein Autorisationsgraph benötigt, um über die transitiven Weitergaben
Buch zu führen.
HS 2013
Web Data Management (CS261) – Datenintegrität
DB-5-39
20