daten – bank 4. vorlesung - uni-frankfurt.deprg2/ss2015/...dr. karsten tolle – prg2 – ss 2015...
TRANSCRIPT
Daten – Bank
5. Vorlesung
Dr. Karsten Tolle – PRG2 – SS 2015 2
4. VL …
SQL:
• create
• insert
• select
(0,n) (0,n) Person
AusweisNr. Name Vorname
lebt_in
von bis
Ort
PLZ Ortsname
• 1. Normalform
• Schlüssel und Funktionale Abhängigkeiten
Dr. Karsten Tolle – PRG2 – SS 2015 3
Heute …
SQL:
• create
• insert
• select
(0,n) (0,n) Person
AusweisNr. Name Vorname
lebt_in
von bis
Ort
PLZ Ortsname
• 1. Normalform
• Schlüssel und Funktionale Abhängigkeiten
• 2. Normalform
Sicherheit
Dr. Karsten Tolle – PRG2 – SS 2015 4
Fahrzeuge der Firma AB
HERSTELLER KENNZEICHEN ZUGEL_GES_GEW FÜHRERSCHEIN
VW F-AB 123 3.5 B
MB F-AB 234 7.5 C1
MB F-AB 235 7.5 C1
MAN F-AB 236 12 C
MB F-AB 239 12 C
MB F-AB 230 17 C
… … … …
… aus ZUGEL_GES_GEW folgt hier FÜHRERSCHEIN: ZUGEL_GES_GEW FÜHRERSCHEIN … umgekehrt nicht! bis 17 Tonnen
Dr. Karsten Tolle – PRG2 – SS 2015 5
Fahrzeuge der Firma AB
HERSTELLER KENNZEICHEN ZUGEL_GES_GEW
VW F-AB 123 3.5
MB F-AB 234 7.5
MB F-AB 235 7.5
MAN F-AB 236 12
MB F-AB 239 12
MB F-AB 230 17
… … …
ZUGEL_GES_GEW FÜHRERSCHEIN
3.5 B
7.5 C1
12 C
17 C
… …
Fremdschlüssel
Dr. Karsten Tolle – PRG2 – SS 2015 6
F = { Teil Lager Menge Lageradresse Lager Lageradresse }
vorrat (Teil Lager Menge Strasse Hausnr)
101 1 25 Waag. 10
102 3 410 Krugerstr. 42
102 1 300 Waag. 10
112 4 10 Brunnerstr. 105
F = { Teil Lager Menge Strasse Hausnr Lager Strasse Hausnr }
Dr. Karsten Tolle – PRG2 – SS 2015 7
1
Probleme: Redundanz – Lageradresse für jedes Teil im Lager
redundant gespeichert
Einfüge-Anomalie – Lager ohne Teile kann nicht eingefügt werden
Lösch-Anomalie – Lager ohne Teile würden gelöscht werden
vorrat (Teil Lager Menge Strasse Hausnr)
101 1 25 Waag. 10
102 3 410 Krugerstr. 42
102 1 300 Waag. 10
112 4 10 Brunnerstr. 105 F = { Teil Lager Menge Strasse Hausnr Lager Strasse Hausnr }
Dr. Karsten Tolle – PRG2 – SS 2015 8
F = { Teil Lager Menge Strasse Hausnr Lager Strasse Hausnr }
Lösung: Zerlegung der Relation
vorrat (Teil Lager Menge)
101 1 25
102 3 410
102 1 300
112 4 10
lager (Lager Strasse Hausnr)
1 Waag. 10
2 RMS 10
3 Krugerstr. 42
4 Brunnerstr. 105
Fremdschlüssel
vorrat (Teil Lager Menge Strasse Hausnr)
101 1 25 Waag. 10
102 3 410 Krugerstr. 42
102 1 300 Waag. 10
112 4 10 Brunnerstr. 105
Dr. Karsten Tolle – PRG2 – SS 2015 9
Prim
Ein Attribut A heißt prim in R, wenn es in einem der Schlüsselkandidaten von R enthalten ist, sonst heiße es nicht prim.
A ist prim
X: X ist Schlüssel, AX
Dr. Karsten Tolle – PRG2 – SS 2015 10
volle funktionale Abhängigkeit
Eine funktionale Abhängigkeit X Y heißt volle funktionale Abhängigkeit, wenn für keine Teilmenge X´ X, X´ Y gilt. Y heißt dann voll funktional abhängig von X.
X Y voll funktional
X´ X : X´ Y
Dr. Karsten Tolle – PRG2 – SS 2015 11
Die 2. NF ist verletzt, wenn ein Teil eines Schlüssels ein Nicht-Schlüsselattribut funktional bestimmt.
2. Normalform
Ein Relationenschema R ist in 2. Normalform (2NF), wenn es in 1NF ist und jedes nicht prime Attribut voll funktional von jedem Schlüssel von R abhängig ist.
2. NF
A nicht prim Schlüssel A voll funktional
Dr. Karsten Tolle – PRG2 – SS 2015 12
F = { Teil Lager Menge Strasse Hausnr Lager Strasse Hausnr }
vorrat (Teil Lager Menge)
101 1 25
102 3 410
102 1 300
112 4 10
lager (Lager Strasse Hausnr)
1 Waag. 10
2 RMS 10
3 Krugerstr. 42
4 Brunnerstr. 105
vorrat (Teil Lager Menge Strasse Hausnr)
101 1 25 Waag. 10
102 3 410 Krugerstr. 42
102 1 300 Waag. 10
112 4 10 Brunnerstr. 105
nicht 2. NF
2. NF
Dr. Karsten Tolle – PRG2 – SS 2015 13
Beispiel 2
R = ABCD F = {ABC, BD}
BD verletzt 2. NF
Zerlegung in:
R1 = (ABC) mit F1 = {ABC}
R2 = (BD) mit F2 = {BD}
Dr. Karsten Tolle – PRG2 – SS 2015 14
Übung
R = ABCDE F = {ABC, BD, DE}
BD verletzt 2. NF
Zerlegung in: R1 = (ABC) und R2 = (BD) und R3 = (DE)
… dann geht D E verloren!
Zerlegung in: R1 = (ABCE) und R2 = (BD)
Dr. Karsten Tolle – PRG2 – SS 2015 15
Vorgehen beim Prüfen auf NF (WICHTIG!!!)
Ausgangspunkt: Relation R (mind. 1. NF) und Menge der FDs gegeben.
1. Welche Schlüssel gibt es? was sind die nicht primen Attribute!
2. Prüfe auf 2. NF (voll funktional abhängig von ALLEN Schlüsseln?)
Dr. Karsten Tolle – PRG2 – SS 2015 16
F = { Kennzeichen R, ZUGEL_GES_GEW FÜHRERSCHEIN}
Fahrzeug
Kennzeichen Zugel_Ges_Gew Führerschein
2. NF?
KENNZEICHEN ZUGEL_GES_GEW FÜHRERSCHEIN
F-AB 123 3.5 B
F-AB 234 7.5 C1
F-AB 235 7.5 C1
F-AB 236 12 C
F-AB 239 12 C
F-AB 230 17 C
… … …
Dr. Karsten Tolle – PRG2 – SS 2015 17
Trotzdem Probleme !
KENNZEICHEN ZUGEL_GES_GEW FÜHRERSCHEIN
F-AB 123 3.5 B
F-AB 234 7.5 C1
F-AB 235 7.5 C1
F-AB 236 12 C
F-AB 239 12 C
F-AB 230 17 C
… … …
Dr. Karsten Tolle – PRG2 – SS 2015 18
Fahrzeuge der Firma AB
HERSTELLER KENNZEICHEN ZUGEL_GES_GEW
VW F-AB 123 3.5
MB F-AB 234 7.5
MB F-AB 235 7.5
MAN F-AB 236 12
MB F-AB 239 12
MB F-AB 230 17
… … …
ZUGEL_GES_GEW FÜHRERSCHEIN
3.5 B
7.5 C1
12 C
17 C
… …
Fremdschlüssel
Dr. Karsten Tolle – PRG2 – SS 2015 19
Normalformen
Über die Normalformen (NF) werden Kriterien definiert, um Redundanzen und Anomalien zu verhindern.
Es gibt: 1 NF 2 NF 3 NF BCNF 4 NF 5 NF 6 NF
1 NF
2 NF
… 3 NF
Dr. Karsten Tolle – PRG2 – SS 2015 20
Auswirkungen!?
Entität_1
Attribute 1-n
A1 A2 … An Bi Ci
Evt. Erweitert um Beziehungsattribute (0,1) oder (1,1) oder sogar Attribute anderer Entitäten (bei 1-to-1 Bez.).
Normalisierung
Mapping ER rel. Model
Dr. Karsten Tolle – PRG2 – SS 2015 21
Normalisierung / Denormalisierung
• Normalisierung: Der Prozess des Aufspaltens von Relationen, um Anomalien/Redundanzen zu verhindern.
• Denormalisierung: Der Prozess des Zusammenlegens von Relationen, um Performanz zu gewinnen.
Dr. Karsten Tolle – PRG2 – SS 2015 22
Tabellen Ansicht ≠ ER-Modell
http://www.projekt-datenschutz.de/
Dr. Karsten Tolle – PRG2 – SS 2015 24
Datenlecks zum Stöbern …
• http://www.datenleck.net/
• http://opalkatze.wordpress.com/to-be-
continue/datenpannen/ (Listen für 2009 – 2013)
• http://www.faz.net/aktuell/wirtschaft/netzwirtschaft/chronik-die-wichtigsten-hackerangriffe-13345391.html
Dr. Karsten Tolle – PRG2 – SS 2015 25
http://www.heise.de/newsticker/meldung/Bundestag-verabschiedet-IT-Sicherheitsgesetz-2689526.html
Dr. Karsten Tolle – PRG2 – SS 2015 26
Sicherheit • 3-Schicht Architektur
Bild vom BSI IT-Grundschutz-Katalog: https://www.bsi.bund.de/DE/Themen/ITGrundschutz/ITGrundschutzKataloge/Inhalt/_content/baust/b05/b05007.html
Dr. Karsten Tolle – PRG2 – SS 2015 27
Benutzer und Rechte
• CREATE USER test IDENTIFIED BY PASSWORD ‘li720lhjLJ2' –
erzeugt einen neuen Benutzer
• GRANT select ON star_trek.* TO test – weist dem Benutzer
test die select Rechte für alle Tabellen der DB star_trek zu
• REVOKE select ON star_trek.* from test – nimmt ihm die
Rechte wieder
• GRANT select ON star_trek.offizier TO test – weist dem
Benutzer test die select Rechte für die Tabelle offizier zu
Siehe auch: http://dev.mysql.com/doc/refman/5.6/en/account-management-sql.html
Dr. Karsten Tolle – PRG2 – SS 2015 28
Grant und Revoke unter Workbench
Dr. Karsten Tolle – PRG2 – SS 2015 29
Server Variablen wie bind-address
bind-address = 127.0.0.1 // erlaubt nur Verbindungen von Localhost
Windows (my.ini):
C:\ProgramData\MySQL\MySQL Server 5.6
Linux (my.cnf):
/etc/my.cnf or mysql-data-dir/my.cnf
Dr. Karsten Tolle – PRG2 – SS 2015 30
Empno Vorname Nachname Gehalt
1 Ines Müller 75000
2 Fred Wagner 60000
3 Ingo Meyer 72000
4 Max Müller 26000
Jemand der die Vor- und Nachnamen der Mitarbeiter benötigt, würde z.B. die Select-Rechte an der Tabelle „Mitarbeiter“ erhalten und so auch die Gehälter erfahren
Mitarbeiter
Dr. Karsten Tolle – PRG2 – SS 2015 31
Nutzung von Views
Empno Vorname Nachname Gehalt
1 Ines Müller 75000
2 Fred Wagner 60000
3 Ingo Meyer 72000
4 Max Müller 26000
Mitarbeiter
CREATE VIEW Mitarbeiter_Namen AS select Vorname, Nachname from Mitarbeiter;
Vorname Nachname
Ines Müller
Fred Wagner
Ingo Meyer
Max Müller
Dr. Karsten Tolle – PRG2 – SS 2015 32 Mitarbeiter_Dept
Empno Vorname Nachname Gehalt
1 Ines Müller 75000
2 Fred Wagner 60000
3 Ingo Meyer 72000
4 Max Müller 26000
Depno Name Head …
1 Finanzen 2
2 Einkauf 1
3 HR 1
Empno Depno …
1 2
1 3
2 1
3 3
4 2
CREATE VIEW Mitarbeiter_Dept AS select m.Empno, m.Nachname, d.Name as Abt from Mitarbeiter m, Abteilung d, Arbeitet_in a where m.Empno = a.Empno and a.Depno = d.Depno;
Mitarbeiter Abteilung
Arbeitet_in
Empno Nachname Abt
1 Müller Einkauf
1 Müller HR
2 Wagner Finanzen
3 Meyer HR
4 Müller Einkauf
Mitarbeiter_Dept
Dr. Karsten Tolle – PRG2 – SS 2015 33
Absichern am Bsp. MySQL
• keine Standard Benutzer – z.B. „root“ mysql> RENAME USER root TO new_user;
• „show databases“ nicht unterstützen
• Netzwerkzugang nur erlauben, wenn nötig
• Firewall installieren
• …
Siehe auch: http://dev.mysql.com/doc/refman/5.6/en/general-security-issues.html
SQL Injection
… (böswilliges) Einschleusen oder Verändern von SQL-Befehlen an die
Datenbank durch den Benutzer.
Dr. Karsten Tolle – PRG2 – SS 2015 35 Application Security Trends Report – März 2012 - CENZIC
Web Related Vulnerabilities 2011 (commercial applications)
Dr. Karsten Tolle – PRG2 – SS 2015 36
Zusätzliche Bedingungen einzufügen:
Eingabe:
select * from mytable where id = 1 or id > 1 or name like ‘%‘??
Daten der Tabelle können ausgespäht werden.
Mögliche Gefahren
1 or id > 1 or name like ‘%‘
Dr. Karsten Tolle – PRG2 – SS 2015 37
Zusätzliche Statements einzufügen:
Eingabe:
select * from mytable where id = 1; drop mytable; # ??
Direkter Schaden an der Datenbank, eventuell Ausfall
der Applikation.
Mögliche Gefahren
1; drop mytable; #
Dr. Karsten Tolle – PRG2 – SS 2015 38
Was möglich ist hängt ab von …
• DBMS
– Erlaubt es mehrere SQL-Statements gleichzeitig auszuführen?
– Welche Zeichen werden als Kommentare interpretiert? (z.B.: -- oder #)
• Verbindungsart / Programmiersprache
– JDBC, ODBC, … ???
Dr. Karsten Tolle – PRG2 – SS 2015 39
Gegenmaßnahmen
• Positivlisten Methode – wurde eingegeben was man erwartet?
• Negativlisten Methode – Ablehnung von Schlüsselwörtern
• Gegebene Funktion in PHP: mysql_real_escape_string() Sie Maskiert alle Zeichen mit denen man aus den Anführungszeichen ausbrechen kann.
• Kombination aller 3 ist die sicherste Variante.
• Anwendung diesbezüglich testen (testen lassen) Siehe auch (für PHP):
– http://www.inside-php.de/tutorial/PHP-Sicherheit-13/SQL-Injections-vermeiden.html
– http://php.net/manual/en/security.database.sql-injection.php
Sicherheit
Auf DBMS-Ebene
• Benutzer entsprechend anlegen
• Bereitstellung von Views
• Überwachung
• …
Auf Programmier-Ebene
• Wenig Infos nach Außen
• Mittel der verwendeten Prog. Sprache verwenden (z.B. PreparedStatement in Java oder Escape-Funktionen in PHP)
• Entsprechende Tests vorsehen
• …
Dr. Karsten Tolle – PRG2 – SS 2015 41
Literaturhinweise
• Handling Java Web Application Input, Part 1 von Stephen Enright
– http://today.java.net/pub/a/today/2005/09/08/handling-java-web-app-input.html
• Advanced SQL Injection In SQL Server Applications von Chris Anley
– http://www.cgisecurity.com/lib/advanced_sql_injection.pdf – (more) http://www.encription.co.uk/downloads/more_advanced_sql_injection.pdf
• BSI zu Datenbanken – https://www.bsi.bund.de/DE/Themen/ITGrundschutz/ITGrundschutzKataloge/Inhalt/_content/baust/b05/b05007.html
• MySQL Manual zu Security – http://dev.mysql.com/doc/refman/5.6/en/general-security-issues.html
• Advanced SQL Injection in MySQL (deutsch) – http://www.alirecaiyekta.com/uploads/Advanced-SQL-Injection-in-MySQL-GERMAN.pdf