Base de données?

Code

let characters = ["Batman", "Oracle", "Joker", "Harley Quinn", "Bane"];

Character dcCharacters[...];

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

Base de données?

Fichiers simples

Batman
Bruce Wayne
Oracle
Barbara Gordon

alias,name
Batman,Bruce Wayne
Oracle,Barbara Gordon

Base de données?

Fichiers structurés

<Characters>
    <Character alias="Batman" name="Bruce Wayne"></Character>
    <Character alias="Oracle" name="Barbara Gordon"></Character>
</Characters>

[
    {
        alias:"Batman", 
        name: "Bruce Wayne"
    },
    {
        alias:"Oracle", 
        name: "Barbara Gordon"
    }
]

Base de données?

• Tableurs

Base de données?

• Base de données relationnelles

Base de données relationnelle

On utilise un modèle relationnel 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.

SGBD

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

SGBD

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

MariaDB

MariaDB est un système de gestion de bases de données relationnelles :

• Connu et reconnu
• Open source
• Interopérabilité MySQL
• Sauvegarde et restauration
• Réplication
• Partitions

Installation

• Effectuer les opérations de configuration en tant que root via SSH ou directement sur la machine
• Récupérer le paquet mariadb-server

apt update && apt install -y mariadb-server

• Confirmer l'initialisation du service

systemctl status mariadb

Connexion

• Le serveur MariaDB est actif, on peut s'y connecter pour exécuter des commandes SQL

# En tant que root
mysql
# Version compacte de 
mysql -u [username] -p -h [server_host] [database]
# man mysql pour voir les options disponibles

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

Commandes SQL

Une fois la commande de connexion mysql exécutée, le terminal MariaDB[()]> est disponible.

• Chaque commandes SQL doit se terminer par un point-virgule ;
• Souvent, on écrit les mots-clés SQL en MAJUSCULE.
• Le code SQL à exécuter dans le terminal MariaDB sera mis en évidence dans des encadrés bleus dans les notes de cours.

COMMANDE SQL;

Clients SQL

Scripts SQL

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

SOURCE path/to/script.sql

Par exemple, script.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');

VS Code et l'extension Remote SSH peuvent offrir un environnement de rédaction pratique.

Clients SQL GUI

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

• Beekeeper Studio
• sqlectron
• MySQL Workbench
• ...

Utilisateurs

La configuration initiale du serveur peut varier selon le paquet utilisé pour l'installation. Les distributions Debian et Ubuntu maintiennent une variante du script d'installation.

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

mysql_secure_installation

Utilisateurs et privilèges

Plusieurs commandes sont disponibles pour la gestion des utilisateurs et leurs privilèges.

📚 Documentation

• Mot de passe, Méthode d'authentification
• Plusieurs niveaux de privilèges( grants ) offrent un accès granulaire
  • Bases de données > Tables > Colonnes
  • Fonctions / Procédures

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

Utilisateurs

Chaque utilisateur, account name, est défini par un identifiant ET un hôte

• Account name = 'username'@'host'

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

Les manipulations des utilisateurs doivent utiliser explicitement le account name complet.

📚 Documentation

Créer les utilisateurs

📚 Documentation CREATE USER

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')

Créer les utilisateurs

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';

-- 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;

-- 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'); 

Gérer les utilisateurs

-- Supprimer un utilisateur
DROP USER [IF EXISTS] username@host;

-- Modifier les identifiants
RENAME USER username@host TO newUsername@newHost;

-- 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');

-- Modifie un utilisateur, avec la syntaxe du CREATE USER
ALTER USER [IF EXISTS] username@host [...];

Voir les utilisateurs

-- 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;

-- Voir la commande de création d'un utilisateur
SHOW CREATE USER username@host;

La nomenclature username@host 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.

📚 Documentation

-- Voir quel utilisateur a été résolu par MariaDB à partir des identifiants fourni
SELECT user() AS 'fourni', current_user() AS 'resolu';

Connexion externe

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

📚 Documentation

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

# Décommenter et mettre a jour la ligne suivante avec l'IP du serveur

bind-address            = A.B.C.D 

Puis, redémarrer le service systemctl restart mariadb.

Privilèges

Pour exploiter le SGBD, un utilisateur fraîchement créé doit posséder des permissions.

📚 Documentation

-- Voir les permissions
SHOW GRANTS [FOR username@host];

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

Privilèges

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

• Attention, on GRANT ... TO et on REVOKE ...FROM

-- Retirer les permissions
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
  ON [object_type] priv_level
  FROM username@host [, username@host ...]

-- Retirer TOUTES les permissions
REVOKE ALL PRIVILEGES, GRANT OPTION
  FROM username@host [, username@host ...];

Privilèges

Il est également possible de faire un retrait partiel des permissions.

MariaDB [(none)]> SHOW GRANTS FOR alice@localhost;
+--------------------------------------------------------+
| Grants for alice@localhost                             |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'alice'@'localhost'              |
| GRANT ALL ON `testdb`.* TO 'alice'@'localhost'         |
+--------------------------------------------------------+

MariaDB [(none)]> REVOKE DROP ON testdb.* FROM alice@localhost;

MariaDB [(none)]> 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'
+--------------------------------------------------------+

Créer une base de données

Pour nous permettre de valider au minimum nos permissions, nous allons créer des bases données que nous pourrons assigner aux utilisateurs.

CREATE DATABASE [IF NOT EXISTS] db_name;

On peut préciser l'accès aux bases de données

GRANT ALL ON db_name.* TO username@host;

Puis, lister les bases données disponibles à l'utilisateur connecté

SHOW DATABASES;