Dans le monde de la gestion de bases de données, le contrôle de l'accès des utilisateurs est crucial pour maintenir l'intégrité et la sécurité des données. Cet article de blog vous guidera à travers un scénario réel de configuration des autorisations utilisateur MySQL, y compris le processus, les pièges potentiels et les étapes de débogage.

Le scénario

Imaginez que vous êtes administrateur de bases de données dans une entreprise qui possède plusieurs bases de données :

• Bases de données originales : produits, clients, commandes, analyses
• Copier les bases de données : products_copy, customer_copy,orders_copy, Analytics_copy

Votre tâche consiste à configurer les autorisations pour un utilisateur nommé « analyste » avec les exigences suivantes :

1. L'analyste doit être capable de visualiser les bases de données et les tables des bases de données d'origine.
2. L'analyste ne doit pas être en mesure de modifier les bases de données originales.
3. L'analyste ne doit pas être en mesure de créer de nouvelles bases de données.
4. L'analyste doit avoir un accès complet (afficher, modifier, supprimer, créer des tables) aux bases de données « _copy ».

Voyons comment nous pouvons y parvenir en utilisant les instructions GRANT et REVOKE de MySQL.

Étape 1 : configuration initiale

Tout d'abord, nous devons nous connecter au serveur MySQL avec un compte administratif :

mysql -h hostname -P port -u admin -p

Remplacez « nom d'hôte », « port » et « admin » par les détails réels de votre serveur et le nom d'utilisateur administrateur.

Étape 2 : Créer l'utilisateur

Si l'utilisateur n'existe pas déjà, il faut le créer :

CREATE USER 'analyst'@'%' IDENTIFIED BY 'password';

Remplacez « mot de passe » par un mot de passe fort et sécurisé.

Étape 3 : Accorder les autorisations nécessaires

Maintenant, accordons les autorisations requises :

-- Grant SELECT on original databases
GRANT SELECT ON products.* TO 'analyst'@'%';
GRANT SELECT ON customers.* TO 'analyst'@'%';
GRANT SELECT ON orders.* TO 'analyst'@'%';
GRANT SELECT ON analytics.* TO 'analyst'@'%';

-- Grant all privileges on copy databases
GRANT ALL PRIVILEGES ON products_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON customers_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON orders_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON analytics_copy.* TO 'analyst'@'%';

-- Grant global privileges
GRANT PROCESS, SHOW DATABASES ON *.* TO 'analyst'@'%';

-- Apply the changes
FLUSH PRIVILEGES;

Étape 4 : Vérifier les autorisations

Après avoir configuré les autorisations, il est crucial de les vérifier :

SHOW GRANTS FOR 'analyst'@'%';

Débogage et dépannage

Problème 1 : Trop de privilèges

Dans notre scénario, nous avons initialement rencontré un problème où « analyste » disposait de trop de privilèges :

mysql> SHOW GRANTS FOR 'analyst'@'%';
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| Grants for analyst@%                                                                                                                                                                                                                                                                                                              |
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO "analyst"@"%" WITH GRANT OPTION |
| GRANT REPLICATION_APPLIER,ROLE_ADMIN ON *.* TO "analyst"@"%" WITH GRANT OPTION                                                                                                                                                                                                                                                    |
...
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

Solution:

Pour résoudre ce problème, nous avons révoqué tous les privilèges, puis accordé uniquement ceux nécessaires :

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'analyst'@'%';
GRANT PROCESS, SHOW DATABASES ON *.* TO 'analyst'@'%';
-- Then re-grant the specific permissions as shown in Step 3

Problème 2 : autorisations manquantes sur les bases de données de copie

Après avoir corrigé les privilèges excessifs, nous avons remarqué que les autorisations pour les bases de données de copie étaient manquantes :

mysql> SHOW GRANTS FOR 'analyst'@'%';
 ----------------------------------------------------- 
| Grants for analyst@%                                |
 ----------------------------------------------------- 
| GRANT PROCESS, SHOW DATABASES ON *.* TO "analyst"@"%"|
| GRANT SELECT ON "products".* TO "analyst"@"%"       |
| GRANT SELECT ON "customers".* TO "analyst"@"%"      |
| GRANT SELECT ON "orders".* TO "analyst"@"%"         |
| GRANT SELECT ON "analytics".* TO "analyst"@"%"      |
 ----------------------------------------------------- 

Solution:

Nous avons ajouté les autorisations manquantes pour les bases de données de copie :

GRANT ALL PRIVILEGES ON products_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON customers_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON orders_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON analytics_copy.* TO 'analyst'@'%';
FLUSH PRIVILEGES;

Résultat final

Après avoir appliqué toutes ces modifications et corrections, les subventions finales devraient ressembler à ceci :

mysql> SHOW GRANTS FOR 'analyst'@'%';
 ----------------------------------------------------- 
| Grants for analyst@%                                |
 ----------------------------------------------------- 
| GRANT PROCESS, SHOW DATABASES ON *.* TO "analyst"@"%"|
| GRANT SELECT ON "products".* TO "analyst"@"%"       |
| GRANT SELECT ON "customers".* TO "analyst"@"%"      |
| GRANT SELECT ON "orders".* TO "analyst"@"%"         |
| GRANT SELECT ON "analytics".* TO "analyst"@"%"      |
| GRANT ALL PRIVILEGES ON "products_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "customers_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "orders_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "analytics_copy".* TO "analyst"@"%"|
 ----------------------------------------------------- 

Conclusion

La configuration des autorisations utilisateur MySQL appropriées peut être délicate, mais c'est un aspect crucial de la gestion de la base de données. En utilisant soigneusement les instructions GRANT et REVOKE et en vérifiant toujours les résultats, vous pouvez créer un environnement sécurisé et fonctionnel pour vos utilisateurs.

N'oubliez pas ces points clés :

1. Commencez toujours par le principe du moindre privilège.
2. Utilisez SHOW GRANTS pour vérifier les autorisations après avoir apporté des modifications.
3. Soyez prudent avec les privilèges globaux (ON .).
4. N'oubliez pas de FLUSH PRIVILEGES après avoir apporté des modifications.

En suivant ces directives et les étapes décrites dans cet article, vous serez bien équipé pour gérer efficacement les autorisations des utilisateurs MySQL.