Správa databází v MySQL - I

2. 5. 2003 | David Hauzar

Články - Správa databází v MySQL - I  

Poslední tři díly seriálu o MySQL se věnují praktickým otázkám správy databází. Vytvořením databáze starost nekončí. V tomto dílu se dozvíte, jak ji správně zabezpečit.

Nyní už umíte vytvořit dobře strukturovanou databázi. Vytvořením databáze by ale vaše starost o databázi končit neměla. Databázi musíte především zabezpečit před zlomyslnými hackery. Dále byste neměli podcenit zálohování databáze, možná budete muset také importovat data do databáze nebo naopak exportovat data z databáze. Databázi je také třeba chránit proti fragmentaci. Někdy možná budete muset opravit poškozené tabulky databáze. Jak vidíte, jste zatím jen na půlce cesty ke zvládnutí práce s databázovým systémem MySQL.

V tomto díle se naučíte zabezpečit databázi. Zabezpečení databáze je velmi důležité, protože v databázi se často uchovávají důležité informace a mohlo by proto být dost nepříjemné, kdyby je nějaký hacker prohlížel, nebo rovnou vymazal.

Databáze mysql

Možná jste si všimli, že se po instalaci MySQL automaticky vytvořily dvě databáze - test a mysql. V databázi mysql jsou uloženy tabulky uživatelských účtů. Je to databáze jako každá jiná. Na tabulky v databázi mysql můžete odesílat stejné dotazy jako na jiné tabulky. Tabulky v této databázi obsahují veškerá oprávnění přidělená jednotlivým uživatelům.

Většina sloupců těchto tabulek může obsahovat pouze hodnoty: Y(=yes) nebo N(=no). Hodnoty ukládané do sloupců Host a Column_name jsou case insensitive, zatímco hodnoty ukládané do sloupců User, Password, Db a Table_name jsou case sensitive.

Databáze mysql obsahuje následující tabulky:

• user
• db
• host
• func
• columns_priv
• tables_priv

Tabulka user

Tabulka user obsahuje veškerá oprávnění uživatelů. Oprávnění nastavená v této tabulce jsou globální. Tj. když má uživatel privilegium odstraňovat záznamy nastavená v tabulce user, může odstraňovat záznamy v kterémkoliv sloupci kterékoli tabulky v kterékoli databázi.

Sloupce tabulky user

Sloupec	Význam
Host	Tento sloupec obsahuje název hostitelského počítače, ze kterého se může k databázi daný uživatel přihlásit. Můžete sem ukládat adresy IP, názvy domén nebo localhost. Zástupný symbol % má stejnou funkci jako * jinde. Pokud tedy zadáte do sloupce Host pouze %, bude se uživatel moci přihlásit odkudkoli. Můžete ho ale použít i při definování názvů domén (např. %.domena.cz znamená, že se uživatel bude moci přihlásit z abc.domena.cz i xy.domena.cz) Localhost je název počítače, na němž je spuštěna databáze MySQL.
User	Uživatelské jméno daného uživatele.
Password	Heslo uživatele.
Select_priv	Dovoluje uživateli spouštět výběrové dotazy SQL.
Insert_priv	Umožňuje uživateli přidávat data do databáze pomocí přidávacích dotazů SQL.
Update_priv	Umožňuje uživateli aktualizovat existující data pomocí aktualizačních dotazů SQL.
Delete_priv	Dovoluje uživateli data z databáze odstraňovat.
Create_priv	Umožňuje uživateli vytvářet nové databáze a tabulky (ale ne indexy - viz Index_priv).
Drop_priv	Umožňuje uživateli odstraňovat databáze a tabulky. Toto může být velmi riskantní.
Reload_priv	Umožňuje uživateli aktualizovat tabulky uživatelských účtů pomocí příkazu FLUSH PRIVILEGES.
Shutdown_priv	Umožňuje uživateli vypnout databázový server MySQL
Process_priv	Umožňuje uživateli sledovat zpracování požadavků na serveru MySQL příkazem mysqladmin processlist nebo SHOW PROCESSLIST. Umožňuje i odstraňování procesů.
File_priv	Umožňuje číst soubory na serveru MySQL nebo do nich zapisovat. Pokud bude služba MySQL spuštěna systémovým uživatelem root (tj. uživatelem root daného serveru), může uživatel s právy File_priv smazat i třeba systémové soubory na serverovém počítači.
Grant_priv	Uživatelé s tímto privilegiem mohou udělovat privilegia jiným uživatelům.
References_priv	V současné době se nepoužívá.
Index_priv	Umožňuje uživateli vytvářet/odstraňovat indexy.
Alter_priv	Dovoluje uživateli měnit strukturu tabulky. (ale ne pracovat s indexy)

Tabulka db

Tabulka db obsahuje informace o udělených privilegiích pro jednotlivé databáze. Privilegia udělená v této tabulce jsou platná pouze pro určenou databázi. Nemá-li tedy uživatel privilegium Drop_priv v tabulce user, ale má ho v tabulce db, může odstraňovat pouze příslušnou databázi a její tabulky.

Tabulka db obsahuje téměř stejné sloupce jako tabulka user. Neobsahuje ale žádná privilegia pro správu (obsahuje jen privilegia udělitelná v rámci práce s databází). Navíc obsahuje sloupec Db, který obsahuje název databáze, pro kterou budou nastavení platná.

Tabulka host

V tabulce host můžete omezit privilegia hostitelů v rámci databáze uvedené v sloupci db. Můžete zde tedy nastavit, že pokud se někdo přihlásí k MySQL z počítače, na kterém běží databáze (localhost), bude mít privilegium Drop_priv v rámci dané databáze a pokud odněkud jinud, nebude dané privilegium mít.

Tabulka host obsahuje stejné sloupce jako tabulka db, ale neobsahuje sloupec user.

Tabulky columns_priv a tables_priv

Tabulky columns_priv a tables_priv řídí přístup k tabulkám a jednotlivým sloupcům tabulek. Je to úplně nejspodnější stupeň udělování privilegií. Pokud uživateli přidělíte privilegium SELECT v tabulce columns_priv, bude smět pouze spouštět výběrové dotazy pouze na daný sloupec.

V následující tabulce jsou společné sloupce pro tabulky columns_priv a tables_priv.

Sloupce tabulek columns_priv a tables_priv

Sloupec	Význam
Host	Název hostitele, ze kterého se uživatel k systému připojuje.
Db	Databáze, která obsahuje tabulky, na něž se příslušná privilegia vztahují.
User	Uživatelské jméno osoby, které privilegia udělujete.
Table_name	Název tabulky, pro kterou udělujete privilegia. Rozlišují se velká a malá písmena.
Timestamp	Datum poslední změny.
Column_priv	Nastavení privilegií pro vybranou tabulku. Je to typ množiny (SET). Může obsahovat pouze hodnoty: SELECT, INSERT, UPDATE a REFERENCES. Hodnoty musí být odděleny čárkami.

Tabulka tables_priv obsahuje navíc sloupce:

• Grantor - uchovává jméno osoby, která právo udělila.
• Table_priv - jsou tu uložena privilegia pro danou tabulku. Sloupec může obsahovat následující hodnoty: (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, REFERENCES, INDEX, ALTER).

Tabulka columns_priv obsahuje navíc sloupec:

• Column_name - obsahuje název sloupce, na který se vztahují privilegia udělená v poli Column_priv.

Zásady správného zabezpečení

• Je vhodné udělovat uživatelům jenom taková privilegia, která skutečně potřebují. Pokud uživatel pracuje pouze s jednou databází, není dobré mu nastavovat příslušná privilegia v tabulce user atd. Například pokud uživatel bude potřebovat prohlížet všechny tabulky i sloupce nějaké databáze, ale aktualizovat jenom jeden sloupec, nastavte mu v tabulce db privilegium Select_priv příslušné databáze a v tabulce columns_priv privilegium UPDATE ve sloupci Column_priv příslušného sloupce.
• Žádnému uživateli (vyjma uživatele root MySQL) nedávejte privilegia na přístup do tabulky user v databázi mysql. Takový uživatel může snadno zjistit hesla ostatních uživatelů.
• Nenechávejtele uživatele root (a nejlépe ani žádné jiné uživatele) bez hesla.
• Vybírejte taková hesla, která nejsou ve slovníku (existují programy, které zkouší právě hesla ze slovníku).
• Snažte se omezovat uživatelské účty s hostitelským názvem nastaveným na %.
• Je vhodné nespouštět službu mysqld jako systémový uživatel počítače, na kterém bude mysqld spuštěno. Pak by totiž všichni uživatelé s privilegiem FILE mohli mazat všechny (tedy i systémové) soubory počítače, na kterém je mysqld spuštěno.
• Pokud v databázi ukládáte hesla, zašifrujte je (například funkcí MD5()).
• Soubory v adresáři data by měly být vlastněny uživatelem, který spouští službu mysqld. Tento uživatel by měl mít výhradní právo zde uložené soubory číst nebo upravovat.

Jak zabezpečení funguje?

Když se uživatel přihlásí k databázi MySQL, systém ověří, zda má uživatel záznam v tabulce user. Dále ověří název hostitele, uživatelské jméno a heslo. Když přihlášený uživatel odešle do databáze dotaz, zjistí MySQL, jaké má uživatel privilegia v tabulce user. Pokud tam uživatel nemá potřebná privilegia, prohledá tabulku db. Tam nejprve ověří, zda se shoduje název hostitele, uživatelské jméno a databáze. Pokud ano, ověří zde nastavená oprávnění. Pokud je nenajde, začne prohledávat tabulku tables_priv a dále columns_priv. Pokud ani tam oprávnění nenajde, generuje chybu.

Celý proces je velmi rychlý, takže se nemusíte obávat, že když nastavíte uživateli práva až v tabulce columns_priv, tak se bude dotaz provádět postřehnutelně delší dobu.

Toto je dvoustupňové zabezpečení. Musí dojít k autentifikaci uživatele a dále musí mít daný uživatel potřebná privilegia.

Zabezpečení prakticky

Vytváření nových uživatelů a přidávání privilegií uživatelům

1. Přímé úpravy tabulek uživatelských účtů

   Tabulky uživatelských účtů jsou běžné tabulky, takže pokud máte potřebná privilegia, můžete tabulky ručně upravovat. Takto můžete nastavit opravdu cokoli. Nevýhoda je, že přímá úprava uživatelských účtů je pomalá a uživatelsky nepřívětivá. Při přidávání nových záznamů se podívejte, jaké sloupce jsou povinné.

   Příklad přidání nového uživatele Martin s heslem termistor:

   INSERT INTO user (Host, User, password)
  VALUES("localhost", "Martin", PASSWORD("termistor"));

   Toto vytvoří uživatele Martin s heslem termistor, který se do databáze může připojit pouze z počítače, na němž je spuštěna databáze MySQL.

2. Příkaz GRANT

   Příkaz GRANT slouží k snadnému vytváření uživatelů a následnému přidávání privilegií.

   Syntaxe příkazu GRANT je následující:

   GRANT {privilegia} [(seznam sloupců)] [, {privilegia} [(seznam sloupců)]...]
  ON názevdatabáze.názevtabulky
  TO uživatelskéjméno@názevhostitele
  IDENTIFIED BY "heslo"
  [WITH GRANT OPTIONS]

   kde za slovo privilegia doplníte privilegium, které chcete udělit.

   Hodnoty parametru privilegia v příkazu GRANT

   Privilegium	Význam
   ALL	Uděluje uživateli všechna dostupná privilegia.
   ALTER	Umožňuje aktualizovat a upravovat tabulky, sloupce a indexy.
   CREATE	Umožňuje vytvářet tabulky nebo databáze.
   DELETE	Umožňuje odstraňovat záznamy z tabulek.
   DROP	Umožňuje odstraňovat tabulky nebo databáze.
   FILE	Umožňuje manipulovat se soubory serverového počítače.
   INDEX	Umožňuje přidávat nebo odstraňovat indexy.
   INSERT	Umožňuje přidávat do databáze nové záznamy.
   PROCESS	Umožňuje sledovat a odstraňovat procesy systému MySQL.
   REFERENCES	Nepoužívá se.
   RELOAD	Umožňuje používat příkaz FLUSH.
   SELECT	Umožňuje spouštět výběrové dotazy.
   SHUTDOWN	Umožňuje vypnout službu MySQL.
   UPDATE	Umožňuje upravovat existující záznamy v databázi.
   USAGE	Umožňuje připojit se k serveru.

   • Za seznam sloupců můžete nepovinně doplnit, na které sloupce tabulky se budou vybraná privilegia vztahovat.
   • Za názevdatabáze.názevtabulky můžete doplnit názevdatabáze.názevtabulky nebo můžete použít zástupný znak * (*.* znamená, že privilegia aplikujete globálně na všechny databáze, Názevdatabáze.*, že je aplikujete na jednu celou databázi).
   • Dále doplníte uživatelskéjméno@názevhostitele, přičemž v části názehostitele můžete použít zástupný znak % (má stejnou funkci jako * výše).
   • Poslední část příkazu je nepovinná a dává přílušnému uživateli právo používat příkaz GRANT

   Příklad vytvoření uživatele Martin s heslem termistor, který se může k databázi připojit jen z počítače, na kterém je databáze spuštěna, a který má veškerá privilegia na všechny tabulky všech databází:

   GRANT ALL PRIVILEGES
  ON *.* TO Martin@localhost
  IDENTIFIED BY "termistor";

3. Udělování privilegií pomocí programu mysql_setpermission

   Další možnost udělování privilegií je program mysql_setpermission. Spustíte ho z příkazového řádku. Je to jednoduchý program, který umožňuje jen základní nastavení. Práce s ním je ale velmi rychlá a intuitivní.

Odstranění uživatele a privilegií

1. Odstranění uživatele

   Uživatele můžete odstranit jen pomocí dotazu SQL na tabulky uživatelských účtů. Uživatele odstraníte takto:

   DELETE FROM user WHERE User = "Uživatel_kterého_chcete_odstranit" AND Host="%";
DELETE FROM db WHERE User = "Uživatel_kterého_chcete_odstranit" AND Host="%";

   atd. (musíte samozřejmě odstranit všechny záznamy příslušející uživateli ve všech tabulkách).

2. Odstranění privilegií (příkaz REVOKE)

   Privilegia můžete odstranit také pomocí dotazů SQL. Lepší je ale použít příkaz REVOKE, jehož syntaxe je:

   REVOKE {privilegia} [(seznam sloupců)] [, {privilegia} [(seznam sloupců)]...]
  ON názevdatabáze.názevtabulky
  FROM uživatelskéjméno@názevhostitele;

   Za privilegia můžete doplnit stejná privilegia jako v případě příkazu GRANT.

   Všechna privilegia uživatele Uživatel odstraníte takto:

   REVOKE ALL ON *.* FROM UŽIVATEL@%;

   Takto ale odvoláte jen privilegia, která přísluší uživateli v případě, že se přihlašoval do systému ze vzdáleného počítače. Pokud se mohl přihlašovat i z místního počítače, musíte zadat ještě:

   REVOKE ALL ON *.* Uživatel@localhost;

Závěr

Tabulky uživatelských účtů jsou uloženy v databázi mysql. Privilegia můžete uživatelům udělovat globálně (tabulka user), na určitou databázi (tabulka db), v rámci určitého hostitele (tabulka host), na určité tabulky určité databáze (tabulka tables_priv), nebo na určité sloupce určité tabulky určité databáze (tabulka columns_priv).

Uživatele stejně jako jejich privilegia můžete vytvářet a odstraňovat přímými úpravami tabulek uživatelských účtů. Privilegia můžete udělovat i příkazem GRANT a odstraňovat příkazem REVOKE.

I když databázi správně zabezpečíte, data v bezpečí stále ještě nebudou. Aby byly, je nutné databázi zálohovat. Jak na to se dozvíte v příštím dílu - zabývá se importem/exportem dat do databáze a protokolováním transakcí.

Související články

Tvorba databází v MySQL - I (Základní pojmy, návrh databáze)
Tvorba databází v MySQL - II (Instalace a používání)
Tvorba databází v MySQL - III (Datové typy)
Tvorba databází v MySQL - IV (Klíče a fulltext)
Tvorba databází v MySQL - V (Základní funkce)
Tvorba databází v MySQL - VI (Datum a čas)

Seriál Správa databází v MySQL (dílů: 3)

První díl: Správa databází v MySQL - I, poslední díl: Správa databází v MySQL - III.
Následující díl: Správa databází v MySQL - II

Diskuse k tomuto článku

[Krišpín]

Zdravim, uz nekolikrat sem potreboval pridelit prava v databazi jen na tabulky s urcitym nazvem, ten nazev u me zati treba db_* -- no, tak sem myslel, ze bych nstavil GRANT takto: GRANT select ON JmenoDB.db_* TO krispin@localhost IDENTIFIED BY ("heslo"); -- no, ale ono to nejak s tou hvezdickou nespolupracuje....de to jen takto: JmenoDB.*, nebo takto: JmenoDB.db_prvni -- delam neco spatne, nebo se to dela jinak, nebo to proste takhle do podrobna nejde??? Jo jeste mozna je taky problem ve verzi MySQL DB, ktera to treba jeste neumi, mam nainstalovanou 3.23.43 -- v novejsi si nejsem jistej, jestli sem to taky uz zkousel...

[misotolar]

GRANT select ON JmenoDB.db_% TO krispin@localhost IDENTIFIED BY ("heslo");

[unchallenger]

Možná by stálo za to zmínit se o změnách v systému práv v MySQL 4.x oproti 3.x. V administrativních právech je tam změn poměrně hodně.

[David Hauzar]

Musim se priznat, ze s MySQL 4.x jsem jeste nepracoval. Momentalne pouzivam MySQL 3.23.52. Pri psani serialu jsem cerpal z manualu k teto verzi.

Co nejdrive to ale napravim a pokud budou zmeny takove, ze neco z mnou napsaneho uz nebude platit, samozrejme clanek doplnim. Bohuzel to bude nejaky cas trvat, takze do zitrka to rozhodne nebude

[dep]

super tohle sem presne potreboval diky :)

[Malousek]

Vim, ze je tu diskuze k clanku, ale nik¨de se nemohu docist, jak mohu prekopirovat svoji databazi v MySQL na jiny server. Jedna se asi o to, ze doma vlozim data do databaze a pak danou tabulku (stejnou) musim aktualizovat na serveru (hosting) venku. Jak se to da udelat, popr. jake prikazy musim v MySQL napsat? Moc Diky

[OldFrog]

Prenos dat na jiny server: 1) export databaze do textove podoby (sql skriptu) 2) import databaze (spusteni sql skriptu) Prakticke provedeni zalezi na datazovem serveru, v MySQL (kterou nepouzivam) odhaduji nasledujici prikazy: 1) mysqldump moje-databaze > moje-databaze.sql 2) mysql < moje-databaze.sql Krok 2 zrejme provedete pomoci nejakeho weboveho rozhrani, ktere ke sprave databaze poskytuje Vas provider. Soubor moje-databaze.sql jednoduse vlozite do pole pro sql prikazy a potvrdite. Presne detaily si jiste zvladnete najit sam. Dobrou noc preje OldFrog.

[Robert Krátký]

dalsi dil se bude mimo jine zabyvat i importem a exportem

[dep]

kdyztak se tam zminte o synchronizaci ... mam doma db, a jednu na serveru. admin mi rekl ze tu doma mam pouzivat jako testovaci a ostrou mam pouzit tu jeho (ja chci mit taky ostrou db) ... jde to udelat neak normalne pohodlne ? (ciste teoreticky kdyz vydumpuju db dam ji na server, doma upravim vydumpuju, dam na server ... cela db se prepise nebo se tam prida jen to nove ?) etc ... tesim se na dalsi dil

[David Hauzar]

Reseni by bylo (doma) zapnout protokolovani zmen dat a struktury tabulek. (Do souboru protokolu se zapisou vsechny SQL prikazy, ktere vyvolaly zmenu dat nebo struktury tabulek) a pak (na serveru) davkove spracovat SQL prikazy z (doma) vznikleho souboru protokolu.

Pristi dil se bude protokolovanim transakci zabyvat. Primo o synchronizaci se tam nezminuji, ale ono to z toho vyplyva. I kdyz zminit bych se o tom mohl .

[RAV]

...koukněte se na free nástroj http://www.webyog.com/ . Myslím si, že na správu MySQL lepší v současnosti nenajdete.

[Obiwan]

Dík za seriál o MySQL. Začínám jak s Linuxem, tak s MySQL a prosím o radu. Nemohu se připojit z jiných PC prostřednictvím MyODBC - MySQL 3.23 a na klientských PC s OS WIN MyODBC 3.51. Uživatele jsem nakonfiguroval podle výše uvedeného, ale vždy když se pokusím připojit vypadne hláška "Lost connection to MySQL server during query".

[Barbucha.eu]

Pokud přidáváte uživatele tím, že zavoláte use mysq a pak INSERT INTO user...;, nesmíte zapomenout po tomto příkazu zavolat ještě flush privileges;. Teprve pak MySQL-server přizpůsobí své chování změnám v tabulce user a dovolí novému uživateli přihlášení.