Module 3Manipuler les données
Chapitre 8 sur 9
Administrer une base de données
Gérer les utilisateurs et les droits, sauvegarder, utiliser les transactions, les index, les procédures et les déclencheurs.
- ≈ 135 min
- 5 leçons
- 2 vidéos
- 3 exercices
Module 3Manipuler les données
Chapitre 8 sur 9
Gérer les utilisateurs et les droits, sauvegarder, utiliser les transactions, les index, les procédures et les déclencheurs.
Objectifs
Avec ce chapitre, tu sauras…
CREATE USER, GRANT, REVOKE) ;mysqldump, import) ;START TRANSACTION, COMMIT, ROLLBACK) ;EXPLAIN) ;Administrer une base, c'est garantir qu'elle reste sécurisée (chacun n'accède qu'à ce qu'il doit), disponible (sauvegardes) et performante (index). En entreprise, c'est le rôle de l'administrateur de bases de données (DBA), mais tout développeur doit en connaître les bases.
Leçon 1
Créer un utilisateur
CREATE USER 'secretaire'@'localhost' IDENTIFIED BY 'M0tDePasse!Fort';
CREATE USER 'app_web'@'%' IDENTIFIED BY 'Autre#MotDePasse2026'; -- '%' : depuis n'importe quel hôte
ALTER USER 'secretaire'@'localhost' IDENTIFIED BY 'Nouveau#MotDePasse';
RENAME USER 'secretaire'@'localhost' TO 'accueil'@'localhost';
DROP USER 'accueil'@'localhost';
SELECT user, host FROM mysql.user; -- la liste des comptesTableau: Accorder et retirer des droits
| Privilège | Autorise… |
|---|---|
SELECT, INSERT, UPDATE, DELETE | lire, ajouter, modifier, supprimer des lignes |
CREATE, ALTER, DROP | créer, modifier, supprimer des tables (ou des bases) |
INDEX, CREATE VIEW, EXECUTE | créer des index, des vues, exécuter des procédures |
ALL PRIVILEGES | tous les droits (sauf GRANT OPTION) |
GRANT OPTION | transmettre ses propres droits à d'autres |
| Portée | Écriture |
|---|---|
| toutes les bases | ON *.* |
| une base | ON centre_formation.* |
| une table | ON centre_formation.ETUDIANT |
| des colonnes | GRANT UPDATE (adresseEtu) ON centre_formation.ETUDIANT |
Leçon 2
Leçon 3
Vidéo · À regarder
ENMySQL Database - Full Course
freeCodeCamp.org · YouTube
Leçon 4
Colonne EXPLAIN | Ce qu'elle indique |
|---|---|
type | ALL : toute la table est lue (lent) ; ref, eq_ref, const : un index est utilisé (rapide) |
key | le nom de l'index utilisé (NULL si aucun) |
rows | le nombre estimé de lignes à examiner |
Leçon 5
1Le principe du moindre privilège signifie…
2Pour retirer un droit :
3mysqldump s'exécute…
4Après START TRANSACTION, ROLLBACK…
5Un index accélère surtout…
6Dans EXPLAIN, type = ALL signifie…
7Dans un trigger BEFORE INSERT, la ligne insérée s'appelle…
L'essentiel du chapitre
'nom'@'hôte' : CREATE USER, ALTER USER, DROP USER ; moindre privilège, jamais root dans une application.GRANT privilèges ON base.table TO compte;, REVOKE … FROM …;, SHOW GRANTS ; rôles : CREATE ROLE, GRANT rôle TO compte.mysqldump -u root -p base > fichier.sql ; restauration : mysql -u root -p base < fichier.sql ; règle 3-2-1, tester la restauration.START TRANSACTION; … COMMIT; ou ROLLBACK; (InnoDB).CREATE INDEX nom ON table (colonne); sur les colonnes des WHERE et jointures ; EXPLAIN pour vérifier.CREATE PROCEDURE, CALL) et déclencheurs (CREATE TRIGGER … BEFORE INSERT, NEW, OLD) avec DELIMITER.Vidéo · À regarder
MySQL Database - Full Course
freeCodeCamp.org · YouTube
-- la secrétaire lit tout et gère les étudiants et les inscriptions
GRANT SELECT ON centre_formation.* TO 'secretaire'@'localhost';
GRANT INSERT, UPDATE ON centre_formation.ETUDIANT TO 'secretaire'@'localhost';
GRANT INSERT, DELETE ON centre_formation.INSCRIPTION TO 'secretaire'@'localhost';
-- le directeur ne voit que les statistiques, à travers une vue
GRANT SELECT ON centre_formation.v_ca_formation TO 'directeur'@'localhost';
SHOW GRANTS FOR 'secretaire'@'localhost'; -- vérifier
REVOKE DELETE ON centre_formation.INSCRIPTION FROM 'secretaire'@'localhost';Les rôles (MySQL 8)
Un rôle est un ensemble nommé de droits. On donne les droits au rôle, puis le rôle aux utilisateurs : quand l'organisation change, on modifie le rôle une seule fois.
CREATE ROLE 'role_lecture', 'role_saisie';
GRANT SELECT ON centre_formation.* TO 'role_lecture';
GRANT INSERT, UPDATE, DELETE ON centre_formation.* TO 'role_saisie';
GRANT 'role_lecture', 'role_saisie' TO 'secretaire'@'localhost';
SET DEFAULT ROLE ALL TO 'secretaire'@'localhost'; -- rôles actifs dès la connexionUne sauvegarde logique (un dump) est un fichier .sql qui contient toutes les instructions (CREATE TABLE, INSERT) nécessaires pour recréer la base et ses données. L'outil standard est mysqldump, qui s'exécute dans le terminal, pas dans le client mysql.
mysqldump -u root -p centre_formation > sauvegarde_2026-11-01.sql # une base
mysqldump -u root -p --databases centre_formation bibliotheque > deux_bases.sql # plusieurs bases
mysqldump -u root -p --all-databases > tout.sql # tout le serveur
mysqldump -u root -p --no-data centre_formation > structure.sql # la structure seule
mysqldump -u root -p centre_formation ETUDIANT > etudiants.sql # une tablemysql -u root -p -e "CREATE DATABASE centre_formation_test"
mysql -u root -p centre_formation_test < sauvegarde_2026-11-01.sqlGarde 3 copies des données, sur 2 supports différents, dont 1 hors site (un autre bâtiment, le cloud). Automatise les sauvegardes (tâche planifiée tous les soirs) et teste régulièrement la restauration : une sauvegarde qu'on n'a jamais restaurée n'est pas une sauvegarde fiable.
Une transaction est un ensemble d'opérations qui doivent toutes réussir ou toutes être annulées. Exemple : un virement bancaire retire 500 DH d'un compte et les ajoute à un autre ; si la deuxième opération échoue, la première doit être annulée. Les transactions respectent les propriétés ACID : Atomicité (tout ou rien), Cohérence (la base reste valide), Isolation (les transactions ne se gênent pas), Durabilité (une fois validée, la modification est définitive).
START TRANSACTION;
DELETE FROM INSCRIPTION WHERE numCINEtu = 'CD445566' AND codeSess = 'S01';
INSERT INTO INSCRIPTION (numCINEtu, codeSess, typeCours) VALUES ('CD445566', 'S02', 'presentiel');
COMMIT; -- tout est validé
-- ou ROLLBACK; -- tout est annulé, comme si rien ne s'était passéPar défaut, MySQL est en mode autocommit : chaque instruction est validée immédiatement, et ROLLBACK n'a plus rien à annuler. Il faut commencer par START TRANSACTION pour regrouper des opérations. Les transactions ne fonctionnent qu'avec le moteur InnoDB (celui par défaut).
Les transactions et leurs niveaux d'isolation (en anglais).
Un index est une structure (un arbre B) qui permet de retrouver rapidement les lignes selon la valeur d'une colonne, comme l'index à la fin d'un livre évite de lire toutes les pages. Sans index, MySQL lit toute la table (full table scan).
CREATE INDEX idx_etudiant_ville ON ETUDIANT (nomVilleEtu);
CREATE INDEX idx_session_dates ON SESSION (dateDebutSess, dateFinSess); -- index sur deux colonnes
SHOW INDEX FROM ETUDIANT;
DROP INDEX idx_etudiant_ville ON ETUDIANT;
EXPLAIN SELECT * FROM ETUDIANT WHERE nomVilleEtu = 'Rabat';WHERE, les jointures et les ORDER BY ;INSERT, UPDATE et DELETE, qui doivent le mettre à jour.Les procédures stockées
Une procédure stockée est un programme SQL enregistré dans le serveur, qu'on appelle par son nom avec CALL.
DELIMITER //
CREATE PROCEDURE inscrire(IN p_cin CHAR(9), IN p_sess VARCHAR(9), IN p_type VARCHAR(10))
BEGIN
DECLARE nb INT;
-- l'étudiant est-il déjà inscrit à une session de la même formation ?
SELECT COUNT(*) INTO nb
FROM INSCRIPTION i JOIN SESSION s ON s.codeSess = i.codeSess
WHERE i.numCINEtu = p_cin
AND s.codeForm = (SELECT codeForm FROM SESSION WHERE codeSess = p_sess);
IF nb > 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Déjà inscrit à cette formation';
ELSE
INSERT INTO INSCRIPTION (numCINEtu, codeSess, typeCours) VALUES (p_cin, p_sess, p_type);
END IF;
END //
DELIMITER ;
CALL inscrire('G683909', 'S02', 'presentiel'); -- refusé : Alaoui suit déjà S01 (Base de données)Le corps d'une procédure contient des ;. La commande DELIMITER // indique au client que l'instruction se termine désormais par //, pour qu'il envoie toute la procédure au serveur d'un seul bloc. On remet ensuite DELIMITER ;.
Les déclencheurs (triggers)
Un déclencheur est un programme exécuté automatiquement avant ou après un INSERT, un UPDATE ou un DELETE sur une table.
DELIMITER //
CREATE TRIGGER trg_session_dates
BEFORE INSERT ON SESSION
FOR EACH ROW
BEGIN
IF NEW.dateFinSess <= NEW.dateDebutSess THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La date de fin doit suivre la date de début';
END IF;
END //
DELIMITER ;Dans un déclencheur, NEW désigne la ligne en cours d'insertion ou la nouvelle version d'une ligne modifiée, OLD l'ancienne version (pour UPDATE et DELETE).
Sur ta base bibliotheque : crée un rôle role_bibliothecaire (lecture de tout, saisie des prêts et des adhérents) et un rôle role_consultation (lecture du catalogue seulement : LIVRE, AUTEUR, ECRIT_PAR, GENRE) ; attribue-les à deux comptes. Écris une transaction qui enregistre un retour de prêt (date de retour effective) et passe l'exemplaire à l'état « bon ». Crée un index utile et vérifie-le avec EXPLAIN. Fais enfin une sauvegarde de la base et restaure-la dans bibliotheque_test.
Écris les instructions pour : (1) créer le compte formateur (connexion locale) qui peut seulement lire les tables SESSION, FORMATION et INSCRIPTION ; (2) créer le compte app_inscription (depuis le serveur web 192.168.1.20) qui peut lire toutes les tables et ajouter des étudiants et des inscriptions ; (3) vérifier les droits de formateur ; (4) retirer à app_inscription le droit d'ajouter des étudiants.
Le centre de formation te demande un plan de sauvegarde. Écris : la commande de sauvegarde complète de la base, la commande de sauvegarde de la structure seule, les commandes pour restaurer la sauvegarde dans une base de test, et trois règles d'organisation (fréquence, conservation, lieu).
Les index (en anglais) ; le chapitre suivant de la vidéo présente la commande EXPLAIN.