Activité Création d'une base de données

Consigne

Avant de commencer, veuillez lire une première fois le document dans son ensemble.

Le travail est individuel. Vous pouvez communiquer en respectant le code d’honneur. Si vous être bloqué, demandez de l’aide afin de ne pas prendre trop de retard.

Situation

Vous êtes un administrateur de base de données, et on vous demande d’installer un nouveau serveur avec mysql, d’y créer une base de données, de créer un·e utilisateur·rice pour une application web et d’y importer des données.

Objectifs

À la fin de cette activité, vous devez :

• Être capable de créer des utilisateur·rice·s avec SQL.
• Être capable d’accorder des droits à des utilisateur·rice·s.
• Connaître les droits que l’on peut accorder à des utilisateur·rice·s dans MariaDB.
• Être capable de modifier le mot de passe d’un·e utilisateur·rice.
• Être capable d’afficher tous les utilisateur·rice·s.
• Être capable de supprimer un·e utilisateur·rice.
• Être capable de créer une base de données en utilisant la redirection de l’entrée standard (stdin).
• Être capable d’expliquer les problèmes que l’on peut rencontrer avec les clés étrangères lors de l’importation de données.
• Connaître différents moyens d’importer et d’exporter des données.

Résultat attendu

Un serveur mysql avec une base de données accessible.

Consignez vos actions dans un rapport d’une à deux pages.

Ressources

Matériel :

• Une machine dont le nom est : « svr-m140-$NUM-sql.lab.epai-ict.ch ». La variable « $NUM » est le numéro de votre réseau tel qu’il apparait dans les informations d’identification que vous avez reçues par courriel.

Mise en route

Commencez par vous connecter en ssh à votre machine et, si ce n’est pas déjà fait, installez le serveur mariaDB avec la commande suivante :

1
sudo apt install -y mariadb-server

Si la commande s’est exécutée avec succès, vous devriez être en mesure d’ouvrir le client mysql avec l’utilisateur·rice root en exécutant la commande suivante :

1
sudo mysql

Si cela fonctionne, le serveur MariaDB est correctement installé, mais il faut encore le configurer.

Par défaut, le serveur n’accepte des connexions que de l’hôte local sur l’adresse de boucle locale (127.0.0.1). Pour l’utiliser depuis l’extérieur, il est nécessaire de le lier à son adresse publique. Pour cela, il faut ouvrir le fichier de configuration /etc/mysql/mariadb.conf.d/50-server.cnf dans vi ou nano avec une élévation de privilège.

Lorsque vous y êtes, il faut localiser la configuration bind-address dans la section mysql et remplacer le 127.0.0.1 par 0.0.0.0 pour dire au serveur d’écouter toutes les adresses IP.

Lorsque c’est fait, vous pouvez redémarrer le service mariadb avec la commande suivante :

1
sudo systemctl restart mariadb

En remplaçant restart par status vous pouvez voir l’état du service. La commande systemctl supporte également start et stop pour démarrer et arrêter un service.

Avant de pouvoir nous connecter depuis notre PC, nous devons encore créer un·e utilisateur·rice avec la commande CREATE USER. Un·e utilisateur·rice est toujours créé·e pour un hôte (un nom ou une adresse IP), par défaut, il s’agit de localhost. Un·e utilisateur·rice n’a le droit de se connecter que depuis cet hôte. C’est un moyen simple d’ajouter un peu de sécurité pour l’utilisateur·rice d’un serveur Web par exemple. Mais que faire si l’on veut pouvoir se connecter depuis n’importe où ? Pour cela, on remplace l’hôte par le métacaractère %.

Ouvrez le client mysql en tant que root et lancez la commande suivante en remplaçant le mot de passe your_password par celui que vous voulez :

1
CREATE USER 'admin'@'%' IDENTIFIED BY 'you_password';

Quittez le client mysql et ouvrez une nouvelle fenêtre de terminal sur votre PC. Dans cette fenêtre, essayez de vous connecter au serveur MariaDB avec la commande suivante :

1
mysql -h svr-m140-###-sql.lab.epai-ict.ch -u admin -p

Si la commande mysql n’est pas reconnue, installez le client mysql avec Chocolatey (choco install mysql-cli) ou HomeBrew (brew install mysql-client).

Lorsque vous êtes dans l’interpréteur de commande de mysql, essayez de créer une base de données. Cela ne fonctionne pas. Par ce que vous n’avez pas suffisamment de privilèges.

Votre première tâche sera de résoudre ce problème. À vous de jouer !

Tâches

Accorder des privilèges

Votre première tâche est d’utiliser la commande SQL GRANT pour accorder tous les privilèges sur l’ensemble du serveur à l’utilisateur·rice admin.

Si vous parvenez à créer une base de données en vous connectant au serveur avec l’utilisateur·rice admin, en exécutant le client mysql de votre PC (sur la machine locale, pas sur le serveur), vous avez réussi.

Créer une base de données

On vous confie le soin de créer la base de données suivante dans le serveur. Le but est de ne pas avoir à faire de copier-coller, de ne pas utiliser d’interface graphique, de réaliser l’opération en exécutant le client mysql sur votre PC (sur la machine locale, pas sur le serveur), et de pouvoir reproduire facilement l’opération. Comment vous y prenez-vous ?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
/* Create the database */
CREATE DATABASE IF NOT EXISTS order_management;

/* Switch to the order_management database */
USE order_management;

/* Create the tables */
CREATE TABLE productline (
  productLine varchar(50),
  textDescription varchar(4000) DEFAULT NULL,
  htmlDescription mediumtext,
  image mediumblob,

  PRIMARY KEY (productLine)
);

CREATE TABLE product (
  productCode varchar(15),
  productName varchar(70) NOT NULL,
  productLine varchar(50) NOT NULL,
  productScale varchar(10) NOT NULL,
  productVendor varchar(50) NOT NULL,
  productDescription text NOT NULL,
  quantityInStock smallint(6) NOT NULL,
  buyPrice decimal(10,2) NOT NULL,
  MSRP decimal(10,2) NOT NULL,

  PRIMARY KEY (productCode),
  FOREIGN KEY (productLine) REFERENCES productline (productLine)
);

CREATE TABLE office (
  officeCode varchar(10),
  city varchar(50) NOT NULL,
  phone varchar(50) NOT NULL,
  addressLine1 varchar(50) NOT NULL,
  addressLine2 varchar(50) DEFAULT NULL,
  state varchar(50) DEFAULT NULL,
  country varchar(50) NOT NULL,
  postalCode varchar(15) NOT NULL,
  territory varchar(10) NOT NULL,

  PRIMARY KEY (officeCode)
);

CREATE TABLE employee (
   employeeNumber int,
   lastName varchar(50) NOT NULL,
   firstName varchar(50) NOT NULL,
   extension varchar(10) NOT NULL,
   email varchar(100) NOT NULL,
   officeCode varchar(10) NOT NULL,
   reportsTo int DEFAULT NULL,
   jobTitle varchar(50) NOT NULL,

   PRIMARY KEY (employeeNumber),
   FOREIGN KEY (reportsTo) REFERENCES employee (employeeNumber),
   FOREIGN KEY (officeCode) REFERENCES office (officeCode)
);

CREATE TABLE customer (
   customerNumber int,
   customerName varchar(50) NOT NULL,
   contactLastName varchar(50) NOT NULL,
   contactFirstName varchar(50) NOT NULL,
   phone varchar(50) NOT NULL,
   addressLine1 varchar(50) NOT NULL,
   addressLine2 varchar(50) DEFAULT NULL,
   city varchar(50) NOT NULL,
   state varchar(50) DEFAULT NULL,
   postalCode varchar(15) DEFAULT NULL,
   country varchar(50) NOT NULL,
   salesRepEmployeeNumber int DEFAULT NULL,
   creditLimit decimal(10,2) DEFAULT NULL,

   PRIMARY KEY (customerNumber),
   FOREIGN KEY (salesRepEmployeeNumber) REFERENCES employee (employeeNumber)
);

CREATE TABLE payment (
   customerNumber int,
   confirmationCode varchar(50) NOT NULL,
   paymentDate date NOT NULL,
   amount decimal(10,2) NOT NULL,

   PRIMARY KEY (customerNumber,confirmationCode),
   FOREIGN KEY (customerNumber) REFERENCES customer (customerNumber)
);

CREATE TABLE `order` (
   orderNumber int,
   orderDate date NOT NULL,
   requiredDate date NOT NULL,
   shippedDate date DEFAULT NULL,
   status varchar(15) NOT NULL,
   comments text,
   customerNumber int NOT NULL,

   PRIMARY KEY (orderNumber),
   FOREIGN KEY (customerNumber) REFERENCES customer (customerNumber)
);

CREATE TABLE orderdetail (
  orderNumber int,
  productCode varchar(15) NOT NULL,
  quantityOrdered int NOT NULL,
  priceEach decimal(10,2) NOT NULL,
  orderLineNumber smallint(6) NOT NULL,

  PRIMARY KEY (orderNumber,productCode),
  FOREIGN KEY (orderNumber) REFERENCES `order` (orderNumber),
  FOREIGN KEY (productCode) REFERENCES product (productCode)
);

Pourquoi le nom de la table order est-il placé entre des backticks ?

Si vous devez refaire l’opération, vous devez d’abord supprimer la base de données. Est-ce une bonne idée d’ajouter une instruction de suppression dans le script de création ? Justifiez.

Créée un·e utilisateur·rice pour une application web

Maintenant que vous avez une base de données et que vous vous êtes familiarisé·e avec les commandes CREATE USER et GRANT, créez un·e nouvel utilisateur·rice order-appqui peut se connecter uniquement depuis votre serveur www et qui a le droit de lire et d’écrire dans toutes les tables de la base de données que vous venez de créer. Cet utilisateur·rice ne doit pas pouvoir modifier la structure de la base de données, ni accéder à une autre base de données, ni en créer une nouvelle.

Si un·e utilisateur·rice existe déjà, comment modifie-t-on son mot de passe ?

Assurez-vous également de savoir comment lister les utilisateur·rice·s, supprimer un·e utilisateur·rice, afficher les droits d’un·e utilisateur·rice, etc.

Charger des données dans la base de données

On vous a fourni un URI vers un fichier ZIP qui contient des données qu’il faut charger dans la base de données. Vous devez déterminer le format de ces fichiers, vous assurer que cela correspond aux tables que vous avez créées et trouver un moyen reproductible de charger ces données (pas d’utilisation interactive du client, pas de copier-coller).

Peut-on charger les données dans n’importe quel ordre ? En particulier, que se passe-t-il si on importe des données dans une table qui contient une clé étrangère et que les données de table référencée ne sont pas encore chargées ?

Énumérez les options qui s’offrent à vous et essayez chacune d’elles, d’abord, en recréant la base de données avant chaque importation, puis en essayant de charger plusieurs fois les données dans une même base de données.

Profitez de cet exercice pour apprendre à télécharger une archive et en extraire les données en ligne de commande sur le serveur. Apprenez également à utiliser la commande scp pour copier des fichiers de votre système local vers un système distant et inversement.

Exporter des données de la base de données

Déterminez les différents moyens d’exporter les données d’une base de données pour les importer dans une nouvelle base de données. Pour cela, créez une nouvelle base de données avec le même schéma.

Effectuer la même tâche en modifiant légèrement le schéma de la seconde base de données pour simuler une migration de données. Par exemple, modifiez le nom d’un champ dans une table, ajoutez un champ dans une autre table, et supprimez un champ dans une troisième.