CHAPITRE 1

Maitriser le langage de programmation

procédurale sous MySQL

1. Rappeler le langage de manipulation des données (SQL) ;

2. Présenter le langage de programmation procédural de
MySQL ;
3. Distinguer les différents types des programmes MySQL ;
4. Maitriser les instructions de bases ;
5. Maitriser les structures de contrôle ;
6. Gérer les transactions ;
7. Gérer les exceptions ;
8. Manipuler les curseurs ;
9. Créer les procédures stockées et les fonctions ;
10. Mettre en place les déclencheurs.
 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les transactions

▪ Par défaut, le client MySQL est configuré pour utiliser la

validation automatique.
▪ On n'a donc pas à le faire. Si on souhaite avoir la possibilité
d'annuler l'instruction INSERT, on doit utiliser une BEGIN ; - - Ceci indique le début de la transaction
transaction. INSERT INTO mytable VALUES (1, ‘foo’,’bar’,’bas’) ;
SELECT * FROM mytable ;
▪ Cela peut être fait avec une instruction BEGIN ou une COMMIT; - - Utiliser ROLLBACK au lieu de COMMIT si on
instruction START TRANSACTION. veut annuler l’operation
▪ Une fois qu’on a exécuté une ou plusieurs instructions pour
modifier les données, on doit utiliser COMMIT ou ROLLBACK.
PARTIE 1

Remarque : Pour désactiver le commit automatique utiliser : SET autocommit = 0;

Ou
SET autocommit = OFF

Copyright - Tout droit réservé - OFPPT 53

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les transactions

Contrôle des transactions

▪ Les commandes suivantes sont utilisées pour contrôler les transactions:

COMMIT
▪ Pour enregistrer les modifications

ROLLBACK
• Pour annuler les modifications

▪ Ces commandes ne sont utilisées qu'avec les commandes DML telles que - INSERT, UPDATE et DELETE uniquement.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 54

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les transactions

Exemple

▪ Le meilleur exemple pour comprendre une Débit -200 DH

Compte Compte
TRANSACTION MySQL est un transfert
émetteur Crédit +200 DH récepteur
d'argent entre 2 comptes d'une même
banque.
▪ Supposons que le débit ait été un succès, mais que le crédit n'ait pas eu lieu, peut-être à cause de problèmes de
base de données. Dans ce cas, la base de données serait dans un état incohérent.
▪ Idéalement, on veut que cette transaction (à la fois de crédit et de débit) se produise, sinon aucune d'entre elles ne
se produira. c'est-à-dire que dans ce cas, s'il s'agissait d'une transaction, un échec de crédit aurait entraîné une
annulation de l'opération de débit et il n'y aurait eu aucun changement dans l'état de la base de données.

START TRANSACTION ;
PARTIE 1

UPDATE compte SET solde = solde – 200 WHERE accountno = ‘ACC1’ ; - - Débiter le compte ACC1
UPDATE compte SET solde = solde + 200 WHERE accountno = ‘ACC2’ ; - - Crediter le compte ACC2
COMMIT ; - - Valider

Copyright - Tout droit réservé - OFPPT 55

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Évènements programmées (Events)

CREATE EVENT Something_To_Do

Définition ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 DAY
DO DROP TABLE t;

▪ Les événements MySQL sont des tâches qui CREATE EVENT The_Main_Event
ON SCHEDULE AT TIMESTAMP '2023-01-20 12:00:00'
s'exécutent selon un calendrier spécifié. Par DO DROP TABLE t;
conséquent, les événements MySQL sont
CREATE EVENT test_event_03
parfois appelés événements planifiés.
ON SCHEDULE EVERY 1 MINUTE
▪ MySQL utilise un thread spécial appelé thread STARTS CURRENT_TIMESTAMP
du planificateur d'événements pour exécuter ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
tous les événements planifiés. DO
INSERT INTO messages (message, created_at
▪ On peut afficher l'état du thread du VALUES (‘Test MySQL recurring Event’, NOW());
planificateur d'événements en exécutant la
commande SHOW PROCESSLIST : CREATE EVENT e
ON SCHEDULE EVERY 1 YEAR
PARTIE 1

STARTS CURRENT_TIMESTAMP + INTERVAL 5 DAY

ENDS CURRENT_TIMESTAMP + INTERVAL 5 YEAR
DO DROP TABLE t;

Copyright - Tout droit réservé - OFPPT 56

 CHAPITRE 1
Maitriser le langage de programmation
procédurale sous MySQL

1. Rappeler le langage de manipulation des données (SQL) ;

2. Présenter le langage de programmation procédural de
MySQL ;
3. Distinguer les différents types des programmes MySQL ;
4. Maitriser les instructions de bases ;
5. Maitriser les structures de contrôle ;
6. Gérer les transactions ;
7. Gérer les exceptions ;
8. Manipuler les curseurs ;
9. Créer les procédures stockées et les fonctions ;
10. Mettre en place les déclencheurs.
 01 – Maitriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

Introduction

▪ Afin d’éviter qu’un programme ne s’arrête dès la première erreur suite à une instruction SQL, il est indispensable de prévoir
les cas potentiels d’erreurs et d’associer à chacun de ces cas la programmation d’une exception (handler dans le vocabulaire
de MySQL).

▪ Les exceptions peuvent être gérées dans un sous-programme (fonction ou procédure cataloguée) ou un déclencheur.

▪ Une exception MySQL correspond à une condition d’erreur et peut être associée à un identificateur (exception nommée).

▪ Une exception est détectée (aussi dite « levée ») si elle est prévue dans un handler au cours de l’exécution d’un bloc (entre
BEGIN et END ).
PARTIE 1

▪ Une fois levée, elle fait continuer (ou sortir du bloc) le programme après avoir réalisé une ou plusieurs instructions que le
programmeur aura explicitement spécifiées.

▪ Deux mécanismes qui peuvent être mis en œuvre pour gérer une exception en Mysql : CONTINUE et EXIT.

Copyright - Tout droit réservé - OFPPT 58

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

Syntaxe

DECLARE handler_action HANDLER • CONTINUE : (appelée handler) force à poursuivre l’exécution de

FOR condition_value [ , condition_value] … programme lorsqu’il se passe un événement prévu dans la clause FOR
statement • EXIT fait sortir l’exécution du bloc courant entre BEGIN et END
Handler_action : { • SQLSTATE : code d’erreur qui permet de couvrir toutes les erreurs d’un
état donné.
CONTINUE
(https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-
| EXIT
error-sqlstates.html)
| UNDO • nomException : s’applique à la gestion des exceptions nommées
} (étudiées plus loin).
Condition_value : { • SQLWARNING :permet de couvrir toutes les erreurs d’état SQLSTATE
mysql_error_code
débutant par 01.
• NOT FOUND : permet de couvrir toutes les erreurs d’état SQLSTATE
| SQLSTATE [VALUE] sqlstate_value
débutant par 02 (relatives à la gestion des curseurs).
PARTIE 1

| condition_name • SQLEXCEPTION : gère toutes les erreurs qui ne sont ni gérées par
| SQLWARNING SQLWARNING ni par NOT FOUND
| NOT FOUND • statement MySQL : une ou plusieurs instructions du langage de MySQL
| SQL EXCEPTION (bloc, appel possibles par CALL d’une fonction ou d’une procédure
} cataloguée).
Copyright - Tout droit réservé - OFPPT 59
 01 – Maitriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

Exceptions avec EXIT

▪ Essayons d’insérer un client sans spécifier le champs
obligatoire (NOT NULL) nom. Une exception et déclenchée
ayant le code 1040.
▪ Gérons nous cette exception avec le handler Exit
DELEMITER//;
CREATE PROCEDURE ps_ajouter_client_Exception1(IN p_nom VARCHAR (200),
IN p_prenom VARCHAR(200),
IN p_adresse VARCHAR(200))
BEGIN
DECLARE flagNOTNULL BOOLEAN DEFAULT 0 ;
BEGIN - - début bloc de déclaration des deux exceptions
DECLARE EXIT HANDLER FOR 1048
SET flagNOTNULL := -1;
INSERT INTO clients (nom, prenom, adresse) VALUES (p_nom, p_prenom, p_adresse);
SELECT ‘le client est ajouté avec succès’;
PARTIE 1

END; - - fin bloc de déclaration des deux exceptions

IF flagNOTNULL THEN
SELECT CONCAT (‘Le chmp nom doit être non null’) AS ‘Resultat ps_ajouter_client_Exception1’;
END IF;
END;//
CALL ps_ajouter_client_Exception1(null, ‘Saloua’, ‘Safi’);

Copyright - Tout droit réservé - OFPPT 60

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

Exceptions avec EXIT

CREATE PROCEDURE ps_exc_not_found_Exemple (IN p_nom VARCHAR (200))

BEGIN
▪ L’ exemple suivant décrit une DECLARE flagNOTFOUND BOOLEAN DEFAULT 0 ;
DECLARE var1 VARCHAR(20) ;
procédure qui gère une erreur : BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND
‘aucun client n’est associé à ID SET flagNOTFOUND := -1;
passé en paramètre (NOT SELECT nom INTO var1 FROM clients
WHERE nom = p_nom ;
FOUND)’. SELECT CONCAT (‘Le seul client avec le nom ‘, p_nom, ‘ est ‘, var1)
AS ‘Resultat ps_exc_not_found_Exemple1’;
▪ La procédure ne se termine pas END
correctement si plusieurs lignes IF flagNOTFOUND THEN
SELECT CONCAT (‘Il n`’y a pas de client aven le nom ‘, p_nom)
sont retournées (erreur Result AS ‘Resultat ps_exc_not_found_Exemple1’ ;
END IF;
consisted of more than one row). END;
PARTIE 1

CALL ps_exc_not_found_Exemple1(‘Dalaj’);

Copyright - Tout droit réservé - OFPPT 61

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

Exception FOR SQLEXCEPTION

▪ L’exemple suivant décrit une

DELIMITER // ;
procédure qui gère une erreur CREATE PROCEDURE autreErreur ()
BEGIN
non spécifique . SELECT ‘Une autre erreur est survenue’ ;
END ; //
▪ Remarque: MySQL ne permet DELIMITER // ;
pas, pour l’instant, de récupérer CREATE PROCEDURE handlerdemoSQLEXCEPTION ()
dynamiquement, au sein d’un BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION CALL autreErreur();
sous-programme, le code et le INSERT INTO table_inexistante VALUES (1);
SET @x = 99;
message de l’erreur associée à END;//
une exception levée suite à une CALL handlerdemoSQLEXCEPTION();
SELECT @x;
instruction SQL, et qui n’a pas été
PARTIE 1

prévue dans un handler

Copyright - Tout droit réservé - OFPPT 62

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

Exceptions avec Continue

▪ L’exemple suivant décrit une

CREATE TABLE test (code INT, PRIMARY KEY (code));
procédure qui gère une erreur : DELIMITER // ;
Sqlstate : ER_DUP_KEY ‘23000’ CREATE PROCEDURE handlerdemo ()
BEGIN
▪ Malgré qu’une exception de DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’
duplication de clé primaire SET @x2 = 1;
SET @x = 1;
ER_DUP_KEY est causée par INSERT INTO test VALUES (1);
SET @x = 2;
l’instruction insert, l'exécution INSERT INTO test VALUES (1);
des instructions après insert SET @x = 3;
END;//
continue et la variable de session CALL handlerdemo();
@x est initialisée. SELECT @x;
PARTIE 1

Copyright - Tout droit réservé - OFPPT 63

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

Exceptions nommées (condition)

▪ Pour intercepter une erreur MySQL et lui attribuer au passage un identificateur, il faut utiliser la clause DECLARE
CONDITION.
▪ Pour la déclaration, on utilise la syntaxe suivante :
DECLARE nomException CONDITION FOR
{SQLSTATE [VALUE] ‘valeur_sqlstate’ | code_erreur_mysql}
PARTIE 1

Copyright - Tout droit réservé - OFPPT 64

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

L’instruction SIGNAL
▪ On utilise l'instruction SIGNAL pour renvoyer une condition
CREATE PROCEDURE ajouter_commande (IN commandeNo,
d'erreur ou d'avertissement à l'appelant à partir d'un sous IN produitCode varchar(45),
programme, par exemple une procédure stockée, une IN qte int,
IN prix double,
fonction stockée, un déclencheur ou un événement. IN ligneNO int )
BEGIN
L'instruction SIGNAL permet de contrôler les informations à DECLARE C INT ;
renvoyer, telles que la valeur et le message SQLSTATE. SELECT COUNT (commandeNumber) INTO C
FROM commandes
▪ Pour l’utilisation de l’instruction, on utilise la syntaxe WHERE commandeNumber = commandeNo;
- - teste si le numéro de la commande existe
suivante : IF (C != 1) THEN
SIGNAL SQLSTATE | nom_exception_nomee; SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = ‘Commande
SET info_1 = valeur_1, introuvable’;
END IF ;
info_2= valeur_2, etc; - - suite de code a exécuter si pas d’erreur
PARTIE 1

--…
▪ Info pour prendre : END
MESSAGE_TEXT, MYSQL_ERRNO , SCHEMA_NAME,
nom_table, COLUMN_NAME,

Copyright - Tout droit réservé - OFPPT 65

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Gestion des exceptions

L’instruction RESIGNAL
▪ l'instruction RESIGNAL est similaire à l'instruction
DELIMITER $$
SIGNAL en termes de fonctionnalité et de syntaxe,
CREATE PROCEDURE Divide (IN numerator INT,
sauf que : IN denominator INT,
OUT result double)
▪ On doit utiliser l'instruction RESIGNAL dans un BEGIN
gestionnaire d'erreurs ou d'avertissements, DECLARE division_by_zero CONDISTION FOR SQLSTATE ‘22012’ ;
DECLARE CONTINUE HANDLER FOR division_by_zero
sinon on obtient un message d'erreur « RESIGNAL SET MESSAGE_TEXT = ‘Division by zero / Denominator cannot be
RESIGNAL lorsque le gestionnaire n'est pas actif zero’;
--
». Notons qu’on peut utiliser l'instruction IF denominator = 0 THEN
SIGNAL division_by_zero;
SIGNAL n'importe où dans une procédure ELSE
stockée. SET result := numerator / denominator;
END IF ;
▪ On peut omettre tous les attributs de END
PARTIE 1

l'instruction RESIGNAL, même la valeur

SQLSTATE.

Copyright - Tout droit réservé - OFPPT 66

 CHAPITRE 1
Maitriser le langage de programmation
procédurale sous MySQL

1. Rappeler le langage de manipulation des données (SQL) ;

2. Présenter le langage de programmation procédural de
MySQL ;
3. Distinguer les différents types des programmes MySQL ;
4. Maitriser les instructions de bases ;
5. Maitriser les structures de contrôle ;
6. Gérer les transactions ;
7. Gérer les exceptions ;
8. Manipuler les curseurs ;
9. Créer les procédures stockées et les fonctions ;
10. Mettre en place les déclencheurs.
 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les curseurs

Définition

▪ Un curseur est une zone mémoire qui est générée côté serveur (mise en cache) et qui permet de traiter individuellement
chaque ligne renvoyée par un SELECT.
▪ Un sous-programme peut travailler avec plusieurs curseurs en même temps. Un curseur, durant son existence (de l’ouverture
à la fermeture), contient en permanence l’adresse de la ligne courante.
▪ Tout curseur MySQL dispose des propriétés suivantes :
Read-only (lecture seule)
▪ Aucune modification dans la base n’est possible à travers ce dernier (sauf si on ajoute la clause FOR
UPDATE) ;

Non-scrollable (non navigable)

▪ Une fois ouvert, le curseur est parcouru du début à la fin sans pouvoir revenir à l’enregistrement
PARTIE 1

précédent ;

Asensitive (insensible )
▪ Toute mise à jour opérée dans la base de données n’est pas répercutée dans le curseur une fois ouvert
(utilise une copie temporaire des données et ne pointe pas sur les données réelles.
Copyright - Tout droit réservé - OFPPT 68
 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les curseurs

Syntaxe

Pour l’utilisation des curseurs on suit les étapes suivantes :

1. Déclaration du curseur avant la déclaration des variables
2. Utilisation de l'instruction OPEN pour initialiser le jeu de résultats pour le curseur
3. Utilisation de l’instruction FETCH pour récupérer la ligne suivante pointée par le curseur et déplacer le
curseur vers la ligne suivante dans le jeu de résultats.
4. 4-Fermeture du curseur.

DECLARE nom_curseur CURSOR FOR instruction_SELECT

OPEN nom_curseur;
PARTIE 1

FETCH nom_curseur INTO liste_variables;

CLOSE nom_curseur;

Copyright - Tout droit réservé - OFPPT 69

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les curseurs

DELIMITER $$
Exemple
CREATE PROCEDURE lister_clients (INOUT resultat_txt VARCHAR(4000))
▪ Ce curseur permet de construire une chaine contenant BEGIN
les emails des employés. DECLARE finished INTEGER DEFAULT 0 ;
▪ Remarques : DECLARE v_id INT ;
DECLARE v_nom VARCHAR (100) ;
▪ Un curseur doit toujours être associé à une DECLARE v_prenom VARCHAR (100) ;
instruction SELECT. DECLARE info VARVHAR (400) DEFAULT ‘’’’ ;
▪ Lorsque on travaille avec le curseur MySQL, on doit DECLARE cur_info_client CURSOR FOR SELECT id,nom,prenom FROM clients;
également déclarer un gestionnaire NOT FOUND DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
pour gérer la situation où le curseur ne trouve OPEN cur_info_client ;
boucle_parcours_clients : LOOP
aucune ligne.
FETCH cur_info_client INTO v_id, v_nomn v_prenom ;
- - Appel de la ps IF finished = 1 THEN
SET @resultat_txt = ‘’’’; LEAVE boucle_parcours_clients ;
END IF ;
PARTIE 1

CALL lister_clients(@resltat_txt) ;
SELECT @resultat_txt ; SET info = CONCAT (v_id, ’’-’’,v_nom,’’-’,v_prenom);
SET resultat_txt = CONCAT (info, ’’;’’,resultat_txt) ;
END LOOP boucle_parcours_clients ;
CLOSE cur_info_client;
END $$
Copyright - Tout droit réservé - OFPPT 70
 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les curseurs

Curseur pour modification

▪ Si on veut verrouiller les lignes d’une table interrogée par un curseur dans le but de mettre à jour la table, sans qu’un autre utilisateur ne la
modifie en même temps, il faut utiliser la clause FOR UPDATE. Elle s’emploie lors de la déclaration du curseur et verrouille les lignes
concernées dès l’ouverture du curseur.
▪ Les verrous sont libérés à la fin de la transaction. boucle _parcours_clients : LOOP
FETCH cur_info_client INTO v_id,v_nom,v_prenom,v_adresse;
DELIMITER $$
IF finished = 1 THEN
CREATE PROCEDURE lister_clients_avec_MAJ_adresse ( INOUT resultat_txt VARCHAR (4000))
LEAVE boucle _parcours_clients ;
BEGIN
END IF;
DECLARE finished INTEGER DEFAULT 0 ;
IF UPPER(v_adresse)=‘AGADIR’ THEN
DECLARE v_id INT ;
UPDATE clients
DECLARE v_nom VARCHAR(100) ;
SET adresse = ‘GRANDE AGADAIR’
DECLARE v_prenom VARCHAR(100) ;
WHERE id = v_id;
DECLARE v_adresse VARCHAR(100) ;
END IF;
DECLARE info VARCHAR(400) DEFAULT ‘’’’ ;
SET info = CONCAT (v_id, ’’-’’,v_nom,’’-’,v_prenom);
PARTIE 1

DECLARE cur_info_client CURSOR FOR SELECT id,nom,prenom,adresse

SET resultat_txt = CONCAT (info, ’’;’’,resultat_txt) ;
FROM clients FOR UPDATE ;
END LOOP boucle_parcours_clients ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET set finished = 1 ;
CLOSE cur_info_client;
OPEN cur_info_client ;
END $$
DELIMITER ;

Copyright - Tout droit réservé - OFPPT 71

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les curseurs

Restrictions d’utilisation

Il n’est pas possible de déclarer un curseur FOR UPDATE en utilisant dans la requête les directives :
▪ DISTINCT ;
▪ GROUP BY;
▪ Un opérateur ensembliste ;
▪ Une fonction d’agrégat.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 72

 CHAPITRE 1
Maitriser le langage de programmation
procédurale sous MySQL

1. Rappeler le langage de manipulation des données (SQL) ;

2. Présenter le langage de programmation procédural de
MySQL ;
3. Distinguer les différents types des programmes MySQL ;
4. Maitriser les instructions de bases ;
5. Maitriser les structures de contrôle ;
6. Gérer les transactions ;
7. Gérer les exceptions ;
8. Manipuler les curseurs ;
9. Créer les procédures stockées et les fonctions ;
10. Mettre en place les déclencheurs.
 01 – Maitriser le langage de programmation
procédurale sous MySQL
titre
PARTIE 1

Copyright - Tout droit réservé - OFPPT 74

 CHAPITRE 1
Maitriser le langage de programmation
procédurale sous MySQL

1. Rappeler le langage de manipulation des données (SQL) ;

2. Présenter le langage de programmation procédural de
MySQL ;
3. Distinguer les différents types des programmes MySQL ;
4. Maitriser les instructions de bases ;
5. Maitriser les structures de contrôle ;
6. Gérer les transactions ;
7. Gérer les exceptions ;
8. Manipuler les curseurs ;
9. Créer les procédures stockées et les fonctions ;
10. Mettre en place les déclencheurs.
 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Définition

▪ Un déclencheur est une procédure stockée, dans la base de données, qui s'appelle automatiquement à chaque fois
qu'un événement spécial se produit sur la table à laquelle le déclencheur est attaché.
▪ Un déclencheur peut être utilisé pour valider les données, enregistrer les anciennes et les nouvelles valeurs dans une
table d'audit (log) ou s'assurer que les règles métier sont respectées.
▪ Par exemple, un déclencheur peut être invoqué lorsqu'une ligne est insérée dans une table spécifique ou lorsque
certaines colonnes de la table sont mises à jour.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 76

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Syntaxe

▪ [before | after] ( [avant | apres]) : Ceci spécifie quand le déclencheur

CREATE TRIGGER [nom_declencheur] sera exécuté.
[before | after] ▪ {insert | update | delete} ({insérer | mise à jour | supprimer}) : ceci
{insert | update | delete} spécifie l'opération DML.

ON [nom_table] ▪ ON [nom_table] : Ceci spécifie le nom de la table associée au

déclencheur.
[for each row]
▪ [for each row] : Ceci spécifie un déclencheur au niveau de la ligne, c'est-
[corps_declencheur]
à-dire que le déclencheur sera exécuté pour chaque ligne affectée.
▪ [corps_declencheur] : Ceci fournit les opérations à effectuer lorsque le
PARTIE 1

déclencheur est déclenché.

Copyright - Tout droit réservé - OFPPT 77

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Types

▪ Les déclencheurs BEFORE exécutent l'action du déclencheur

avant l'exécution de l'instruction de déclenchement(
ÉVÉNEMENT).
▪ Les déclencheurs AFTER exécutent l'action du déclencheur
après l'exécution de l'instruction de déclenchement
▪ Il y a trois ÉVÉNEMENTS possibles auxquels un déclencheur
peut être assigné : INSÉRER, METTRE À JOUR et
SUPPRIMER.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 78

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Exemple
PARTIE 1

Copyright - Tout droit réservé - OFPPT 79

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Avantages

Les déclencheurs contribuent :

À renforcer l'intégrité des données

À la détection des erreurs

Aux tâches qui peuvent être exécutées automatiquement lorsque le déclencheur se déclenche
plutôt que d'être planifiées.
PARTIE 1

À auditer les changements de données, enregistrer les événements et aider à prévenir les
transactions invalides.

Copyright - Tout droit réservé - OFPPT 80

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Restrictions avec les déclencheurs

On ne peut avoir qu'un seul déclencheur par événement ; On ne peut avoir qu'un seul déclencheur BEFORE UPDATE
sur une table donnée, mais on peut y exécuter plusieurs instructions ;

Les déclencheurs ne renvoient pas de valeurs ;

Ils ne peuvent pas utiliser l'instruction CALL et ils ne peuvent pas créer de tables ou de vues temporaires ;

Les déclencheurs peuvent entraîner des résultats incohérents en fonction de plusieurs facteurs, notamment le type
de moteur de base de données (InnoDB, MyISAM,..) utilisé avec la table à laquelle le déclencheur a été attribué.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 81

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

OLD/NEW

▪ le mot-clé OLD est utilisé pour accéder aux données de ligne qui sont remplacées par la mise à jour.
▪ Le mot-clé NEW permet d'accéder aux données de ligne entrantes qui remplaceront l'ancienne ligne, en cas de succès.
▪ Selon le type de déclencheur créé, les lignes OLD et NEW peuvent ne pas être disponibles :
▪ INSERT TRIGGER : Accès possible a New uniquement.
▪ UPDATE TRIGGER : Accès aux deux pseudo-lignes NEW et OLD
▪ DELETE TRIGGER : Accès uniquement a OLD pseudo-lignes, c'est-à-dire qu'il n'y a pas de ligne OLD sur un
déclencheur INSERT et pas de ligne NEW sur un déclencheur DELETE.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 82

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Exemples : BEFORE INSERT

DELIMITER //
CREATE TRIGGER tr_contrainte_age BEFORE INSERT
FOR clients
BEGIN
DECLARE MinAge INT ;
SET MinAge := 18;
IF NEW.date_naissance >(SELECT DATE_SUB (curdate(), interval MinAge year)) THEN
SIGNAL SQLSTATE ‘50001’ SET MESSAGE_TEXT = ‘La personne doit etre agee de plus de 18 ans. ’ ;
END IF ;
END //
DELIMITER ;
- - tester le déclencheur par une opération insert sur la table clients
INSERT INTO clients (nom, prenom, date_naissance, adresse)
PARTIE 1

VALUES (‘’Slami’’, ‘’Saida’’,’’2010-5-22’’, ’’casa’’)

Copyright - Tout droit réservé - OFPPT 83

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Exemples : AFTER INSERT

- - Initialisation de l age moyenne age
CREATE TABLE moyenne_age (moyenne double);
INSERT INTO moyenne_age SELECT (AVG (TIMESTAMPDIFF(YEAR, date_naissance, CURDATE())) FROM clients ;
SET SQL_SAFE_UPDATES = 0;

- - Creation du trigger
DROP TRIGGER IF EXISTS mise_ajour_moyenne_age ;
DELIMITER //
CREATE TRIGGER mise_ajour_moyenne_age AFTER INSERT
ON clients
FOR EACH ROW
UPDATE moyenne_age SET moyenne = age SELECT (AVG (TIMESTAMPDIFF(YEAR, date_naissance, CURDATE())) FROM clients ;
DELIMITER ;
PARTIE 1

- - déclencher le triger
INSERT INTO clients (nom, prenom, date_naissance, adresse)
VALUES (‘’Dadi’’,’’Hamza’’,’’2000-5-22’’,’’casa’’);
- - afficher la nouvelle valeur de l’age moyen
SELECT * FROM moyenne_age;
Copyright - Tout droit réservé - OFPPT 84
 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Exemples : BEFORE UPDATE

DELIMITER //
CREATE TRIGGER tr_modification_age_controle BEFORE UPDATE
ON clients
FOR EACH ROW
BEGIN
DECLARE MinAge INT ;
SET MinAge := 18 ;
IF NEW.date_naissace > (SELECT DATE_SUB(curdate(), interval MinAge year )) THEN
SIGNAL SGLSTATE ‘50002’ SET MESSAGE_TEXT = ‘La personne doit etre agee de plus de 18 ans.’;
END IF;
END;
DELIMITER ;
PARTIE 1

- - tester le trigger
UPDATE Clients SET date_naissance = ‘2010-9-23’
WHERE id = 6;
UPDATE Clients SET date_naissance = ‘1999-9-23’
WHERE id = 7;
Copyright - Tout droit réservé - OFPPT 85
 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Exemples : AFTER UPDATE

DELIMITER //
▪ Ce déclencheur after_sales_update est
automatiquement déclenché avant qu'un CREATE TRIGGER after_sales_update AFTER UPDATE
événement de mise à jour ne se produise ON sales
pour chaque ligne de la table des FOR EACH ROW
ventes(sales). BEGIN
IF OLD.quantity <> NEW.quantity THEN
▪ Si on met à jour la valeur dans la colonne
INSERT INTO SalesChanges (salesId, beforeQuantity, afterQuantity)
de quantité à une nouvelle valeur, le VALUES (OLD.id, OLD.quantity, NEW.quantity) ;
déclencheur insère une nouvelle ligne END IF ;
pour consigner les modifications dans la END $$
table SalesChanges.
PARTIE 1

DELIMITER ;

Copyright - Tout droit réservé - OFPPT 86

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Exemples : BEFORE DELETE

DELIMITER //

CREATE TRIGGER before_salaries_delete BEFORE DELETE

Ce déclencheur permet avant de supprimer ON salaries
un salaire de le sauvegarder dans une table FOR EACH ROW
d’archivage SalaryArchives. BEGIN
INSERT INTO SalaryArchives (employeeNumber, validFrom, amount)
VALUES (OLD.employeeNumber, OLD.validFrom, OLD.amount) ;
END $$

DELIMITER ;
PARTIE 1

Copyright - Tout droit réservé - OFPPT 87

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Exemples : AFTER DELETE

Ce déclencheur permet après la suppression d’un CREATE TRIGGER after_salaries_delete AFTER DELETE
salaire de mettre à jour le budget des salaires ON salaries
FOR EACH ROW
stockée dans la table SalaryBudgets.
UPDATE SalaryBudgets
SET total = total - OLD.salary;
PARTIE 1

Copyright - Tout droit réservé - OFPPT 88

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

LISTE des déclencheurs

Pour lister les déclencheur de la base de données, on utilise :

SHOW TRIGGERS;
PARTIE 1

Copyright - Tout droit réservé - OFPPT 89

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Suppression d’un déclencheur

▪ Pour supprimer un trigger, on utilise :

DROP TRIGGER [IF EXISTS] [nom_bd.nom_declencheur];

• Remarque : Si on supprime une table, MySQL supprimera automatiquement tous les déclencheurs
associés à la table.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 90

 01 – Maitriser le langage de programmation
procédurale sous MySQL
Les déclencheurs (Trigger)

Modification d’un déclencheur

LOCK TABLES t1 WRITE ;

▪ Il n’existe pas une instruction pour modifier un trigger, on doit le DROP TRIGGER t1_bi ;
supprimer et le recréer. DELIMITER $$
▪ LOCK permet aux sessions client d'acquérir explicitement des verrous CREATE TRIGGER t1_bi BEFORE INSERT
de table dans le but de coopérer avec d'autres sessions pour accéder ON t1
aux tables, ou d'empêcher d'autres sessions de modifier les tables FOR EACH ROW
pendant les périodes où une session nécessite un accès exclusif à BEGIN
celles-ci. …
▪ NB: MariaDB, dans la version 10.1.4, a ajouté la prise en charge de END $$
CREATE OR REPLACE TRIGGER . DELIMITER ;
PARTIE 1

UNLOCK TABLES ;

Copyright - Tout droit réservé - OFPPT 91

 CHAPITRE 2
Optimiser une base de données MySQL

Ce que vous allez apprendre dans ce chapitre :

• Optimiser les requêtes SQL ;

• Optimiser la structure de la base de données ;
• Optimiser la configuration de serveur MySQL.
 CHAPITRE 2
Optimiser une base de données MySQL

1. Optimiser les requêtes SQL ;

2. Optimiser la structure de la base de données ;
3. Optimiser la configuration de serveur MySQL.
 02 - Optimiser une base de données MySQL

Conseils

Quelque conseils pour optimiser les requêtes SQL:

Éviter si possible les SELECT * et réduire le nombre de champs, afin de réduire les données chargées en mémoire ;

Remplacer lees clauses WHERE … IN par WHERE EXISTS ;

Éviter d'utiliser des fonctions dans les prédicats : exemple SELECT * FROM TABLE1 WHERE UPPER(COL1)='ABC‘ ;

Éviter d'utiliser un caractère générique (%) au début d'un prédicat like ;

Utiliser la jointure interne (inner join), au lieu de la jointure externe (outer join) si possible ;

N'utiliser DISTINCT et UNION que si nécessaire ;

N'utiliser ORDER BY que si nécessaire ;

Compter les requêtes sur chaque page, un grand nombre de requêtes peut signifier un « problème N+1 », c’est à dire une requête SELECT placée dans
PARTIE 1

une boucle ;
Utiliser les requêtes préparées ou les procédures stockées facilite la mise en cache des requêtes en interne par MySQL et assure un bon niveau de
sécurité ;

Utiliser la clause EXPLAIN pour comprendre le fonctionnement d’une requête et quelles sont les clauses qui impactent ses performances.

Copyright - Tout droit réservé - OFPPT 94

 02 - Optimiser une base de données MySQL

Instruction préparée

▪ Avant, MySQL utilise le protocole textuel pour PREPARE stmt1 FROM

communiquer avec le client , ce protocole a de ‘SELECT id, designation FROM produit
sérieuses implications sur les performances. WHERE id = ? ‘ ;
▪ Pour résoudre ce problème, MySQL a ajouté une - - execution
nouvelle fonctionnalité appelée instruction préparée SET @id = ‘999’ ;
depuis la version 4.1. EXECUTE stm1 USING @id ;
- - libération
DEALLOCATE PREPARE stmt1 ;
PARTIE 1

DEALLOCATE
PREPARE EXECUTE
PREPARE

Copyright - Tout droit réservé - OFPPT 95

 02 - Optimiser une base de données MySQL

Les Index

▪ Comme l’index d’un ouvrage vous aide à atteindre les pages concernées par un mot recherché, un index MySQL permet
d’accélérer l’accès aux données d’une table.
▪ La plupart des index de MySQL (PRIMARY KEY, UNIQUE, INDEX, et FULLTEXT) sont stockés dans des arbres équilibrés
(balanced trees : B-trees).
▪ D’autres types d’index existent, citons ceux qui portent sur des colonnes SPATIAL (reverse key : R-trees), et ceux appliqués
aux tables MEMORY (tables de hachage : hash).
▪ L'optimiseur de requête peut utiliser des index pour localiser rapidement les données sans avoir à analyser chaque ligne
d'une table pour une requête donnée.
▪ Lorsqu’on crée une table avec une clé primaire ou une clé unique, MySQL crée automatiquement un index spécial
PARTIE 1

nommé PRIMARY. Cet index est appelé index clusterisé.

▪ L'index PRIMARY est spécial car l'index lui-même est stocké avec les données dans la même table. L'index clusterisé
applique l'ordre des lignes dans la table.
▪ Les autres index autres que l'index PRIMARY sont appelés index secondaires ou index non clusterisés.
Copyright - Tout droit réservé - OFPPT 96
 02 - Optimiser une base de données MySQL

Les Index

Création d’index

En utilisant ALTER TABLE

▪ UNIQUE : permet de créer un index qui
• ALTER TABLE nom_table n’accepte pas les doublons.
ADD INDEX nom_index(liste_colonne)
Lors de la création de la table:
▪ FULLTEXT permet de bénéficier de fonctions
de recherche dans des textes (flot de
• CREATE TABLE nom_table(
………. caractères).
INDEX (liste_colonne)
); ▪ SPATIAL permet de profiter de fonctions
pour les données géographiques
3-Apres la création de la table:
• CREATE [UNIQUE | FULLTEXT | SPATIAL] ▪ ASC et DESC précisent l’ordre (croissant ou
INDEX nomIndex [USING BTREE | HASH] décroissant).
ON [nomBase.]nomTable (colonne1 [(taille1)] [ASC | DESC],...) ;
PARTIE 1

USE eshop_app_db;
CREATE UNIQUE INDEX idx_clients_adresse USING BTREE
ON clients (adresse DESC);
Copyright - Tout droit réservé - OFPPT 97
 02 - Optimiser une base de données MySQL

Les Index

Avantages

Avant la définition de l’index

Après la définition de l’index

PARTIE 1

Copyright - Tout droit réservé - OFPPT 98

 02 - Optimiser une base de données MySQL

Les Index

Suppression d’un index

DROP INDEX nom_index ON nom_table; DROP INDEX idx_clients ON clients;

Avantage

Comme nous pouvons le voir, MySQL n'a eu qu'à localiser 1 ligne à partir de l'index nom_index comme indiqué
dans la colonne clé sans parcourir toute la table.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 99

 02 - Optimiser une base de données MySQL

Les Index

Stratégie d'indexation

Les points importants qu’on doit prendre en compte dans votre stratégie d'indexation sont les suivants :
▪ Créer une clé primaire (généralement, la colonne se terminera par id) ;
▪ Prédir les colonnes qui seront souvent interrogées dans votre application avec WHERE, GROUP BY, HAVING et
Clauses ORDER BY ;
▪ Avoir un index sur les colonnes qui seront utilisées avec des fonctions, telles que SUM (), COUNT (), MIN (),
MAX () et AVG (); pour en bénéficier en termes de performances;
▪ Ne surcharger pas la base de données avec trop d'index, car cela aura un impact sur les performances des clés
secondaires MySQL;
▪ Prédire avec des index uniques pour accélérer les requêtes de jointure (généralement, les colonnes qui se
PARTIE 1

terminent par _id) ;

▪ En règle générale, les noms d'utilisateur ou les e-mails les colonnes sont de bons candidats pour l'indexation ;
certaines colonnes comme URL ; la plupart des applications ont généralement un UUID.

Copyright - Tout droit réservé - OFPPT 100

 CHAPITRE 2
Optimiser une base de données MySQL

1. Optimiser les requêtes SQL ;

2. Optimiser la structure de la base de données ;
3. Optimiser la configuration de serveur MySQL.
 02 - Optimiser une base de données MySQL

Optimisation de la structure de la base de données

PARTIE 1

Copyright - Tout droit réservé - OFPPT 102

 CHAPITRE 2
Optimiser une base de données MySQL

1. Optimiser les requêtes SQL ;

2. Optimiser la structure de la base de données ;
3. Optimiser la configuration de serveur MySQL.
 02 - Optimiser une base de données MySQL

Optimisation de la configuration de serveur

Quelque conseils pour l'optimisation des performances d’un serveur MySQL

▪ Si on utilise des disques durs traditionnels (HDD), on peut effectuer une mise à niveau vers des disques à semi-
conducteurs (SSD) pour une amélioration des performances.
▪ On peut ajuster la cache mémoire pour améliorer les performances. Si on n'a pas assez de mémoire ou si la
mémoire existante n'est pas optimisée, on peut finir par nuire à nos performances au lieu de les améliorer.
▪ MyISAM est un ancien style de base de données utilisé pour certaines bases de données MySQL. C'est une
conception de base de données moins efficace. Le plus récent InnoDB prend en charge des fonctionnalités plus
avancées et dispose de mécanismes d'optimisation intégrés.
▪ Essayer d'utiliser la dernière version de MySQL
PARTIE 1

▪ Envisager d'utiliser un outil d'amélioration automatique des performances( tuning-primer, MySQLTuner,.. )

Copyright - Tout droit réservé - OFPPT 104

 CHAPITRE 3
Protéger la base de données MySQL

Ce que vous allez apprendre dans ce chapitre :

• Définir la sécurité générale du serveur,

• Implémenter les règles de sécurité et droits d'accès au
serveur MySQL
• Gérer les comptes utilisateurs.
 CHAPITRE 3
Protéger la base de données MySQL

1. Définir la sécurité générale du serveur ;

2. Implémenter les règles de sécurité et droits d'accès au
serveur MySQL ;
3. Gérer les comptes utilisateurs.
 03 - Protéger la base de données MySQL

Définition

▪ La sécurité des bases de données fait référence à la gamme d'outils, de contrôles et de mesures conçus pour
établir et préserver la confidentialité, l'intégrité et la disponibilité des bases de données;
▪ La sécurité de la base de données doit traiter et protéger les éléments suivants :

Les données dans la base de données

Le système de gestion de base de données (SGBD)

Toutes les applications associées

PARTIE 1

Le serveur de base de données physique et/ou le serveur de base de données virtuel et

le matériel sous-jacent

L'infrastructure informatique et/ou le réseau utilisés pour accéder à la base de données

Copyright - Tout droit réservé - OFPPT 107

 CHAPITRE 3
Protéger la base de données MySQL

1. Définir la sécurité générale du serveur;

2. Implémenter les règles de sécurité et droits d'accès au
serveur MySQL ;
3. Gérer les comptes utilisateurs.
 03 - Protéger la base de données MySQL

Règles de sécurité et droits d'accès au serveur MySQL

▪ Voici les bonnes pratiques de sécurité des bases de données :

1- S’assurer que les 2- Utiliser des 3- Configurer un 4- Implémenter un 5- Créer des

serveurs de bases de serveurs de base de serveur proxy HTTPS protocole de cryptage sauvegardes
données physiques données séparés régulières de la base
sont sécurisées de données
PARTIE 1

6- Mettre à jour les 7- Utiliser une 8- Attribuer des rôles 9- Éviter d'utiliser les
applications authentification de sécurité à tous les ports réseau par
régulièrement utilisateur forte utilisateurs défaut

Copyright - Tout droit réservé - OFPPT 109

 03 - Protéger la base de données MySQL

Règles de sécurité et droits d'accès au serveur MySQL

Sauvegarde et restauration de la base de données

▪ Il est essentiel d'effectuer des sauvegardes régulières de la base de données pour pouvoir la récupérer en cas
de perte.
▪ L'outil mysqldump permet de faire une sauvegarde d'une ou plusieurs bases de données en générant un
fichier texte contenant des instructions SQL qui peuvent recréer les bases de données à partir de zéro.
PARTIE 1

Copyright - Tout droit réservé - OFPPT 110

 03 - Protéger la base de données MySQL

Règles de sécurité et droits d'accès au serveur MySQL

Sauvegarde et restauration de la base de données

▪ Pour restaurer la base de données MySQL avec mysqldump, on suit 2 étapes:

Étape 1

• Créer une nouvelle base de données nom_bd

Étape 2

• Restaurer avec la commande:

mysql -u [utilisateur] -p [nom_bd] <
[nom_fichier].sql
PARTIE 1

Copyright - Tout droit réservé - OFPPT 111

 03 - Protéger la base de données MySQL

Règles de sécurité et droits d'accès au serveur MySQL

PARTIE 1

Copyright - Tout droit réservé - OFPPT 112

 CHAPITRE 3
Protéger la base de données MySQL

1. Définir la sécurité générale du serveur;

2. Implémenter les règles de sécurité et droits d'accès au
serveur MySQL ;
3. Gérer les comptes utilisateurs.
 03 - Protéger la base de données MySQL

Gestion des comptes utilisateurs

1-création utilisateur:

▪ Un utilisateur est défini par deux éléments :

▪ son login ;
▪ l’hôte à partir duquel il se connecte.

▪ Pour la création d’un nouvel utilisateur, on la syntaxe :

CREATE USER ' login '@' hote ' [ IDENTIFIED BY 'mot_de_passe '];

CREATE USER ‘saadi ‘@’ localhost ‘ IDENTIFIED BY ‘saadi_anass’ ;

PARTIE 1

CREATE USER ‘bensaid ‘@’ 194.28.12.4 ‘ IDENTIFIED BY ‘bensaid99’ ;

Copyright - Tout droit réservé - OFPPT 114

 03 - Protéger la base de données MySQL

Gestion des comptes utilisateurs

1-création utilisateur:

▪ Il est également possible de permettre à un utilisateur de se connecter à partir de plusieurs hôtes différents (sans devoir
créer un utilisateur par hôte) : en utilisant le joker %

- - Said peut se connecter à partir de n’importe quel hôte dont l’adresse IP commence par 194.28.12
CREATE USER ‘Said ‘@’ 194.28.12.% ‘ IDENTIFIED BY ‘baskeball18’ ;

- - Hanane peut se connecter à partir de n’importe quel hôte

CREATE USER ‘Hanane ‘@’ % ‘ IDENTIFIED BY ‘looking4sun’
PARTIE 1

Copyright - Tout droit réservé - OFPPT 115

 03 - Protéger la base de données MySQL

Gestion des comptes utilisateurs

1-création utilisateur: Connexion

▪ Pour se connecter à MySQL avec les informations d'identification d’un utilisateur, on utilise cette commande en invite de
commande:
mysql -h <host> -u <user> -p <db>
PARTIE 1

Copyright - Tout droit réservé - OFPPT 116

 03 - Protéger la base de données MySQL

Gestion des comptes utilisateurs

2 – Renommer un utilisateur

RENAME USER ‘ancien_nom '@’ ancien_hote ' TO ‘nouveau_nom'@' noveau_hote';

3 – Modifier le mot de passe d’un utilisateur

SET PASSWORD FOR ‘utilisateur '@‘hote' = PASSWORD (‘nouveau_pw')

3 – Supprimer un utilisateur

DROP USER ' login '@ 'hote ';

PARTIE 1

Copyright - Tout droit réservé - OFPPT 117

 03 - Protéger la base de données MySQL

Gestion des comptes utilisateurs

Les privilèges

▪ Lorsque l’on crée un utilisateur avec CREATE USER, celui-ci n’a au départ aucun privilège, aucun droit ;
▪ En SQL, avoir un privilège, c’est avoir l’autorisation d’effectuer une action sur un objet. Il existe de nombreux
privilèges ;
▪ Les privilèges SELECT, INSERT, UPDATE ,DELETE, CREATE TABLE, CREATE TEMPORARY TABLE , CREATE VIEW, ALTER
TABLE, DROP, CREATE ROUTINE, LOCK TABLES,…etc, permettent aux utilisateurs d’exécuter ces mêmes commandes
▪ Il y a différents niveaux d’application des privilèges :

Globale • *.*

Base de données • [nom_bd].*

PARTIE 1

Table • [nom_bd].[nom_table]

Routine stockée • [nom_bd].[nom_routine]

Copyright - Tout droit réservé - OFPPT 118

 03 - Protéger la base de données MySQL

Gestion des comptes utilisateurs

Ajout d’un privilège à un utilisateur

▪ Pour pouvoir ajouter un privilège à un utilisateur, il faut posséder le privilège GRANT OPTION.
▪ Au début , seul l’utilisateur « root » le possède.
▪ Pour attribuer des privilèges à un utilisateur, on utilise :

GRANT privilege1 [( liste_colonnes )] [, privilege2 [(liste_colonnes )], ...] ON [ type_objet ] niveau_privilege TO utilisateur
[ IDENTIFIED BY mot_de_passe ];
PARTIE 1

Copyright - Tout droit réservé - OFPPT 119

 03 - Protéger la base de données MySQL

Gestion des comptes utilisateurs

Ajout d’un privilège à un utilisateur : exemples

GRANT SELECT ,
UPDATE (nom, prenom, adresse ) ,
DELETE ,
INSERT
ON eshop_app_db.clients
TO saadi@localhost ;

-- accorder le privilège SELECT sur toutes les tables de toutes

-- les base de données à l’utilisateur saadi
GRANT SELECT ,
PARTIE 1

ON *.*
TO saadi@localhost ;

Copyright - Tout droit réservé - OFPPT 120

 03 - Protéger la base de données MySQL

Gestion des comptes utilisateurs

Révocation des privilèges

REVOKE privilege [, privilege , ...] REVOKE INSERT

ON niveau_privilege ON eshop_app_db.clients
FROM utilisateur ; FROM saadi@localhost ;
PARTIE 1

Copyright - Tout droit réservé - OFPPT 121

 03 - Protéger la base de données MySQL

Gestion des comptes utilisateurs

Gestion des rôles

▪ En plus d'accorder des autorisations à des utilisateurs CREATE ROLE ‘webdeveloper’;
individuels, dans MySQL, il est également possible de -- affecter des privileges au rôle
créer des rôles et d'accorder des autorisations aux rôles, GRANT SELECT ON mysql.user TO ‘webdeveloper’;
puis d'attribuer des rôles aux utilisateurs. -- ajouter un utilisateur au rôle
▪ Cela facilite grandement la gestion des groupes GRANT ‘webdeveloper’ TO ‘rh_salmi’@’entrbc.com.net’;
d'utilisateurs avec des autorisations similaires. Pour
créer un rôle de développeur Web, nous pouvons
fournir la requête suivante :
CREATE ROLE ‘nom_role’;

▪ Un compte peut n'avoir aucun rôle, un seul rôle ou plusieurs rôles.

PARTIE 1

▪ Si un rôle est accordé à un utilisateur, on doit peut-être indiquer à MySQL quels rôles vous souhaitez utiliser à l'aide de la
requête suivante: SET ROLE ‘webdeveloper’.

Copyright - Tout droit réservé - OFPPT 122

 03 - Protéger la base de données MySQL

Gestion des comptes utilisateurs

Gestion des rôles

▪ La création d'un utilisateur, l'attribution d'un rôle et la CREATE USER ‘u2’@’%’
définition de ce rôle par défaut peuvent être effectuées INDENTIFIED BY ‘foobar’
dans une seule instruction telle que : DEFAULT ROLE ‘webdevelopper’;

▪ Pour voir quel utilisateur et quel rôle on utilise,

on peut exécuter la commande :
SELECT CURRENT_ROLE(), CURRENT_USER ();
PARTIE 1

Copyright - Tout droit réservé - OFPPT 123

 03 - Protéger la base de données MySQL

Gestion des comptes utilisateurs

Exclusion un utilisateur d’un rôle

▪ Pour exclure un utilisateur d’un rôle, on peut utiliser la

commande REVOKE REVOKE ‘webdevelopper’ FROM ‘med34’@’localhost’;
REVOKE '<role>' FROM '<user>'@'<host>';
PARTIE 1

Copyright - Tout droit réservé - OFPPT 124