Guide de gestion des utilisateurs MariaDB : comment créer des utilisateurs, accorder des privilèges et corriger les erreurs courantes

Introduction

1.1 Voulez-vous apprendre à créer des utilisateurs dans MariaDB?

Avez-vous déjà souhaité créer un nouvel utilisateur dans MariaDB et configurer les privilèges appropriés ?
Par exemple, vous avez peut-être rencontré les situations suivantes :

• Vous avez exécuté CREATE USER mais ne pouvez pas vous connecter
• Vous avez accordé des privilèges avec GRANT, mais ils n’ont pas été appliqués
• Vous n’êtes pas sûr de la façon de configurer les permissions appropriées dans un environnement de production
• Vous souhaitez supprimer en toute sécurité les utilisateurs inutiles

Si vous rencontrez ces problèmes, cet article vous aidera à comprendre pleinement l’ensemble du processus, de la création d’utilisateurs à la gestion des privilèges en passant par la suppression d’utilisateurs dans MariaDB.

1.2 Ce que vous apprendrez dans cet article

Cet article fournit une explication détaillée des aspects suivants de la gestion des utilisateurs MariaDB :

• Comment créer et supprimer des utilisateurs dans MariaDB
• Comment accorder et gérer les privilèges (meilleures pratiques pour des paramètres de permission appropriés)
• Erreurs courantes et comment les résoudre
• Meilleures pratiques opérationnelles pour les environnements de développement et de production

Tous les sujets sont expliqués avec des exemples SQL adaptés aux débutants, vous permettant d’apprendre et de pratiquer simultanément.

1.3 Public cible

Cet article s’adresse à :

• Débutants : ceux qui apprennent la gestion des utilisateurs MariaDB pour la première fois
• Ingénieurs : ceux qui souhaitent configurer les permissions appropriées dans les environnements de développement
• Administrateurs de bases de données : ceux qui visent à renforcer la sécurité dans les environnements de production

Même si vous êtes nouveau sur MariaDB, ce guide fournit des informations pratiques que vous pouvez appliquer en toute confiance dans des opérations réelles.

Création d’utilisateurs dans MariaDB

2.1 Commande de création d’utilisateur de base

Dans MariaDB, vous créez un nouvel utilisateur en utilisant la commande CREATE USER.
Vous pouvez spécifier un nom d’utilisateur et un mot de passe avec la syntaxe suivante :

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

Par exemple, pour créer un utilisateur nommé yamada sur localhost avec le mot de passe password123 :

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

Après l’exécution de cette commande, l’utilisateur yamada sera créé et pourra se connecter uniquement depuis localhost (connexion autorisée uniquement sur le serveur lui‑même).

2.2 Exemples de création d’utilisateurs selon les cas d’utilisation

Lors de la création d’utilisateurs dans MariaDB, vous pouvez spécifier d’où les connexions sont autorisées à l’aide du champ hostname.
Il est important de configurer les paramètres de manière appropriée en fonction de votre cas d’utilisation.

Utiliser '%' permet des connexions depuis tous les hôtes, mais
cela représente des risques de sécurité importants et n’est pas recommandé pour les environnements de production.
En production, il est préférable de spécifier des adresses IP ou de configurer l’accès via un VPN.

2.3 Remarques importantes lors de la création d’utilisateurs

Il y a plusieurs considérations importantes lors de la création d’utilisateurs.

(1) Vérifier les utilisateurs existants

Avant de créer un utilisateur, il est important de vérifier que le même utilisateur n’existe pas déjà.
Vous pouvez lister les utilisateurs actuellement enregistrés avec la commande suivante :

SELECT user, host FROM mysql.user;

Exemple de sortie :

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

Si un utilisateur avec le même username@hostname existe déjà, l’exécution de CREATE USER entraînera une erreur.

(2) Que faire si l’utilisateur existe déjà

Dans MariaDB, vous ne pouvez pas écraser un utilisateur existant en le recréant.
Si le même utilisateur existe déjà, utilisez l’une des approches suivantes :

• Modifier le mot de passe de l’utilisateur existant

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

• Supprimer l’utilisateur inutile et en créer un nouveau

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

Gestion des privilèges dans MariaDB

3.1 Accorder des privilèges

Syntaxe de base

Dans MariaDB, vous pouvez accorder des privilèges à un utilisateur en utilisant la commande GRANT.

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

Par exemple, pour accorder tous les privilèges sur test_db à l’utilisateur yamada :

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

Signification de cette commande :

• ALL PRIVILEGES : accorde tous les privilèges
• test_db.* : s’applique à toutes les tables de la base de données test_db
• 'yamada'@'localhost' : s’applique à l’utilisateur yamada se connectant depuis localhost

3.2 Privilèges couramment utilisés

Dans MariaDB, vous pouvez accorder des privilèges tels que les suivants :

Si vous ne souhaitez autoriser que des opérations spécifiques, il est recommandé d’accorder les privilèges individuellement plutôt que d’accorder tout.

3.3 Paramétrage des privilèges selon les cas d’utilisation

Il est important d’attribuer les bons privilèges pour chaque situation.

Dans les environnements de production, évitez d’accorder ALL PRIVILEGES trop facilement.

3.4 Vérifier les privilèges accordés

Pour vérifier les privilèges accordés, utilisez la commande SHOW GRANTS.

SHOW GRANTS FOR 'username'@'hostname';

Par exemple, pour vérifier les privilèges de l’utilisateur yamada :

SHOW GRANTS FOR 'yamada'@'localhost';

Exemple de sortie :

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

Cela montre les privilèges actuellement accordés à l’utilisateur.

3.5 Révoquer des privilèges (Supprimer des permissions)

Pour révoquer des privilèges spécifiques d’un utilisateur, utilisez la commande REVOKE.

Syntaxe de base

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

Par exemple, pour révoquer le privilège UPDATE de l’utilisateur yamada :

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

Révoquer tous les privilèges

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

3.6 Que faire lorsque les changements de privilèges ne prennent pas effet

Dans MariaDB, les changements de privilèges peuvent ne pas être appliqués immédiatement. Si cela se produit, exécutez la commande suivante :

FLUSH PRIVILEGES;

L’exécution de cette commande force MariaDB à recharger les tables de privilèges afin que vos modifications prennent effet immédiatement.

Suppression d’utilisateurs dans MariaDB

4.1 Supprimer un utilisateur

Pour supprimer un utilisateur MariaDB, utilisez la commande DROP USER.

Syntaxe de base

DROP USER 'username'@'hostname';

Par exemple, pour supprimer l’utilisateur yamada de localhost :

DROP USER 'yamada'@'localhost';

Après l’exécution de cette commande, l’utilisateur yamada sera complètement supprimé du système MariaDB.

4.2 Ce qu’il faut vérifier avant de supprimer un utilisateur

Avant de supprimer un utilisateur, il y a quelques vérifications importantes à effectuer.
Soyez prudent pour ne pas supprimer par erreur des utilisateurs nécessaires.

(1) Vérifier si l’utilisateur cible existe

Dans MariaDB, tenter de supprimer un utilisateur inexistant provoquera une erreur.
Exécutez la commande suivante pour confirmer que l’utilisateur cible existe :

SELECT user, host FROM mysql.user;

Exemple de sortie :

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

Si l’utilisateur yamada apparaît dans cette liste, l’utilisateur peut être supprimé.

(2) Vérifier si l’utilisateur est actuellement connecté

Dans MariaDB, si l’utilisateur que vous supprimez possède actuellement une session active,
la suppression peut ne pas prendre effet immédiatement pour cette session.
Vérifiez si l’utilisateur est actif en utilisant la commande suivante :

SELECT user, host FROM information_schema.processlist;

Exemple de sortie :

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

Dans cet exemple, l’utilisateur yamada est actuellement actif, vous devez donc terminer la session avant la suppression.

(3) Forcer la terminaison d’une session

Vous pouvez forcer la terminaison d’une session spécifique en utilisant la commande KILL.

1. Tout d’abord, vérifiez l’ID du processus (la colonne ID) :

   SELECT id, user, host FROM information_schema.processlist;
   

Exemple de sortie :

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

2. Si yamada a l’ID de processus 10, terminez-le avec :

   KILL 10;
   

4.3 Nettoyage après suppression

Après la suppression d’un utilisateur, des informations de privilèges résiduelles peuvent rester dans la base de données.
Dans de tels cas, exécutez FLUSH PRIVILEGES pour rafraîchir les tables de privilèges.

FLUSH PRIVILEGES;

L’exécution de cela garantit que les informations de l’utilisateur supprimé sont entièrement supprimées du système de base de données.

4.4 Notes importantes lors de la suppression d’utilisateurs

Gardez les points suivants à l’esprit lors de la suppression d’utilisateurs :

1. Les utilisateurs supprimés ne peuvent pas être restaurés

• Une fois que vous avez exécuté DROP USER, l’utilisateur supprimé ne peut pas être récupéré.
• Si la suppression est une erreur, vous devez recréer l’utilisateur en utilisant CREATE USER.

1. Les privilèges peuvent devoir être transférés

• Si l’utilisateur supprimé était responsable des opérations critiques de la base de données, vous devez transférer les privilèges nécessaires à un autre utilisateur approprié au préalable.

1. Soyez prudent dans les environnements de production

• Dans les environnements de production, une suppression soudaine peut entraîner des temps d’arrêt du système ou des erreurs.
• Il est fortement recommandé de analyser l’impact à l’avance et de créer une sauvegarde.

Erreurs courantes et solutions (FAQ)

Lors de la gestion des utilisateurs dans MariaDB, vous pouvez rencontrer diverses erreurs.
Cette section explique les erreurs les plus courantes, leurs causes et comment les résoudre.

5.1 FAQ pour débutants

Q1 : Quelle est la différence entre la gestion des utilisateurs MariaDB et MySQL ?

MariaDB et MySQL utilisent généralement la même syntaxe, mais il existe quelques différences de fonctionnalités.

MariaDB prend en charge CREATE ROLE, permettant une gestion des utilisateurs basée sur les rôles.

Q2 : Quelle est la différence entre localhost et % ?

Dans la gestion des utilisateurs MariaDB, la spécification du nom d’hôte est cruciale.

• localhost : Autorise les connexions uniquement depuis la machine locale
• % : Autorise les connexions depuis n’importe quel hôte (risque de sécurité)
• 192.168.1.100 : Autorise les connexions uniquement depuis une adresse IP spécifique
• 192.168.1.% : Autorise les connexions uniquement depuis un sous-réseau spécifique (192.168.1.*)

5.2 Dépannage

Q3 : Comment corriger « ERROR 1396 (HY000) : L’opération CREATE USER a échoué »

Cause : L’utilisateur spécifié existe déjà, donc un nouvel utilisateur ne peut pas être créé.

Solution : Vérifiez si l’utilisateur existe et supprimez-le ou modifiez-le si nécessaire.

1. Vérifier les utilisateurs existants

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

2. Supprimer si inutile

   DROP USER 'yamada'@'localhost';
   

3. Changer le mot de passe à la place

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

Q4 : Les privilèges accordés avec GRANT ne sont pas appliqués

Cause : Le cache de privilèges de MariaDB n’a pas été rafraîchi.

Solution : Exécutez FLUSH PRIVILEGES pour recharger les privilèges.

FLUSH PRIVILEGES;

Q5 : Impossible de se connecter à distance même en utilisant %

Cause : Les connexions à distance peuvent être restreintes dans le fichier de configuration de MariaDB.

Solution :

1. Modifiez le fichier de configuration /etc/mysql/my.cnf (ou /etc/mysql/mariadb.conf.d/50-server.cnf) et changez la valeur de bind-address à 0.0.0.0.

   bind-address = 0.0.0.0
   

2. Redémarrez MariaDB

   sudo systemctl restart mariadb
   

3. Utilisez % dans la commande GRANT pour autoriser les connexions

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

Q6 : Obtention de l’erreur « Access denied for user ‘user’@’host’ »

Cause : L’utilisateur ne possède pas les privilèges appropriés, ou le mot de passe est incorrect.

Solution :

1. Vérifier les privilèges actuels

   SHOW GRANTS FOR 'user'@'host';
   

2. Accorder les privilèges appropriés

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

3. Vérifier que vous vous connectez avec le bon mot de passe

   mysql -u user -p
   

Conclusion

La gestion des utilisateurs MariaDB est essentielle pour améliorer la sécurité des bases de données et l’efficacité opérationnelle. Dans cet article, nous avons couvert en détail la création d’utilisateurs, la gestion des privilèges, la suppression d’utilisateurs et les erreurs courantes avec leurs solutions.

6.1 Récapitulatif

① Création d’utilisateurs dans MariaDB

• Créez de nouveaux utilisateurs avec la commande CREATE USER
• Restreignez l’accès en utilisant @'hostname'
• Vérifiez toujours les utilisateurs existants avant d’en créer de nouveaux

② Gestion des privilèges dans MariaDB

• Utilisez GRANT pour attribuer les privilèges minimum nécessaires
• Utilisez SHOW GRANTS pour vérifier les privilèges actuels
• Utilisez REVOKE pour supprimer les privilèges inutiles
• Appliquez les changements avec FLUSH PRIVILEGES

③ Suppression d’utilisateurs dans MariaDB

• Supprimez les utilisateurs avec la commande DROP USER
• Vérifiez les sessions actives avant la suppression
• Nettoyez le cache des privilèges avec FLUSH PRIVILEGES

④ Erreurs courantes et solutions

• ERROR 1396 (HY000) → Vérifiez les utilisateurs existants et supprimez-les si nécessaire
• Les changements de privilèges ne sont pas appliqués → Exécutez FLUSH PRIVILEGES
• Access denied for user → Vérifiez les privilèges et le mot de passe
• Impossible de se connecter à distance avec % → Ajustez la configuration bind-address

6.2 Bonnes pratiques pour la gestion des utilisateurs MariaDB

• Accordez uniquement les privilèges minimum requis
• Évitez d’accorder ALL PRIVILEGES de manière désinvolte
• Restreignez les utilisateurs de production à SELECT, INSERT, UPDATE, DELETE lorsque c’est possible
• Vérifiez toujours les changements de privilèges
• Utilisez SHOW GRANTS pour confirmer que les configurations sont correctes
• Analysez l’impact avant de supprimer des utilisateurs
• Soyez prudent en production et créez des sauvegardes
• Appliquez des pratiques de sécurité strictes
• Appliquez des restrictions d’IP pour les connexions à distance
• Définissez un mot de passe fort pour l’utilisateur root
• Supprimez régulièrement les utilisateurs et privilèges inutiles

6.3 Conclusion finale

Grâce à ce guide, vous avez appris tout, des bases aux techniques avancées de gestion des utilisateurs MariaDB. Utilisez ces connaissances pour atteindre une administration de bases de données sécurisée et efficace.

Nous continuerons à partager des informations utiles sur MariaDB, restez à l’écoute ! 🚀