I permessi in MySql

Il sistema dei permessi di MySQL è un sistema piuttosto avanzato ma non standard, basato sul contenuto del database mysql che troverete preinstallato sul vostro server dopo l’esecuzione dello script mysql_install_db (se avete seguito il nostro tutorial per l’installazione nella lezione 2 lo script sarà già stato eseguito automaticamente).

Il primo concetto essenziale da tener presente è che l’identificazione dell’utente non avviene semplicemente attraverso il suo nome utente, ma dalla combinazione di questo con la macchina da cui l’utente si collega. Quindi due utenti che si collegano con lo stesso nome ma da due indirizzi diversi, per MySQL sono due utenti diversi.

La prima ad essere consultata quando un utente cerca di connettersi al server è la tabella user. Il server cerca infatti di riconoscere l’utente in base ai valori contenuti nelle colonne ‘Host’ e ‘User’ di tale tabella: una volta che l’utente è stato “riconosciuto”, gli sarà consentito l’accesso se digiterà la password specificata sulla tabella per quella combinazione di utente e host.

La colonna ‘Host’ della tabella user può contenere nomi host (ad es. www.mysql.com) oppure indirizzi ip. È possibile utilizzare caratteri “wild card” che rappresentano un carattere (_) o “n” caratteri (%). Quindi ad esempio il valore ‘%.mysql.com’ sarà considerato valido per qualsiasi dominio di terzo livello appartenente a mysql.com.

Nel caso venga utilizzato l’indirizzo ip, sarà possibile anche indicare una maschera per specificare quanti bit devono essere utilizzati per trovare la corrispondenza nell’indirizzo; è da notare però che il numero di bit deve essere necessariamente multiplo di 8 (8, 16, 24, 32). Ad esempio il valore ’151.42.62.0/255.255.255.0′ significa che la riga è valida per gli IP da 151.42.62.0 a 151.42.62.255.

Nella colonna ‘User’ sono contenuti evidentemente i nomi degli utenti che si possono collegare. Qui non è consentito l’utilizzo delle wildcard; è consentito però lasciare vuoto il valore: in questo caso tutti gli utenti che si collegheranno dal nome host (o indirizzo IP) corrispondente saranno riconosciuti, qualsiasi nome utilizzino.

In questo caso però saranno considerati da MySQL come ‘utenti anonimi’: questo significa che il nome utente col quale si sono presentati non sarà valido quando verranno fatti i controlli relativi ai permessi per le varie operazioni.

È evidente che con questo sistema può capitare che un utente, quando cerca di collegarsi, possa essere riconosciuto in base al contenuto di più righe della tabella user: vediamo infatti un esempio:

+-----------+----------+-
| Host      | User     |
+-----------+----------+-
| %         | paolo    |
| %         | luca     |
| localhost | paolo    |
| localhost |          |
+-----------+----------+-

In questa situazione, se l’utente paolo si collega da localhost, sia la prima riga che la terza sono in grado di riconoscerlo, in quanto il valore ‘%’ vale per qualsiasi host. Allo stesso modo, se si presenta luca (sempre da localhost), sarà riconosciuto dalla seconda riga ma anche dalla quarta, in quanto il nome utente vuoto corrisponde a qualsiasi utente.

 

Tuttavia MySQL determina una sola riga da utilizzare per riconoscere l’utente (e quindi assegnargli poi i relativi privilegi).

Tale riga viene scelta riconoscendo l’utente prima in base al nome host, e fra i nomi host viene privilegiato quello più specifico (quindi senza wildcard); di seguito viene cercato il nome dell’utente, e anche in questo caso il nome esatto ha la precedenza sull’utente anonimo. La prima riga che corrisponde al nome utente sulla base di questi criteri è quella che verrà utilizzata per l’autenticazione. Quindi, nel nostro esempio precedente, quando paolo si collega da localhost verrà riconosciuto dalla terza riga (nome host più specifico), mentre la prima riga sarà usata quando si collega da altri indirizzi.

Per quanto riguarda luca invece la situazione può facilmente trarre in inganno, perchè quando si collega da localhost sarà riconosciuto in base alla quarta riga e non alla seconda: infatti il riconoscimento del nome host ha la precedenza su quello del nome utente, e quindi la prima riga che soddisfa le credenziali di luca@localhost è quella con utente anonimo e localhost: di conseguenza la connessione di luca avverrà come utente anonimo e non con il suo nome utente!

Naturalmente, una volta riconosciuto l’utente, questi dovrà fornire la password corretta, che il server confronterà con quella contenuta nell’omonima colonna della tabella user.

Ci soffermeremo fra poco sul modo in cui la password viene memorizzata; per il momento vogliamo citare il caso in cui la colonna password della tabella sia vuota. Infatti si potrebbe pensare che in questo caso all’utente venga sempre concesso l’accesso, ma non è così: infatti l’assenza di password significa che l’utente non deve digitare una password; in caso contrario l’accesso gli verrà sempre negato!

Possiamo utilizzare due funzioni di MySQL per sapere con quale utente ci siamo presentati, e con quale siamo stati autenticati:

SELECT USER();
-> mario@localhost
SELECT CURRENT_USER();
-> @localhost

Con USER otteniamo il nome utente e host con i quali abbiamo richiesto l’accesso, mentre CURRENT_USER ci indica quale utente ha usato MySQL per autenticarci.
Nell’esempio l’utente si è presentato come ‘mario’, ma è stato autenticato come utente anonimo.

Una volta ottenuto l’accesso al server, l’utente deve avere i permessi necessari per lavorare sui vari database. La tabella user contiene numerose colonne relative ai permessi (‘Select_priv’, ‘Insert_priv’, ‘Update_priv’ ecc.) ciascuna delle quali può contenere il valore ‘Y’ o ‘N’. La presenza di ‘Y’ significa che l’utente è autorizzato a compiere quell’operazione: il fatto di avere il permesso sulla tabella user implica che il permesso è valido per tutti i database del server. In sostanza, un utente ‘normale’ non dovrebbe avere permessi di questo tipo sulla tabella user.

La tabella db contiene invece i permessi relativi ai singoli database. La chiave di questa tabella è formata da User, Host e Db: quindi ogni riga specifica quali permessi ha un determinato utente su un determinato database. Anche in questo caso possiamo trovare le wildcard per host e db; la colonna db può anche essere vuota per indicare tutti i database. La colonna user invece, se vuota, vale solo per gli utenti anonimi.

Un caso particolare è quando il campo Host della tabella db è vuoto: in questo caso infatti entra in gioco la tabella host, che specifica i permessi sui vari database per i diversi host. Qui la chiave Host e Db, e ancora una volta è possibile avere le wildcard su entrambe le colonne. Il valore vuoto corrisponde alla wildcard ‘%’.

Quando viene usata la tabella host, i permessi per l’utente derivano dall’intersezione dei permessi trovati sulla tabella db con quelli concessi dalla tabella host: questo significa che l’operazione richiesta è consentita solo se il valore relativo è ‘Y’ su entrambe le tabelle, nelle righe corrispondenti. Vediamo un esempio:

Tabella db

Host Db User Select_priv Insert_priv
192.168.0.% acquisti paolo Y N
vend% paolo Y N

Tabella Host

Host Db Select_priv Insert_priv
192.168.0.11 % N N
192.168.0.% % Y Y

Ovviamente per semplicità abbiamo indicato solo due delle colonne relative ai privilegi.

Supponendo che l’utente paolo sia collegato dalla macchina 192.168.0.11 e che non abbia nessun permesso sulla tabella user, ipotizziamo che voglia lavorare sul database acquisti: in questo caso verrà riconosciuto dalla prima riga della tabella db, che gli consentirà di fare le select ma non le insert.

Quando invece paolo cercherà di lavorare sul database vendite, verrà riconosciuto dalla seconda riga, che però non riporta un indirizzo host, per cui il server andrà a verificare i permessi sulla tabella host. Su questa tabella la riga che gli compete è la prima, in quanto coincide esattamente con l’indirizzo della sua macchina ed è valida per qualsiasi database.

Questa riga però gli nega i permessi sia sulla insert che sulla select, per cui paolo, nonostante la tabella db lo autorizzi alle select, non è in grado di effettuare alcuna operazione sul database vendite. Se si fosse collegato da un’altra macchina con indirizzo 192.168.0.%, sarebbe riuscito almeno a fare le select (non le insert, che gli vengono comunque negate dalla tabella db).

Anche per queste tabelle quindi vale il principio che i valori di host, database e user vengono riconosciuti preferenzialmente in base alla maggiore specificità del contenuto dei campi, quando ci sono più righe che coincidono con la richiesta. Per questo paolo non può lavorare sul database vendite quando si collega da 192.168.0.11: la seconda riga della tabella host gli consentirebbe di farlo, ma la prima ha un indirizzo più specifico e quindi prevale.

Esistono poi le tabelle tables_priv e columns_priv, che contengono privilegi ancora più specifici in quanto relativi ad una singola tabella (nel primo caso) e addirittura ad una singola colonna (nel secondo caso).

Queste tabelle vengono consultate quando i privilegi assegnati dalle tabelle user, db e host non sono sufficienti a garantire il permesso di effettuare l’operazione richiesta. Entrambe contengono nella chiave i campi Host, Db e User, oltre ad una colonna Table_name che identifica la tabella interessata; la tabella columns_priv contiene un’ulteriore colonna Column_name che specifica a quale colonna si riferiscono i permessi. In queste tabelle solo il campo host può contenere wildcard od essere vuoto (in questo caso la riga vale per tutti gli indirizzi), mentre il database deve sempre essere specificato. Inoltre queste tabelle non contengono una colonna per ogni permesso come le precedenti, bensì le colonne Table_priv e Column_priv che contengono tutti i permessi assegnati dalla riga.

Riassumendo: quando il server MySQL deve decidere se un determinato utente ha il permesso di compiere l’operazione che sta richiedendo, il controllo viene fatto per passi successivi: prima si controlla la tabella user, poi la combinazione di db e host, infine tables_priv e columns_priv.

Da notare che ad ogni passaggio i permessi trovati vengono sommati a quelli precedenti: infatti una singola istruzione può richiedere più permessi, e un utente potrebbe averli “sparsi” sui vari livelli. Ovviamente quando tutti i permessi necessari sono stati trovati la ricerca si interrompe e l’istruzione viene eseguita; se invece si arriva in fondo senza averli trovati tutti l’istruzione verrà negata.

GRANT e REVOKE

I permessi possono essere gestiti in due modi: attraverso le istruzioni SQL GRANT e REVOKE, oppure con le normali istruzioni SQL (INSERT, UPDATE ecc.) sulle tabelle interessate.

La differenza da tenere presente è che nel primo caso le modifiche sono immediatamente effettive, mentre nel secondo caso è necessario usare il comando FLUSH PRIVILEGES per costringere MySQL a ricaricare in memoria le tabelle dei permessi.

Vediamo alcuni esempi di sintassi per GRANT e REVOKE:

GRANT SELECT ON acquisti.* TO luca@localhost IDENTIFIED BY ‘password’ WITH GRANT OPTION

Questa istruzione assegna il privilegio SELECT all’utente luca@localhost sul database acquisti. Se l’utente non esisteva in precedenza, la riga relativa viene aggiunta alla tabella user e ‘password’ sarà la sua password. Se l’utente esisteva già, la password viene sostituita.

 

Il permesso relativo alle SELECT sarà regisrato sulla tabella db, essendo assegnato a livello di database. Inoltre all’utente viene assegnato il permesso GRANT, grazie al quale sarà in grado di assegnare ad altri utenti i propri permessi. Attenzione: con questa opzione l’utente potrà assegnare ad altri tutti i propri permessi: non solo quelli ricevuti con questa istruzione, ma anche quelli che aveva già e quelli che riceverà in futuro.

GRANT ALL ON acquisti.ordini TO paolo@localhost

Questa istruzione assegna tutti i permessi sulla tabella ordini del database acquisti all’utente paolo@localhost. Non è stata specificata una password, per cui se l’utente esisteva già questa non sarà modificata. Se invece l’utente non esisteva, viene creato senza password.

Attenzione però: l’opzione sql-mode del server (v. lez. 4) prevede il valore NO_AUTO_CREATE_USER fra quelli possibili. Se questo valore è attivo, non sarà possibile creare implicitamente un utente senza password con una GRANT. In questo caso l’istruzione fallirebbe, a meno che ovviamente l’utente non fosse già esistente. In questo esempio abbiamo visto che non viene assegnato il permesso GRANT, ma se l’utente lo possedeva già sarà comunque in grado di riassegnare questi permessi.

REVOKE SELECT on acquisti.* FROM luca@localhost
REVOKE ALL PRIVILEGES, GRANT OPTION FROM paolo@localhost

Con la prima istruzione togliamo il privilegio SELECT sul db acquisti all’utente luca@localhost.
Con la seconda togliamo tutti i privilegi sulle tabelle più quello di GRANT a paolo@localhost. In questo caso l’utente rimarrà privo di privilegi, ma la sua utenza non viene comunque eliminata dalla tabella user.

 

È importante ricordare che quando si elimina un database o una tabella, tutti i permessi esistenti rimangono attivi. Ovviamente questo è influente nel caso in cui venissero ricreati oggetti con lo stesso nome.

Vediamo ora alcune altre istruzioni relative alla gestione dei permessi:

CREATE USER alberto@localhost;
CREATE USER fabio@localhost IDENTIFIED BY ‘password’;
DROP USER alberto@localhost;
SET PASSWORD = PASSWORD(‘pw’);
SET PASSWORD FOR paolo@localhost = PASSWORD(‘pw’);

La prima istruzione crea un utente senza password (in questo caso funziona anche se NO_AUTO_CREATE_USER è attivo); la seconda crea un utente con password. Tali utenti non devono essere già esistenti. La terza istruzione elimina un utente. La quarta imposta la password ‘pw’ per l’utente collegato; l’ultima imposta la password ‘pw’ per l’utente paolo@localhost.

Le prime tre istruzioni richiedono il permesso CREATE USER; la quarta è possibile per chiunque sia collegato come utente non anonimo; la quinta richiede il permesso UPDATE sul database mysql.

Vediamo ora quali sono i principali permessi che possono essere assegnati ad un utente relativamente alle tabelle e ai database, e le istruzioni che autorizzano:

 

Permesso Istruzioni
ALL tutte esclusa GRANT
ALTER ALTER TABLE
CREATE CREATE TABLE
CREATE TEMPORARY TABLES CREATE TEMPORARY TABLE
CREATE VIEW CREATE VIEW
DELETE DELETE
DROP DROP TABLE
INDEX CREATE INDEX, DROP INDEX
INSERT INSERT
LOCK TABLES LOCK TABLES
SELECT SELECT
SHOW VIEW SHOW CREATE VIEW
UPDATE UPDATE
USAGE nessuna
GRANT OPTION GRANT, REVOKE

 

Ci sono alcuni permessi di tipo amministrativo che non ha senso riferire ad un database: tali permessi si trovano infatti solo sulla tabella user. Vediamone alcuni:

 

Permesso Istruzioni
CREATE USER CREATE USER, DROP USER, RENAME USER, REVOKE ALL PRIVILEGES
FILE SELECT … INTO OUTFILE, LOAD DATA INFILE
PROCESS SHOW FULL PROCESSLIST
RELOAD FLUSH
SHOW DATABASES SHOW DATABASES
SHUTDOWN mysqladmin shutdown
SUPER KILL, SET GLOBAL

 

SHOW DATABASES può essere utilizzata anche dagli utenti che non possiedono il permesso relativo (a meno che il server non sia stato avviato con l’opzione –skip-show-database).

Tuttavia questi non vedranno tutti i database presenti sul server, ma solo quelli per i quali possiedono diritti. In generale, comunque, gli utenti non amministrativi non dovrebbero mai possedere i permessi di quest’ultimo gruppo.

L’ultimo argomento da segnalare riguardo al sistema dei permessi è quello molto importante relativo alla memorizzazione delle password. Le password di ogni utente sono memorizzate nella colonna password della tabella user: un campo di 41 caratteri di cui il primo è un asterisco mentre i successivi 40 sono il risultato dell’algoritmo di hashing sulla password che è stata impostata con una GRANT o una CREATE USER, oppure con la funzione PASSWORD().

L’algoritmo di cifratura è monodirezionale, per cui non è possibile risalire alla password partendo dalla stringa criptata. Quando l’utente tenta di collegarsi e digita la password, il client esegue la cifratura ed invia la password criptata al server, che la confronta con quella memorizzata sul database.

Un elemento rilevante però è che l’algoritmo di cifratura attualmente utilizzato è stato introdotto con la versione 4.1 di MySQL: le versioni precedenti utilizzavano un algoritmo più semplice che produceva una stringa criptata di soli 16 caratteri. Questa situazione ha creato un problema di compatibilità che si verifica quando un client di tipo ‘vecchio’ (cioè di versione pre-4.1) tenta di collegarsi ad un server di tipo ‘nuovo’: i vecchi client infatti non sono in grado di supportare il nuovo algoritmo di hashing della password.

La conseguenza di ciò è che un client vecchio non può collegarsi ad un server nuovo se la password dell’utente che cerca di collegarsi è memorizzata col nuovo sistema. La situazione più tipica in cui ciò si verifica è l’utilizzo della vecchia estensione mysql del linguaggio PHP, molto utilizzato con MySQL. Tale estensione infatti è un client pre-4.1, e quindi non supporta il nuovo algoritmo di cifratura.

La soluzione è che un server di tipo nuovo può memorizzare password sia di un tipo che dell’altro: essendo evidente la differenza fra i due tipi di cifratura, il server è in grado di distinguerli facilmente e quindi può consentire l’accesso sia agli utenti con la vecchia password che a quelli con la nuova.

L’opzione del server –old-passwords è quella che determina quali tipi di password vengono creati dalle istruzioni GRANT, SET PASSWORD e dalla funzione PASSWORD(). Se il server viene avviato con questa opzione, le password generate saranno di tipo vecchio. Se invece l’opzione non è attiva, verranno generate password di tipo nuovo.

In sostanza quindi, per utilizzare un client di tipo vecchio su un server post-4.1, è necessario utilizzare un utente la cui password è cifrata col vecchio algoritmo.

Riassumendo:

  • Sul server possono coesistere utenti con la password vecchia e utenti con la password nuova. I client di tipo nuovo possono accedere a tutte le utenze, quelli di tipo vecchio solo alle utenze con la password vecchia.
  • Se il server è avviato senza opzione –old-passwords, le password vengono generate col formato nuovo. Ciò significa che i nuovi utenti creati non saranno accessibili dai vecchi client. Inoltre c’è il rischio che venga modificata la password di un utente che si collega con un client vecchio: la nuova password sarà cifrata con il nuovo algoritmo e l’utente non sarà più in grado di collegarsi. Per generare password di tipo vecchio è possibile utilizzare la funzione OLD_PASSWORD(‘password’).
  • Se il server è avviato con l’opzione –old-passwords, tutte le password verranno generate nel formato vecchio. In questo modo non verranno mai generate password lunghe, e anche quelle preesistenti, se modificate, torneranno al vecchio formato. In questo caso si mantiene la compatibilità con tutti i client, ma si perde la maggior sicurezza derivante dal nuovo algoritmo di cifratura

Evidentemente toccherà all’amministratore del database stabilire, in base alle esigenze dei propri utenti, se utilizzare o meno l’opzione --old-passwords.

Both comments and pings are currently closed.

Comments are closed.