[Pillola] Amministrazione degli utenti con MYSQL

[ringo_mato]

Vi siete mai domandati come si possono mettere i permessi sull'utente che ha l'accesso alle tabelle di mysql??
ok se ve lo siete domandato.. ho scritto questa pillola solo per voi.. :sexpulp:
Mi scuso fin d'ora se alcune parti risultano confuse e/o ci siamo orrori di ortografia

Allora cominciamo.. ^__^
Tante volte ci preoccupiamo della sicurezza sulle nostre pagine PHP, magari facendo un accesso in lettura e scrittura da root
con tutti i permessi attivi.
Sappiamo tutti che mySQL installa un database chiamato mysql dove contiene diverse informazioni..
digitiamo dal prompt di DOS o dalla shell di linux

non preoccupatevi per root@ramengo:~# è il nome della mia macchina (:

root@ramengo:~# mysql
mysql> use mysql
mysql> show tables;

a video vi compariranno le tabelle del mysql.. embè bella scorperta direte..
allora

+-----------------+
| Tables in mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+

Tutte queste tabelle servono per gestire gli accessi al server di mysql.
Attraverso queste tabelle, potete consentire accessi ad interi database oppure a ad alcune colonne
all'interno del database.
Potete settare i privilegi per ogni utente di lettura, scrittura cancellazione del Db etc...

Tutti i diritti assegnati vengono conferiti in modo gerarchico.

I diritti per esempio assegnati nella tabella USER sono universali, cioè se l'utente "tocio" ha privilegi che gli consentono di fare l'eliminazione di un DB (Drop)
l'utente potrà eliminare qualsiasi tabella contenuta in mysql.

Nella tabella DB potrete concedere privilegi relativi a database specifici oppure per più database

Nella tabella TABLES_PRIV potrete concedere privilegi relativi a una tabella oppure per un insieme di tabelle

Nella tabella COLUMNS_PRIV potrete concedere diritti su specifiche colonne se non dovete concedere diritti controllate che i diritti siano assegnati nella columns_priv
Vediamo ora in dettaglio le singole tabelle:

[ringo_mato]

Tabella USER

> show columns from user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | varchar(60) binary                |      | PRI | 	   |       |
| User                  | varchar(16) binary                |      | PRI |         |       |
| password              | varchar(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       |       |
| Show_db_priv          | enum('N','Y')                     |      |     | N       |       |
| Super_priv            | enum('N','Y')                     |      |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     |      |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     |      |     | N       |       |
| Execute_priv          | enum('N','Y')                     |      |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     |      |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     |      |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') |      |     |         |       |
| ssl_cipher            | blob                              |      |     |         |       |
| x509_issuer           | blob                              |      |     |         |       |
| x509_subject          | blob                              |      |     |         |       |
| max_questions         | int(11)                           |      |     | 0       |       |
| max_updates           | int(11) unsigned                  |      |     | 0       |       |
| max_connections       | int(11) unsigned                  |      |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+

Ogni utente che vuole accedere al server di mysql deve essere inserito qui dentro..
le colonne piu' importanti di questa tabelle sono Host,User,password che guarda caso sono quelle che usiamo su mysql_connect()

Se accedete attraverso PHP sulla vostra stessa macchina il valore di host sarà uguale a localhost e modificando i valori di host potete assegnare diritti diversi
a host diversi.

ATTENZIONE!! I diritti attivati in questa tabella sono attivi per qualsiasi
db del server Mysql.

La tabella User contiene una serie di colonne che assicurano diritti amministrativi.
Queste colonne sono:

• File_priv:
  Se concesso Permette al server di database di leggere e scrivere file appartenenti al File System.
  Servirà principalmente per caricare un file in una tabella di database
• Grant_priv:
  Un utente dotato di questo privilegio sarà in grado di assegnare i suoi privilegi ad altri utenti
• Proccess_priv:
  Questo permette all'utente di vedere e terminare tutti i processi e i thread
• Reload_priv:
  Viene usata per lo piu' nell'utility mysqladmin per eseguire comandi di scrittura forzata(flush)
  attraverso root@ramengo:~# mysqladmin reload
• Shutdown_priv:
  Permette all' utente di disattivare il demone il demone di mysql utilizzando mysqladmin shutdown

Dalla versioni > 3.23 si sono aggiunti vado a intuito a spiegarli
anche perchè sinceramente non li ho mai usati

• Create_tmp_table_priv:
  Permette la creazione di tabelle temporanee
• Execute_priv:
  Permette all'utente di lanciare store procedure (MYSQL 5.0)
• Lock_tables_priv:
  Permette di bloccare le tabelle di un db sulle tabelle che hanno privilegi SELECT attivi
• Process_priv:
  Penso serva per visualizzare i processi in esecuzione di mysql
• Repl_client_priv:
  Replication Client permette di conoscere chi è master o chi è slave
• Repl_slave_priv:
  Necessita di privilegi slave per visualizzare i binlogs come master
• Show_db_priv: Permette la visione di tutti i database
• Super_priv:
  permette unna connessione (unica) se si è raggiunto il limite del max_connections
  e permette di eseguire i comandi CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS and SET GLOBAL

la documentazione si trova qui

Dalla versione 4.0.0 si ha il supporto per le connessioni SSL criptografate.
Per capire come usare SLL sono necessari alcuni concetti relativi a SSl e X509.
SSL è un protocollo che usa differenti algoritmi di compressione per scambiare i dati in tutta sicurezza.
Utilizza una chiave criptata in X509 che permette di ottenere un tipo di
dato illeggibile se non si conosce la chiave di lettura.
X509 è uno standard di cifratura che permette l'identificazione di qualcuno attraverso internet,
è generalmente usata nelle applicazioni di E-commerce. le chiavi di cifratura X509,
sono assegnate da un ente chiamato 'Certificate Authority'
per evitare che qualcuno possa intercettare e leggere il flusso di dati che sono spediti e ricevuti dal Server

Mysql usa di default connessioni non criptate tra il Client e il Server
perchè significa rendere le connessioni tra i protocolli client/server molto piu' lente
e ritardare i task di mysql per via dell'uso intensivo della CPU
Di default MySql è ottimizzato per la velocità di esecuzione

ssl_type ssl_cipher x509_issuer x509_subject

le altre colonne contengono per la maggior parte privilegi relativi a istruzioni SQL per la gestione del database :
questi parametri permettono di attivare/disabilitare i diritti di Select/Update/Drop etc

Una piccola nota la spendo su questi 3 campi che possono assegnare limitazioni particolari
per ogni utente che ha l'accesso il server MySQL per ogni ora

• max_questions:
  se si vogliono limitare il numero di query dell'utente
• max_updates:
  se si vogliono limitare il numero di update dell'utente
• max_connections:
  se si vogliono limitare il numero di connessioni dell'utente

[ringo_mato]

Tabella DB

mysql> show columns from db;
+-----------------------+-----------------+------+-----+---------+-------+
| Field                 | Type            | Null | Key | Default | Extra |
+-----------------------+-----------------+------+-----+---------+-------+
| Host                  | char(60) binary |      | PRI |         |       |
| Db                    | char(64) binary |      | PRI |         |       |
| User                  | char(16) binary |      | 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       |       |
| Create_tmp_table_priv | enum('N','Y')   |      |     | N       |       |
| Lock_tables_priv      | enum('N','Y')   |      |     | N       |       |
+-----------------------+-----------------+------+-----+---------+-------+

Per tutti i permessi riguardanti database specifici, userete questa tabella
piu' o meno funziona come la tabella user, solo che i permessi che sono settati
qui dentro valgono solo per il database specificato nela colonna DB.

Tabella TABLES_PRIV e COLUMNS_PRIV

mysql> show columns from tables_priv;
+-------------+------------------------------------------------------------------+
| Field       | Type                              | Null | Key | Default | Extra |
+-------------+------------------------------------------------------------------+
| Host        | char(60) binary                   |      | PRI |         |       |
| Db          | char(64) binary                   |      | PRI |         |       |
| User        | char(16) binary                   |      | PRI |         |       |
| Table_name  | char(60) binary                   |      | 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')                    |      |     |         |       |
+-------------+-----------------------------------+------+-----+---------+-------+

Siccome TABLE_PRIV e COLUMNS_PRIV sono molto simili in queste tabelle sono
memorizzati rispettivamente i diritti degli utenti che possono accedere ad una
tabella piu' tabelle tutti i diritti per le utenze vengono concentrate nelle
ultime due righe della tabella

[ringo_mato]

Istruzioni GRANT e REVOKE , Inserimento e cancellazione Utenti mysql

Allora come si procede all'inserimento nei diritti delle tabelle???

Ci sono 2 modi uno è il buon vecchio SQL

1) INSERIMENTO ATTRAVERSO QUERY

INSERIMENTO DI UN UTENTE
es: INSERT INTO user (Host,User,Password) VALUES ('localhost','pippo',password('figo'))
password serve per fare un ascing della password su Db ma se lo facciamo in MD5 è piu' sicuro (:

+-----------+-------+------------------+
| host      | user  | password         |
+-----------+-------+------------------+
| localhost | root  |                  |
| marantega | root  |                  |
| localhost |       |                  |
| marantega |       |                  |
| %         | pippo | 09e7d92b43759e6c |
| localhost | tocio | paperoga         |
+-----------+-------+------------------+

ATTENZIONE!!!! se si modificano le tabelle di grant, come la tabella user,
direttamente con i comandi di manipolazione delle tabelle, il server MySQL non si accorge
automaticamente del cambiamento e continua ad autenticare gli utenti con le vecchie informazioni.
Per forzare il server a leggere i nuovi diritti, occorre dare il comando
FLUSH PRIVILEGES

che richiede il privilegio RELOAD che abbiamo visto in precedenza per essere eseguita.

AUTENTICAZIONE A CARATTERI JOLLY
Se non ci fosse la possibilità di inserire caratteri jolly e utenti anonimi,
la decisione se un utente può accedere o no al database sarebbe semplice.
Con i caratteri jolly la cosa si complica.
Ad esempio, se io sono l'utente pippo che si collega da localhost devo inserire
la password oppure no? Infatti in teoria l'utente pippo@localhost
potrebbe connettersi al database sia grazie alla riga:

+-----------+-------+------------------+
| host      | user  | password         |
+-----------+-------+------------------+
| localhost |       |                  |
+-----------+-------+------------------+

localhost (cioè un utente anonimo da localhost)
o tramite la riga

+-----------+-------+------------------+
| host      | user  | password         |
+-----------+-------+------------------+
| %         | pippo | 09e7d92b43759e6c |
+-----------+-------+------------------+

(l'utente pippo da un macchina qualunque).

MySQL da più importanza al nome dell'host che al nome dell'utente.
Quando il client tenta di connettersi,
MySQL ordina la tabella utenti dagli host più specifici a quelli più generici.

Ad esempio, abbiamo, dal più specifico al più generico:

localhost (che identifica una macchina qualunque)
'%.html.it' (che specifica una qualunque macchina da html.it)
'%' (che identifica una macchina qualunque).

Una volta ordinato in questo modo, esamina una riga alla volta fino a trovare la prima
che corrisponde all'utente che vuole collegarsi, e usa l'informazione ricavata da essa.
Per far ciò considera, a parità di host, prima i nomi utenti non vuoti e poi i nomi utenti
(che corrispondono ad un utente generico).
Ad esempio, nel nostro caso, pippo@localhost non ha bisogno di password, in quanto viene utilizzata per
l'autenticazione la riga

+-----------+-------+------------------+
| host      | user  | password         |
+-----------+-------+------------------+
| localhost |       |                  |
+-----------+-------+------------------+

che ha l'host più spcecifico e non la riga

+-----------+-------+------------------+
| host      | user  | password         |
+-----------+-------+------------------+
| %         | pippo | 09e7d92b43759e6c |
+-----------+-------+------------------+

CANCELLAZIONE DI UN UTENTE DAL DB
Per eliminare o modificare i diritti ad un utente useremo il comando DELETE per la cancellazione e UPDATE per la modifica
ed eseguire successivamente FLUSH privileges

ATTENZIONE!! Dovete accedere a MySQL con un' utenza che permetta la modifica del database mysql.


2) ISTRUZIONI GRANT , REVOKE e FLUSH

• GRANT
  Per evitare l'inserimento manuale e inserire tutti i campi delle tabella esiste la funzione GRANT
  
  

  mysql> grant all on poenta.* to tocio@localhost
  identified by "papeperoga";

  Questo comando farà in automatico tutti i cambiamenti alle tabelle necessarie al posto vostro.
  la prima parte dell' istruzione grant può usare la parola all, oppure una qualsiasi delle opzioni elencate nella tabella User
  per lo piu' le istruzioni SQL (select,create,drop,index,etc..).
  
  La seconda parte (on poenta.*) dell'istruzione grant identifica dove i privilegi verranno applicati
  i livelli di permessi sono i seguenti
  
• grant all on *.* = Diritti universali i privilegi sono inseriti nella tabella User
• grant all on database.* = Si applica su tutte le tabelle di un solo database
• grant all on database.table_name = Si applica su una singola tabella
• grant all(col1, col2) on database.table_name = Si applica a determinate colonne di una tabella di un database
  
  La terza parte (to tocio@localhost) indica indica l'utente al quale consentire l'accesso.
  Come abbiamo visto in precedenza il MySQL richiedesia un nome sia un host
  Nell'istruzione grant i due sono separati dal simbolo @
  
  la quarta parte (identified by "papeperoga")attribuisce una password all'utente.
  
  
  Per esaminare i diritti di un utente possiamo digitare
  
  

  mysql> show grants for tocio@localhost;
  +---------------------------------------------------------------------+
  | Grants for tocio@localhost                                          |
  +---------------------------------------------------------------------+
  | GRANT ALL PRIVILEGES ON *.* TO 'tocio'@'localhost' WITH GRANT OPTION|
  +---------------------------------------------------------------------+

  Tutto quello che dovete conoscere è lo username e la password.
  
  
• REVOKE
  Se volete togliere alcuni privilegi a un utente, potete usare un REVOKE
  
  es: revoke shutdown on *.* from tocio@localhost
  
  è come il GRANT fa il contrario l'unica differenza nella sintassi è il from anzichè to
  
  per rimuovere del tutto l'utente dovete fare tenendo condo che l'utente è identificato da un nome e un host
  
  delete from user where user='user' and host='host'
  
• FLUSH
  Questa funzione vi permette di ricaricare i diritti appena concessi senza utilizzare GRANT
  per ricaricarli useremo
  
  

  mysql> flush privileges

Grazie per questa pillola a GM e Bubu e i ragazzi dell'OT.
Questa pillola è il mio 3000 post..
Adesso lascio a voi la parola per consigli, critiche è modifiche..

[Ratatuia]

la premiata ditta Html.it è lieta di presentarvi il romanzo best seller di qst'anno: Gestione dei Privilegi in MySQL :metallica :sexpulp: :adhone:

[gm]

Aggiunta alle pillole nel thread in rilievo :sexpulp:

[Ratatuia]

Originariamente inviato da gm
Aggiunta alle pillole nel thread in rilievo :sexpulp:

:sexpulp: :sexpulp: :sexpulp: :sexpulp:

[andr3a]

bella ringo :metallica

[daniele_dll]

la pillola è bellizzima ^^ soprattutto x chi nn ha la + pallida idea di come lavorare su mysql

^^

aspetto quella dell'ftp ^^

anche...se preferirei sperimentare da soloz :sexpulp:

[}gu|do[z]{®©]

è lunga da leggere, ma mi piace com'è impaginata! [commento nuovo ]

grande ringo! :metallica