Analyse et Conception de Systmes Informatiques ACSI Cours

Analyse et Conception de Systèmes Informatiques (ACSI)

Cours 1 : Présentation L'algèbre relationnelle Le modèle entité-relation (E-A) Cours 2 : Du modèle E-A au MPD Le langage SQL Cours 3 : SQL avancé Cours 4 : Méthodes de modélisation Cours 5 : Administration et sécurité 2

Sommaire Architecture Administration Efficacité Fiabilité Outils Reprise Sécurité 3

Architecture Le système de base de données My. SQL est une application de type Client / Serveur. Cette application est un logiciel libre basé sur une licence Open Source GPL. My. SQL est écrit en C++. Il fonctionne sur de nombreux systèmes d'exploitation (Windows, Linux, Unix). Il supporte de nombreuses API telles que C, C++, PHP, Java, … Il dispose d'un driver ODBC sous Windows. 4

Architecture My. SQL est interrogeable par le langage SQL. Il est capable de gérer des bases de données de très grande taille. La taille d'une table est limitée à 8 millions de To (2^63). Chaque table peut contenir jusqu'à 32 indexes constitués de 16 colonnes au maximum. Le nombre maximum de connexions n'a pas de limite théorique mais est lié aux ressources disponibles sur le serveur. 5

Architecture Les clients du SGBD Mysql peuvent être des outils de requêtes SQL, des logiciels ou des sites WEB. Dans tous les cas, le SGBD permet de gérer les connexions ainsi que les accès aux données. 6

Architecture à 2 niveaux Clients Serveur de BD L'architecture à 2 niveaux caractérise les systèmes clients / serveurs pour lesquels la communication est faite directement, sans intermédiaire. C'est le cas avec un requêteur tel que My. SQL Query Browser. 7

Architecture à 3 niveaux Clients Serveur d'applications Serveur de BD L'architecture à 3 niveaux ajoute un serveur d'applications également appelé Middleware chargé des communications avec les clients. Lui seul peut se connecter au serveur de BD ce qui accroît la sécurité. 8

Exemple 1 : logiciel de gestion Client lourd Clients Serveur Logiciel de gestion Base de données Une entreprise a conçu son propre logiciel de gestion. Les différents clients sont installées sur les postes des utilisateurs. Ils communiquent avec l'application Serveur qui est la seule à communiquer avec la base de données. 9

Exemple 2 : site WEB Navigateur Clients Serveur WEB PHP et Apache Pour les sites Internet, le même principe est généralement utilisé. La majorité des sites utilisent la configuration LAMP : Linux : Système Apache : serveur Web Base de données My. SQL : base de données PHP : langage de programmation 10

Exemple 2 : site WEB Navigateur Clients Serveur Dans cette architecture, Apache gère les connexions clientes. Serveur WEB PHP et Apache PHP est utilisé pour générer les pages Web de façon dynamique en interaction avec la base de données. Base de données My. SQL Dans la plupart des offres d'hébergement, la base de données, le serveur Apache et le moteur PHP sont sur le même serveur. 11

Exemple 2 : site WEB Navigateur Clients Serveur Php. My. Admin Base de données My. SQL Lorsque l'on accède à la base de données par le biais de php. My. Admin, c'est l'application qui communique avec la base de données. Pour des raisons de sécurité, généralement, l'accès à la base n'est possible que par un client situé sur le même serveur que le SGBD. 12

Administration Comme toute application, un SGBD doit être géré et maintenu. C'est le rôle du DBA (Data Base Administrator). Celui-ci est chargé de : S'assurer de l'efficacité de la base Assurer un bon fonctionnement de la base Permettre une reprise rapide en cas d'incident 13

Administration : efficacité La définition du modèle est généralement confiée aux développeurs de l'application associée à la base de données. Les développeurs créent des tables afin de ranger les données de façon optimum. Il arrive souvent que les performances ne soient pas celles attendues ou que la volumétrie impose des optimisations. Le DBA a pour rôle de conseiller et d'orienter les développeurs. 14

Administration : efficacité Les principaux champs d'action du DBA sont : Conseil sur la structure des tables Conseil sur la création d'index Recherche des requêtes trop longues et proposition pour les optimiser Recherche des verrous sur les tables et propositions pour les limiter Il est également de la responsabilité des développeurs de ne pas mettre en place des fonctionnalités trop lourdes pour la base. 15

Administration : fiabilité La base de données est composée de tables dont les données sont stockées dans des fichiers. Au fur et à mesure des insertions, suppressions, modifications, les fichiers se fragmentent. Les bases de données proposent généralement des outils pour permettre de défragmenter les fichiers ou de compacter les tables. Il est fréquent que les DBA créent des scripts de maintenance qui sont exécuté régulièrement 16 sur le serveur.

Administration : fiabilité En My. SQL, il existe plusieurs fonctions permettant de vérifier, réparer optimiser ou analyser des tables. CHECK TABLE nomtable [, nomtable 2] [option] retourne une table dont les enregistrements renseignent sur l'état des tables vérifiées. Les options possibles sont : QUICK : ne recherche pas les enregistrements orphelins (qui ne répondent pas aux contraintes d'intégrité référentielle) 17

Administration : fiabilité FAST : Ne vérifie que les tables dont la fermeture ne s'est pas déroulée correctement. CHANGED : Ne vérifie que les tables qui ont subi des modifications depuis la dernière vérification. MEDIUM : Vérifie les enregistrements et calcule une clé d'intégrité (checksum). EXTENDED : Vérifie les enregistrements et calcule une clé d'intégrité pour chacun des enregistrements. C'est la méthode la plus sûre et donc la plus longue. Cette commande ne fonctionne qu'avec les moteurs My. Isam et Inno. DB. 18

Administration : fiabilité Pour corriger les anomalies rencontrées, il existe la commande REPAIR TABLE nomtable [, nomtable 2][option] L'option QUICK permet de ne réparer que l'index. L'option EXTENDED permet de mieux gérer les index formés de chaînes longues. Cette commande ne fonctionne qu'avec le moteur My. Isam. 19

Administration : fiabilité La commande OPTIMIZE TABLE permet de réduire la fragmentation des fichiers. Cela est surtout nécessaire sur des tables contenant des champs de taille variable (varchar, text, blob). Cette commande agit sur 3 points : Corrige et améliore le stockage des enregistrements supprimés ou fragmentés Trie des index Met à jour les statistiques. Les statistiques des tables sont des informations 20 sur leur volumétrie et leur taille.

Administration : fiabilité Les statistiques sont essentielles pour le moteur de base de données. Pour chaque requête SQL, le moteur va définir un plan d'exécution. Il y a toujours plusieurs façon de faire en utilisant tel ou tel index par exemple. C'est à partir des données de statistiques que le moteur va choisir la meilleur façon de faire grâce à des algorithmes relativement compliqués. Si les statistiques ne sont pas à jour, il est alors possible que le moteur choisisse un mauvais plan. 21

Administration : fiabilité La syntaxe est : OPTIMIZE TABLE nomtable [, nomtable 2] Cette commande ne fonctionne qu'avec les moteurs My. Isam et BDB. En cas d'utilisation avec le moteur Inno. DB, le système le remplace automatiquement par les commandes alter table et analyze ce qui a pour effet de recréer la table et les index et de mettre à jour les statistiques. 22

Administration : fiabilité Il existe également des programmes à lancer en ligne de commande. Le programme myisamchk permet d'effectuer toutes les opérations de maintenance vue précédemment. Comme son nom l'indique, ce programme est réservée aux tables de type My. Isam. Ce programme doit toujours être lancé base fermée, c'est à dire qu'aucun client ne doit se connecter à la base pendant l'exécution de l'application. 23

Administration : fiabilité Il existe un autre programme à lancer en ligne de commande : mysqlcheck. Contrairement à myisamchk, celui-ci doit être lancé base ouverte car il se connecte au serveur. Cet utilitaire peut être utilisé pour vérifier, réparer, analyser et optimiser des tables ou des bases de données complètes. 24

Administration : fiabilité Comme toute application digne de ce nom, My. SQL génère des fichiers de logs. Il est indispensable de consulter régulièrement les logs pour s'assurer que le moteur ne rencontre pas d'erreurs récurrentes. Afin de résoudre certains problèmes, il est possible de modifier le niveau de trace des logs. 25

Administration : fiabilité Le suivi général des requêtes : Il est possible de préciser au moteur de logger toutes les requêtes transmises au serveur. Pour cela, il faut modifier le fichier de configuration my. cnf pour qu'il contienne la section suivante : [mysqld] log[=nom_fichier] Le moteur doit être redémarré en cas de modification du fichier de configuration. 26

Administration : fiabilité Le suivi binaires des mises à jour : Il est possible de préciser au moteur de logger toutes les requêtes de mise à jour transmises au serveur. Pour cela, il faut modifier le fichier de configuration my. cnf pour qu'il contienne la section suivante : [mysqld] log-bin[=nom_fichier] Il faut utiliser l'application mysqlbinlog pour lire les fichiers de log car ils sont au format binaire. 27

Administration : fiabilité Il est aussi possible d'activer le suivi des requêtes lentes. Pour cela, il faut modifier le fichier de configuration my. cnf pour qu'il contienne la section suivante : [mysqld] log-slow_queries[=nom_fichier] Il faut utiliser l'application mysqdumpslow pour lire les fichiers de log car ils sont au format binaire. 28

Administration : fiabilité Les fichiers de logs sont donc fondamentaux pour assurer un suivi fiable de la base de données. Il est utile de mettre en place un archivage automatique de ces fichiers. Il arrive souvent de devoir comprendre un fonctionnement passé et ces fichiers sont les seules informations dont disposent les DBA. 29

Administration : outils Les DBA doivent être capable de savoir ce qui se passe à tout moment sur leur base de données. Pour cela, ils disposent d'un panel de commandes permettant de consulter les connexions, de voir ce que font les clients, de vérifier les ressources, les requêtes, … Il existe également des commandes de consultations de structure des tables utiles aux DBA. 30

Administration : outils Les commandes DESCRIBE ou EXPLAIN suivi du nom de la table permettent d'en consulter la structure. EXPLAIN peut également être utilisé pour analyser les requêtes SQL. En exécutant EXPLAIN suivi de la requête, My. SQL retourne le détail de l'action qu'il va mener pour réaliser la requête. Cet commande peut évidemment être utilisée par les développeurs lors de la conception de 31 requêtes complexes.

Administration : outils La fonction BENCHMARK permet de jouer N fois une requête SQL. BENCHMARK (nb_fois, SQL) Cela permet de réaliser des tests périodiques pour s'assurer que le moteur répond de façon habituelle. Note : seule la première requête accède aux données. Les suivantes bénéficient du cache utilisé par le moteur. Cette commande ne permet donc pas de faire une moyenne des temps d'exécution mais juste une statistique. 32

Administration : outils Afficher les structures : Les commandes SHOW DATABASES et SHOW TABLES permettent de lister les bases et les tables du moteur de base de données. La commande SHOW COLUMNS décrit les champs de la table spécifiée. La commande SHOW INDEX affiche les index de la table. 33

Administration : outils Afficher l'activité : La commande SHOW OPEN TABLES indique les tables actuellement ouvertes. Ce sont les tables sur lesquelles des requêtes sont actuellement exécutées ou que My. SQL garde en cache. La commande SHOW PROCESSLIST montre tous les processus du serveur My. SQL. L'instruction KILL permet de mettre fin à un des processus. 34

Administration : reprise Un autre rôle fondamental du DBA est de permettre une reprise rapide en cas d'incident. Une base de données comme tout système informatique est susceptible de planter. Même s'il est toujours possible de rencontrer un bogue, il est plus fréquent de rencontrer des problèmes machine ou matériel (problème avec l'OS, panne machine, …) La seule solution pour prévenir ce genre de problème est de disposer de sauvegardes. 35

Administration : reprise Le DBA dispose de plusieurs outils pour effectuer des sauvegardes de la base de données. L'utilitaire mysqlhotcopy permet de sauvegarder directement les fichiers utilisés par la base. L'inconvénient principal de cette application est qu'elle doit être lancée sur le serveur bases fermées. Cette application va effectuer les actions suivantes : verrouillage des tables, vidage des caches et copie des répertoires. 36

Administration : reprise L'utilitaire mysqldump permet de créer un fichier de script SQL permettant de recréer la base ainsi que tout son contenu. Qu'elle que soit la méthode choisie, le DBA devra mettre en place une politique de sauvegarde en encapsulant ces commandes dans des scripts exécutés régulièrement sur le serveur. Il pourra également définir des tables plus sensibles que d'autres pour lesquelles il effectuera des sauvegardes plus fréquentes. 37

Administration : reprise En cas de nécessité de restauration de la base de données, il suffira de remplacer les répertoires mis de côté par l'utilitaire mysqlhotcopy ou de jouer le script SQL généré par l'utilitaire mysqldump. On appelle usuellement une sauvegarde un DUMP. 38

Sécurité La sécurité est essentielle dans toute application et plus particulièrement dans un SGBD. Les données sont souvent le trésor de l'entreprise. Il est impensable qu'un utilisateur accède à des données qui ne lui sont pas destinées. Il est encore plus impensable qu'un utilisateur malveillant parvienne à dérober les données, les conséquences sont souvent dramatiques. 39

Sécurité : Anonymat Un des premiers réflexes à avoir pour sécuriser la base My. SQL est de supprimer tout compte anonyme. La table système Mysql. user contient tous les utilisateurs déclarés. Il faut s'assurer qu'il n'y a pas d'utilisateur sans mot de passe. En cas de modification manuelle de cette table, il faut exécuter 'Flush Privileges' pour que le moteur les prennent en compte. 40

Sécurité : Gestion utilisateur Il est indispensable de bien gérer les privilèges des utilisateurs en ne lui permettant que le nécessaire. Ceux-ci sont rangés dans 3 catégories : Accès aux données Accès aux structures Droits administrateurs Il est également possible pour chaque utilisateur: de préciser la base accessible limiter le nombre de requêtes ou mises à jour 41

Sécurité : Gestion utilisateur L'accès aux données permet de préciser si l'utilisateur peut : SELECT : lire des données INSERT : ajouter des données UPDATE : modifier des données DELETE : supprimer des données FILE : accéder aux fichiers du serveur La majorité des utilisateurs devra avoir les quatre premiers droits pour interagir avec la base de données. 42

Sécurité : Gestion utilisateur La deuxième catégorie permet de préciser les droits au niveau de la structure : CREATE, ALTER, DROP, . . . Les utilisateurs 'client' de l'application ne devront jamais obtenir ces droits. Ceux-ci sont généralement réservés aux développeurs pour une de développement ou d'intégration et aux DBA seuls pour une base de production. 43

Sécurité : Gestion utilisateur Enfin, My. SQL permet de restreindre la sollicitation de la base de données par utilisateur. Ces fonctionnalités sont assez peu utilisées mais permettent de : Limiter le nombre de requêtes par heure Limiter le nombre de mise à jour par heure Limiter le nombre de connexions simultanées Cela pour éviter toute saturation de la base de données. 44

Sécurité : Gestion utilisateur La troisième catégorie permet de préciser les droits d'administration : création de base de données, gestion des utilisateurs, arrêt démarrage de la base, . . . Généralement, ceux-ci sont réservés aux DBA quel que soit l'environnement de travail (développement, intégration, recette, préproduction, production). Il est également possible d'attribuer des droits différents si plusieurs DBA de niveaux différents 45 administrent la base de données.

Sécurité : Stockage des données Dans une base de données, il est important de crypter les données sensibles. En effet, tout utilisateur ayant les droits de lecture sur une base peut tenter de lire des données qui ne lui sont pas destinées. Des données critiques comme des mots de passe ou des coordonnées bancaires par exemple ne doivent pas être utilisables en l'état. My. SQL propose des fonctions SQL de cryptage et de décryptage. 46

Sécurité : Stockage des données La fonction PASSWORD() permet d'encrypter une chaîne de caractère. Il ne sera pas possible de retrouver la valeur initiale de la chaîne. Il sera donc nécessaire de comparer une nouvelle chaîne cryptée de la même façon avec la chaîne stockée en base. Les fonctions ENCODE() et DECODE() reçoivent en paramètre un mot de passe. Elles permettent donc de retrouver la valeur initiale sous réserve de connaître le mot de passe. 47

Sécurité : Stockage des données Enfin, My. SQL propose des méthodes de cryptage ayant fait leurs preuves : ENCRYPT() utilisant la fonction Unix crypt() MD 5() : cryptage sur 128 bits SHA 1() : cryptage sur 160 bits Bien entendu, ces fonctions ralentissent les temps d'exécution et doivent donc être utilisées avec discernement. 48

Sécurité : Réseau Comme toute application client/serveur, My. SQL communique par le biais de ports avec ses clients. Généralement, on sécurise l'accès à la base de données en ne permettant la communication qu'entre le serveur d'application et la base de données. Pour cela, les deux applications peuvent être hébergées sur le même serveur et le port de communication My. SQL bloqué de l'extérieur par le biais d'un Fire. Wa. LL. 49

Sécurité : Réseau Navigateur Clients Serveur Port 3306 FIREWALL Port 80 PHP / Apache Port 3306 Base de données My. SQL Dans cet exemple, le Fire. Wall est utilisé pour empêcher l'accès de l'extérieur au serveur sur le port 3306 (My. SQL). En revanche, il ne gène par la communication entre le serveur PHP et la base de données. Il permet également la communication entre l'extérieur et le serveur WEB par le port 80. 50

Sécurité : Communication Par défaut, les données transitent entre le serveur et le client sous forme non compressée. Il est ainsi possible d'intercepter une communication et de lire les données transmises. Il peut donc être bon de sécuriser l'échange des données via un protocole tel que SSH. 51

Sécurité : Réseau Clients Serveur Protocole SSH Dans cette architecture, SSH devra être utilisée directement par My. SQL. Les données transmises seront alors cryptées. Serveur de BD 52

Sécurité : Réseau Clients Serveur Protocole SSH Serveur d'application Serveur de BD Dans cette architecture, il n'est pas nécessaire de crypter les données depuis My. SQL. C'est donc le serveur d'application qui se chargera de cette tâche. 53