2. NF
Werbung
Daten – Bank
5. Vorlesung
Dr. Karsten Tolle – PRG2 – SS 2014
Klausur
Bemerkungen:
• Man benötigt keine Übungspunkte um bei der
Klausur teilnehmen zu dürfen!
• Die Online-Anmeldung auf der PRG2-Seite
reicht nicht bei allen aus!
Dr. Karsten Tolle – PRG2 – SS 2014
2
4. VL …
von
Person
AusweisNr.
(0,n)
Name Vorname
lebt_in
bis
(0,n)
Ort
PLZ
Ortsname
SQL:
• create
• insert
• select
Dr. Karsten Tolle – PRG2 – SS 2014
• 1. Normalform
• Schlüssel und Funktionale
Abhängigkeiten
3
Heute …
von
Person
AusweisNr.
(0,n)
Name Vorname
lebt_in
bis
(0,n)
Ort
PLZ
Ortsname
SQL:
• create
• insert
• select
Dr. Karsten Tolle – PRG2 – SS 2014
• 1. Normalform
• Schlüssel und Funktionale
Abhängigkeiten
• 2. Normalform
Sicherheit
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 2014
5
vorrat
(Teil
Lager
Menge Strasse
Hausnr)
101
1
25
Waag.
10
102
3
410
Krugerstr.
42
Waag.
10
Brunnerstr.
105
102
1
300
F = { Teil Lager → Menge Lageradresse
Lager → Lageradresse
}4
112
10
F = { Teil Lager → Menge Strasse Hausnr
Lager → Strasse Hausnr }
Dr. Karsten Tolle – PRG2 – SS 2014
6
vorrat
F = { Teil Lager → Menge Strasse Hausnr
Lager → Strasse Hausnr }
1
(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
Probleme:
Redundanz
Einfüge-Anomalie
Lösch-Anomalie
– Lageradresse für jedes Teil im Lager
redundant gespeichert
– Lager ohne Teile kann nicht eingefügt werden
– Lager ohne Teile würden gelöscht werden
Dr. Karsten Tolle – PRG2 – SS 2014
7
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 }
Lösung: Zerlegung der Relation
vorrat (Teil Lager Menge)
Fremdschlüssel
lager (Lager
Strasse
Hausnr)
101
1
25
1
Waag.
10
102
3
410
2
RMS
10
102
1
300
3
Krugerstr.
42
112
4
10
4
Brunnerstr. 105
Dr. Karsten Tolle – PRG2 – SS 2014
8
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, A∈X
Dr. Karsten Tolle – PRG2 – SS 2014
9
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 2014
10
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
Die 2. NF ist verletzt, wenn ein Teil eines Schlüssels
ein Nicht-Schlüsselattribut funktional bestimmt.
Dr. Karsten Tolle – PRG2 – SS 2014
11
vorrat
nicht 2. NF
(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 }
2. NF
vorrat (Teil Lager Menge)
lager (Lager
Strasse
Hausnr)
101
1
25
1
Waag.
10
102
3
410
2
RMS
10
102
1
300
3
Krugerstr.
42
112
4
10
4
Brunnerstr. 105
Dr. Karsten Tolle – PRG2 – SS 2014
12
Beispiel 2
R = ABCD F = {AB→C, B→D}
B→D verletzt 2. NF
Zerlegung in:
R1 = (ABC) mit F1 = {AB→C}
R2 = (BD) mit F2 = {B→D}
Dr. Karsten Tolle – PRG2 – SS 2014
13
Übung
R = ABCDE
F = {AB→C, B→D, D→E}
B→D verletzt 2. NF
Zerlegung in:
R1 = (ABCE) und
R2 = (BD)
… dann geht D
Zerlegung in:
R1 = (ABC) und
R2 = (BD) und
R3 = (DE)
E verloren!
Dr. Karsten Tolle – PRG2 – SS 2014
14
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 2014
15
Fahrzeug
ID
Hersteller
Zugel_Ges_Gew
Führerschein
ID
HERSTELLER
ZUGEL_GES_GEW
FÜHRERSCHEIN
1
VW
3.5
B
2
MB
7.5
C1
3
MB
8.5
C
4
MAN
12
C
5
MB
12
C
6
VW
12
C
…
…
…
F = { ID R,
ZUGEL_GES_GEW
Dr. Karsten Tolle – PRG2 – SS 2014
2. NF?
FÜHRERSCHEIN}
16
Trotzdem Probleme !
ID
HERSTELLER
ZUGEL_GES_GEW
FÜHRERSCHEIN
1
VW
3.5
B
2
MB
7.5
C1
3
MB
8.5
C
4
MAN
12
C
5
MB
12
C
6
VW
12
C
…
…
…
Dr. Karsten Tolle – PRG2 – SS 2014
17
Normalisierung …
ID
HERSTELLER
ZUGEL_GES_GEW
FÜHRERSCHEIN
1
VW
bis 3.5
B
2
MB
bis 7.5
C1
3
MB
bis 8.5
C
4
MAN
bis 12
C
5
MB
bis 12
C
6
VW
bis 12
C
…
…
…
F = { ID R,
ZUGEL_GES_GEW
FÜHRERSCHEIN}
ID
HERSTELLER
ZUGEL_GES_GEW
ZUGEL_GES_GEW
FÜHRERSCHEIN
1
VW
bis 3.5
bis 3.5
B
2
MB
bis 7.5
bis 7.5
C1
3
MB
bis 8.5
bis 8.5
C
4
MAN
bis 12
bis 12
C
5
MB
bis 12
bis 12
C
6
VW
bis 12
bis 12
C
…
…
…
…
Dr. Karsten Tolle – PRG2 – SS 2014
18
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 2014
…
19
Auswirkungen!?
Evt. Erweitert um Beziehungsattribute
(0,1) oder (1,1) oder sogar Attribute
anderer Entitäten (bei 1-to-1 Bez.).
Attribute 1-n
Mapping
ER rel. Model
A1
A2
…
An
Bi
Ci
Entität_1
Normalisierung
Dr. Karsten Tolle – PRG2 – SS 2014
20
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 2014
21
Tabellen Ansicht ≠ ER-Modell
Dr. Karsten Tolle – PRG2 – SS 2014
22
Sicherheit
Jahr für Jahr kosten Datenpannen Firmen mehr Geld. Quelle: Symantec
Datenlecks zum Stöbern …
• http://www.projekt-datenschutz.de/
• http://opalkatze.wordpress.com/to-becontinue/datenpannen/ (Listen für 2009 – 2013)
• http://www.datenleck.net/
Dr. Karsten Tolle – PRG2 – SS 2014
24
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 2014
25
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.1/de/grant.html
Dr. Karsten Tolle – PRG2 – SS 2014
26
Grant und Revoke unter Workbench
Dr. Karsten Tolle – PRG2 – SS 2014
27
Mitarbeiter
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 SelectRechte an der Tabelle „Mitarbeiter“ erhalten
und so auch die Gehälter erfahren
Dr. Karsten Tolle – PRG2 – SS 2014
28
Nutzung von Views
Mitarbeiter
Empno
Vorname
Nachname
Gehalt
1
Ines
Müller
75000
2
Fred
Wagner
60000
3
Ingo
Meyer
72000
4
Max
Müller
26000
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 2014
29
Mitarbeiter
Abteilung
Empno
Vorname
Nachname
Gehalt
Depno
Name
Head
1
Ines
Müller
75000
1
Finanzen
2
2
Fred
Wagner
60000
2
Einkauf
1
3
Ingo
Meyer
72000
3
HR
1
4
Max
Müller
26000
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;
…
Arbeitet_in
Empno
Depno
1
2
1
3
2
1
3
3
4
2
…
Mitarbeiter_Dept
Empno
Nachname
Abt
1
Müller
Einkauf
1
Müller
HR
2
Wagner
Finanzen
3
Meyer
HR
4 – SS 2014
Müller
Dr. Karsten Tolle – PRG2
Mitarbeiter_Dept
Einkauf
30
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
Dr. Karsten Tolle – PRG2 – SS 2014
31
SQL Injection
… (böswilliges) Einschleusen oder
Verändern von SQL-Befehlen an die
Datenbank durch den Benutzer.
Web Related Vulnerabilities 2011
(commercial applications)
Dr. Karsten
TolleTrends
– PRG2Report
– SS 2014
Application
Security
– März 2012 - CENZIC
33
Mögliche Gefahren
Zusätzliche Bedingungen einzufügen:
Eingabe:
1 or id > 1 or name like ‘%‘
select * from mytable where id = 1 or id > 1 or name like ‘%‘??
Daten der Tabelle können ausgespäht werden.
Dr. Karsten Tolle – PRG2 – SS 2014
34
Mögliche Gefahren
Zusätzliche Statements einzufügen:
Eingabe:
1; drop mytable; #
select * from mytable where id = 1; drop mytable; # ??
Direkter Schaden an der Datenbank, eventuell Ausfall
der Applikation.
Dr. Karsten Tolle – PRG2 – SS 2014
35
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 2014
36
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
Dr. Karsten Tolle – PRG2 – SS 2014
37
Sicherheit
Auf DBMS-Ebene
Auf Programmier-Ebene
• Benutzer entsprechend
anlegen
• Bereitstellung von Views
• Überwachung
• …
• Wenig Infos nach Außen
• Mittel der verwendeten
Prog. Sprache verwenden
(z.B. PreparedStatement in
Java oder EscapeFunktionen in PHP)
• Entsprechende Tests
vorsehen
• …
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
Dr. Karsten Tolle – PRG2 – SS 2014
39
