Guida alla gestione degli utenti MariaDB: come creare utenti, concedere privilegi e risolvere gli errori più comuni

Introduzione

1.1 Vuoi imparare a creare utenti in MariaDB?

Ti è mai capitato di dover creare un nuovo utente in MariaDB e configurare i privilegi appropriati?
Ad esempio, potresti aver incontrato le seguenti situazioni:

• Hai eseguito CREATE USER ma non riesci ad accedere
• Hai concesso privilegi con GRANT, ma non sono stati applicati
• Non sei sicuro di come configurare i permessi corretti in un ambiente di produzione
• Vuoi eliminare in modo sicuro gli utenti non necessari

Se ti trovi di fronte a questi problemi, questo articolo ti aiuterà a comprendere appieno l’intero processo, dalla creazione degli utenti alla gestione dei privilegi e all’eliminazione degli utenti in MariaDB.

1.2 Cosa imparerai in questo articolo

Questo articolo fornisce una spiegazione dettagliata dei seguenti aspetti della gestione degli utenti in MariaDB:

• Come creare e eliminare utenti in MariaDB
• Come concedere e gestire i privilegi (best practice per impostare correttamente i permessi)
• Errori comuni e come risolverli
• Migliori pratiche operative per ambienti di sviluppo e produzione

Tutti gli argomenti sono spiegati con esempi SQL adatti ai principianti, permettendoti di apprendere e praticare allo stesso tempo.

1.3 Pubblico di riferimento

Questo articolo è destinato a:

• Principianti: chi si avvicina per la prima volta alla gestione degli utenti in MariaDB
• Ingegneri: chi desidera configurare i permessi corretti negli ambienti di sviluppo
• Amministratori di database: chi vuole rafforzare la sicurezza negli ambienti di produzione

Anche se sei nuovo a MariaDB, questa guida fornisce informazioni pratiche che potrai applicare con sicurezza nelle operazioni reali.

Creazione di utenti in MariaDB

2.1 Comando base per la creazione di un utente

In MariaDB, crei un nuovo utente usando il comando CREATE USER.
Puoi specificare nome utente e password con la seguente sintassi:

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

Ad esempio, per creare un utente chiamato yamada su localhost con la password password123:

CREATE USER 'yamada'@'localhost' IDENTIFIED BY 'password123';

Dopo aver eseguito questo comando, l’utente yamada verrà creato e potrà accedere solo da localhost (login consentito solo sul server stesso).

2.2 Esempi di creazione di utenti in base al caso d’uso

Quando crei utenti in MariaDB, puoi specificare da dove sono consentite le connessioni usando il campo hostname.
È importante configurare le impostazioni in modo appropriato in base al tuo caso d’uso.

Usare '%' consente connessioni da tutti gli host, ma
ciò comporta rischi di sicurezza significativi e non è consigliato per gli ambienti di produzione.
In produzione è preferibile specificare gli indirizzi IP o configurare l’accesso tramite una VPN.

2.3 Note importanti nella creazione di utenti

Ci sono diverse considerazioni importanti da tenere presente quando si creano utenti.

(1) Verifica degli utenti esistenti

Prima di creare un utente, è fondamentale accertarsi che lo stesso utente non esista già.
Puoi elencare gli utenti attualmente registrati con il seguente comando:

SELECT user, host FROM mysql.user;

Esempio di output:

+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | localhost |
| yamada  | localhost |
+---------+-----------+

Se esiste già un utente con lo stesso username@hostname, l’esecuzione di CREATE USER genererà un errore.

(2) Cosa fare se l’utente esiste già

In MariaDB non è possibile sovrascrivere un utente esistente ricreandolo.
Se lo stesso utente è già presente, utilizza una delle seguenti soluzioni:

• Modifica la password dell’utente esistente

  ALTER USER 'yamada'@'localhost' IDENTIFIED BY 'newpassword123';
  

• Elimina l’utente non necessario e creane uno nuovo

  DROP USER 'yamada'@'localhost';
  CREATE USER 'yamada'@'localhost' IDENTIFIED BY 'password123';
  

Gestione dei privilegi in MariaDB

3.1 Concedere privilegi

Sintassi di base

In MariaDB, è possibile concedere privilegi a un utente utilizzando il comando GRANT.

GRANT privilege ON database_name.table_name TO 'username'@'hostname';

Ad esempio, per concedere tutti i privilegi su test_db all’utente yamada:

GRANT ALL PRIVILEGES ON test_db.* TO 'yamada'@'localhost';

Significato di questo comando:

• ALL PRIVILEGES : Concede tutti i privilegi
• test_db.* : Si applica a tutte le tabelle nel database test_db
• 'yamada'@'localhost' : Si applica all’utente yamada che accede da localhost

3.2 Privilegi Comuni Utilizzati

In MariaDB, è possibile concedere privilegi come i seguenti:

Se si desidera consentire solo operazioni specifiche, è consigliabile concedere i privilegi individualmente piuttosto che concedere tutto.

3.3 Impostazioni dei Privilegi per Caso d’Uso

È importante assegnare i privilegi corretti per ogni situazione.

Negli ambienti di produzione, evitare di concedere ALL PRIVILEGES troppo facilmente.

3.4 Controlla i Privilegi Concessi

Per controllare i privilegi concessi, utilizzare il comando SHOW GRANTS.

SHOW GRANTS FOR 'username'@'hostname';

Ad esempio, per controllare i privilegi per l’utente yamada:

SHOW GRANTS FOR 'yamada'@'localhost';

Output di esempio:

+-----------------------------------------------------------------------------------+
| Grants for yamada@localhost                                                       |
+-----------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON test_db.* TO 'yamada'@'localhost'                |
+-----------------------------------------------------------------------------------+

Questo mostra i privilegi attualmente concessi all’utente.

3.5 Revoca Privilegi (Rimuovi Permessi)

Per revocare privilegi specifici da un utente, utilizzare il comando REVOKE.

Sintassi Base

REVOKE privilege ON database_name.table_name FROM 'username'@'hostname';

Ad esempio, per revocare il privilegio UPDATE dall’utente yamada:

REVOKE UPDATE ON test_db.* FROM 'yamada'@'localhost';

Revoca Tutti i Privilegi

REVOKE ALL PRIVILEGES ON *.* FROM 'yamada'@'localhost';

3.6 Cosa Fare Quando le Modifiche ai Privilegi Non Hanno Effetto

In MariaDB, le modifiche ai privilegi potrebbero non essere applicate immediatamente. Se ciò accade, eseguire il seguente comando:

FLUSH PRIVILEGES;

Eseguire questo comando forza MariaDB a ricaricare le tabelle dei privilegi in modo che le modifiche abbiano effetto immediatamente.

Eliminazione Utenti in MariaDB

4.1 Elimina un Utente

Per eliminare un utente MariaDB, utilizzare il comando DROP USER.

Sintassi Base

DROP USER 'username'@'hostname';

Ad esempio, per eliminare l’utente yamada da localhost:

DROP USER 'yamada'@'localhost';

Dopo aver eseguito questo comando, l’utente yamada verrà completamente rimosso dal sistema MariaDB.

4.2 Cosa Controllare Prima di Eliminare un Utente

Prima di eliminare un utente, è necessario eseguire alcuni controlli importanti.
Fare attenzione a non eliminare utenti necessari per errore.

(1) Controlla se l’Utente Destinatario Esiste

In MariaDB, tentare di eliminare un utente inesistente causerà un errore.
Eseguire il seguente comando per confermare che l’utente destinatario esista:

SELECT user, host FROM mysql.user;

Output di esempio:

+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | localhost |
| yamada  | localhost |
| admin   | 192.168.1.100 |
+---------+-----------+

Se l’utente yamada appare in questa lista, l’utente può essere eliminato.

(2) Controlla se l’Utente è Attualmente Connesso

In MariaDB, se l’utente che si sta eliminando ha attualmente una sessione attiva,
l’eliminazione potrebbe non avere effetto immediatamente per quella sessione.
Controllare se l’utente è attivo utilizzando il seguente comando:

SELECT user, host FROM information_schema.processlist;

Output di esempio:

+---------+-----------+
| user    | host      |
+---------+-----------+
| yamada  | localhost |
| root    | localhost |
+---------+-----------+

In questo esempio, l’utente yamada è attualmente attivo, quindi dovresti terminare la sessione prima dell’eliminazione.

(3) Terminare forzatamente una sessione

Puoi terminare forzatamente una sessione specifica usando il comando KILL.

1. Prima, controlla l’ID del processo (la colonna ID):

   SELECT id, user, host FROM information_schema.processlist;
   

Esempio di output:

+----+---------+-----------+
| id | user    | host      |
+----+---------+-----------+
| 10 | yamada  | localhost |
| 11 | root    | localhost |
+----+---------+-----------+

2. Se yamada ha l’ID processo 10, terminare con:

   KILL 10;
   

4.3 Pulizia post-eliminazione

Dopo aver eliminato un utente, potrebbero rimanere informazioni sui privilegi residui nel database. In tali casi, esegui FLUSH PRIVILEGES per aggiornare le tabelle dei privilegi.

FLUSH PRIVILEGES;

Eseguire questo garantisce che le informazioni dell’utente eliminato siano completamente rimosse dal sistema di database.

4.4 Note importanti quando si eliminano gli utenti

Tieni presente i seguenti punti quando elimini gli utenti:

1. Gli utenti eliminati non possono essere ripristinati

• Una volta eseguito DROP USER, l’utente eliminato non può essere recuperato.
• Se eliminato per errore, devi ricreare l’utente usando CREATE USER.

1. I privilegi potrebbero dover essere trasferiti

• Se l’utente eliminato era responsabile di operazioni critiche del database, devi trasferire i privilegi necessari a un altro utente appropriato in anticipo.

1. Fai attenzione negli ambienti di produzione

• Negli ambienti di produzione, un’eliminazione improvvisa può causare tempi di inattività del sistema o errori.
• È fortemente consigliato analizzare l’impatto in anticipo e creare un backup.

Errori comuni e soluzioni (FAQ)

Gestendo gli utenti in MariaDB, potresti incontrare vari errori. Questa sezione spiega gli errori più comuni, le loro cause e come risolverli.

5.1 FAQ per principianti

Q1: Qual è la differenza nella gestione degli utenti tra MariaDB e MySQL?

MariaDB e MySQL generalmente usano la stessa sintassi, ma ci sono alcune differenze di funzionalità.

MariaDB supporta CREATE ROLE, abilitando la gestione degli utenti basata sui ruoli.

Q2: Qual è la differenza tra localhost e %?

Nella gestione degli utenti di MariaDB, specificare il nome host è fondamentale.

• localhost : Consente connessioni solo dalla macchina locale
• % : Consente connessioni da qualsiasi host (rischio di sicurezza)
• 192.168.1.100 : Consente connessioni solo da un indirizzo IP specifico
• 192.168.1.% : Consente connessioni solo da una subnet specifica (192.168.1.*)

5.2 Risoluzione dei problemi

Q3: Come risolvere “ERROR 1396 (HY000): Operazione CREATE USER fallita”

Causa: L’utente specificato esiste già, quindi non è possibile creare un nuovo utente.

Soluzione: Verifica se l’utente esiste e, se necessario, eliminalo o modificalo.

1. Verifica gli utenti esistenti

   SELECT user, host FROM mysql.user WHERE user='yamada';
   

2. Elimina se non necessario

   DROP USER 'yamada'@'localhost';
   

3. Cambia la password invece

   ALTER USER 'yamada'@'localhost' IDENTIFIED BY 'newpassword123';
   

Q4: I privilegi concessi con GRANT non vengono applicati

Causa: La cache dei privilegi di MariaDB non è stata aggiornata.

Soluzione: Esegui FLUSH PRIVILEGES per ricaricare i privilegi.

FLUSH PRIVILEGES;

Q5: Impossibile connettersi da remoto anche usando %

Causa: Le connessioni remote potrebbero essere limitate nel file di configurazione di MariaDB.

Soluzione:

1. Modifica il file di configurazione /etc/mysql/my.cnf (o /etc/mysql/mariadb.conf.d/50-server.cnf) e cambia il valore di bind-address in 0.0.0.0 .

   bind-address = 0.0.0.0
   

2. Riavvia MariaDB

   sudo systemctl restart mariadb
   

3. Usa % nel comando GRANT per consentire le connessioni

   GRANT ALL PRIVILEGES ON test_db.* TO 'remote_user'@'%' IDENTIFIED BY 'password';
   

Q6: Ottenere l’errore “Access denied for user ‘user’@’host'”

Cause: L’utente non ha i privilegi appropriati, o la password è errata.

Solution:

1. Verifica i privilegi attuali

   SHOW GRANTS FOR 'user'@'host';
   

2. Concedi i privilegi appropriati

   GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'host';
   

3. Verifica di aver effettuato l’accesso con la password corretta

   mysql -u user -p
   

Conclusione

La gestione degli utenti MariaDB è essenziale per migliorare la sicurezza del database e l’efficienza operativa.
In questo articolo, abbiamo trattato in dettaglio creazione di utenti, gestione dei privilegi, eliminazione di utenti e errori comuni con le loro soluzioni.

6.1 Riepilogo

① Creazione di utenti in MariaDB

• Crea nuovi utenti usando il comando CREATE USER
• Limita l’accesso usando @'hostname'
• Sempre verifica gli utenti esistenti prima di crearne di nuovi

② Gestione dei privilegi in MariaDB

• Usa GRANT per assegnare i privilegi minimi necessari
• Usa SHOW GRANTS per verificare i privilegi attuali
• Usa REVOKE per rimuovere i privilegi non necessari
• Applica le modifiche con FLUSH PRIVILEGES

③ Eliminazione di utenti in MariaDB

• Elimina gli utenti usando il comando DROP USER
• Verifica sessioni attive prima dell’eliminazione
• Pulisci la cache dei privilegi usando FLUSH PRIVILEGES

④ Errori comuni e soluzioni

• ERROR 1396 (HY000) → Verifica la presenza di utenti esistenti ed elimina se necessario
• Modifiche ai privilegi non applicate → Esegui FLUSH PRIVILEGES
• Access denied for user → Verifica privilegi e password
• Impossibile connettersi in remoto con % → Regola la configurazione bind-address

6.2 Buone pratiche per la gestione degli utenti MariaDB

• Concedi solo i privilegi minimi richiesti
• Evita di concedere casualmente ALL PRIVILEGES
• Limita gli utenti di produzione a SELECT, INSERT, UPDATE, DELETE dove possibile
• Verifica sempre le modifiche ai privilegi
• Usa SHOW GRANTS per confermare che le configurazioni siano corrette
• Analizza l’impatto prima di eliminare gli utenti
• Sii cauto in produzione e crea backup
• Applica pratiche di sicurezza robuste
• Applica restrizioni IP per connessioni remote
• Imposta una password forte per l’utente root
• Rimuovi regolarmente utenti e privilegi non necessari

6.3 Considerazioni finali

Grazie a questa guida, hai imparato tutto, dalle basi alle tecniche avanzate di gestione degli utenti MariaDB.
Usa queste conoscenze per ottenere una amministrazione del database sicura ed efficiente.

Continueremo a condividere utili approfondimenti su MariaDB, quindi resta sintonizzato! 🚀