2. NF

Daten – Bank
5. Vorlesung
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 2016
• 1. Normalform
• Schlüssel und Funktionale
Abhängigkeiten
2
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 2016
• 1. Normalform
• Schlüssel und Funktionale
Abhängigkeiten
• 2. Normalform
Sicherheit
3
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 2016
4
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
…
…
…
Dr. Karsten Tolle – PRG2 – SS 2016
Fremdschlüssel
ZUGEL_GES_GEW
FÜHRERSCHEIN
3.5
B
7.5
C1
12
C
17
C
…
…
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 2016
6
vorrat
F = { Teil Lager  Menge Strasse Hausnr
Lager  Strasse Hausnr }
(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
1
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 2016
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 2016
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 2016
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 2016
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 2016
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 2016
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}
… nicht immer ist eine Zerlegung so einfach!
Dr. Karsten Tolle – PRG2 – SS 2016
13
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 2016
14
Fahrzeug
Kennzeichen
Zugel_Ges_Gew
Führerschein
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
…
…
…
2. NF?
F = { Kennzeichen  R,
ZUGEL_GES_GEW  FÜHRERSCHEIN}
Dr. Karsten Tolle – PRG2 – SS 2016
15
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 2016
16
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
…
…
…
Dr. Karsten Tolle – PRG2 – SS 2016
Fremdschlüssel
ZUGEL_GES_GEW
FÜHRERSCHEIN
3.5
B
7.5
C1
12
C
17
C
…
…
17
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 2016
…
18
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 2016
19
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 2016
20
http://www.projekt-datenschutz.de/
Datenlecks zum Stöbern …
• http://www.datenleck.net/
• http://opalkatze.wordpress.com/to-becontinue/datenpannen/ (Listen für 2009 – 2013)
• http://www.faz.net/aktuell/wirtschaft/netzwirtsc
haft/chronik-die-wichtigsten-hackerangriffe13345391.html (2009 - 2014)
Dr. Karsten Tolle – PRG2 – SS 2016
22
http://www.heise.de/newsticker/meldung/Bundestag-verabschiedet-IT-Sicherheitsgesetz-2689526.html
Gesetz zum Nachlesen:
http://www.bmi.bund.de/SharedDocs/Downloads/DE/Gesetzestexte/it-sicherheitsgesetz.html
Dr. Karsten Tolle – PRG2 – SS 2016
23
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 2016
24
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.7/en/account-management-sql.html
Dr. Karsten Tolle – PRG2 – SS 2016
25
Grant und Revoke unter Workbench
Dr. Karsten Tolle – PRG2 – SS 2016
26
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.x
Linux (my.cnf):
/etc/my.cnf or mysql-data-dir/my.cnf
Dr. Karsten Tolle – PRG2 – SS 2016
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 2016
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 2016
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 2016
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;
• Netzwerkzugang nur erlauben, wenn nötig (siehe
Folien 27, 28)
• Firewall installieren
• show databases verbieten (my.ini):
skip_show_database=on # Global nicht dyn
• …
Siehe auch: http://dev.mysql.com/doc/refman/5.7/en/general-security-issues.html
Dr. Karsten Tolle – PRG2 – SS 2016
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 2016
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 2016
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 2016
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 2016
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 (und testen lassen!)
Siehe auch (für PHP):
– http://php.net/manual/en/security.database.sql-injection.php
Dr. Karsten Tolle – PRG2 – SS 2016
37
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 EscapeFunktionen in PHP)
• Entsprechende Tests
vorsehen
• …
Literaturhinweise
• 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.7/en/general-security-issues.html
• Advanced SQL Injection in MySQL (deutsch)
–
http://www.alirecaiyekta.com/uploads/Advanced-SQL-Injection-in-MySQL-GERMAN.pdf
Dr. Karsten Tolle – PRG2 – SS 2016
39