Lezione Precedente Lezione Successiva

Laboratorio di Sistemi Informativi

Gestione Utenti

Utenti di un DBMS

Una installazione di MySQL a regime può contenere vari database e varie tabelle in ogni database. In generale, nasce il problema che non tutti gli utenti di MySQL possono avere un accesso indiscriminato a questi dati. Ad esempio, l'ufficio stipendi in una impresa potrebbe aver bisogno di accedere all'archivio anagrafico dei dipendenti, ma solo in lettura. La modifica di questi dati spetta invece all'ufficio personale, e solo a questo deve essere consentito l'accesso ai dati anagrafici in scrittura.

Per fortuna, MySQL consente di dare diritti ben precisi ad utenti diversi. Il compito di assegnare questi diritti spetta all'amministratore di database (DBA: database administrator). Il DBA è una persona che si occupa della gestione e del corretto funzionamento del DBMS usato: installazione, creazione dei database, backup periodici dei dati sono tipici esempi di attività affidate al DBA. Non è invece detto che il DBA si occupi anche della progettazione dei database o delle applicazioni che li utilizzano: spesso questi compiti, almeno nelle grosse installazioni, vengono affidate a persone diverse, per cui il DBA ha solo il ruolo di coordinatore e di amministratore del sistema nel suo complesso.

Mentre siamo in argomento, esaminiamo altre tipice figure professionali che sono correlate al mondo dei DBMS:

Utenti e MySQL

Ogni qualvolta noi ci colleghiamo ad un server MySQL, esso ci identifica sulla base di due informazioni: il nome utente e il computer da cui ci colleghiamo, secondo la sintassi

<nomeutente>@<macchina da cui si collega>

o, in inglese

<username>@<hostname>

Ad esempio, l'utente pippo, quando è collegato dalla stessa macchina dove risiede il server, si chiama pippo@localhost, mentre per pluto collegato da pc-clei-001.unich.it si ha pluto@pc-clei-001.unich.it. Possiamo conoscere l'utente corrente con la funzione intrinseca user(), come in:

SELECT user();

+-----------------+
| user()          |
+-----------------+
| amato@localhost |
+-----------------+

Abbiamo visto che possiamo specificare il nome utente con cui ci vogliamo collegare facendo partire il client mysql con l'opzione -u

mysql -u pippo

Ricordiamo anche che è possibile usare l'opzione -h per indicare il server a cui collegarsi. Ad esempio:

mysql -h 172.272.11.34 -u pippo

si collega al computer il cui numero IP è 172.272.11.34 con il nome utente pippo.

N.B. Le opzione -u e -h possono essere usate anche con il programma mysqldump, che abbiamo esaminato nelle lezioni precedenti.

GRANT e REVOKE

Il comando principale che SQL mette a disposizione per concedere diritti e creare nuovi utenti è GRANT. Con GRANT è possibile concedere diritti ad un utente su tutti i dati di un server, su uno specifico database, su un tabella o su specifiche colonne di una tabella.

Diritti a livello di server

La sua sintassi è la seguente Alcuni dei privilegi che è possibile concedere sono:
Un po' particolari sono i seguenti:
Un privilegio concesso con questa sintassi riguarda tutti i database presenti in una macchina. Così il comando GRANT SELECT ON *.* TO pippo@localhost consente, all'utente pippo, quando si connette da localhost, di accedere in lettura a qualunque database presente nel computer. Non consente tuttavia alcuna modifica dei dati.

Il comando GRANT non revoca mai privilegi già concessi, ma semplicemente aggiunge nuovi privilegi. Così dopo i comandi

GRANT SELECT ON *.* TO pippo@localhost
GRANT INSERT ON *.* TO pippo@localhost

l'utente pippo@localhost avrà sia il privilegio SELECT che INSERT. Per eliminare i diritti già concessi, invece, è possibile usare l'istruzione REVOKE, che ha una sintassi simile a quella del comandoi GRANT:
Infine, per esaminare i privilegi che sono stati concessi a un utente, su può usare il comando SHOW GRANTS.
In pratica, vengono visualizzati i comandi GRANT che sarebbero necessari per concedere all'utente, su un'altra installazione di MySQL, gli stessi diritti che ha attualmente.

Autenticazione tramite password

Abbiamo visto che con il comando GRANT SELECT ON *.* TO pippo@localhost l'utente pippo ha accesso in lettura a tutti i dati memorizzati nel server.  Tuttavia, chiunque abbia accesso fisico alla macchina server può spacciarsi per pippo, poichè non viene effettuato nessun controllo sulla persona fisica che esegue il comando mysql -u pippo. Se poi avessimo concesso l'accesso all'utente pippo anche da altre macchine con  GRANT SELECT ON *.* TO  pippo@xyz.unich.it, allora chiunque avesse accesso alla macchina xyz.unich.it potrebbe collegarsi al nostro server MySQL con nome utente pippo e  leggere il contenuto di tutti i database.  Servirebbe poter immettere una password da richiedere al momento in cui qualcuno si vuole connettere con il nome utente pippo. Questo si può fare con il comando GRANT, come segue:

GRANT SELECT ON *.* TO pippo@localhost IDENTIFIED BY 'miapassword'

Da quel momento in poi, per collegarsi col nome di utente "pippo" dalla macchina locale, occorrerà fornire la password di accesso. A tale scopo, si può invocare mysql in questo modo:

mysql -u <username> -p

che chiede la password in maniera interattiva o con

mysql -u <username> -p<passwd>

che consente di specificarla dalla linea di comando.

ATTENZIONE! La prima cosa che un DBA deve fare quando installa MySQL (o un qualunque altro database) su una macchina condivisa tra più utenti, è assicurarsi che l'acceso al database con l'account root sia protetto da password!  Questo è possibile, ad esempio, con il comando:

GRANT USAGE ON *.* TO root@localhost IDENTIFIED BY '<password>';

Notare l'uso di GRANT USAGE per settare una password senza alterare i privilegi di un utente. Questo non è stato fatto sulle macchine in laboratorio visto che MySQL è installato solo a scopo didattico.

Diritti a livello di database

Si possono anche concedere diritti che non valgolo su tutti i dati di un server,ma selettivamente a livello di un singolo database. La sintassi è simile al caso precedente:

GRANT <lista privilegi> ON <nome database>.* TO <utente formale>

Analogamente per REVOKE.

Ad esempio supponiamo di concedere all'utente pippo la possibilità di agire indiscriminatamente sul database airdb:

GRANT ALL PRIVILEGES ON airdb.* TO pippo@localhost

In questo modo pippo può accedere in lettura a tutti i database, ed ha pieno accesso in lettura e scrittura su airdb. Notare che i diritti a livello database non vengono influenzati quando si revoca un diritto a livello di server. Ovvero, se noi diamo il comando:

REVOKE SELECT ON *.* FROM pippo@localhost

L'utente pippo conserve tutti i suoi diritti su airdb. Perde però tutti i diritti di lettura non esplicitamente concessi a livello di singolo database.

GRANT option

Se concediamo dei diritti ad un utente, possiamo anche consentirgli, a sua volta, di cedere questi diritti ad altri utenti. Per far ciò basta aggiungere la clausola WITH GRANT OPTION alla fine del comando GRANT:

GRANT ALL PRIVILEGES ON airdb.* TO pippo@localhost WITH GRANT OPTION

In questo modo non solo pippo potrà utilizzare come vuole il database airdb, ma potrà a sua volta concedere diritti su questo database ad altri utenti. Notare che un utente non può mai concedere diritti superiori di quelli di cui dispone.

Per revocare il diritto di grant, si usa REVOKE con il privilegio GRANT OPTION, ovvero:

REVOKE GRANT OPTION ON airdb.* FROM pippo@localhost

Diritti a livello di tabella e colonna

I diritti possono anche essere concessi a livello di singola tabella con

GRANT <lista privilegi> ON <nome database>.<nome tabella> TO <utente formale>

I privilegi sono gli stessi possibili nel caso di diritti a livello di server o di database. L'unica differenza è che i diritti CREATE o DROP a livello database consentono di eseguire le istruzioni CREATE DATABASE e DROP DATABASE, mentre in un diritto a livello di singola tabella, è possibile solo utilizzare la CREATE TABLE e la DROP TABLE.

Infine, i diritti a livello di colonna si concedono con

GRANT <privilegio>(<lista colonne>) ON <nome database>.<nome tabella> TO <lista utenti>

In questo caso, gli unici privilegi che si possono usare sono ALTER, DELETE, INSERT, SELECT e UPDATE. Ad esempio con

GRANT SELECT(id) ON airdb.aeroporti TO paperino@localhost

l'utente paperino potra accedere al database airdb e dare il comando

SELECT id FROM aeroporti

ma non il comando

SELECT * FROM aeroporti

Caratteri jolly e utenti formali

Finora abbiamo visto come si concedono dei diritti a degli utenti, specificando sia il nome utente che l'hostname. Ma come si fa, ad esempio, a concedere ad un utente il diritto di collegarsi al database ovunque si trovi nel mondo? Si può usare il carattere jolly % come hostname (che però va racchiuso tra accenti). Il % sta a indicare una stringa generica. Ad esempio:
Per i nomi utenti non è possibile usare il carattere jolly %. Tuttavia, è possibile specificare un nome utente vuoto, che sta ad indicare l'utente anonimo. Ad esempio:
Con il comando GRANT SELECT ON *.* TO pippo@`%` l'utente pippo può collegasi da qualunque macchina al server e avere accesso in lettura a tutti i database. Invece con GRANT SELECT ON *.* TO ``@localhost l'accesso in lettura viene garantito a tutti gli utenti locali che non hanno dei diritti più specifici.

Quando si usano comandi GRANT con caratteri jolly, spesso il sistema si comporta in modo inaspettato. Ad esempio, se diamo i comandi

GRANT ALL PRIVILEGES ON airdb.* TO a@`%.unich.it`
GRANT USAGE ON *.* TO a@`xyz.unich.it`

e dalla macchina xyz.unich.it ci colleghiamo con nome utente a, avremo o no l'accesso al database airdb? La risposta è no, non avremo accesso al database airdb. Se invece ci colleghiamo, sempre con nome utente a ma dalla macchina uvw.unich.it, allora avremo l'accesso desiderato.

Tentiamo di spiegare perchè. Indichiamo come utente reale la combinazione username@host effettiva di chi si connette a MySQL (ricavabile dalla funzione intrinseca USER) e utente formale la combinazione username@host indicata nei comandi GRANT e REVOKE (e che, a differenza dell'utente reale, può contenere caratteri jolly). Al momento della connessione, MySQL deve scegliere, a partire dall'utente reale che si sta connettendo, a quale utente formale farlo corrispondere, e quindi quale diritti concedergli. MySQL sceglie sempre, tra tutti gli utente formali disponibili che possono corrispondere a quello reale (in slang si dice che "matchano"), quello che ha il nome host più specifico. Per cui, siccome l'utente a@xyz.unich.it potrebbe corrispondere sia ad a@`%.unich.it` che ad a@`xyz.unich.it`, viene scelto ques'ultimo, perchè ha il nome host più specifico.  Quando invece ci colleghiamo come a@uvw.unich.it, soltanto a@`%.unich.it` può essere utilizzato, e quindi l'utente avrà i privilegi desiderati.

Per scoprire chi è l'utente formale attuale si può usare la funzione intrinseca
Se ci colleghiamo da xyz.unich.it il risultato sarà a@xyz.unich.it ma se ci colleghiamo da uvw.unich.it il risultato sarà a`@%.unich.it`. La cosa più strana è forse che, se aggiungiamo il comando

GRANT USAGE ON *.* to ``@`uvw.unich.it`

allora neanche l'utente a collegato da uvw.unich.it avrà il diritto di accesso al database airdb. Questo perchè il nome utente vuoto (quello dell'utente anonimo), può  "matchare" con qualunque altro nome utente. Pertanto, sia a@`%.unich.it` che ``@`uvw.unich.it` sono utenti formali che "matchano" con a@uvw.unich.it, ma il secondo ha il nome di host più specifico. Tutti gli utenti che si collegano da uvw.unich.it hanno, a questo punto, solo i privilegi concessi da quest'ultimo comando GRANT.

Per ripristinare la situazione che si aveva prima del comando GRANT USAGE ON *.* to ``@`uvw.unich.it` , non si può usare il comando REVOKE, perchè non ha alcun effetto. Occorre eliminare del tutto l'utente ``@`uvw.unich.it`  con il comando DROP USER.
Ad esempio drop DROP USER ``@`uvw.unich.it` l'utente a@uvw.unich.it potrà di nuovo accedere al database.

Esercizio 1

Scrivere i comandi GRANT necessari per concedere i seguenti diritti di accesso:
Controllare di aver dato i comandi giusti invitando un vicino a collegarsi al proprio server. A tal scopo, ricordatevi che si può usare il comando /sbin/ifconfig per determinare l'indirizzo IP di una macchina.

Esercizio 2

Supponiamo di avere i seguenti utenti formali:
  1. root@localhost
  2. andy@localhost
  3. ``@localhost
  4. andy@`%`
  5. tim@`%.imaginary.com`
  6. randy@`%`
A quali utenti formali vengono fatti corrispondere i seguenti utenti reali?

root@localhost
andy@localhost
george@localhost
andy@www.imaginary.com
randy@localhost
root@www.imaginary.com

Lezione Precedente Lezione Successiva