PA152: Efektivní využívání DB 12. Další Vlastislav Dohnal PA152, Vlastislav Dohnal, FI MUNI, 2009 2 Poděkování Zdrojem materiálů tohoto předmětu jsou: Přednášky CS245, CS345, CS345 Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer Widom Stanford University, California Přednáška CS145 podle knihy Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer Widom: Database Systems: The Complete Book Kniha Andrew J. Brust, Stephen Forte: Mistrovství v programování SQL Serveru 2005 Materiály knihovny MSDN firmy Microsoft PA152, Vlastislav Dohnal, FI MUNI, 2009 3 Osnova Generování ID Prostorová data Datové typy, indexy Zabezpečení DB Přístupová práva v DB Uložené procedury Útoky na DB PA152, Vlastislav Dohnal, FI MUNI, 2009 4 Generování primárního klíče Obvykle rostoucí posloupnost čísel Příklad: student(učo, jméno, příjmení) Ad-hoc přístup 1: Zjištění aktuálního maxima maxučo := SELECT max(učo) FROM student; Zvýšení o jedna a uložení nového záznamu INSERT INTO student VALUES (maxučo+1, ‘Pepík’, ‘Všeználek’); PA152, Vlastislav Dohnal, FI MUNI, 2009 5 Generování primárního klíče Ad-hoc přístup 1: nevýhody Souběžný přístup → duplicitní hodnota Ad-hoc přístup 2: Spojení INSERT a SELECT dohromady INSERT INTO student VALUES ( (SELECT max(učo) FROM student)+1, ‘Pepík’, ‘Všeználek’ ); Problém duplicitních hodnot méně pravděpodobný. Pokud nejsou používány transakce (resp. pouze přikaz INSERT tvoří celou transakci) PA152, Vlastislav Dohnal, FI MUNI, 2009 6 Generování primárního klíče Ad-hoc přístup 3: Vytvoření pomocné tabulky klíče(tabulka VARCHAR, id INTEGER) Aktualizace poslední hodnoty UPDATE klíče SET id=id+1 WHERE tabulka=‘student’; Načtení nového id novéid := SELECT id FROM klíče WHERE tabulka=‘student’; Uložení záznamu INSERT INTO student VALUES (novéid+1, ‘Pepík’, ‘Všeználek’); PA152, Vlastislav Dohnal, FI MUNI, 2009 7 Generování primárního klíče Možné problémy při paralelním zpracování Vždy při spouštění v transakci Přístup 3 Příkaz UPDATE zamkne řádek tabulky klíče Zámek je uvolněn až po commit → hodnoty jsou správné, ale ostatní jsou blokováni Přístup 2 Příkaz INSERT provede vložení (vlastní zámek na primárním klíči) Zámek uvolněn až po commit → Ostatní blokovaní na INSERT → SELECT max je ale již proveden → duplicitní hodnota PA152, Vlastislav Dohnal, FI MUNI, 2009 8 Generování primárního klíče Ideál: použít nástroje DB PostgreSQL: datový typ SERIAL, BIGSERIAL SQLServer: datový typ IDENTITY Oracle: sekvence MySQL: auto_increment přepínač u atributu Obvykle umožňují i zjištění posledního vygenerovaného čísla Lze jej použít pro ukládání do více tabulek Např. objednávka zboží neregistrovaného uživatele → INSERT nového uživatele & INSERT jeho nové objednávky. PA152, Vlastislav Dohnal, FI MUNI, 2009 9 Generování primárního klíče Sekvence (CREATE SEQUENCE …) Generátor posloupnosti čísel Lze různě nastavit min. a max. hodnota, cyklická Funkce Nextval – nová hodnota sekvence Currval – posledně vrácená hodnota sekvence Lze použít přímo v INSERT INSERT INTO tabulka VALUES (nextval(‘sekvence’), …); PA152, Vlastislav Dohnal, FI MUNI, 2009 10 Generování primárního klíče Příklad accounts(number, branchnum, balance); Shlukovaný index nad number counter(nextkey); Vložen jeden záznam s hodnotou 1 Pro přidělování id ad-hoc metodou HW Nastavanení transakce: READ COMMITTED Viditelné pouze změny potvrzené commitem. Dual Xeon (550MHz,512Kb), 1GB RAM, RAID controller, 4x 18GB drives (10000RPM), Windows 2000. PA152, Vlastislav Dohnal, FI MUNI, 2009 11 Generování primárního klíče Dávka: vlož 100 000 záznamů do accounts Generování ID: Podpora DB: SQLServer 7 (identity) insert into accounts values (94496, 2789); Oracle 8i (sekvence) insert into accounts values (seq.nextval, 94496, 2789); Oracle (sekvence) Ad-hoc: begin transaction update counter set nextkey = nextKey+1; NewKey := select nextkey from counter; commit transaction begin transaction insert into accounts values(NewKey, 94496, 2789); commit transaction PA152, Vlastislav Dohnal, FI MUNI, 2009 12 Generování primárního klíče SQLServer 0 10 20 30 40 50 Number of concurrent insertion threads Throughput (statements/sec) system ad-hoc Oracle 0 10 20 30 40 50 Number of concurrent insertion threads Throughput (statements/sec) system ad-hoc Různý počet paralelních vkládání (x vláken) Podpora DB (system) vítězí nad ad-hoc. PA152, Vlastislav Dohnal, FI MUNI, 2009 13 Generování primárního klíče PostgreSQL CREATE TABLE vyrobek ( id SERIAL PRIMARY KEY, nazev VARCHAR(10) ); Vnitřní implementace Vytvořena sekvence vyrobek_id_seq Nastavena implicitní hodnota atributu id nextval(‘vyrobek_id_seq’) PA152, Vlastislav Dohnal, FI MUNI, 2009 14 Generování primárního klíče PostgreSQL (ručně) CREATE SEQUENCE vyrobek_id_seq; CREATE TABLE vyrobek ( id INT PRIMARY KEY DEFAULT nextval(‘vyrobek_id_seq’), nazev VARCHAR(10) ); Používání: INSERT INTO vyrobek (nazev) VALUES (‘Cívka’); INSERT INTO vyrobek (id, nazev) VALUES (DEFAULT, ‘Cívka’); PA152, Vlastislav Dohnal, FI MUNI, 2009 15 Zpracování prostorových dat Prostorová data Obvykle geografická, 2d geometrická Souřadnice X, Y x y … PA152, Vlastislav Dohnal, FI MUNI, 2009 16 Zpracování prostorových dat Typické dotazy Jaké je město na pozici ? Co se vyskytuje v okolí 5 km od ? Jaké je nejbližší místo (uložené v DB) k bodu ? Bez podpory DB Index na X, popř. na XY apod. Problematické Jak měřit vzdálenost? (např. u GPS souřadnic) Naimplementovat jako uživatelskou funkci PA152, Vlastislav Dohnal, FI MUNI, 2009 17 Zpracování prostorových dat Jiná data: úsečky, obdélníky, regiony, … Dotazy: Je bod součástí regionu, … PA152, Vlastislav Dohnal, FI MUNI, 2009 18 Zpracování prostorových dat Vhodná je podpora DB Speciální datové typy a funkce PostgreSQL Typy: point, line, box, circle, … Funkce: area(), center(), length(), … Operátory: ~= same as?, ~ contains?, ?# intersects?, … Index: R-strom SQL Server 2008 Index: Grid Oracle 9i Index: R-strom, Quad-strom PA152, Vlastislav Dohnal, FI MUNI, 2009 19 Zpracování prostorových dat Grid (mřížka) Prostor ohraničen: xmin, ymin, xmax, ymax SQL Server Rozdělení na pevný počet buněk 4x4, 8x8, 16x16 Více úrovní Převzato z Microsoft MSDN: http://msdn.microsoft.com/en-us/library/bb964712.aspx PA152, Vlastislav Dohnal, FI MUNI, 2009 20 Zpracování prostorových dat Quad-strom Vyhledávací strom, každý uzel dělí do d2 stejných oblastí (např. 2d data → 4 oblasti) Listové uzly mohou mít větší kapacitu PA152, Vlastislav Dohnal, FI MUNI, 2009 21 Zpracování prostorových dat Quad-strom Pouze pro body Rozšíření na regiony: Region je dělen podle hranic „kvadrantů“ PA152, Vlastislav Dohnal, FI MUNI, 2009 22 Zpracování prostorových dat R-strom (Rectangle Tree) Rozšíření B+ stromů na d rozměrná data Vkládání, mazání je v podstatě stejné List obsahuje několik datových prvků List je popsán minimálním pokrývajícím obdélníkem (minimum bounding rectangle - MBR) Vnitřní uzly odkazují na potomky Každý potomek má MBR Vnitřní uzel je pak popsán novým obdélníkem pokrývajícím všechny potomky MBR uzlů se mohou překrývat → vyhledávání pak musí následovat všechny odpovídající větve stromu. Každý datový prvek je uložen pouze jednou Výhoda oproti Grid, Quad-stromu PA152, Vlastislav Dohnal, FI MUNI, 2009 23 Zpracování prostorových dat R-strom ukládání jiných dat pomocí MBR (objekt jím obalím a pak uložím do stromu) PA152, Vlastislav Dohnal, FI MUNI, 2009 24 Přístupová práva Analogie se souborovým systémem Přístupová práva definována na objektech (soubor, adresář, …) Typicky: čtení, zápis, spuštění Existence subjektů, kterým se práva přidělují Typicky: vlastník, skupina, ostatní PA152, Vlastislav Dohnal, FI MUNI, 2009 25 Přístupová práva Databáze Obvykle jemnější práva než u souborového systému Specifická práva pro tabulky, sekvence, schéma, databáze, procedury, … Pohledy (views) jsou základním nástrojem pro řízení kontroly. Subjektem jsou obvykle uživatelé a skupiny (role) Často nazýváno jako authorization id Subjekt „ostatní“ je označován jako PUBLIC Povolení přístupu pro PUBLIC znamená povolení přístup komukoli. PA152, Vlastislav Dohnal, FI MUNI, 2009 26 Přístupová práva Práva pro relace (tabulky) SELECT – čtení obsahu (tj. výběr řádků) INSERT – vkládání řádků Někdy lze omezit na vybrané atributy DELETE – mazání řádků UPDATE – aktualizace řádků Někdy lze omezit na vybrané atributy PA152, Vlastislav Dohnal, FI MUNI, 2009 27 Vložení piv, které ještě nemám v evidenci. Příklad INSERT INTO Beers(name) SELECT beer FROM Sells WHERE NOT EXISTS (SELECT * FROM Beers WHERE name = beer); Požadavky: SELECT pro relace Sells a Beers INSERT pro relaci Beers Přístupová práva PA152, Vlastislav Dohnal, FI MUNI, 2009 28 Přístupová práva Omezení přístupu pomocí pohledu Relace Zamestnanci(id, jmeno, adresa, plat) Chceme chránit výši platu: CREATE VIEW ZamestnanciAdresa AS SELECT id, jmeno, adresa FROM Zamestnanci; Práva: Odebrání práva SELECT na relaci Zamestnanci Přidání práva SELECT na ZamestnanciAdresa PA152, Vlastislav Dohnal, FI MUNI, 2009 29 Přístupová práva Udílení práv GRANT ON TO ; Lze povolit i „udílení práv“ oprávnění Přidá se fráze „WITH GRANT OPTION“ PA152, Vlastislav Dohnal, FI MUNI, 2009 30 Přístupová práva Příklad (jako vlastník relace Sells provedu) GRANT SELECT, UPDATE(price) ON Sells TO sally; Nyní uživatel Sally může zobrazovat obsah relace Sells a měnit obsah atributu price. PA152, Vlastislav Dohnal, FI MUNI, 2009 31 Přístupová práva Příklad (jako vlastník relace Sells provedu) GRANT UPDATE ON Sells TO sally WITH GRANT OPTION; Nyní uživatel Sally může měnit libovolná atribut relace Sells, navíc může udělovat oprávnění dalším uživatelům. Může udělit pouze UPDATE oprávnění, např. omezené na jednotlivé atributy. PA152, Vlastislav Dohnal, FI MUNI, 2009 32 Přístupová práva Odebírání práv REVOKE ON FROM ; Daným uživatelům je odebráno určité oprávnění. Pozor uživatelé ale stále mohou mít přístup povolený → protože jim byl udělený ještě někým jiným. PA152, Vlastislav Dohnal, FI MUNI, 2009 33 Přístupová práva Odebírání práv Přidání za REVOKE CASCADE – zruší i opránění povolené uživetelem, kterému právě oprávnění odebírám RESTRICT (implicitní volba) – odebere pouze toto oprávnění Pokud existují další oprávnění udělená uživatelem, kterému právo odebírám, příkaz skončí chybou. REVOKE GRANT OPTION FOR … Zruší pouze povolení udělovat oprávnění dalším uživatelům. Bez tohoto modifikátoru je odebráno oboje. PA152, Vlastislav Dohnal, FI MUNI, 2009 34 Přístupová práva – diagram Diagramy reprezentují práva udělená kým a komu Uzel je určen Uživatelem (skupinou) Uděleným právem Povolením udělovat právo dál Vlastnictvím objektu Hrana mezi X a Y X bylo použito pro udělení oprávnění Y PA152, Vlastislav Dohnal, FI MUNI, 2009 35 Přístupová práva – diagram „AP“ označuje, že uživatel A má oprávnění P. P* – oprávnění P s povolením udílení oprávnění dalším (with grant option) P** – zdroj vzniku oprávnění P A je vlastníkem objektu, na kterém má dovoleno oprávnění P (obvykle vše (ALL)). Toto implikuje povolení udílet oprávnění dalším PA152, Vlastislav Dohnal, FI MUNI, 2009 36 Přístupová práva – diagram Vytváření hran Když A udílí P dalšímu uživateli B, pak vytvoříme hranu z AP* nebo z AP** do BP. Nebo do BP*, pokud bylo použito „with grant option“. Když A uděluje nižší oprávnění Q než je P, pak hrana vede do uzlu BQ nebo BQ*. PA152, Vlastislav Dohnal, FI MUNI, 2009 37 Přístupová práva – diagram Základní pravidlo Uživatel C má oprávnění Q, pokud existuje cesta z XP** do CQ, CQ*, CQ** a P je vyšší oprávnění než nebo stejné jako Q. Poznámka: P může být rovno Q. X může být i samo C. PA152, Vlastislav Dohnal, FI MUNI, 2009 38 Přístupová práva – diagram Mazání hran Pokud A ručí oprávnění P pro subjekt B s modifikátorem CASCADE, smaž hranu AP → BP. Pokud bylo použito RESTRICT a existuje nějaká hrana z BP, pak nedovol odebrání oprávnění. PA152, Vlastislav Dohnal, FI MUNI, 2009 39 Přístupová práva – diagram Mazaní hran Po smazání hrany se musí otestovat zda neexistují uzly, které nejsou dosažitelné z nějakého ** uzlu (tj. od vlastníka). Pokud nějaký takový uzel existuje, je z diagramu smazán. PA152, Vlastislav Dohnal, FI MUNI, 2009 40 Přístupová práva – diagram AP** A vlastní objekt s oprávněním P. BP* A: GRANT P TO B WITH GRANT OPTION CP* B: GRANT P TO C WITH GRANT OPTION CP A: GRANT P TO C PA152, Vlastislav Dohnal, FI MUNI, 2009 41 Přístupová práva – diagram AP** A: REVOKE P FROM B CASCADE BP* Nejenom, že B ztratí P*, ale i C ztratí P*. Uzly BP* a CP* jsou smazány. CP* Uzly jsou smazány, i když C udělilo P* zpět B. CP Avšak C bude stále mít oprávnění P, protože jej získalo i přímo od A. PA152, Vlastislav Dohnal, FI MUNI, 2009 42 Uložené procedury Vlastní kód provádějící nějakou činnost Např. výpočet faktoriálu, vkládání řádků do různých tabulek, vypočítání průměrného platu, … PostgreSQL CREATE FUNCTION název ([parametry,…]) [RETURNS typ] kód funkce PA152, Vlastislav Dohnal, FI MUNI, 2009 43 Uložené procedury Příklad: Výpočet průměrného platu bez zveřejnění jednotlivých platů Relace Zamestnanci(id, jmeno, adresa, plat) PostgreSQL: CREATE FUNCTION avgsal() RETURNS real AS ‘SELECT avg(plat) FROM zamestnanci’ LANGUAGE SQL; Uživatel použije pro získání průměru: SELECT avgsal(); PA152, Vlastislav Dohnal, FI MUNI, 2009 44 Uložené procedury Příklad (pokr.): Takové řešení nám ale platy nezabezpečí Je nutné provést REVOKE SELECT ON Zamestnanci FROM … GRANT EXECUTE ON FUNCTION avgsal() TO … Při provádění SELECT avgsal(); je funkce spuštěna pod aktuálním uživatelem → musí mít povolení SELECT pro Zamestnanci PA152, Vlastislav Dohnal, FI MUNI, 2009 45 Uložené procedury Kontext provádění Nastavení uživatele, kterého oprávnění se použijí Typy: Volající – provede se v kontextu uživatele, který proceduru volá (obvykle aktuální uživatel) Vlastník – provede se v kontextu vlastníka uložené procedury „určený uživatel“ – provede se v kontextu daného uživatele … PA152, Vlastislav Dohnal, FI MUNI, 2009 46 Uložené procedury Kontext provádění PostgreSQL Volající – SECURITY INVOKER Vlastník – SECURITY DEFINER Řešením je nastavit kontext vlastníka CREATE FUNCTION …. LANGUAGE SQL SECURITY DEFINER; Předpoklad: vlastník má k relaci Zamestnanci oprávnění SELECT PA152, Vlastislav Dohnal, FI MUNI, 2009 47 Útoky na DB systém Připojení z internetu Otevřené připojení na DB → používat firewall Přihlášení Slabé heslo (zejména správce) Povolení přihlášení uživatele odkudkoli Lze omezit na konkrétní uživatele, IP adresy a databáze Zabezpečené připojení Šifrování spojení pomocí SSL (obvykle podporováno) Používání jediného účtu k DB systému PA152, Vlastislav Dohnal, FI MUNI, 2009 48 Útoky na DB systém SQL injection Útok, kdy uživatel systému zadá příkazy SQL místo platných vstupních údajů ve formuláři aplikace. Souvisí zejména s používáním jediného účtu k DB Který má oprávnění správce )-: PA152, Vlastislav Dohnal, FI MUNI, 2009 49 Útoky na DB systém SQL injection příklad Aplikace zobrazí formulář pro zadání poznámky: Uživatel zadá: „Vader’; DROP TABLE zakaznik; --“ Aplikace připraví příkaz pro databázi: UPDATE zakaznik SET pozn=‘$poznamka’ WHERE id=current_user; Po doplnění vstupu se provede: UPDATE zakaznik SET pozn=‘Vader’; DROP TABLE zakaznik; --’ WHERE id=current_user;