Cours MariaDB / Créer un utilisateur et limiter ses privilèges avec GRANT
Un compte MariaDB associe un nom d’utilisateur à un hôte ; GRANT lui accorde des opérations sur un périmètre précis. Pour autoriser seulement la lecture des produits, créez un compte distinct, accordez SELECT sur cette table et vérifiez les lectures permises comme les opérations refusées dans une nouvelle connexion de ce compte.
Distinguer le compte, la connexion et le périmètre
Le principe du moindre privilège consiste à accorder uniquement les droits nécessaires au travail attendu. Un programme qui affiche un catalogue n’a généralement pas besoin de créer des comptes ni de modifier les clients. Séparez sa connexion de celle de l’administrateur et définissez les besoins avant d’écrire GRANT.
Les définitions 'nom'@'localhost' et 'nom'@'127.0.0.1' peuvent désigner des comptes différents. Le protocole et la résolution des noms interviennent dans la sélection du compte. USER() décrit l’utilisateur et l’origine de la connexion, tandis que CURRENT_USER() indique le compte retenu pour le contrôle des privilèges ; ne supposez pas que leur affichage sera toujours identique.
| Périmètre | Exemple | Décision |
|---|---|---|
| Serveur | *.* | Réserver aux besoins d’administration correspondants |
| Base | shopcourse.* | Autoriser les objets de cette base |
| Table | shopcourse.c43_products | Limiter la lecture aux produits de cet exercice |
| Colonne | UPDATE(stock) | Limiter une modification à la colonne prévue |
Choisissez un mot de passe unique à la place de la chaîne de démonstration. Pour ouvrir une connexion, utilisez l’invite de mot de passe ou un fichier d’options dont l’accès est limité. Un secret placé dans les arguments d’une commande ou copié dans un compte rendu peut être exposé. Les sorties de SHOW GRANTS peuvent aussi contenir des informations d’authentification : conservez-les à l’abri.
Préparer les données avec le compte d’apprentissage
Ouvrez une connexion course_user vers le serveur d’apprentissage et la base shopcourse. Le compte doit pouvoir créer, remplir et nettoyer les tables de l’exercice. Le guide du client MariaDB et des fichiers SQL UTF-8 explique la connexion. Contrôlez SELECT VERSION(), DATABASE(); et SHOW TABLES; avant de poursuivre.
Les noms c43_customers, c43_products, c43_orders et c43_items sont réservés à ce jeu de données. S’ils existent, inspectez leurs définitions et leurs lignes, puis sauvegardez ce qui doit être conservé. Ne les remplacez que si vous avez établi qu’il s’agit d’objets jetables de cet exercice.
Enregistrez le bloc complet en UTF-8, par exemple dans C:/mariadb-lab/c43-droits.sql. Dans le client MariaDB, exécutez SOURCE C:/mariadb-lab/c43-droits.sql; avec le chemin réel. Les instructions SQL s’exécutent dans la connexion MariaDB, et non à l’invite du système.
USE shopcourse;
CREATE TABLE c43_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c43_order_customer FOREIGN KEY (customer_id)
REFERENCES c43_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c43_item_order FOREIGN KEY (order_id)
REFERENCES c43_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c43_item_product FOREIGN KEY (product_id)
REFERENCES c43_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c43_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c43_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c43_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c43_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
Vous obtenez quatre clients, quatre produits, quatre commandes et six détails. Les littéraux coréens restent identiques pour reproduire les résultats : les produits 1 à 4 sont un carnet, un stylo, une tasse et une lampe. Les clients 1 et 3 ont la ville Séoul, les clients 2 et 4 Busan et Daejeon. Suivez les identifiants et préservez les caractères UTF-8.
Gardez une seconde connexion d’administrateur pour CREATE USER et GRANT. Le droit de préparer les tables ne donne pas à lui seul celui de gérer des comptes. Vérifiez aussi que 'shop_reader43'@'127.0.0.1' n’existe pas déjà. Si ce nom est utilisé, choisissez un autre nom d’exercice dans toutes les étapes ; n’écrasez ni ne supprimez le compte existant.
Accorder SELECT sur la seule table des produits
Dans la connexion d’administrateur disposant des droits nécessaires, remplacez le mot de passe de démonstration puis exécutez :
CREATE USER 'shop_reader43'@'127.0.0.1' IDENTIFIED BY 'Replace_With_Unique_Lab_Password!';
GRANT SELECT ON shopcourse.c43_products TO 'shop_reader43'@'127.0.0.1';
SHOW GRANTS FOR 'shop_reader43'@'127.0.0.1';
CREATE USER crée le compte ; GRANT SELECT fixe son droit de lecture sur c43_products. Ce compte neuf n’a reçu ni lecture des clients et commandes, ni INSERT, UPDATE ou droit de définition des tables. N’ajoutez pas WITH GRANT OPTION : l’exercice ne demande pas que ce lecteur transmette ses droits.
Examinez le périmètre indiqué par SHOW GRANTS, sans publier les secrets éventuels. Une ligne USAGE ne signifie pas que l’utilisateur peut lire toutes les données. Les références officielles CREATE USER et GRANT et ses niveaux de privilèges détaillent ces instructions. Leurs changements ne nécessitent pas l’ajout systématique de FLUSH PRIVILEGES ; utilisez les instructions de gestion des comptes plutôt que de modifier directement les tables de privilèges.
Tester une nouvelle connexion réellement restreinte
À l’invite du système, lancez le client ci-dessous. Le port 3306 est celui de l’exemple : adaptez-le au port réel de votre serveur d’apprentissage. L’option --password sans valeur demande le nouveau secret à l’invite ; ne le collez pas dans la commande.
mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=shop_reader43 --password --default-character-set=utf8mb4 shopcourse
Dans cette nouvelle connexion, contrôlez le compte effectivement utilisé et lisez les produits :
SELECT USER(),CURRENT_USER();
SELECT product_id,product_name FROM c43_products ORDER BY product_id;
Le compte de contrôle doit être shop_reader43@127.0.0.1. La seconde requête renvoie les quatre identifiants 1, 2, 3 et 4 avec leurs noms coréens. Si vous voyez encore course_user ou un compte d’administrateur, le résultat ne vérifie pas les restrictions du lecteur : fermez cette connexion et ouvrez celle du bon compte.
Exécutez ensuite chacune de ces deux instructions séparément, dans la connexion du lecteur :
SELECT * FROM c43_customers;
UPDATE c43_products SET stock=stock WHERE product_id=1;
Les deux doivent être refusées faute de droits : la première cible une autre table et la seconde demande UPDATE, même si elle réaffecte la valeur actuelle du stock. Dans cet exercice, une erreur de permission est le résultat attendu. Contrôlez avec le compte de préparation que les quatre stocks restent 20, 50, 12 et 5.
Si une opération interdite réussit, vérifiez CURRENT_USER(), les droits directs, les droits plus larges et les rôles actifs avant de conclure. Une connexion ou un rôle différent peut changer les permissions effectives. Si une opération nécessaire échoue, identifiez précisément le besoin et son périmètre ; accorder ALL PRIVILEGES ne constitue pas un diagnostic. Le programme Python utilisant PyMySQL et des transactions montre pourquoi une application doit utiliser une connexion distincte adaptée à ses opérations.
Révoquer le droit puis vérifier dans une autre connexion
Revenez à l’administrateur pour retirer la permission de lecture :
REVOKE SELECT ON shopcourse.c43_products FROM 'shop_reader43'@'127.0.0.1';
SHOW GRANTS FOR 'shop_reader43'@'127.0.0.1';
Fermez le lecteur puis ouvrez une nouvelle connexion avec son compte. La sélection de shopcourse comme base par défaut peut désormais être refusée, puisque son seul privilège sur cette base vient d’être retiré. Dans ce cas, reprenez la commande du client en supprimant le dernier argument shopcourse, puis testez SELECT * FROM shopcourse.c43_products;. Cette lecture doit être refusée.
La révocation retire le droit, pas l’identité du compte. USAGE peut donc subsister dans SHOW GRANTS. Les droits conservés à un autre niveau ou via un rôle doivent être examinés séparément ; retirer un droit de table ne garantit pas une interdiction s’il est encore obtenu ailleurs. La référence REVOKE précise les différentes formes de retrait.
La prise en compte dans une connexion déjà ouverte peut dépendre du périmètre du privilège. Utiliser une nouvelle connexion rend le contrôle de cet exercice explicite ; ne fondez pas une procédure de retrait d’accès sur un seul essai dans une ancienne session.
Supprimer uniquement les objets créés pour cet exercice
Vérifiez que le lecteur a été créé exclusivement pour cet exercice, fermez ses connexions, puis supprimez-le avec l’administrateur :
DROP USER 'shop_reader43'@'127.0.0.1';
DROP USER ne supprime pas automatiquement les données ni ne règle toutes les dépendances d’objets ayant ce compte comme DEFINER, c’est-à-dire comme compte définissant leur contexte de sécurité. Avant de retirer un compte réellement utilisé, examinez les connexions d’application, les vues et les routines concernées. Ici, seul le nouveau lecteur est visé.
Contrôlez la base et sauvegardez les données utiles avant de supprimer les tables jetables. Les tables enfants se suppriment avant leurs parents référencés :
DROP TABLE shopcourse.c43_items;
DROP TABLE shopcourse.c43_orders;
DROP TABLE shopcourse.c43_products;
DROP TABLE shopcourse.c43_customers;
DROP TABLE enlève les définitions et les lignes et ne se défait pas par un ROLLBACK ordinaire. Gardez ce nettoyage séparé des essais de transactions. Pour recommencer, vérifiez l’absence des quatre noms et du seul compte d’exercice, puis recréez les données et le lecteur ; les autres tables et comptes doivent rester intacts.




