5.3.5 CREATE / DROP VIEW Sichten/Views als Sprachmittel zur
Werbung
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
