Hlavní obsah
Jak zabezpečit SQL?
SQL může být báječná věc, ale také může být velmi nebezpečná. Pokud používáš SQL k přístupu k databázi nějaké aplikace, kterou používají stovky tisíc, nebo dokonce milionů uživatelů, postupuj velice opatrně - jinak můžeš omylem poškodit nebo úplně vymazat všechna data. Naštěstí existuje několik technik, díky kterým může být práce s SQL přeci jen bezpečnější.
Vyhýbání se špatným příkazům UPDATE/DELETE
Dříve než použiješ příkaz
UPDATE, spusť dotaz SELECT s tím samým příkazem WHERE, ať si ověříš, že budeš aktualizovat ten správný řádek a sloupec.Tak například, než spustíš příkaz:
UPDATE users SET deleted = true WHERE id = 1;Nejprve si to zkus takto:
SELECT id, deleted FROM users WHERE id = 1;Jakmile už se rozhodneš pro příkaz UPDATE, můžeš použít operátor LIMIT, aby bylo jisté, že se nebude aktualizovat více řádků, než chceš:
UPDATE users SET deleted = true WHERE id = 1 LIMIT 1;To samé platí i pro mazání dat:
DELETE users WHERE id = 1 LIMIT 1;Používání transakcí
Když zadáváš nějaký SQL příkaz, který nějakým způsobem mění databázi, tak vlastně začíná něco, čemu se říká transakce. Transakce je sled operací, které se berou jako jeden logický celek (něco jako bankovní transakce), a ve světě databází se takováto transakce musí řídit tzv. principy "ACID", aby bylo zaručeno, že probíhají spolehlivě.
Použití příkazů
CREATE, UPDATE, INSERT, nebo DELETE automaticky spouští transakci. Pokud je k tomu důvod, je možné zabalit několik příkazů do jedné větší transakce. Třeba pokud chceš, aby nějaký příkaz UPDATEproběhl pouze v případě, kdy proběhl i jiný příkaz UPDATE, je dobré je dát do jedné a té samé transakce.V takovém případě se používají obalovací příkazy
BEGIN TRANSACTION a COMMIT:BEGIN TRANSACTION;
UPDATE people SET husband = "Winston" WHERE user_id = 1;
UPDATE people SET wife = "Winnefer" WHERE user_id = 2;
COMMIT;Pokud databáze nemůže z nějakého důvodu provést oba příkazy
UPDATE, celou transakci zamítne a ponechá databázi ve stavu, v jakém byla před touto transakcí.Transakce lze také použít v případě, když chceš mít jistotu, že všechny příkazy probíhají na jedné a té samé verzi dat, tedy že žádné jiné transakce na měněných datech zrovna neprobíhají. Když se podíváš na sérii příkazů, polož si otázku, co by se mohlo stát, když by jiný uživatel spustil v tu samou chvíli jiné příkazy. Mohla by se data ocitnout v nějakém divném stavu? Pak je lepší použít transakci.
Tak například následující příkazy vytváří řádek, který říká, že uživatel obdržel odznáček, a poté aktualizují uživatelovu nedávnou aktivitu:
INSERT INTO user_badges VALUES (1, "Borec na SQL", "16:00");
UPDATE user SET recent_activity = "Získán odznak Borec na SQL" WHERE id = 1;Ve stejnou dobu však jiný uživatel nebo proces může udělit tomuto uživateli druhý odznáček:
INSERT INTO user_badges VALUES (1, "Výborný posluchač", "16:05");
UPDATE user SET recent_activity = "Získán odznak Výborný posluchač" WHERE id = 1;Tyto příkazy by mohly být vydány v tomto pořadí:
INSERT INTO user_badges VALUES (1, "Borec na SQL");
INSERT INTO user_badges VALUES (1, "Výborný posluchač");
UPDATE user SET recent_activity = "Získán odznak Výborný posluchač" WHERE id = 1;
UPDATE user SET recent_activity = "Získán odznak Borec na SQL" WHERE id = 1;Nedávná aktivita našeho uživatele by tak byla "Získán odznak Borec na SQL" (získání prvního odznáčku) i přesto, že nejnovější odznáček, který dostal, byl "Výborný posluchač". Sice to není žádná tragédie, ale také to není to, co bychom očekávali.
Takže místo toho můžeme tyto příkazy spustit v transakci, aby bylo jisté, že žádná jiná transakce mezitím neproběhne:
BEGIN TRANSACTION;
INSERT INTO user_badges VALUES (1, "SQL Master");
UPDATE user SET recent_activity = "Earned SQL Master badge" WHERE id = 1;
COMMIT;Vytváření záloh
Určitě dodržuj všechny tyhle tipy, ale i tak se občas může stát nehoda. A proto si spousta firem vytváří zálohy svých databází - každou hodinu, den, týden - v závislosti na velikosti jejich databáze a volném místě v úložišti. Když se stane nějaká nehoda, mohou si jednoduše naimportovat data ze starší verze databáze, kteroukoliv tabulku, která byla poškozena nebo ztracena. Tyto data mohou být sice starší, ale pořád je lepší mít starší data než žádná data.
Replikace
Podobným přístupem pak může být replikace - tedy ukládání několika kopií databáze na různých místech. Pokud je z nějakého důvodu určitá kopie nedostupná (například když do budovy, ve které je databáze uložena, udeří blesk, což se mi mimochodem stalo!), potom může být dotaz zaslán na jinou kopii databáze, která je dostupná. Pokud jsou data velice důležitá, měla by být taková databáze replikována, kvůli zachování dostupnosti. Tak například, pokud doktor v naléhavé situaci potřebuje získat seznam pacientových alergií, pak nemůže čekat na to, až inženýři obnoví data ze zálohy, on je potřebuje hned.
Nicméně se jedná o náročnější postup, který znamená pomalejší výkon takových replikovaných databází, protože každý zápis musí být proveden ve všech replikacích, takže si každá firma musí určit, zda výhody replikace stojí za náklady s replikací spojené, a přijít na nejlepší způsob, jak si tyto replikace nastavit pro své prostředí.
Udělování oprávnění
Některé databázové systémy používají uživatelské účty s nastavenými právy pro tyto uživatele, jelikož jsou uloženy na serverech, na které může přistupovat více uživatelů. To ale není případ SQL skriptů zde na Khan Academy, protože SQLite je používáno jen jedním uživatelem, který má zrovna přístup k disku, na kterém je tato databáze uložena.
Pokud však budete používat databázi na sdíleném serveru, nastavte hned na začátku uživatelská oprávnění. Obecně platí, že pouze pár vybraných uživatelů by mělo mít plný přístup do databáze (například lidé v backendu), jinak to může být velmi nebezpečné.
Zde je příklad, jak udělit uživateli plný přístup do databáze:
GRANT FULL ON TABLE users TO super_admin;Zde je příklad, jak uživateli přidělit oprávnění pouze pro SELECT:
GRANT SELECT ON TABLE users TO analyzing_user;Ve velké společnosti často ani nechcete udělit přístup pro
SELECT většině uživatelů, jelikož databáze může obsahovat soukromá data, například emaily uživatelů a jejich jména. Proto spousta firem používá anonymizované verze jejich databází, které pak mohou používat bez obav z přístupu nepovolaných uživatelů k citlivým údajům.Bonus: Přečti si oblíbený XKCD komiks o bezpečnějším SQL (plus toto vysvětlení).
Chceš se zapojit do diskuze?
Zatím žádné příspěvky.