Introduction à MariaDB | Base de données

class: main-title

<h1>Base de données</h1>

<h2>Introduction à MariaDB</h2>

---

<ul>
<li>Qu'est-ce qu'une base de données?</li>
<li>Caractéristiques de MariaDB</li>
<li>Mise en place d'un serveur MariaDB</li>
</ul>

---
class: section-title

<h1>Base de données?</h1>

---

<h1 class="slide-only">Base de données?</h1>

<pre><code class="language-js">let characters = ["Batman", "Oracle", "Joker", "Harley Quinn", "Bane"];
</code></pre>

<pre><code class="language-cpp">Character dcCharacters[...];

dcCharacters[0] = new Character("Batman", "Bruce Wayne");
dcCharacters[1] = new Character("Oracle", "Barbara Gordon");
dcCharacters[...] = ...;
</code></pre>

---

<h1 class="slide-only">Base de données?</h1>

<h3>Fichiers simples</h3>

<pre><code class="language-markdown">Batman
Bruce Wayne
Oracle
Barbara Gordon
</code></pre>

</div>

<pre><code class="language-markdown">alias,name
Batman,Bruce Wayne
Oracle,Barbara Gordon
</code></pre>

</div>

---

<h1 class="slide-only">Base de données?</h1>

<h3>Fichiers structurés</h3>

</div>

<div>

<pre><code class="language-xml">&lt;Characters&gt;
    &lt;Character alias="Batman" name="Bruce Wayne"&gt;&lt;/Character&gt;
    &lt;Character alias="Oracle" name="Barbara Gordon"&gt;&lt;/Character&gt;
&lt;/Characters&gt;
</code></pre>

</div>

</div>

<div>

<pre><code class="language-json">[
    {
        alias:"Batman", 
        name: "Bruce Wayne"
    },
    {
        alias:"Oracle", 
        name: "Barbara Gordon"
    }
]
</code></pre>

</div>

---

<h1 class="slide-only">Base de données?</h1>

<ul>
<li>Tableurs</li>
</ul>

---

<h1 class="slide-only">Base de données?</h1>

<ul>
<li>Base de données <em>relationnelles</em></li>
</ul>

---

<h1 class="menu">Base de données relationnelle</h1>

<p>On utilise un <strong>modèle relationnel</strong> pour organiser les données de façon structurée et standardisée pour faciliter l'accès par les développeurs via le langage SQL.</p>

---

<p>Logiciel permettant de stocker et manipuler(<em>CRUDL</em>) une base de données en assurant l'intégrité de l'information.</p>

---

<p>Une <em>instance</em> d'un SGBD peut héberger plusieurs bases de données, elles-mêmes contenant plusieurs tables.</p>

---

<h1 class="menu">MariaDB</h1>

<p>MariaDB est un système de gestion de bases de données relationnelles :</p>

<ul>
<li>Connu et reconnu</li>
<li>Open source</li>
<li>Interopérabilité MySQL</li>
<li>Sauvegarde et restauration</li>
<li>Réplication</li>
<li>Partitions</li>
</ul>

---

<h1 class="slide-only">Installation</h1>

<ul>
<li>Effectuer les opérations de configuration en tant que <em>root</em> via SSH ou directement sur la machine</li>
<li>Récupérer le paquet <em>mariadb-server</em></li>
</ul>

<pre><code class="language-bash">apt update &amp;&amp; apt install -y mariadb-server
</code></pre>

<ul>
<li>Confirmer l'initialisation du service</li>
</ul>

<pre><code class="language-bash">systemctl status mariadb
</code></pre>

---

<h1 class="menu">Connexion</h1>

<ul>
<li>Le serveur MariaDB est actif, on peut s'y connecter pour exécuter des commandes SQL</li>
</ul>

<pre><code class="language-bash"># En tant que root
mysql
# Version compacte de 
mysql -u [username] -p -h [server_host] [database]
# man mysql pour voir les options disponibles
</code></pre>

<ul>
<li><strong>Attention</strong>, <em>root</em> est un utilisateur possédant tous les privilèges et devrait être utilisé uniquement pour les tâches administratives</li>
<li>Par défaut, l'utilisateur <em>root</em> MariaDB est relié à l'utilisateur système <em>root</em> et la connexion n'est possible que via une console <em>root</em></li>
</ul>

---

<h1 class="slide-only">Commandes SQL</h1>

<p>Une fois la commande de connexion <code>mysql</code> exécutée, le terminal <code>MariaDB[()]&gt;</code> est disponible.</p>

<ul>
<li>Chaque commandes SQL doit se terminer par un point-virgule <strong>;</strong></li>
<li>Souvent, on écrit les mots-clés SQL en <strong>MAJUSCULE</strong>.</li>
<li>Le <span style="font-weight: bold; color: rgb(148, 173, 243);">code SQL</span> à exécuter dans le terminal MariaDB sera mis en évidence dans des <span style="font-weight: bold; color: rgb(148, 173, 243);">encadrés bleus</span> dans les notes de cours.</li>
</ul>

<pre><code class="language-sql">COMMANDE SQL;
</code></pre>

---
class: section-title

<h1 class="menu">Clients SQL</h1>

---

<h1 class="slide-only">Scripts SQL</h1>

<p>Il est possible de regrouper plusieurs commandes SQL dans un fichier <code>.sql</code> et de les exécuter via le client en ligne de commande</p>

<pre><code class="language-sql">SOURCE path/to/script.sql
</code></pre>

<p>Par exemple, <code>script.sql</code></p>

<pre><code class="language-sql">CREATE USER 'alice'@'localhost';

GRANT ALL ON *.* TO 'alice'@'localhost';

CREATE USER 'bob'@'%';
GRANT ALL ON public.* TO 'bob'@'%';

SET PASSWORD FOR 'bob'@'%' = PASSWORD('pwd');
</code></pre>

<p>VS Code et l'extension <em>Remote SSH</em> peuvent offrir un environnement de rédaction pratique.</p>

---

<h1 class="slide-only">Clients SQL GUI</h1>

<p>Par contre, certaines fonctionnalités (gestion des serveurs, création de fichiers, réadaction de requêtes, consultation du résultat, etc.) peuvent être plus agréable via une application avec interface graphique</p>

<ul>
<li><a href="https://www.beekeeperstudio.io/" target="_blank" rel="noopener noreferrer">Beekeeper Studio</a></li>
<li><a href="https://sqlectron.github.io/" target="_blank" rel="noopener noreferrer">sqlectron</a></li>
<li><a href="https://www.mysql.com/fr/products/workbench/" target="_blank" rel="noopener noreferrer">MySQL Workbench</a></li>
<li>...</li>
</ul>

---

<h1 class="menu">Utilisateurs</h1>

<p>La configuration initiale du serveur peut varier selon le paquet utilisé pour l'installation. Les distributions Debian et Ubuntu maintiennent une <a href="https://mariadb.com/kb/en/differences-in-mariadb-in-debian-and-ubuntu/" target="_blank" rel="noopener noreferrer">variante du script d'installation</a>.</p>

<p>Pour les autres systèmes d'exploitation, il est recommandé d'exécuter le script <code>mysql_secure_installation</code> pour un serveur utilisé en production.</p>

<pre><code class="language-bash">mysql_secure_installation
</code></pre>

---

<h1 class="slide-only">Utilisateurs et privilèges</h1>

<p>Plusieurs commandes sont disponibles pour la gestion des utilisateurs et leurs privilèges.</p>

<p>📚 <a href="https://mariadb.com/kb/en/account-management-sql-commands/" target="_blank" rel="noopener noreferrer">Documentation</a></p>

<ul>
<li>Mot de passe, Méthode d'authentification</li>
<li>Plusieurs niveaux de privilèges( <em>grants</em> ) offrent un accès granulaire

<ul>
<li>Bases de données > Tables > Colonnes</li>
<li>Fonctions / Procédures</li>
</ul></li>
</ul>

<p>Il est également possible de regrouper plusieurs privilèges dans un <strong>rôle</strong> pour faciliter la  réutilisation.</p>

---

<h1 class="slide-only">Utilisateurs</h1>

<p>Chaque utilisateur, <em>account name</em>, est défini par un identifiant ET un hôte</p>

<ul>
<li><strong>Account name</strong> = <code>'username'@'host'</code></li>
</ul>

<p>Le <em>username</em> identifie l'utilisateur et le <em>host</em> la source de la connexion. Par exemple, l'utilisateur <em>root</em> créé par défaut est défini <code>'root'@'localhost'</code>. Seules les connexions en provenance de la machine elle-même, <em>localhost</em>, seront autorisées pour <em>root</em>.</p>

<p>Les manipulations des utilisateurs doivent utiliser explicitement le <em>account name</em> complet.</p>

<p>📚 <a href="https://mariadb.com/kb/en/create-user/#account-names" target="_blank" rel="noopener noreferrer">Documentation</a></p>

---

<h1 class="slide-only">Créer les utilisateurs</h1>

<p>📚 <a href="https://mariadb.com/kb/en/create-user/#syntax" target="_blank" rel="noopener noreferrer">Documentation CREATE USER</a></p>

<pre><code class="language-sql">CREATE [OR REPLACE] USER [IF NOT EXISTS] 
 user_specification [,user_specification ...];

user_specification:
  username@host [authentication_option]

authentication_option:
  IDENTIFIED BY 'password' 
  | IDENTIFIED BY PASSWORD 'password_hash'
  | IDENTIFIED {VIA|WITH} authentication_rule
}

authentication_rule:
    authentication_plugin
  | authentication_plugin {USING|AS} 'authentication_string'
  | authentication_plugin {USING|AS} PASSWORD('password')
</code></pre>

---

<h1 class="slide-only">Créer les utilisateurs</h1>

<pre><code class="language-sql">CREATE USER 'alice';
-- Correspond a CREATE USER 'alice'@'%'
-- % est un wildcard pour le host

CREATE USER 'alice'@'localhost';

CREATE USER 'alice'@'A.B.C.D/S';
</code></pre>

<pre><code class="language-sql">-- Securiser l'utilisateur, avec un mot de passe
CREATE USER 'bob'@'localhost' IDENTIFIED BY 'pwd';
-- ou avec le plugin unix_socket
CREATE USER 'bob'@'localhost' IDENTIFIED VIA unix_socket;
</code></pre>

<pre><code class="language-sql">-- Pour eviter une erreur en creant un duplicata
CREATE USER IF NOT EXISTS 'charlie'@'localhost';
-- Pour reinitialiser un account name avec de nouvelles caracteristiques
CREATE OR REPLACE USER 'charlie'@'localhost' IDENTIFIED BY 'pwd';
-- ou PLUSIEURS mecanismes (10.4+)
CREATE USER 'etd'@'localhost' IDENTIFIED VIA unix_socket OR mysql_native_password USING PASSWORD('pwd'); 
</code></pre>

---

<h1 class="slide-only">Gérer les utilisateurs</h1>

<pre><code class="language-sql">-- Supprimer un utilisateur
DROP USER [IF EXISTS] username@host;
</code></pre>

<pre><code class="language-sql">-- Modifier les identifiants
RENAME USER username@host TO newUsername@newHost;
</code></pre>

<pre><code class="language-sql">-- Attribuer/Modifier/Retirer un mot de passe
-- Mettre le mot de passe a vide pour le retirer ''
SET PASSWORD [FOR username@host] = PASSWORD('clear_password');
</code></pre>

<pre><code class="language-sql">-- Modifie un utilisateur, avec la syntaxe du CREATE USER
ALTER USER [IF EXISTS] username@host [...];
</code></pre>

---

<h1 class="slide-only">Voir les utilisateurs</h1>

<pre><code class="language-sql">-- Voir les utilisateurs existants, en tant que root ou equivalent
SELECT user, host, plugin, password FROM mysql.user;
-- Pour voir si plusieurs mécanismes d'authentification (10.4+)
SELECT * FROM mysql.global_priv;
</code></pre>

<pre><code class="language-sql">-- Voir la commande de création d'un utilisateur
SHOW CREATE USER username@host;
</code></pre>

<p>La nomenclature <em>username@host</em> offre beaucoup de flexibilité pour définir les utilisateurs. Toutefois, MariaDB utilise plusieurs règles pour déterminer à quel utilisateur correspond une demande de connexion.</p>

<p>📚 <a href="https://mariadb.com/kb/en/create-user/#user-name-component" target="_blank" rel="noopener noreferrer">Documentation</a></p>

<pre><code class="language-sql">-- Voir quel utilisateur a été résolu par MariaDB à partir des identifiants fourni
SELECT user() AS 'fourni', current_user() AS 'resolu';
</code></pre>

---

<h1 class="menu">Connexion externe</h1>

<p>Bien qu'il soit possible de définir des utilisateurs autorisé à se connecter de différents <em>host</em>, il faut configurer le service MariaDB pour être à l'écoute des connexions externes(et ouvrir le pare-feu si nécessaire).</p>

<p>📚 <a href="https://mariadb.com/kb/en/configuring-mariadb-for-remote-client-access/" target="_blank" rel="noopener noreferrer">Documentation</a></p>

<p>Le fichier de configuration du service MariaDB est <code>/etc/mysql/mariadb.conf.d/50-server.cnf</code></p>

<pre><code class="language-ruby"># Décommenter et mettre a jour la ligne suivante avec l'IP du serveur

bind-address            = A.B.C.D 
</code></pre>

<p>Puis, redémarrer le service <code>systemctl restart mariadb</code>.</p>

---

<h1 class="menu">Privilèges</h1>

<p class="doc-only">
Pour exploiter le SGBD, un utilisateur fraîchement créé doit posséder des permissions.
</p>

<p>📚 <a href="https://mariadb.com/kb/en/grant/" target="_blank" rel="noopener noreferrer">Documentation</a></p>

<pre><code class="language-sql">-- Voir les permissions
SHOW GRANTS [FOR username@host];
</code></pre>

<pre><code class="language-sql">GRANT priv_type [(column_list)] [, priv_type [(column_list)], ...]
  ON db_name.obj_name
  TO username@host [WITH GRANT OPTION];

-- Le priv_type représente l'élément autorisé
-- https://mariadb.com/kb/en/grant/#privilege-levels

-- Le wildcard * peut être utilisé pour le db_name et obj_name

-- WITH GRANT OPTION permet à l'utilisateur d'attributer a d'autres utilisateurs 
-- les permissions qu'il possède
</code></pre>

---

<h1 class="slide-only">Privilèges</h1>

<p>Pour retirer une permission, on l'identifie avec la même structure que lors de l'attribution.</p>

<ul>
<li>Attention, on GRANT ... <strong>TO</strong> et on REVOKE ...<strong>FROM</strong></li>
</ul>

<pre><code class="language-sql">-- Retirer les permissions
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
  ON [object_type] priv_level
  FROM username@host [, username@host ...]
</code></pre>

<pre><code class="language-sql">-- Retirer TOUTES les permissions
REVOKE ALL PRIVILEGES, GRANT OPTION
  FROM username@host [, username@host ...];
</code></pre>

---

<h1 class="slide-only">Privilèges</h1>

<p>Il est également possible de faire un <strong>retrait partiel</strong> des permissions.</p>

<pre><code class="language-sql">MariaDB [(none)]&gt; REVOKE DROP ON testdb.* FROM alice@localhost;
</code></pre>

<pre><code class="language-sql">MariaDB [(none)]&gt; SHOW GRANTS FOR alice@localhost;
+--------------------------------------------------------+
| Grants for alice@localhost                             |
+--------------------------------------------------------+
| Grants for alice@localhost: GRANT USAGE ON *.* TO 'alice'@'localhost'
| Grants for alice@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `testdb`.* TO 'alice'@'localhost'
+--------------------------------------------------------+
</code></pre>

---

<h1 class="menu">Créer une base de données</h1>

<p>Pour nous permettre de valider au minimum nos permissions, nous allons <a href="https://mariadb.com/kb/en/create-database/" target="_blank" rel="noopener noreferrer">créer des bases données</a> que nous pourrons assigner aux utilisateurs.</p>

<pre><code class="language-sql">CREATE DATABASE [IF NOT EXISTS] db_name;
</code></pre>

<p>On peut préciser l'accès aux bases de données</p>

<pre><code class="language-sql">GRANT ALL ON db_name.* TO username@host;
</code></pre>

<p>Puis, lister les bases données disponibles à l'utilisateur connecté</p>

<pre><code class="language-sql">SHOW DATABASES;
</code></pre>

---
class: section-title

<h1>Exercices</h1>

<p>👉  <a href=/sessions/2023A/0Q7/documentation/1.2-exercices>Énoncé</a></p>