Gestion des utilisateurs - Administrez vos bases de données avec MySQL • Bibliothèque • Zeste de Savoir

À plusieurs reprises dans ce cours, j'ai mentionné la possibilité de créer des utilisateurs, et de leur permettre de faire certaines actions. Le moment est venu de découvrir comment faire. Au sommaire :

création, modification et suppression d'utilisateurs ;
explication des privilèges et options des utilisateurs ;
attribution et révocation de privilèges aux utilisateurs.

Etat actuel de la base de données

Note : les tables de test, les vues de test et les procédures stockées ne sont pas reprises.

SET NAMES utf8;

DROP VIEW IF EXISTS V_Animal_details;
DROP VIEW IF EXISTS V_Animal_espece;
DROP VIEW IF EXISTS V_Animal_stagiaire;
DROP VIEW IF EXISTS V_Chien;
DROP VIEW IF EXISTS V_Chien_race;
DROP VIEW IF EXISTS V_Client;
DROP VIEW IF EXISTS V_Espece_dollars;
DROP VIEW IF EXISTS V_Nombre_espece;
DROP VIEW IF EXISTS V_Revenus_annee_espece;

DROP TABLE IF EXISTS Erreur;
DROP TABLE IF EXISTS Animal_histo;
DROP TABLE IF EXISTS VM_Revenus_annee_espece;

DROP TABLE IF EXISTS Adoption;
DROP TABLE IF EXISTS Animal;
DROP TABLE IF EXISTS Race;
DROP TABLE IF EXISTS Espece;
DROP TABLE IF EXISTS Client;

CREATE TABLE Client (
  id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  nom varchar(100) NOT NULL,
  prenom varchar(60) NOT NULL,
  adresse varchar(200) DEFAULT NULL,
  code_postal varchar(6) DEFAULT NULL,
  ville varchar(60) DEFAULT NULL,
  pays varchar(60) DEFAULT NULL,
  email varbinary(100) DEFAULT NULL,
  date_naissance date DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY ind_uni_email (email)
) ENGINE=InnoDB AUTO_INCREMENT=17;

LOCK TABLES Client WRITE;
INSERT INTO Client VALUES (1,'Dupont','Jean','Rue du Centre, 5','45810','Houtsiplou','France','jean.dupont@email.com',NULL),(2,'Boudur','Marie','Place de la Gare, 2','35840','Troudumonde','France','marie.boudur@email.com',NULL),(3,'Trachon','Fleur','Rue haute, 54b','3250','Belville','Belgique','fleurtrachon@email.com',NULL),
(4,'Van Piperseel','Julien',NULL,NULL,NULL,NULL,'jeanvp@email.com',NULL),
(5,'Nouvel','Johan',NULL,NULL,NULL,'Suisse','johanetpirlouit@email.com',NULL),(6,'Germain','Frank',NULL,NULL,NULL,NULL,'francoisgermain@email.com',NULL),
(7,'Antoine','Maximilien','Rue Moineau, 123','4580','Trocoul','Belgique','max.antoine@email.com',NULL),(8,'Di Paolo','Hector',NULL,NULL,NULL,'Suisse','hectordipao@email.com',NULL),
(9,'Corduro','Anaelle',NULL,NULL,NULL,NULL,'ana.corduro@email.com',NULL),
(10,'Faluche','Eline','Avenue circulaire, 7','45870','Garduche','France','elinefaluche@email.com',NULL),(11,'Penni','Carine','Boulevard Haussman, 85','1514','Plasse','Suisse','cpenni@email.com',NULL),(12,'Broussaille','Virginie','Rue du Fleuve, 18','45810','Houtsiplou','France','vibrousaille@email.com',NULL),
(13,'Durant','Hannah','Rue des Pendus, 66','1514','Plasse','Suisse','hhdurant@email.com',NULL),
(14,'Delfour','Elodie','Rue de Flore, 1','3250','Belville','Belgique','e.delfour@email.com',NULL),(15,'Kestau','Joel',NULL,NULL,NULL,NULL,'joel.kestau@email.com',NULL);
UNLOCK TABLES;


CREATE TABLE Espece (
  id smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  nom_courant varchar(40) NOT NULL,
  nom_latin varchar(40) NOT NULL,
  description text,
  prix decimal(7,2) unsigned DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY nom_latin (nom_latin)
) ENGINE=InnoDB AUTO_INCREMENT=8;

LOCK TABLES Espece WRITE;
INSERT INTO Espece VALUES (1,'Chien','Canis canis','Bestiole à quatre pattes qui aime les caresses et tire souvent la langue',200.00),(2,'Chat','Felis silvestris','Bestiole à quatre pattes qui saute très haut et grimpe aux arbres',150.00),
(3,'Tortue d''Hermann','Testudo hermanni','Bestiole avec une carapace très dure',140.00),
(4,'Perroquet amazone','Alipiopsitta xanthops','Joli oiseau parleur vert et jaune',700.00),(5,'Rat brun','Rattus norvegicus','Petite bestiole avec de longues moustaches et une longue queue sans poils',10.00),(6,'Perruche terrestre','Pezoporus wallicus','Joli oiseau aux plumes majoritairement vert brillant',20.00);
UNLOCK TABLES;


CREATE TABLE Race (
  id smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  nom varchar(40) NOT NULL,
  espece_id smallint(6) unsigned NOT NULL,
  description text,
  prix decimal(7,2) unsigned DEFAULT NULL,
  date_insertion datetime DEFAULT NULL,
  utilisateur_insertion varchar(20) DEFAULT NULL,
  date_modification datetime DEFAULT NULL,
  utilisateur_modification varchar(20) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=11;

LOCK TABLES Race WRITE;
INSERT INTO Race VALUES (1,'Berger allemand',1,'Chien sportif et élégant au pelage dense, noir-marron-fauve, noir ou gris.',485.00,'2012-05-21 00:53:36','Test','2012-05-21 00:53:36','Test'),(2,'Berger blanc suisse',1,'Petit chien au corps compact, avec des pattes courtes mais bien proportionnées et au pelage tricolore ou bicolore.',935.00,'2012-05-21 00:53:36','Test','2012-05-21 00:53:36','Test'),
(3,'Singapura',2,'Chat de petite taille aux grands yeux en amandes.',985.00,'2012-05-21 00:53:36','Test','2012-05-21 00:53:36','Test'),
(4,'Bleu russe',2,'Chat aux yeux verts et à la robe épaisse et argentée.',835.00,'2012-05-21 00:53:36','Test','2012-05-21 00:53:36','Test'),(5,'Maine Coon',2,'Chat de grande taille, à poils mi-longs.',735.00,'2012-05-21 00:53:36','Test','2012-05-27 18:10:46','sdz@localhost'),(7,'Sphynx',2,'Chat sans poils.',1235.00,'2012-05-21 00:53:36','Test','2012-05-21 00:53:36','Test'),
(8,'Nebelung',2,'Chat bleu russe, mais avec des poils longs...',985.00,'2012-05-21 00:53:36','Test','2012-05-21 00:53:36','Test'),
(9,'Rottweiller',1,'Chien d''apparence solide, bien musclé, à la robe noire avec des taches feu bien délimitées.',630.00,'2012-05-21 00:53:36','Test','2012-05-22 00:54:13','sdz@localhost'),(10,'Yorkshire terrier',1,'Chien de petite taille au pelage long et soyeux de couleur bleu et feu.',700.00,'2012-05-22 00:58:25','sdz@localhost','2012-05-22 00:58:25','sdz@localhost');
UNLOCK TABLES;


CREATE TABLE Animal (
  id smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  sexe char(1) DEFAULT NULL,
  date_naissance datetime NOT NULL,
  nom varchar(30) DEFAULT NULL,
  commentaires text,
  espece_id smallint(6) unsigned NOT NULL,
  race_id smallint(6) unsigned DEFAULT NULL,
  mere_id smallint(6) unsigned DEFAULT NULL,
  pere_id smallint(6) unsigned DEFAULT NULL,
  disponible tinyint(1) DEFAULT '1',
  PRIMARY KEY (id),
  UNIQUE KEY ind_uni_nom_espece_id (nom,espece_id)
) ENGINE=InnoDB AUTO_INCREMENT=81;

LOCK TABLES Animal WRITE;
INSERT INTO Animal VALUES (1,'M','2010-04-05 13:43:00','Rox','Mordille beaucoup',1,1,18,22,1),(2,NULL,'2010-03-24 02:23:00','Roucky',NULL,2,NULL,40,30,1),(3,'F','2010-09-13 15:02:00','Schtroumpfette',NULL,2,4,41,31,0),
(4,'F','2009-08-03 05:12:00',NULL,'Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),(5,NULL,'2010-10-03 16:44:00','Choupi','Né sans oreille gauche',2,NULL,NULL,NULL,0),(6,'F','2009-06-13 08:17:00','Bobosse','Carapace bizarre',3,NULL,NULL,NULL,1),
(7,'F','2008-12-06 05:18:00','Caroline',NULL,1,2,NULL,NULL,1),(8,'M','2008-09-11 15:38:00','Bagherra',NULL,2,5,NULL,NULL,0),(9,NULL,'2010-08-23 05:18:00',NULL,'Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),
(10,'M','2010-07-21 15:41:00','Bobo','Petit pour son âge',1,NULL,7,21,1),(11,'F','2008-02-20 15:45:00','Canaille',NULL,1,NULL,NULL,NULL,1),(12,'F','2009-05-26 08:54:00','Cali',NULL,1,2,NULL,NULL,1),
(13,'F','2007-04-24 12:54:00','Rouquine',NULL,1,1,NULL,NULL,1),(14,'F','2009-05-26 08:56:00','Fila',NULL,1,2,NULL,NULL,1),(15,'F','2008-02-20 15:47:00','Anya',NULL,1,NULL,NULL,NULL,0),
(16,'F','2009-05-26 08:50:00','Louya',NULL,1,NULL,NULL,NULL,0),(17,'F','2008-03-10 13:45:00','Welva',NULL,1,NULL,NULL,NULL,1),(18,'F','2007-04-24 12:59:00','Zira',NULL,1,1,NULL,NULL,0),
(19,'F','2009-05-26 09:02:00','Java',NULL,2,4,NULL,NULL,1),(20,NULL,'2007-04-24 12:45:00','Balou',NULL,1,1,NULL,NULL,1),(21,'F','2008-03-10 13:43:00','Pataude','Rhume chronique',1,NULL,NULL,NULL,0),
(22,'M','2007-04-24 12:42:00','Bouli',NULL,1,1,NULL,NULL,1),(24,'M','2007-04-12 05:23:00','Cartouche',NULL,1,NULL,NULL,NULL,1),(25,'M','2006-05-14 15:50:00','Zambo',NULL,1,1,NULL,NULL,1),
(26,'M','2006-05-14 15:48:00','Samba',NULL,1,1,NULL,NULL,0),(27,'M','2008-03-10 13:40:00','Moka',NULL,1,NULL,NULL,NULL,0),(28,'M','2006-05-14 15:40:00','Pilou',NULL,1,1,NULL,NULL,1),
(29,'M','2009-05-14 06:30:00','Fiero',NULL,2,3,NULL,NULL,1),(30,'M','2007-03-12 12:05:00','Zonko',NULL,2,5,NULL,NULL,0),(31,'M','2008-02-20 15:45:00','Filou',NULL,2,4,NULL,NULL,1),
(32,'M','2009-07-26 11:52:00','Spoutnik',NULL,3,NULL,52,NULL,0),(33,'M','2006-05-19 16:17:00','Caribou',NULL,2,4,NULL,NULL,1),(34,'M','2008-04-20 03:22:00','Capou',NULL,2,5,NULL,NULL,1),
(35,'M','2006-05-19 16:56:00','Raccou','Pas de queue depuis la naissance',2,4,NULL,NULL,1),(36,'M','2009-05-14 06:42:00','Boucan',NULL,2,3,NULL,NULL,1),(37,'F','2006-05-19 16:06:00','Callune',NULL,2,8,NULL,NULL,1),
(38,'F','2009-05-14 06:45:00','Boule',NULL,2,3,NULL,NULL,0),(39,'F','2008-04-20 03:26:00','Zara',NULL,2,5,NULL,NULL,0),(40,'F','2007-03-12 12:00:00','Milla',NULL,2,5,NULL,NULL,0),
(41,'F','2006-05-19 15:59:00','Feta',NULL,2,4,NULL,NULL,0),(42,'F','2008-04-20 03:20:00','Bilba','Sourde de l''oreille droite à 80%',2,5,NULL,NULL,0),(43,'F','2007-03-12 11:54:00','Cracotte',NULL,2,5,NULL,NULL,1),
(44,'F','2006-05-19 16:16:00','Cawette',NULL,2,8,NULL,NULL,1),(45,'F','2007-04-01 18:17:00','Nikki','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),(46,'F','2009-03-24 08:23:00','Tortilla','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),
(48,'F','2006-03-15 14:56:00','Lulla','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),(49,'F','2008-03-15 12:02:00','Dana','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,0),(50,'F','2009-05-25 19:57:00','Cheli','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),
(51,'F','2007-04-01 03:54:00','Chicaca','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),(52,'F','2006-03-15 14:26:00','Redbul','Insomniaque',3,NULL,NULL,NULL,1),
(54,'M','2008-03-16 08:20:00','Bubulle','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),
(55,'M','2008-03-15 18:45:00','Relou','Surpoids',3,NULL,NULL,NULL,0),(56,'M','2009-05-25 18:54:00','Bulbizard','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),
(57,'M','2007-03-04 19:36:00','Safran','Coco veut un gâteau !',4,NULL,NULL,NULL,0),
(58,'M','2008-02-20 02:50:00','Gingko','Coco veut un gâteau !',4,NULL,NULL,NULL,0),(59,'M','2009-03-26 08:28:00','Bavard','Coco veut un gâteau !',4,NULL,NULL,NULL,0),
(60,'F','2009-03-26 07:55:00','Parlotte','Coco veut un gâteau !',4,NULL,NULL,NULL,1),(61,'M','2010-11-09 00:00:00','Yoda',NULL,2,5,NULL,NULL,1),(62,'M','2010-11-05 00:00:00','Pipo',NULL,1,9,NULL,NULL,0),
(69,'F','2012-02-13 15:45:00','Baba',NULL,5,NULL,NULL,NULL,0),(70,'M','2012-02-13 15:48:00','Bibo','Agressif',5,NULL,72,73,1),(72,'F','2008-02-01 02:25:00','Momy',NULL,5,NULL,NULL,NULL,1),
(73,'M','2007-03-11 12:45:00','Popi',NULL,5,NULL,NULL,NULL,1),(75,'F','2007-03-12 22:03:00','Mimi',NULL,5,NULL,NULL,NULL,0),(76,'M','2012-03-12 00:00:00','Rocco',NULL,1,9,NULL,NULL,1),
(77,'F','2011-09-21 15:14:00','Bambi',NULL,2,NULL,NULL,NULL,1),(78,'M','2012-02-28 03:05:00','Pumba','Prématuré, à surveiller',1,9,NULL,NULL,1),(79,'M','2011-05-24 23:51:00','Lion',NULL,2,5,NULL,NULL,1);
UNLOCK TABLES;


CREATE TABLE Adoption (
  client_id smallint(5) unsigned NOT NULL,
  animal_id smallint(5) unsigned NOT NULL,
  date_reservation date NOT NULL,
  date_adoption date DEFAULT NULL,
  prix decimal(7,2) unsigned NOT NULL,
  paye tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (client_id,animal_id),
  UNIQUE KEY ind_uni_animal_id (animal_id)
) ENGINE=InnoDB;

LOCK TABLES Adoption WRITE;
INSERT INTO Adoption VALUES (1,8,'2012-05-21',NULL,735.00,1),(1,39,'2008-08-17','2008-08-17',735.00,1),(1,40,'2008-08-17','2008-08-17',735.00,1),
(2,3,'2011-03-12','2011-03-12',835.00,1),(2,18,'2008-06-04','2008-06-04',485.00,1),(3,27,'2009-11-17','2009-11-17',200.00,1),
(4,26,'2007-02-21','2007-02-21',485.00,1),(4,41,'2007-02-21','2007-02-21',835.00,1),(5,21,'2009-03-08','2009-03-08',200.00,1),
(6,16,'2010-01-27','2010-01-27',200.00,1),(7,5,'2011-04-05','2011-04-05',150.00,1),(8,42,'2008-08-16','2008-08-16',735.00,1),
(9,38,'2007-02-11','2007-02-11',985.00,1),(9,55,'2011-02-13','2011-02-13',140.00,1),(9,59,'2012-05-22',NULL,700.00,0),
(10,49,'2010-08-17','2010-08-17',140.00,0),(11,32,'2008-08-17','2010-03-09',140.00,1),(11,62,'2011-03-01','2011-03-01',630.00,0),
(12,15,'2012-05-22',NULL,200.00,1),(12,69,'2007-09-20','2007-09-20',10.00,1),(12,75,'2012-05-21',NULL,10.00,0),
(13,57,'2012-01-10','2012-01-10',700.00,1),(14,58,'2012-02-25','2012-02-25',700.00,1),(15,30,'2008-08-17','2008-08-17',735.00,1);
UNLOCK TABLES;


CREATE TABLE VM_Revenus_annee_espece (
  annee int(4) NOT NULL DEFAULT '0',
  espece_id smallint(6) unsigned NOT NULL DEFAULT '0',
  somme decimal(29,2) DEFAULT NULL,
  nb bigint(21) NOT NULL DEFAULT '0',
  PRIMARY KEY (annee,espece_id),
  KEY somme (somme)
) ENGINE=InnoDB;

LOCK TABLES VM_Revenus_annee_espece WRITE;
INSERT INTO VM_Revenus_annee_espece VALUES (2007,1,485.00,1),(2007,2,1820.00,2),(2007,5,10.00,1),(2008,1,485.00,1),(2008,2,2940.00,4),
(2008,3,140.00,1),(2009,1,400.00,2),(2010,1,200.00,1),(2010,3,140.00,1),(2011,1,630.00,1),
(2011,2,985.00,2),(2011,3,140.00,1),(2012,1,200.00,1),(2012,2,735.00,1),(2012,4,2100.00,3),(2012,5,10.00,1);
UNLOCK TABLES;


CREATE TABLE Animal_histo (
  id smallint(6) unsigned NOT NULL,
  sexe char(1) DEFAULT NULL,
  date_naissance datetime NOT NULL,
  nom varchar(30) DEFAULT NULL,
  commentaires text,
  espece_id smallint(6) unsigned NOT NULL,
  race_id smallint(6) unsigned DEFAULT NULL,
  mere_id smallint(6) unsigned DEFAULT NULL,
  pere_id smallint(6) unsigned DEFAULT NULL,
  disponible tinyint(1) DEFAULT '1',
  date_histo datetime NOT NULL,
  utilisateur_histo varchar(20) NOT NULL,
  evenement_histo char(6) NOT NULL,
  PRIMARY KEY (id,date_histo)
) ENGINE=InnoDB;

LOCK TABLES Animal_histo WRITE;
INSERT INTO Animal_histo VALUES (10,'M','2010-07-21 15:41:00','Bobo',NULL,1,NULL,7,21,1,'2012-05-22 01:00:34','sdz@localhost','UPDATE'),(19,'F','2009-05-26 09:02:00','Java',NULL,1,2,NULL,NULL,1,'2012-05-27 18:14:29','sdz@localhost','UPDATE'),(21,'F','2008-03-10 13:43:00','Pataude',NULL,1,NULL,NULL,NULL,0,'2012-05-27 18:10:40','sdz@localhost','UPDATE'),
(47,'F','2009-03-26 01:24:00','Scroupy','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1,'2012-05-22 01:00:34','sdz@localhost','DELETE'),(77,'F','0000-00-00 00:00:00','Toxi',NULL,1,NULL,NULL,NULL,1,'2012-05-27 18:12:38','sdz@localhost','DELETE');
UNLOCK TABLES;


CREATE TABLE Erreur (
  id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  erreur varchar(255) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY erreur (erreur)
) ENGINE=InnoDB AUTO_INCREMENT=8;

LOCK TABLES Erreur WRITE;
INSERT INTO Erreur VALUES (5,'Erreur : date_adoption doit être >= à date_reservation.'),(3,'Erreur : paye doit valoir TRUE (1) ou FALSE (0).'),(1,'Erreur : sexe doit valoir \"M\", \"F\" ou NULL.');
UNLOCK TABLES;


ALTER TABLE Race ADD CONSTRAINT fk_race_espece_id FOREIGN KEY (espece_id) REFERENCES Espece (id);
ALTER TABLE Animal ADD CONSTRAINT fk_espece_id FOREIGN KEY (espece_id) REFERENCES Espece (id);
ALTER TABLE Animal ADD CONSTRAINT fk_mere_id FOREIGN KEY (mere_id) REFERENCES Animal (id);
ALTER TABLE Animal ADD CONSTRAINT fk_pere_id FOREIGN KEY (pere_id) REFERENCES Animal (id);
ALTER TABLE Animal ADD CONSTRAINT fk_race_id FOREIGN KEY (race_id) REFERENCES Race (id);
ALTER TABLE Adoption ADD CONSTRAINT fk_adoption_animal_id FOREIGN KEY (animal_id) REFERENCES Animal (id);
ALTER TABLE Adoption ADD CONSTRAINT fk_client_id FOREIGN KEY (client_id) REFERENCES Client (id);
ALTER TABLE VM_Revenus_annee_espece ADD CONSTRAINT fk_vm_revenu_espece_id FOREIGN KEY (espece_id) REFERENCES Espece (id) ON DELETE CASCADE;


DELIMITER |

CREATE TRIGGER before_insert_adoption BEFORE INSERT
ON Adoption FOR EACH ROW
BEGIN
    IF NEW.paye != TRUE                                     
    AND NEW.paye != FALSE     
      THEN
        INSERT INTO Erreur (erreur) VALUES ('Erreur : paye doit valoir TRUE (1) ou FALSE (0).');

    ELSEIF NEW.date_adoption < NEW.date_reservation THEN    
        INSERT INTO Erreur (erreur) VALUES ('Erreur : date_adoption doit être >= à date_reservation.');
    END IF;
END |

CREATE TRIGGER after_insert_adoption AFTER INSERT
ON Adoption FOR EACH ROW
BEGIN
    UPDATE Animal
    SET disponible = FALSE
    WHERE id = NEW.animal_id;

    INSERT INTO VM_Revenus_annee_espece (espece_id, annee, somme, nb)
    SELECT espece_id, YEAR(NEW.date_reservation), NEW.prix, 1
    FROM Animal
    WHERE id = NEW.animal_id
    ON DUPLICATE KEY UPDATE somme = somme + NEW.prix, nb = nb + 1;
END |

CREATE TRIGGER before_update_adoption BEFORE UPDATE
ON Adoption FOR EACH ROW
BEGIN
    IF NEW.paye != TRUE                                     
    AND NEW.paye != FALSE     
      THEN
        INSERT INTO Erreur (erreur) VALUES ('Erreur : paye doit valoir TRUE (1) ou FALSE (0).');

    ELSEIF NEW.date_adoption < NEW.date_reservation THEN    
        INSERT INTO Erreur (erreur) VALUES ('Erreur : date_adoption doit être >= à date_reservation.');
    END IF;
END |

CREATE TRIGGER after_update_adoption AFTER UPDATE
ON Adoption FOR EACH ROW
BEGIN
    IF OLD.animal_id <> NEW.animal_id THEN
        UPDATE Animal
        SET disponible = TRUE
        WHERE id = OLD.animal_id;

        UPDATE Animal
        SET disponible = FALSE
        WHERE id = NEW.animal_id;
    END IF;

    INSERT INTO VM_Revenus_annee_espece (espece_id, annee, somme, nb)
    SELECT espece_id, YEAR(NEW.date_reservation), NEW.prix, 1
    FROM Animal
    WHERE id = NEW.animal_id
    ON DUPLICATE KEY UPDATE somme = somme + NEW.prix, nb = nb + 1;

    UPDATE VM_Revenus_annee_espece
    SET somme = somme - OLD.prix, nb = nb - 1
    WHERE annee = YEAR(OLD.date_reservation)
    AND espece_id = (SELECT espece_id FROM Animal WHERE id = OLD.animal_id);
 
    DELETE FROM VM_Revenus_annee_espece
    WHERE nb = 0;
END |

CREATE TRIGGER after_delete_adoption AFTER DELETE
ON Adoption FOR EACH ROW
BEGIN
    UPDATE Animal
    SET disponible = TRUE
    WHERE id = OLD.animal_id;

    UPDATE VM_Revenus_annee_espece
    SET somme = somme - OLD.prix, nb = nb - 1
    WHERE annee = YEAR(OLD.date_reservation)
    AND espece_id = (SELECT espece_id FROM Animal WHERE id = OLD.animal_id);
 
    DELETE FROM VM_Revenus_annee_espece
    WHERE nb = 0;
END |

CREATE TRIGGER before_insert_animal BEFORE INSERT
ON Animal FOR EACH ROW
BEGIN
    IF NEW.sexe IS NOT NULL   
    AND NEW.sexe != 'M'       
    AND NEW.sexe != 'F'       
      THEN
        INSERT INTO Erreur (erreur) VALUES ('Erreur : sexe doit valoir "M", "F" ou NULL.');
    END IF;
END |

CREATE TRIGGER before_update_animal BEFORE UPDATE
ON Animal FOR EACH ROW
BEGIN
    IF NEW.sexe IS NOT NULL   
    AND NEW.sexe != 'M'       
    AND NEW.sexe != 'F'       
      THEN
        SET NEW.sexe = NULL;
    END IF;
END |

CREATE TRIGGER after_update_animal AFTER UPDATE
ON Animal FOR EACH ROW
BEGIN
    INSERT INTO Animal_histo (
        id, sexe, date_naissance, nom, commentaires, espece_id, race_id, 
        mere_id, pere_id, disponible, date_histo, utilisateur_histo, evenement_histo)
    VALUES (
        OLD.id, OLD.sexe, OLD.date_naissance, OLD.nom, OLD.commentaires, OLD.espece_id, OLD.race_id,
        OLD.mere_id, OLD.pere_id, OLD.disponible, NOW(), CURRENT_USER(), 'UPDATE');
END |

CREATE TRIGGER after_delete_animal AFTER DELETE
ON Animal FOR EACH ROW
BEGIN
    INSERT INTO Animal_histo (
        id, sexe, date_naissance, nom, commentaires, espece_id, race_id, 
        mere_id,  pere_id, disponible, date_histo, utilisateur_histo, evenement_histo)
    VALUES (
        OLD.id, OLD.sexe, OLD.date_naissance, OLD.nom, OLD.commentaires, OLD.espece_id, OLD.race_id,
        OLD.mere_id, OLD.pere_id, OLD.disponible, NOW(), CURRENT_USER(), 'DELETE');
END |

CREATE TRIGGER before_delete_espece BEFORE DELETE
ON Espece FOR EACH ROW
BEGIN
    DELETE FROM Race
    WHERE espece_id = OLD.id;
END |

CREATE TRIGGER before_insert_race BEFORE INSERT
ON Race FOR EACH ROW
BEGIN
    SET NEW.date_insertion = NOW();
    SET NEW.utilisateur_insertion = CURRENT_USER();
    SET NEW.date_modification = NOW();
    SET NEW.utilisateur_modification = CURRENT_USER();
END |

CREATE TRIGGER before_update_race BEFORE UPDATE
ON Race FOR EACH ROW
BEGIN
    SET NEW.date_modification = NOW();
    SET NEW.utilisateur_modification = CURRENT_USER();
END |

CREATE TRIGGER before_delete_race BEFORE DELETE
ON Race FOR EACH ROW
BEGIN
    UPDATE Animal 
    SET race_id = NULL
    WHERE race_id = OLD.id;
END |

DELIMITER ;

CREATE VIEW V_Animal_details AS 
select Animal.id,Animal.sexe,Animal.date_naissance,Animal.nom,Animal.commentaires,Animal.espece_id,Animal.race_id,
    Animal.mere_id,Animal.pere_id,Animal.disponible,Espece.nom_courant AS espece_nom,Race.nom AS race_nom 
from Animal 
join Espece on Animal.espece_id = Espece.id 
left join Race on Animal.race_id = Race.id;

CREATE VIEW V_Animal_espece AS 
select Animal.id,Animal.sexe,Animal.date_naissance,Animal.nom,Animal.commentaires,Animal.espece_id,Animal.race_id,
    Animal.mere_id,Animal.pere_id,Animal.disponible,Espece.nom_courant AS espece_nom,Espece.nom_latin AS espece_nom_latin 
from Animal 
join Espece on Espece.id = Animal.espece_id;

CREATE VIEW V_Animal_stagiaire AS 
select Animal.id,Animal.nom,Animal.sexe,Animal.date_naissance,Animal.espece_id,Animal.race_id,Animal.mere_id,Animal.pere_id,Animal.disponible 
from Animal 
where Animal.espece_id = 2
WITH CASCADED CHECK OPTION;

CREATE VIEW V_Chien AS 
select Animal.id,Animal.sexe,Animal.date_naissance,Animal.nom,Animal.commentaires,Animal.espece_id,Animal.race_id,Animal.mere_id,Animal.pere_id,Animal.disponible 
from Animal 
where Animal.espece_id = 1;

CREATE VIEW V_Chien_race AS 
select V_Chien.id,V_Chien.sexe,V_Chien.date_naissance,V_Chien.nom,V_Chien.commentaires,V_Chien.espece_id,V_Chien.race_id,V_Chien.mere_id,V_Chien.pere_id,V_Chien.disponible 
from V_Chien 
where V_Chien.race_id is not null
WITH CASCADED CHECK OPTION;

CREATE VIEW V_Client AS 
select Client.id,Client.nom,Client.prenom,Client.adresse,Client.code_postal,Client.ville,Client.pays,Client.email 
from Client;

CREATE VIEW V_Espece_dollars AS 
select Espece.id,Espece.nom_courant,Espece.nom_latin,Espece.description,round((Espece.prix * 1.30813),2) AS prix_dollars 
from Espece;

CREATE VIEW V_Nombre_espece AS 
select Espece.id,count(Animal.id) AS nb 
from Espece 
left join Animal on Animal.espece_id = Espece.id 
group by Espece.id;

CREATE VIEW V_Revenus_annee_espece AS 
select year(Adoption.date_reservation) AS annee,Espece.id AS espece_id,sum(Adoption.prix) AS somme,count(Adoption.animal_id) AS nb 
from Adoption 
join Animal on Animal.id = Adoption.animal_id
join Espece on Animal.espece_id = Espece.id 
group by year(Adoption.date_reservation),Espece.id;

Introduction
Création, modification et suppression des utilisateurs
Les privilèges - introduction
Ajout et révocation de privilèges
Privilèges particuliers
Options supplémentaires

Introduction

Pendant ce cours, nous avons créé une base de données : elevage. Vous avez peut-être créé également d'autres bases de données pour vos tests et projets personnels.

Mais ce ne sont pas les seules bases de données existant sur votre serveur MySQL. Connectez-vous avec l'utilisateur "root" (sinon certaines bases de données vous seront cachées) et exécutez la requête suivante :

1	SHOW DATABASES;

Database
information_schema
elevage
mysql
performance_schema
test

La base elevage est bien là, en compagnie de quelques autres :

information_schema : cette base de données stocke les informations sur toutes les bases de données. Les tables, les colonnes, le type des colonnes, les procédures des bases de données y sont recensées, avec leurs caractéristiques. Nous verrons cette base de données plus en détail dans le prochain chapitre.
performance_schema : permet de stocker des informations sur les actions effectuées sur le serveur (temps d'exécution, temps d'attente dus aux verrous, etc.)
test : il s'agit d'une base de test automatiquement créée. Si vous ne l'avez pas utilisée, elle ne contient rien.
mysql : qui contient de nombreuses informations sur le serveur. Entre autres, c'est dans cette base que sont stockés les utilisateurs et leurs privilèges.

Les utilisateurs et leurs privilèges

Privilèges des utilisateurs

Lorsque l'on se connecte à MySQL, on le fait avec un utilisateur. Chaque utilisateur possède une série de privilèges, relatifs aux données stockées sur le serveur : le privilège de sélectionner les données, de les modifier, de créer des objets, etc. Ces privilèges peuvent exister à plusieurs niveaux : base de données, tables, colonnes, procédures, etc.

Par exemple, au tout début de ce cours, vous avez créé un utilisateur avec la commande suivante :

1	GRANT ALL PRIVILEGES ON elevage.* TO 'sdz'@'localhost' IDENTIFIED BY 'mot_de_passe';

Cette requête a créé un utilisateur 'sdz'@'localhost', et lui a donné tous les droits sur la base de données elevage.

Stockage des utilisateurs et privilèges

Toutes ces informations sont stockées dans la base de données mysql. Les utilisateurs sont stockés dans la table user, avec leurs privilèges globaux (c'est-à-dire valables au niveau du serveur, sur toutes les bases de données). La base mysql possède par ailleurs quatre tables permettant de stocker les privilèges des utilisateurs à différents niveaux.

db : privilèges au niveau des bases de données.
tables_priv : privilèges au niveau des tables.
columns_priv : privilèges au niveau des colonnes.
proc_priv : privilèges au niveau des routines (procédures et fonctions stockées).

Modifications

Il est tout à fait possible d'ajouter, modifier et supprimer des utilisateurs en utilisant des requêtes INSERT, UPDATE ou DELETE directement sur la table mysql.user. De même pour leurs privilèges, avec les tables mysql.db, mysql.tables_priv, mysql.columns_priv et mysql.procs_priv.

Cependant, en général, on utilise plutôt des commandes dédiées à cet usage. Ainsi, pas besoin de se soucier de la structure de ces tables, et le risque d'erreur est moins grand. Ce sont ces commandes que nous allons décortiquer dans la suite de ce chapitre.

Tout comme on peut préciser la table à laquelle appartient une colonne en préfixant le nom de la colonne par le nom de la table : nom_table.nom_colonne, il est possible de préciser à quelle base de données appartient une table : nom_bdd.nom_table, voire même préciser à la fois la table et la base de données dans laquelle se trouve une colonne : nom_bdd.nom_table.nom_colonne.

Création, modification et suppression des utilisateurs

Création et suppression

On l'a vu, il est possible de créer un utilisateur en lui donnant directement des privilèges, grâce à la commande GRANT. Cependant, il existe des commandes dédiées uniquement à la manipulation des utilisateurs. Ce sont ces commandes que nous allons voir maintenant. Nous reparlerons de GRANT plus tard.

Syntaxe

Voici les requêtes à utiliser pour créer et supprimer un utilisateur :

-- Création
CREATE USER 'login'@'hote' [IDENTIFIED BY 'mot_de_passe'];

-- Suppression
DROP USER 'login'@'hote';

Utilisateur

L'utilisateur est donc défini par deux éléments :

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

Login

Le login est un simple identifiant. Vous pouvez le choisir comme vous voulez. Il n'est pas obligatoire de l'entourer de guillemets, sauf s'il contient des caractères spéciaux comme - ou @. C'est cependant conseillé.

Hôte

L'hôte est l'adresse à partir de laquelle l'utilisateur va se connecter. Si l'utilisateur se connecte à partir de la machine sur laquelle le serveur MySQL se trouve, on peut utiliser 'localhost'. Sinon, on utilise en général une adresse IP ou un nom de domaine.

Exemples

1
2
3

CREATE USER 'max'@'localhost' IDENTIFIED BY 'maxisthebest';
CREATE USER 'elodie'@'194.28.12.4' IDENTIFIED BY 'ginko1';
CREATE USER 'gabriel'@'arb.brab.net' IDENTIFIED BY 'chinypower';

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 %, on peut préciser des noms d'hôtes partiels, ou permettre à l'utilisateur de se connecter à partir de n'importe quel hôte.

Exemples

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

-- joelle peut se connecter à partir de n'importe quel hôte du domaine brab.net
CREATE USER 'joelle'@'%.brab.net' IDENTIFIED BY 'singingisfun';

-- hannah peut se connecter à partir de n'importe quel hôte
CREATE USER 'hannah'@'%' IDENTIFIED BY 'looking4sun';

Comme pour le login, les guillemets ne sont pas obligatoires, sauf si un caractère spécial est utilisé (comme le joker % par exemple). Notez que si vous ne précisez pas d'hôte, c'est comme si vous autorisiez tous les hôtes. 'hannah'@'%' est donc équivalent à 'hannah'.

Les guillemets se placent indépendamment autour du login et autour de l'hôte. N'entourez pas tout par des guillemets : CREATE USER 'marie@localhost' créera un utilisateur dont le login est 'marie@localhost', autorisé à se connecter à partir de n'importe quel hôte.

Renommer l'utilisateur

Pour modifier l'identifiant d'un utilisateur (login et/ou hôte), on peut utiliser RENAME USER ancien_utilisateur TO nouvel_utilisateur.

Exemple : on renomme max en maxime, en gardant le même hôte.

1	RENAME USER 'max'@'localhost' TO 'maxime'@'localhost';

Mot de passe

Le mot de passe de l'utilisateur est donné par la cause IDENTIFIED BY. Cette clause n'est pas obligatoire, auquel cas l'utilisateur peut se connecter sans donner de mot de passe. Ce n'est évidemment pas une bonne idée d'un point de vue sécurité. Évitez au maximum les utilisateurs sans mot de passe.

Lorsqu'un mot de passe est précisé, il n'est pas stocké tel quel dans la table mysql.user. Il est d'abord hashé, et c'est cette valeur hashée qui est stockée.

Modifier le mot de passe

Pour modifier le mot de passe d'un utilisateur, on peut utiliser la commande SET PASSWORD (à condition d'avoir les privilèges nécessaires, ou d'être connecté avec l'utilisateur dont on veut changer le mot de passe). Cependant, cette commande ne hashe pas le mot de passe automatiquement. Il faut donc utiliser la fonction PASSWORD().

Exemple

1	SET PASSWORD FOR 'thibault'@'194.28.12.%' = PASSWORD('basket8');

Les privilèges - introduction

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.

Un utilisateur sans aucun privilège ne peut rien faire d'autre que se connecter. Il n'aura pas accès aux données, ne pourra créer aucun objet (base/table/procédure/autre), ni en utiliser.

Les différents privilèges

Il existe de nombreux privilèges dont voici une sélection des plus utilisés (à l'exception des privilèges particuliers ALL, USAGE et GRANT OPTION que nous verrons plus loin).

Privilèges du CRUD

Les privilèges SELECT, INSERT, UPDATE et DELETE permettent aux utilisateurs d'utiliser ces mêmes commandes.

Privilèges concernant les tables, les vues et les bases de données

Privilège	Action autorisée
`CREATE TABLE`	Création de tables
`CREATE TEMPORARY TABLE`	Création de tables temporaires
`CREATE VIEW`	Création de vues (il faut également avoir le privilège `SELECT` sur les colonnes sélectionnées par la vue)
`ALTER`	Modification de tables (avec `ALTER TABLE`)
`DROP`	Suppression de tables, vues et bases de données

Autres privilèges

Privilège	Action autorisée
`CREATE ROUTINE`	Création de procédures stockées (et de fonctions stockées - non couvert dans ce cours)
`ALTER ROUTINE`	Modification et suppression de procédures stockées (et fonctions stockées)
`EXECUTE`	Exécution de procédures stockées (et fonctions stockées)
`INDEX`	Création et suppression d'index
`TRIGGER`	Création et suppression de triggers
`LOCK TABLES`	Verrouillage de tables (sur lesquelles on a le privilège `SELECT`)
`CREATE USER`	Gestion d'utilisateur (commandes `CREATE USER`, `DROP USER`, `RENAME USER` et `SET PASSWORD`)

Les différents niveaux d'application des privilèges

Lorsque l'on accorde un privilège à un utilisateur, il faut également préciser à quoi s'applique ce privilège.

Niveau	Application du privilège
.	Privilège global : s'applique à toutes les bases de données, à tous les objets. Un privilège de ce niveau sera stocké dans la table mysql.user.
*	Si aucune base de données n'a été préalablement sélectionnée (avec `USE nom_bdd`), c'est l'équivalent de . (privilège stocké dans mysql.user). Sinon, le privilège s'appliquera à tous les objets de la base de données qu'on utilise (et sera stocké dans la table mysql.db).
nom_bdd.*	Privilège de base de données : s'applique à tous les objets de la base nom_bdd (stocké dans mysql.db).
nom_bdd.nom_table	Privilège de table (stocké dans mysql.tables_priv).
nom_table	Privilège de table : s'applique à la table nom_table de la base de données dans laquelle on se trouve, sélectionnée au préalable avec `USE nom_bdd` (stocké dans mysql.tables_priv).
nom_bdd.nom_routine	S'applique à la procédure (ou fonction) stockée nom_bdd.nom_routine (privilège stocké dans mysql.procs_priv).

Les privilèges peuvent aussi être restreints à certaines colonnes, auquel cas, ils seront stockés dans la table mysql.columns_priv. Nous verrons comment restreindre un privilège à certaines colonnes avec la commande GRANT.

Ajout et révocation de privilèges

Ajout de privilèges

Pour pouvoir ajouter un privilège à un utilisateur, il faut posséder le privilège GRANT OPTION. Pour l'instant, seul l'utilisateur "root" le possède. Étant donné qu'il s'agit d'un privilège un peu particulier, nous n'en parlerons pas tout de suite. Connectez-vous donc avec "root" pour exécuter les commandes de cette partie.

Syntaxe

La commande pour ajouter des privilèges à un utilisateur est la suivante :

1
2
3

GRANT privilege [(liste_colonnes)] [, privilege [(liste_colonnes)], ...]
ON [type_objet] niveau_privilege
TO utilisateur [IDENTIFIED BY mot_de_passe];

privilege : le privilège à accorder à l'utilisateur (SELECT, CREATE VIEW, EXECUTE,…) ;
(liste_colonnes) : facultatif - liste des colonnes auxquelles le privilège s'applique ;
niveau_privilege : niveau auquel le privilège s'applique (., nom_bdd.nom_table,…) ;
type_objet : en cas de noms ambigus, il est possible de préciser à quoi se rapporte le niveau : TABLE ou PROCEDURE.

On peut accorder plusieurs privilèges en une fois : il suffit de séparer les privilèges par une virgule. Si l'on veut restreindre tout ou partie des privilèges à certaines colonnes, la liste doit en être précisée pour chaque privilège.

Si l'utilisateur auquel on accorde les privilèges n'existe pas, il sera créé. Auquel cas, il vaut mieux ne pas oublier la clause IDENTIFIED BY pour donner un mot de passe à l'utilisateur. Sinon, il pourra se connecter sans mot de passe.

Si l'utilisateur existe, et qu'on ajoute la clause IDENTIFIED BY, son mot de passe sera modifié.

Exemples

1. On crée un utilisateur 'john'@'localhost', en lui donnant les privilèges SELECT, INSERT et DELETE sur la table elevage.Animal, et UPDATE sur les colonnes nom, sexe et commentaires de la table elevage.Animal.

GRANT SELECT, 
      UPDATE (nom, sexe, commentaires), 
      DELETE, 
      INSERT
ON elevage.Animal
TO 'john'@'localhost' IDENTIFIED BY 'exemple2012';

2. On accorde le privilège SELECT à l'utilisateur 'john'@'localhost' sur la table elevage.Espece, et on modifie son mot de passe.

1
2
3

GRANT SELECT
ON TABLE elevage.Espece -- On précise que c'est une table (facultatif)
TO 'john'@'localhost' IDENTIFIED BY 'change2012';

3. On accorde à 'john'@'localhost' le privilège de créer et exécuter des procédures stockées dans la base de données elevage.

1
2
3

GRANT CREATE ROUTINE, EXECUTE
ON elevage.*
TO 'john'@'localhost';

Révocation de privilèges

Pour retirer un ou plusieurs privilèges à un utilisateur, on utilise la commande REVOKE.

1
2
3

REVOKE privilege [, privilege, ...]
ON niveau_privilege
FROM utilisateur;

Exemple

1
2
3

REVOKE DELETE
ON elevage.Animal
FROM 'john'@'localhost';

Privilèges particuliers

Les privilèges ALL, USAGE et GRANT OPTION

Pour terminer avec les différents privilèges, nous allons parler de trois privilèges un peu particuliers.

ALL

Le privilège ALL (ou ALL PRIVILEGES), comme son nom l'indique, représente tous les privilèges. Accorder le privilège ALL revient donc à accorder tous les droits à l'utilisateur. Il faut évidemment préciser le niveau auquel tous les droits sont accordés (on octroie tous les privilèges possibles sur une table, ou sur une base de données, etc.).

Un privilège fait exception : GRANT OPTION n'est pas compris dans les privilèges représentés par ALL.

Exemple : on accorde tous les droits sur la table Client à 'john'@'localhost'.

1
2
3

GRANT ALL 
ON elevage.Client
TO 'john'@'localhost';

USAGE

À l'inverse de ALL, le privilège USAGE signifie "aucun privilège". À première vue, utiliser la commande GRANT pour n'accorder aucun privilège peut sembler un peu ridicule. En réalité, c'est extrêmement utile : USAGE permet en fait de modifier les caractéristiques d'un compte avec la commande GRANT, sans modifier les privilèges du compte. USAGE est toujours utilisé comme un privilège global (donc ON *.*).

Exemple : modification du mot de passe de 'john'@'localhost'. Ses privilèges ne changent pas.

1
2
3

GRANT USAGE
ON *.*
TO 'john'@'localhost' IDENTIFIED BY 'test2012usage';

Nous verrons plus tard les autres options de la commande GRANT, que l'on pourrait vouloir modifier en utilisant le privilège USAGE.

GRANT OPTION

Nous voici donc au fameux privilège GRANT OPTION. Un utilisateur ayant ce privilège est autorisé à utiliser la commande GRANT, pour accorder des privilèges à d'autres utilisateurs. Ce privilège n'est pas compris dans le privilège ALL. Par ailleurs, un utilisateur ne peut accorder que les privilèges qu'il possède lui-même.

On peut accorder GRANT OPTION de deux manières :

comme un privilège normal, après le mot GRANT ;
à la fin de la commande GRANT, avec la clause WITH GRANT OPTION.

Exemple : on accorde les privilèges SELECT, UPDATE, INSERT, DELETE et GRANT OPTION sur la base de données elevage à 'joseph'@'localhost'.

GRANT SELECT, UPDATE, INSERT, DELETE, GRANT OPTION
ON elevage.*
TO 'joseph'@'localhost' IDENTIFIED BY 'ploc4';

-- OU

GRANT SELECT, UPDATE, INSERT, DELETE
ON elevage.*
TO 'joseph'@'localhost' IDENTIFIED BY 'ploc4'
WITH GRANT OPTION;

Le privilège ALL doit s'utiliser tout seul. Il n'est donc pas possible d'accorder GRANT OPTION et ALL de la manière suivante : GRANT ALL, GRANT OPTION… Il est nécessaire dans ce cas d'utiliser WITH GRANT OPTION.

Particularité des triggers, vues et procédures stockées

Les triggers, les vues et les procédures stockées (ainsi que les événements et les fonctions stockées, non couvertes par ce cours) ont un système spécial quant à la vérification des privilèges des utilisateurs. En effet, ces objets sont créés dans le but d'être exécutés dans le futur, et l'utilisateur créant un tel objet pourrait bien être différent de l'utilisateur se servant de l'objet. Il y a donc deux types d'utilisateurs potentiels pour ces types d'objet : celui ayant défini l'objet, et celui utilisant l'objet. Quels privilèges faut-il vérifier lorsqu'une procédure est exécutée ? Lorsque la requête SELECT d'une vue est exécutée ?

Par défaut, ce sont les privilèges du définisseur (celui qui a défini l'objet) qui sont vérifiés. Ce qui veut dire qu'un utilisateur pourrait exécuter une procédure agissant sur des tables sur lesquelles il n'a lui-même aucun privilège. L'important étant les privilèges de l'utilisateur ayant défini la procédure. Bien entendu, un utilisateur doit toujours avoir le privilège EXECUTE pour exécuter une procédure stockée, les privilèges du définisseur concernent les instructions à l'intérieur de la procédure ou du trigger (ou de la requête SELECT pour une vue).

Exemple : avec l'utilisateur sdz, on définit une procédure faisant une requête SELECT sur la table Adoption. On exécute ensuite cette procédure avec l'utilisateur john, qui n'a aucun droit sur la table Adoption.

Utilisateur sdz :

USE elevage;
DELIMITER |
CREATE PROCEDURE test_definer()
BEGIN
    SELECT * FROM Adoption;
END |
DELIMITER ;

Utilisateur john :

1
2
3

USE elevage;
SELECT * FROM Adoption;
CALL test_definer();

L'utilisateur john n'a aucun droit sur Adoption. La requête SELECT échoue donc avec le message d'erreur suivant :

1	ERROR 1142 (42000): SELECT command denied to user 'john'@'localhost' for table 'adoption'

Par contre, john a le droit d'exécuter les procédures de la base de données elevage. Il exécute donc sans problème test_definer(), qui lui affiche le contenu d'Adoption. Les privilèges vérifiés au niveau des instructions exécutées par la procédure sont en effet ceux de l'utilisateur sdz, ayant défini celle-ci.

Préciser et modifier le définisseur

Les commandes de création des vues, triggers et procédures stockées permettent de préciser une clause DEFINER, dans laquelle on précise l'identifiant d'un utilisateur. Par défaut, c'est l'utilisateur courant qui est utilisé. Cependant il est possible de donner un autre utilisateur comme définisseur de l'objet, à condition d'avoir le privilège global SUPER. Sans ce privilège, on ne peut donner comme DEFINER que soi-même, soit avec CURRENT_USER ou CURRENT_USER(), soit avec l'identifiant ('sdz'@'localhost' par exemple). La clause DEFINER se place après le mot-clé CREATE.

Exemple : définition de deux procédures stockées avec l'utilisateur root (le seul ayant le privilège SUPER sur notre serveur), l'une avec root pour DEFINER (CURRENT_USER()), l'autre avec john.

DELIMITER |
CREATE DEFINER = CURRENT_USER() PROCEDURE test_definer2()
BEGIN
    SELECT * FROM Race;
END |

CREATE DEFINER = 'john'@'localhost' PROCEDURE test_definer3()
BEGIN
    SELECT * FROM Race;
END |
DELIMITER ;

Si l'on exécute ces deux procédures avec l'utilisateur john, on obtient deux résultats différents.

1 2	CALL test_definer2(); CALL test_definer3();

La première procédure s'exécute sans problème. john n'a aucun droit sur la table Race, mais le définisseur de test_definer2() étant root, ce sont ses privilèges qui comptent. Par contre, la seconde échoue, car le définisseur de test_definer3() a été initialisé à john.

Modification du contexte

Il est possible, pour les vues et les procédures stockées, de changer la manière dont les privilèges sont vérifiés à l'exécution. On peut faire en sorte que ce soit les privilèges de l'utilisateur qui se sert de la vue ou de la procédure ("l'invocateur") qui soient vérifiés, et non plus les privilèges du définisseur.

Ce n'est pas possible pour les triggers, car ceux-ci ne sont pas exécutés directement par un utilisateur, mais par une action de l'utilisateur (insertion, modification, suppression dans la table sur laquelle le trigger est défini).

Pour changer le contexte de vérification des privilèges d'une vue ou d'une procédure, il faut utiliser la clause SQL SECURITY {DEFINER | INVOKER}.

Syntaxe de création des vues et des procédures, clauses DEFINER et SQL SECURITY comprises.

-- Vues
CREATE [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { utilisateur | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW nom_vue [(liste_colonnes)]
    AS requete_select
    [WITH [CASCADED | LOCAL] CHECK OPTION]

-- Procédures
CREATE
    [DEFINER = { utilisateur | CURRENT_USER }]
    PROCEDURE nom_procedure ([parametres_procedure])
    SQL SECURITY { DEFINER | INVOKER }
    corps_procedure

Exemple : création, par l'utilisateur root, de deux vues avec des contextes de vérification des privilèges différents.

CREATE DEFINER = CURRENT_USER
       SQL SECURITY DEFINER
       VIEW test_contexte1
AS SELECT * FROM Race;

CREATE DEFINER = CURRENT_USER
       SQL SECURITY INVOKER
       VIEW test_contexte2
AS SELECT * FROM Race;

Toujours avec l'utilisateur root, on autorise john à faire des SELECT sur ces vues.

1 2	GRANT SELECT ON elevage.test_contexte1 TO 'john'@'localhost'; GRANT SELECT ON elevage.test_contexte2 TO 'john'@'localhost';

Utilisons maintenant ces vues avec l'utilisateur john, qui n'a toujours aucun droit sur Race.

1 2	SELECT * FROM test_contexte1; SELECT * FROM test_contexte2;

La première requête affiche bien la table Race. Par contre, la seconde échoue avec l'erreur suivante :

1	ERROR 1356 (HY000): View 'elevage.test_contexte2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Options supplémentaires

La commande GRANT possède encore deux clauses facultatives supplémentaires, permettant de limiter les ressources serveur de l'utilisateur, et d'obliger l'utilisateur à se connecter via SSL.

Limitation des ressources

On peut limiter trois choses différentes pour un utilisateur :

le nombre de requêtes par heure (MAX_QUERIES_PER_HOUR) : limitation de toutes les commandes exécutées par l'utilisateur ;
le nombre de modifications par heure (MAX_UPDATES_PER_HOUR) : limitation des commandes entraînant la modification d'une table ou d'une base de données ;
le nombre de connexions au serveur par heure (MAX_CONNECTIONS_PER_HOUR).

Pour cela, on utilise la clause WITH MAX_QUERIES_PER_HOUR nb | MAX_UPDATES_PER_HOUR nb | MAX_CONNECTIONS_PER_HOUR nb de la commande GRANT. On peut limiter une des ressources, ou deux, ou les trois en une fois, chacune avec un nombre différent.

Exemple : création d'un compte 'aline'@'localhost' ayant tous les droits sur la base de données elevage, mais avec des ressources limitées.

GRANT ALL ON elevage.*
TO 'aline'@'localhost' IDENTIFIED BY 'limited'
WITH MAX_QUERIES_PER_HOUR 50
     MAX_CONNECTIONS_PER_HOUR 5;

Pour limiter les ressources d'un utilisateur existant sans modifier ses privilèges, on peut utiliser le privilège USAGE.

Exemple

1
2
3

GRANT USAGE ON *.*
TO 'john'@'localhost'
WITH MAX_UPDATES_PER_HOUR 15;

Pour supprimer une limitation de ressources, il suffit de la mettre à zéro.

Exemple

1
2
3

GRANT USAGE ON *.*
TO 'john'@'localhost'
WITH MAX_UPDATES_PER_HOUR 0;

Connexion SSL

La clause REQUIRE de la commande GRANT permet d'obliger l'utilisateur à se connecter via SSL, c'est-à-dire à l'aide d'une connexion sécurisée. Avec une telle connexion, toutes les données transitant entre le client et le serveur sont chiffrées, et non plus passées en clair.

Nous ne verrons pas les détails de cette clause dans ce cours. Je vous renvoie donc à la documentation si vous êtes intéressés par le sujet.

En résumé

Les utilisateurs et leurs privilèges sont stockés dans la base de données mysql.
On peut manipuler directement les tables de la base mysql, ou utiliser les commandes dédiées pour gérer les utilisateurs (CREATE USER, DROP USER,…) et leurs privilèges (GRANT, REVOKE).
Lorsque l'on accorde un privilège à un utilisateur, il faut également préciser à quel niveau on le lui accorde (global, base de données, table, colonne, procédure).
Le privilège ALL permet d'accorder en une fois tous les privilèges, sauf GRANT OPTION. Le privilège USAGE permet de modifier les options d'un utilisateur avec la commande GRANT sans modifier ses privilèges. Quant à GRANT OPTION, cela permet à un utilisateur d'accorder à d'autres les privilèges qu'il possède.
Les privilèges des vues, procédures stockées et triggers sont vérifiés de manière particulière, grâce aux clauses DEFINER et SQL SECURITY utilisées à la création de ces objets.
Il est également possible, avec la commande GRANT, de limiter les ressources disponibles pour un utilisateur, et d'obliger un utilisateur à se connecter via SSL.