Administrarea MySQL

Administrarea oricarui sistem de gestiune a bazelor de date relationale (SGBDR) necesita putina munca. Fiecare sistem are propriile metode pentru utilizare si propriile dificultati cand vine vorba de adaugarea si stergerea de conturi ale utilizatorilor, backup si asigurarea securitatii. Administarea server-ului MySQL nu este dificila per ansamblu, dar poate speria la inceput.

Administrarea utilizatorilor

Tabelele de grant
Drepturile utilizatorilor MySQL sunt stocate in cateva tabele care sunt făurite automat la instalarea MySQL. Aceste tabele apartin bazei de date mysql:

mysql> use mysql
Database changed
mysql> show tables;
+-------------------+
| Tables in mysql |
+-------------------+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+-------------------+
6 rows in set (0.00 sec)

Fiecare dintre aceste tabele corespunde controlului unui nivel de acces. Poti crea un oricati utilizatori care pot accesa MySQL de pe diferite gazde. Pentru fiecare combinatie utilizator/gazda, acorzi acces la o intreaga baza de data, la anumite tabele, sau la anumite coloane dintr-o tabele. Aditional, aceste tabele acorda privilegii de administrare. Utilizatorilor le poate fi dat dreptul de a adauga/sterge baze de date sau de a acorda altor utilizatori drepturi de administare.

In practica, nu ar necesită sa acorzi mai numeroase drepturi decat e necesar pentru a proteja datele de curiosi sau de incompetenti. Pentru asta trebuie sa stii ca drepturile sunt acordate intr-un mod ierarhic. Cele acordate pe tabela user sunt universale: daca un utilizator are privilegiu de stergere(drop) in tabela user, acesta poate sterge orice tabela din rocie baza de date din MySQL. Tabela db acorda privilegii asupra unei intregi baze de date. Pentru o tabela/set de tabele foloseste tables_priv. Tabela columns_priv acorda drepturi asupra unor anumite colobne dintr-o tabela.

Tabela user
Orice utilizator care vrea sa lucreze in MySQL trebuie sa fie inscris in acesta tabela. Drepturile pot fi acordate in alta parte, dar fara o inscriere aici, utilizatorului nu i se va permite conectarea la server.

mysql> show columns from user;
+-------------------+---------------+-------+-------+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------------------------------------------------+
| Host | char(60) | | PRI | | |
| User | char(16) | | PRI | | |
| Password | char(16) | | | | |
| Select_priv | enum(�N�,�Y�) | | | N | |
| Insert_priv | enum(�N�,�Y�) | | | N | |
| Update_priv | enum(�N�,�Y�) | | | N | |
| Delete_priv | enum(�N�,�Y�) | | | N | |
| Create_priv | enum(�N�,�Y�) | | | N | |
| Drop_priv | enum(�N�,�Y�) | | | N | |
| Reload_priv | enum(�N�,�Y�) | | | N | |
| Shutdown_priv | enum(�N�,�Y�) | | | N | |
| Process_priv | enum(�N�,�Y�) | | | N | |
| File_priv | enum(�N�,�Y�) | | | N | |
| Grant_priv | enum(�N�,�Y�) | | | N | |
| References_priv | enum(�N�,�Y�) | | | N | |
| Index_priv | enum(�N�,�Y�) | | | N | |
| Alter_priv | enum(�N�,�Y�) | | | N | |
+-------------------+---------------+-------+-------+-----------+-------+
17 rows in set (0.00 sec)

MySQL identifica un utilizator dupa combinatia user-host. Prin urmare, un utilizator poate avea cate un set de drepturi variate pentru fiecare gazda (host) pe care o foloseste pentru a se conecta la MySQL. Drepturile setate pe Y (yes) sunt acodate pentru toate tabele din orice baza de date. Alte drepturi:
- File_priv acorda dreptul de a scrie si citi fisiere de pe disc.
- process_priv acorda dreptul de a vedea si de a termina toate comenzile si threrad-rile in curs de executie(poti vedea ce fac si ce tasteaza ceilalti utilizatori !!).
- Grant_priv da dreptul de a putea acorda mai departe privilegiile proprii altor utilizatori.
- Reload_priv e folosit, in aparte pentru a face comenzi flush
- Shutdown_priv da dreptul de a opri server-ul folosind mysqladmin shutdown.
Tabela db

mysql> show columns from db;
+---------------------------------------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------------------------------------------------------+
| Host | char(60) | | PRI | | |
| Db | char(32) | | PRI | | |
| User | char(16) | | PRI | | |
| Select_priv | enum(�N�,�Y�) | | | N | |
| Insert_priv | enum(�N�,�Y�) | | | N | |
| Update_priv | enum(�N�,�Y�) | | | N | |
| Delete_priv | enum(�N�,�Y�) | | | N | |
| Create_priv | enum(�N�,�Y�) | | | N | |
| Drop_priv | enum(�N�,�Y�) | | | N | |
| Grant_priv | enum(�N�,�Y�) | | | N | |
| References_priv | enum(�N�,�Y�) | | | N | |
| Index_priv | enum(�N�,�Y�) | | | N | |
| Alter_priv | enum(�N�,�Y�) | | | N | |
+---------------------------------------------------------------------------+
13 rows in set (0.01 sec)

Drepturile specificate aici merg doar pentru baza de date specificate in coloana db_column.
Tabelele tables_priv si columns_priv
Ofera drepturi asupra unei tabele sau asupra unor coloane dintr-o tabela.

mysql> show columns from tables_priv;
+-------------------------------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------------------------------------------------+
| Host | char(60) | | PRI | | |
| Db | char(60) | | PRI | | |
| User | char(16) | | PRI | | |
| Table_name | char(60) | | PRI | | |
| Grantor | char(77) | | MUL | | |
| Timestamp | timestamp(14) | YES | | NULL | |
| Table_priv |set(�Select�,�Insert�,�Update�, | |
| | �Delete�,�Create�,�Drop�,�Grant�, | |
| | �References�,�Index�,�Alter�) | |
| | | | | | |
| Column_priv | set(�Select�,�Insert�, | | |
| | �Update�,�References�) | | |
+-------------------------------------------------------------------+
8 rows in set (0.00 sec)
Controlul accesului

MySQL foloseste tabelele de privilegii pentru a determina ce are si ce nu are un utilizator dreptul sa faca in doi pasi:
Verificarea conexiunii: MySQL verifica daca ai voie sa te conectezi la server bazandu-se pe numele de utilizator (blank insemna orice utilizator), numele de gazda si pe parola. Pentru numele gazdei poti folosi caracterul wildcard '%' ca semnificand orice gazda.
Verificarea cererii: MySQL verifica in toate tabele de drepturi daca ai drepturile necesare pentru a obtine informatiile dorite.
Comenzile GRANT si REVOKE

Tabele descrise mai sus sunt tabele naturale MySQl si pot fi modificate cu comenzile invatate. De exemplu, pentru a crea un nou utilizator:
INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv) VALUES (�localhost�, �juan�, �password�, �N�, �N�, �N�, �N�, �N�, �N�, �N�, �N�, �N�, �N�, �N�, �N�, �N�, �N�)
Nu este insa ce si-ar dori sa fac administratorul de fiecare data. Din fericire, MySQL ofera cateva comenzi care fac lucrul cu utilizatorii mult mai usor:
GRANT

GRANT privilegii [coloane]
ON obiect
TO utilizator [IDENTIFIED BY �parola�]
[WITH GRANT OPTION]
Niveluri de drepturi
grant all on *.* Acorda drepturi universale
grant all on database.* Acorda drepturi asupra tuturor tabelelor din baza de date
grant all on database.table_name Drepturi pentru o singura tabela
grant all(col1, col2) on database.table_name Drepturi pentru anumite coloane dintr-o tabela

mysql> grant all on guestbook.* to ion@localhost identified by �parola1�;
Prima parte a comenzii poate fi all sau alta optiune din tabela user. Partea a doua a comenzii (on guestbook) arata unde se vor aplica drepturile (vezi tabelul de mai sus). Partea a treia arata cui i se acorda drepturile (utilizator@gazda), iar ultima parte arata parola cu care este identificat utilizatorul.
grant select, update, insert on guestbook2k.guestbook to adi@localhost identified by �parola2�;
Acesta comanda permite lui adi sa vada, sa modifice, sa actualizeze si sa insereze inregistrari in tabela 'guestbook' din baza de date 'guestbook2k'.
grant select, update (nume, email) on guestbook2k.guestbook to monica@localhost identified by �Mel12068�;
Monica poate vedea si actualiza doar campurile 'nume' si 'email'.
grant all on *.* to marius@localhost identified by �xxx456�;
Marius are drepturi depline (poate chiar acorda privilegii altor utilizatori).
REVOKE

Daca vrei sa anulezi drepturile acordate unui utilizator poti folosi comanda REVOKE:
REVOKE privilegii [(coloane)]
ON item
FROM utilizator

Comanda revoke Shutdown on *.* from cosmin@localhost; revoca dreptul de shutdown al lui Cosmin. Daca vrei sa stergi un utilizator de tot trebuie sa-l stergi din tabela 'user':
delete from user where user=�user� and host=�host�
Afisarea drepturilor
Din versiunea 3.23.4 MySQL a introdus comanda show grants care îngăduie vizualizarea drepturilor acordate la un clipă dat:

mysql> show grants for jayg@localhost;
+------------------------------------------------------------- +
| Grants for ion@localhost |
+------------------------------------------------------------- +
| GRANT ALL PRIVILEGES ON testul1.* TO �ion�@�localhost� |
+------------------------------------------------------------- +
1 row in set (0.00 sec)
Reincarcarea drepturilor
MySQL citeste automat tabelele de privilegii cand e pornit si cand sunt date comenzi GRANT sau REVOKE. Modificarile facute manual tabelelor de drepturi nu iau efect decat daca se restarteaza MySQL. Ca sa functioneze aceste modificari reincarca tabelele:
flush privileges
sau
mysqladmin flush-privileges
sau
mysqladmin reload
Backup-ul bazelor de date

Pentru a face un backup complet al bazelor de date:
mysql> mysqldump --tab=/cale/director --opt --full
sau
mysql> mysqlhotcopy database /path/director
Poti, de asemenea replica toate fisierele tabelelor (`*.frm', `*.MYD', and `*.MYI' files) cat timp serverul nu actualizeaza nimic. Poti face si backup-uri selective cu SELECT * INTO OUTFILE 'nume_fisier' FROM nume_tabela si sa restaurezi datele cu LOAD DATA INFILE 'nume_fisier' REPLACE ... Pentru a evita inregistrarile duplicat (inregistrarile noi le inlociesc pe cele mai vechi daca sunt identice in cazul comenzii REPLACE) ai trebuință de o cheie primara sau de o cheie unica.
Securitatea in MySQL

Securitatea e foarte importanta, in singular cand vei conecta baza ta de date MySQL la site-ul Web. Cand rulezi MySQL ia in considerare urmatoarele sfaturi:
Nu da nimanui acces la tabela user !
Invata sistemul de privilegii al MySQL ! Tasteaza mysql -u root. Daca te poti conecta fara sa ti se ceara o parola, oricine poate face acelasi lucru. Vezi, in pagina de instalare cum se poate pune parola de root pe sever.
Nu folosi parole din dictionar fiindcă exista programe care le pot sparge! Foloseste initialele cuvintelor dintr-o propozitie (de exemplu aaum 'Ana are un miel') sau combinatii de pe tastatura (in loc de stefan-dyrgsm, adica o litera spre dreapta de pe tastatura).
Investeste intr-un firewall.
Foloseste parole pentru toti utilizatorii MySQL si ai grija in ce fisiere script le memorezi. Poti cripta parolele cu functiile PASSWORD() sau MD5().
La conectarea pe Web, ar trebui sa creezi un utilizator special numai pentru Web (de exemplu, sa aiba doar drepturi de SELECT pe tabela cu articole si de INSERT pe tabela cu comenzile facute).
Daca utilizatorii care folosesc formularele de pe Web tasteaza date confidentiale, trebuie sa folosesti SSL (Secure Socket Layer) pentru ca altfel datele vor fi transmise spre server in fomatul text.
Optimizarea MySQL

Optimizeaza tabele care pot deveni fragmentate dupa multe actualizari si modificari cu OPTIMIZE TABLE nume_tabela
Alege cel mai adecvat si cel mai scurt tip de date pentru campurile din tabele.
Foloseste indecsi cand sunt necesari pentru a face interogarile mai rapide.
Foloseste valori implicite in comanda INSERT
Invata sa folosesti comanda EXPLAIN SELECT.. care arata ce se va intampla in cazul unei interogari.
Nu uita ca privilegiile sunt citite la fiecare interogare. Deci, simplifica cat mai mult privilegiile acordate.
Nu uita ca exista mai multe feluri de tabele care pot fi create cu CREATE TABLE nume TYPE=tip (MyISAM-implicit, HEAP-temporar, BDB-sigure pentru tranzactii).