Aperçu des sections

  • Généralités

    Ce cours parle de toutes les tâches annexes aux bases de données qui ne se classent ni dans la conception, ni dans le langage SQL. Vous y retrouverez des concepts pour optimiser votre bdd ou vos requêtes, effectuer des opérations de maintenance, améliorer la sécurité, etc...
    • Les index

      Un index est un mécanisme existant à l'intérieur d'une table, qui a pour but d'optimiser la vitesse de recherche sur une colonne particulière. On va donc pouvoir indexer un champ qui est soumis à de nombreuses recherches, et qui contient de nombreuses lignes, afin d'optimiser les temps d'accès sur celui-ci, et donc augmenter la vitesse de notre application.

      Cette technique s'applique très régulièrement, et elle est facile à mettre en œuvre.


      Pour créer un index sur phpMyAdmin, il vous suffit de cliquer sur la colonne que vous souhaitez indexer, puis de cliquer sur l'icône Index, qui se trouve sous le tableau :



      Cette action aura pour conséquences d'exécuter cette requête SQL :

      ALTER TABLE `photos` ADD INDEX( `title`);


      Cette requête va entraîner la création d'un fichier géré automatiquement par le SGBDR, qui servira à optimiser les recherches qui concernent les titres des photos :


      C'est le même principe que les index des livres... :)


      On pourra alors visualiser tous nos index déjà créés pour cette table dans le tableau suivant, en dessous de la structure de la table dans phpMyAdmin :


      La troisième ligne concerne bien notre chanmp "title".


      Note : chaque champ qui est une clé primaire est automatiquement indexé. Donc ça ne sera à rien de créer un index sur une clé primaire, car il existe déjà. Idem pour les clés étrangères. Cela s'explique par le fait que les requêtes qui vont vérifier les contraintes relationnelles entre vos tables vont automatiquement vérifier la présence des clés dans les différentes tables, et vont devoir les parcourir le plus vite possible avant même d'exécuter votre propre requête.
      Attention : la création d'un index génère des fichiers supplémentaires, et assez lourds, proportionnellement à la taille de votre table. Donc si vous faites trop d'index, votre base de données va s'alourdir, et cela peut être générateur de divers problèmes.


      Voici un wiki assez précis sur les indexes dans les bases de données : https://fr.wikipedia.org/wiki/Index_(base_de_donn%C3%A9es)
    • Les vues

      Les vues sont des sortes de tables virtuelles qui agrègent les données de plusieurs tables en une seule. Elles sont basées sur une requête SQL de référence, et évitent d'avoir à retaper celle-ci à chaque fois que l'on a besoin d'accéder à une information. 

      Elles permettent aussi de donner un accès sécurisés aux données (via la gestion des utilisateurs et de leurs privilèges), sans avoir à ouvrir des droits sur une table particulière. On pourra alors donner des droits à un utilisateur sur un ensemble de vues, au lieu de lui donner des droits directement sur les tables concernées par celles-ci.


      Une fois que votre vue est créé et opérationnelle, vous pourrez exécuter des requêtes SQL dessus, comme une table normale.
      Lorsque les données présentes dans les différentes tables visées par la vue changent, les données de la vue changent aussi, bien évidemment. Une vue n'est pas un instantané du contenu de votre base de données, mais une sorte de requête mémorisée.


      Création et suppression d'une vue

      Les données présentes dans une vue sont définies par une clause SELECT après avoir créé la vue à l'aide de la clause CREATE VIEW (un peu comme un CREATE TABLE).


      La création d'une vue se fait avec la syntaxe suivante :

      CREATE VIEW Nom_Vue (colonnes) AS SELECT ...(la requête correspondant à de la vue)...


      Et voici la syntaxe pour la suppression de vue :

      DROP VIEW Nom_Vue;

    • Utilisateurs et privilèges

      Quand on gère une base de données, on va pouvoir définir plusieurs utilisateurs qui peuvent s'y connecter et travailler dessus. Au même titre qu'un utilisateur système, chaque utilisateur d'une base de données peux avoir des droits différents en fonction du rôle que vous lui donnerez. On parle alors de privilèges utilisateurs.

      Cette pratique peux s'avérer très utile quand on a un environnement complexe de base de données, avec différentes applications, mais bien souvent, et encore plus en tant que débutant, on aura un utilisateur qui aura tous les droits sur une base de données, et on fera attention à ne pas coder de choses trop dangereuses.

      Par défaut, l'utilisateur créé lors de l'installation est root. Dans MySQL, il n'a pas de password. Ceci devra être modifié sur une base en production car c'est très peu sécurisé.



      Utilisateurs


      Les utilisateurs ne correspondent pas vraiment à des personnes physiques (bien que cela soit possible), mais plutôt à des utilisateurs applicatifs. Par exemple : une application particulière qui doit accéder à certaines données de notre base de données.

      Un utilisateur est défini par trois choses de base :

      • son login
      • son mot de passe
      • l'hôte via lequel il se connecte (localhost par défaut, mais cela peut être un hôte distant par exemple (adresse IP, url, etc...))


      Voici un exemple de requête SQL permettant de créer un utilisateur :

      CREATE USER 'julien'@'localhost' IDENTIFIED BY '1234azerty'; 


      Requête SQL pour changer le login :

      RENAME USER 'julien'@'localhost' TO 'juju'@'localhost';


      Requête SQL pour changer le mot de passe :

      SET PASSWORD FOR 'julien'@'localhost' = PASSWORD('azerty1234');


      Toutes ces actions sont réalisables via des requêtes SQL, mais pour le cours, nous les réaliserons via le GUI pour plus de confort. Attention toutefois, si vous devez écrire des requêtes SQL à la main, n'oubliez pas que l'usage des guillemets peux changer la bonne exécution d'une requête. Le GUI les rajoutent automatiquement, mais elles sont optionnelles dans la plupart des cas.


      Utiliser le GUI pour gérer les utilisateurs


      Lister les utilisateurs :



      Autre pages :

      Création d'un user

      Choix d'une DB pour un user



      Modifier le mot de passe

      Modifier l'utilisateur



      Vérification des droits par le SGBDR

      Voici un diagramme d'exécution d'une requête SQL, prenant en compte les droits de l'utilisateur :




      Tous les utilisateurs, et leurs privilèges, sont stockées dans des DB système, qui sont créées par défaut dans le SGBDR.

      Exemple avec MySQL :

      • base de données : mysql
      • table : user


      Vous pouvez la parcourir pour voir l'intégralité de son contenu, et comment sont stockées les informations sur les user en DB.


      Privilèges

      Par défaut, un utilisateur n'a aucun droit dans un SGBDR (si ce n'est celui de se connecter). Il va falloir lui donner l'autorisation de travailler sur une ou plusieurs des bases de données que contient votre SGBDR, puis l'autoriser à faire certaines actions. On peut imaginer par exemple qu'un utilisateur n'a qu'un droit de lecture sur une base de données (par exemple : une application qui va seulement avoir besoin de lire des données), tandis qu'un autre aura la possibilité d'insérer ou de modifier les données (exemple : la partie back-end d'une API REST).


      Les privilèges vont plus loin que les simples éléments du CRUD, on va pouvoir autoriser des actions telles que :

      • créer de nouvelles tables (CREATE TABLE)
      • modifier la structure de table existante.  (ALTER TABLE)
      • supprimer une table (DROP TABLE)
      • gérer les contraintes, les indexes
      • etc...


      Vous pouvez gérer les privilèges pour presque tous les types de requêtes possibles au sein du SGBDR.


      Cela peux être utile si vous travailler dans une équipe de dev avec différents rôles :

      • le sysadmin aurai par exemple tous les droits
      • le lead dev aurait des droits de modification de structure + CRUD
      • les autres dev n'auraient que des droits de CRUD


      Cette sécurité empêche à certaines personnes de pouvoir faire des erreurs graves durant des phases de dev, ou on tatonne forcément.

      Quand le sysadmin nous donne enfin l'accès root : source les joies du code. 

       


      Cet exemple s'appliquerai bien à une équipe assez grande et avec de nombreuses compétences, mais la réalité est souvent plus simple : un seul utilisateur, et on fait attention à ce qu'on fait ! La sécurité sera testée entre les différents environnements du programme (dev/test/prod), et on prendra bien soin de tout vérifier à l'aide de différents mécanisme (tests unitaires, etc...). Donc tout ça va dépendre de la taille du projet sur lequel vous travaillez, des compétences en interne, et de la conception de la base de données. Vous n'aurez pas les mêmes problématiques sur la BDD d'une banque, et celle d'une petite entreprise.


      Privilèges accordés aux utilisateurs, et niveaux de privilèges



      Voici quelques mots-clés importants sur la gestion des droits utilisateurs :

      • GRANT : permet de définir des nouveaux privilèges pour un utilisateur
      • REVOKE : permet de supprimer des privilèges pour un utilisateur
      • ALL : permet d'accorder tous les droits à un utilisateur sans les spécifier un par un
      • ON/TO : éléments de syntaxe pour spécifier les bases de données, tables et user visés


      Et voici quelques exemples de requêtes de gestion des droits utilisateurs :

      GRANT ALL ON db1.* TO 'julien'@'localhost';  //Donnes tous les droits à l'user julien sur toutes les tables de la bdd db1
      GRANT SELECT ON db2.commandes TO 'maxime'@'localhost'; //Donnes seulement un droit en lecture pour l'utilisateur maxime sur la table commandes de la bdd db2


      Voici un lien vers la doc officielle, qui recense toutes les possibilités liées à la gestion des privilèges.
    • Dump, backup et import/export

      Note : vous devez absolument maitriser les façons de sauvegarder une base de données, car c'est crucial en termes de sécurité.




      Nous allons voir ici comment faire une sauvegarde brute de la base de données :

      • vous pouvez le faire manuellement en utilisant la fonction Export de PHPMyAdmin
      • vous pouvez le faire automatiquement via un script qui s'exécute régulièrement


      C'est la façon automatisée qui va nous intéressé le plus, car elle va permettre de faire des sauvegardes régulières de la base de données. L'import/export étant un outil que l'on utilise plutôt de manière "ponctuelle".


      Le Dump

      MySQL propose une commande spéciale, que l'on peut lancer directement en ligne de commande, et qui va vous rassemblez toutes les données et la structure de la BDD en un seul fichier. Ce fichier pourra alors être compressé, puis envoyé sur un ordinateur distant servant de stockage des sauvegardes. Cette technique est très importante car si un problème survient sur la BDD en production, vous serez bien content d'avoir un instantané assez frais pour "retrouver vos billes".

      Pour ceux qui n'ont jamais entendu parler de Dump : voici un lien Wiki : https://fr.wikipedia.org/wiki/Dump#Informatique


      Voici un exemple de commade dump mysql, exécutable en shell :

      mysqldump --user=mon_user --password=mon_password --databases nom_de_la_base > fichier_destination.sql

      Cette commande permet de sauvegarder l'intégralité d'une base de données (structure + données).

      La commande est assez simple à comprendre :
      1. mysqldump : c'est le nom de la commande qui permet de lancer un dump
      2. le couple user/password pour pouvoir se connecter à mysql
      3. le nom de la base de données que l'on souhaite dumper
      4. et en dernier, le nom du fichier qui contiendra le dump


      Note : évidemment, il y a de nombreuses options possibles (cibler toutes les bases du SGBDR, cibler seulement une table, etc...)

      Voici un lien vers la documentation officielle : https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

      Et quelques exemples plus digestes en français : https://devoups.fr/exporter-en-ligne-de-commande-avec-mysqldump/


      La méthode est assez simple, mais il faut maitriser un minium la ligne de commande. Il y a en général quelques étapes à respecter :

      1. on va exécuter un script shell régulièrement via un cron job (par exemple : toutes les nuits à 1h du matin)
      2. ce script va tout d'abord effectuer le Dump via la commande mysqldump
      3. ensuite on va compresser le résultat du Dump, car une base de données peut être très lourde (optionnel)
      4. puis, on va transférer ce fichier vers divers emplacements (on peut en mettre une copie en local par exemple, puis une autre sur un serveur distant)


      Voici quelques exemples de commandes liées aux backup de DB : https://www.memoinfo.fr/tutoriels-linux/guide-sauvegarde-restauration-mysql/


      Import/export

      -> Approfondir les fonctionnalités d'import/export.


      Note : vous trouverez une vidéo détaillée du processus de backup automatisée dans l'exercice suivant.
    • Cette section

      Procédures stockées

      Ce sont des sortes de fonctions utilisateur SQL, qui renvoient le résultat d'une requête sur un simple appel.
      Exemple : CALL getUser()  -> renvoit le résultat de SELECT * FROM user

      Procédure stockée avec paramètre :

      DELIMITER //
      CREATE PROCEDURE country_hos
       (IN con CHAR(20))
       BEGIN
        SELECT Name, HeadOfState
        FROM Country
        WHERE Continent = con;
       END //
      DELIMITER ;
      CALL country_hos('Europe');

      Exemples :
       


      Wiki très complet : https://fr.wikibooks.org/wiki/MySQL/Proc%C3%A9dures_stock%C3%A9es

      • Triggers

        Les triggers (gâchettes) permettent de déclencher des actions dans la DB lors de la manipulation des données.

        Exemple avec une table "audit" qui est mise à jour à chaque update dans une table employes :

        CREATE TRIGGER before_employee_update 
            BEFORE UPDATE ON employees
            FOR EACH ROW 
         INSERT INTO employees_audit
         SET action = 'update',
             employeeNumber = OLD.employeeNumber,
             lastname = OLD.lastname,
             changedat = NOW();
        


        https://sql.sh/cours/create-trigger

        (et tuto grafikart aussi)

        et aussi cette page.