5.3.5 CREATE / DROP VIEW Sichten/Views als Sprachmittel zur

5.3.5 CREATE / DROP VIEW
Sichten/Views als Sprachmittel zur Realisierung der externen Ebene gemäß
ANSI/SPARC-Architektur
- "Entkopplung" der Benutzer auf der externen Ebene von den tiefer
liegenden Ebenen (
Datenunabhängigkeit)
- Benutzer brauchen gesamtes, oftmals sehr komplexes Datenbankschema
(konzeptuelle Ebene) nicht im Detail zu kennen
- Zugeschnittene Sichten auf individuelle Benutzergruppen und die
entsprechenden Aufgaben
- Änderungen der konzeptuellen Ebene "schlagen" nicht unbedingt mehr
unmittelbar zum Benutzer hin durch
• Lediglich Anpassung der Sichtdefinition durch DB-Administrator
bzw. den Anwendungsadministrator
• Benutzer muss dies nicht unbedingt mitbekommen bei seiner Arbeit
- Möglichkeit zur zielgerichteten Vergabe von Zugriffsrechten für den
Benutzer durch den Administrator (Datensicherheitaspekt)
• Bsp: Ein Angestellter soll stets nur seinen eigenen Datensatz in der
Angest-Relation sehen dürfen und nicht die Datensätze anderer
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 210
5.3.5 CREATE / DROP VIEW
Definition:
- Sichten/Views sind "virtuelle Relationen" basierend auf
• Basisrelationen (erzeugt mit CREATE TABLE) oder
• zuvor erzeugten Sichten/Views, d.h. beliebig schachtelbar
- Sichten werden durch SQL-Queries (SFW-Konstrukt) "on top" definiert
- Sichten unterscheiden sich nicht (wesentlich) von Basisrelationen für
den Benutzer
• SELECT-Anfragen gegen Sichten sind beliebig erlaubt
• Einschänkung allerdings bei INSERT, UPDATE und DELETE
Syntax:
- Anlegen der View:
CREATE VIEW Sichtname [(Attr1, ..., Attrn)]
AS Subselect
- Löschen der View:
DROP VIEW Sichtname
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 211
5.3.5 CREATE / DROP VIEW
Beispiel 1: Sicht auf Relation Angest, die die Spalte Beruf, Jahres-/Resturlaub
sowie die Tupel mit AbtNr>5 ausblendet
CREATE VIEW MeineSichtDerDinge AS
SELECT AngNr, Name, Wohnort, AbtNr
FROM Angest
WHERE AbtNr <= 5
- Attribute in der SELECT-Anweisung legen das Relationsschema fest
- Testausführung in MS Access ohne CREATE VIEW
Beispiel 2: Sicht auf Relation Angest, die (nur) die Nummern und Namen jener
Angestellten umfasst, die in (irgend) einem Projekt zu 100% mitarbeiten
CREATE VIEW HundertprozentigeMitarbeit AS
SELECT a.AngNr, a.Name
FROM Angest a INNER JOIN Mitarbeit m ON a.AngNr = m.AngNr
WHERE m.Prozent = 100
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 212
5.3.5 CREATE / DROP VIEW
Beispiel 3: Sicht auf virtuelle Relation HundertprozentigeMitarbeit, welche um
die Projektnummer ergänzt werden soll
CREATE VIEW HundertprozentigeMitarbeitProjektNr AS
SELECT h.AngNr, h.Name, m.ProNr
FROM HundertprozentigeMitarbeit h, Mitarbeit m
WHERE h.AngNr = m.AngNr
- Sichten dürfen auch Daten aus mehreren (Basis-)Relationen und/oder
anderen Sichten umfassen
- Beliebige Schachtelbarkeit erlaubt
Beispiel 4: Umbenennung von Attributen
CREATE VIEW MeineSichtDerDingeUmbenannt (Nr, Nachname, Ort, AbtNr) AS
SELECT AngNr, Name, Wohnort, AbtNr
FROM MeineSichtDerDinge
- Testausführung in MS Access:
SELECT AngNr AS Nr, Name AS Nachname, Wohnort AS Ort, AbtNr
FROM MeineSichtDerDinge
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 213
5.3.5 CREATE / DROP VIEW
Beispiel 5: Sicht auf Relation Mitarbeit, ergänzt um den Namen des jeweiligen
Angestellen und Projektes
CREATE VIEW MitarbeitErweitert AS
SELECT m.ProNr, m.AngNr, m.Prozent, a.Name, p.PName
FROM (Mitarbeit m INNER JOIN Angest a ON m.AngNr = a.AngNr)
INNER JOIN Projekt p ON m.ProNr = p.ProNr
- Realisierung virtueller Attribute zu Relationen
- Benutzer sieht MitarbeitErweitert als (virtuelle) Relation und kann normal
damit arbeiten, z.B. Änderung des Projektnamens
- Testausführung in MS Access
• Geänderter Projektname wird sowohl in der Basisrelation Mitarbeit
geändert als auch sofort an allen Stellen in MitarbeitErweitert
• Änderung nicht möglich bei Syntax mit impliziter JOIN-Notation:
CREATE VIEW MitarbeitErweitert AS
SELECT m.ProNr, m.AngNr, m.Prozent, a.Name, p.PName
FROM Mitarbeit m, Angest a, Projekt p
WHERE m.AngNr = a.AngNr AND m.ProNr = p.ProNr
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 214
5.3.5 CREATE / DROP VIEW
Änderung auf Sichten View-Update-Problematik
Rückabbildung auf zugrundeliegende Basisrelationen nicht immer möglich
- SQL-92 schränkt "updatability" von Sichten stark ein:
• Kein DISTINCT, keine arithmetischen Ausdrücke/Aggregate
• Kein GROUP BY / HAVING, kein JOIN
- Produkte weichen diese Restriktionen teilweise stark auf
Beispiel: Sicht auf Relation Angest mit Wohnort und dem durchschnittlichen
Resturlaub gruppiert nach Wohnorten (siehe Folie 190)
CREATE VIEW ResturlaubStatistik AS
SELECT Wohnort, AVG(Resturlaub)
FROM Angest
GROUP BY Wohnort
- Jegliche Änderungen über die Sicht nicht erlaubt!
- Rückabbildung des Durchschnitts wäre unklar
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 215
5.3.5 CREATE / DROP VIEW
Weitere Problematik bei Sicht-Änderung:
- Rückabbildung wäre möglich, aber geändertes/neues verlässt den
Definitionsbereich der Sicht
- Beispiel:
• Grundlage sei die Sicht MeineSichtDerDinge (siehe Folie 212)
• Zulässige Einfüge-Operation (will man das wirklich?)
INSERT
INTO MeineSichtDerDinge (AngNr, Name, Wohnort, AbtNr)
VALUES (666, 'Teufel', 'Hölle', 9)
- Explizites Verbieten von Änderungen außerhalb der Sicht-Definition:
CREATE VIEW Sichtname [(Attr1, ..., Attrn)]
AS Subselect
WITH CHECK OPTION
INSERT
• Änderungen der Sicht, die den in ihr
nicht sichtbaren Teil der Datenbank
beeinflussen, werden erkannt und
zurückgewiesen
Datenbanksysteme
Friedrich-Schiller-Universität Jena
DB
Sicht
UPDATE
Seite 216
5.4 Datenkontrolle mit SQL (DCL)
Vergabe bzw. Entzug von Zugriffsrechten für Datenbankobjekte
- Zugriff zu einer Relation (inkl. Daten) mit allen Rechten hat zunächst nur
der Benutzer, der sie erzeugt hat
- Situation in realen Anwendungen
• Datenbankadministrator ist "Eigentümer" der DB und damit Inhaber
aller Rechte auf allen Relationen
• Notwendigkeit zur selektiven, zielgerichteten Weitergabe von Rechten
Syntax zur Rechtevergabe (ausführlich nächste Folie):
GRANT <privileges> ON <object> TO <grantee> [WITH GRANT OPTION]
Syntax zum Rechteentzug:
REVOKE <privileges> FROM <grantee> <drop_behaviour>
- Angabe <drop_behaviour> steuert Verhalten bei abhängigen Objekten
• CASCADE: vererbte Rechte werden entzogen
• RESTRICT: Rechteentzug schlägt fehl
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 217
5.4 Datenkontrolle mit SQL (DCL)
Ausführliche Syntax für GRANT:
- Privileges: ALL, SELECT, INSERT, DELETE, UPDATE, ...
- Object: TABLE, DOMAIN, TYPE, ...
- Grantee: PUBLIC, Role, User
- WITH GRANT OPTION: Möglichkeit für den berechtigten Nutzer, das
empfangene Recht an andere Nutzer weiterzugeben
relation
GRANT
ALL
ON
PUBLIC
TO
view
DELETE
benutzer
,
SELECT
WITH GRANT OPTION
INSERT
(
UPDATE
attribut
)
,
,
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 218
5.4 Datenkontrolle mit SQL (DCL)
Beispiel 1:
- Benutzer ""KK" soll die Berechtigung zum Lesen der "Angest"-Relation
erhalten und die Möglichkeit, diese Berechtigung weiterzugeben:
- GRANT SELECT ON Angest TO KK WITH GRANT OPTION
Beispiel 2:
- Benutzer "ML" soll nur die Spalte "Wohnort" von "MeineSichtderDinge"
ändern dürfen, Weitergabe dieser Berechtigung sei unzulässig:
- GRANT UPDATE (Wohnort) ON MeineSichtderDinge TO ML
Beispiel 3:
- Jedermann soll das Recht erhalten, die "Projekt"-Relation zu lesen:
- GRANT SELECT ON Projekt TO PUBLIC
Beispiel 4:
- Benutzer "KK" wird das Leserecht auf "Angest" entzogen, anderen durch
"KK" berechtigten Nutzern ebenfalls:
- REVOKE SELECT ON Angest FROM KK CASCADE
Datenbanksysteme
Friedrich-Schiller-Universität Jena
Seite 219