Configuration MariaDB : rôles, utilisateurs et bases

Rôles, utilisateurs et bases de données — 3 serveurs

1. Objectif

Configurer de manière identique les trois serveurs MariaDB. Chaque serveur héberge une seule base de données dont le nom correspond au nom de la machine.

2. Pré-requis

• MariaDB installé et démarré : systemctl status mariadb
• Accès root à l’instance MariaDB
• mysql_secure_installation exécuté lors de l’installation
• Version MariaDB ≥ 10.0.5 (support des rôles)

3. Procédure pas à pas

Cette procédure est identique sur les trois serveurs. Seul le nom de la base diffère (= nom de la machine).

Étape 1 — Connexion à MariaDB

mysql -u root -p

Étape 2 — Création du rôle administrateur

Centralise les droits des admins. Tout changement sur le rôle s’applique automatiquement à tous les utilisateurs qui en héritent.

CREATE ROLE 'admin_role';

Étape 3 — Création des utilisateurs

Quatre comptes avec le mot de passe commun, accessibles depuis n’importe quel hôte (@’%’).

CREATE USER 'student'@'%'  IDENTIFIED BY '[mot de passe masqué]';
CREATE USER 'utilisateur1'@'%'  IDENTIFIED BY '[mot de passe masqué]';
CREATE USER 'utilisateur1'@'%'  IDENTIFIED BY '[mot de passe masqué]';
CREATE USER 'utilisateur1'@'%'   IDENTIFIED BY '[mot de passe masqué]';

Étape 4 — Attribution du rôle aux administrateurs

GRANT 'admin_role' TO 'utilisateur1'@'%';
GRANT 'admin_role' TO 'utilisateur1'@'%';
GRANT 'admin_role' TO 'utilisateur1'@'%';

Étape 5 — Activation du rôle par défaut

Sans cette étape, les admins devraient exécuter SET ROLE admin_role; à chaque connexion.

SET DEFAULT ROLE 'admin_role' FOR 'utilisateur1'@'%';
SET DEFAULT ROLE 'admin_role' FOR 'utilisateur1'@'%';
SET DEFAULT ROLE 'admin_role' FOR 'utilisateur1'@'%';

Étape 6 — Création de la base de données

Remplacer <NOM_MACHINE> par DBPHARMA, DBMED ou DBMEDOCLAB selon le serveur :

CREATE DATABASE <NOM_MACHINE> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Étape 7 — Attribution des privilèges

Rôle admin — tous les droits

GRANT ALL PRIVILEGES ON <NOM_MACHINE>.* TO 'admin_role';

Utilisateur student — lecture/écriture des données uniquement

student peut lire et modifier les données, mais ne peut pas modifier la structure de la base (pas de CREATE/DROP/ALTER).

GRANT SELECT, INSERT, UPDATE, DELETE ON <NOM_MACHINE>.* TO 'student'@'%';

Étape 8 — Application des changements

FLUSH PRIVILEGES;

4. Scripts complets par serveur

Serveur DBPHARMA

-- ROLE
CREATE ROLE 'admin_role';
-- UTILISATEURS
CREATE USER 'student'@'%'  IDENTIFIED BY '[mot de passe masqué]';
CREATE USER 'utilisateur1'@'%'  IDENTIFIED BY '[mot de passe masqué]';
CREATE USER 'utilisateur1'@'%'  IDENTIFIED BY '[mot de passe masqué]';
CREATE USER 'utilisateur1'@'%'   IDENTIFIED BY '[mot de passe masqué]';
-- ATTRIBUTION DU ROLE
GRANT 'admin_role' TO 'utilisateur1'@'%';
GRANT 'admin_role' TO 'utilisateur1'@'%';
GRANT 'admin_role' TO 'utilisateur1'@'%';
SET DEFAULT ROLE 'admin_role' FOR 'utilisateur1'@'%';
SET DEFAULT ROLE 'admin_role' FOR 'utilisateur1'@'%';
SET DEFAULT ROLE 'admin_role' FOR 'utilisateur1'@'%';
-- BASE DE DONNEES
CREATE DATABASE DBPHARMA CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- PRIVILEGES
GRANT ALL PRIVILEGES ON DBPHARMA.* TO 'admin_role';
GRANT SELECT, INSERT, UPDATE, DELETE ON DBPHARMA.* TO 'student'@'%';
FLUSH PRIVILEGES;

Serveur DBMED

-- ROLE
CREATE ROLE 'admin_role';
-- UTILISATEURS
CREATE USER 'student'@'%'  IDENTIFIED BY '[mot de passe masqué]';
CREATE USER 'utilisateur1'@'%'  IDENTIFIED BY '[mot de passe masqué]';
CREATE USER 'utilisateur1'@'%'  IDENTIFIED BY '[mot de passe masqué]';
CREATE USER 'utilisateur1'@'%'   IDENTIFIED BY '[mot de passe masqué]';
-- ATTRIBUTION DU ROLE
GRANT 'admin_role' TO 'utilisateur1'@'%';
GRANT 'admin_role' TO 'utilisateur1'@'%';
GRANT 'admin_role' TO 'utilisateur1'@'%';
SET DEFAULT ROLE 'admin_role' FOR 'utilisateur1'@'%';
SET DEFAULT ROLE 'admin_role' FOR 'utilisateur1'@'%';
SET DEFAULT ROLE 'admin_role' FOR 'utilisateur1'@'%';
-- BASE DE DONNEES
CREATE DATABASE DBMED CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- PRIVILEGES
GRANT ALL PRIVILEGES ON DBMED.* TO 'admin_role';
GRANT SELECT, INSERT, UPDATE, DELETE ON DBMED.* TO 'student'@'%';
FLUSH PRIVILEGES;

Serveur DBMEDOCLAB

-- ROLE
CREATE ROLE 'admin_role';
-- UTILISATEURS
CREATE USER 'student'@'%'  IDENTIFIED BY '[mot de passe masqué]';
CREATE USER 'utilisateur1'@'%'  IDENTIFIED BY '[mot de passe masqué]';
CREATE USER 'utilisateur1'@'%'  IDENTIFIED BY '[mot de passe masqué]';
CREATE USER 'utilisateur1'@'%'   IDENTIFIED BY '[mot de passe masqué]';
-- ATTRIBUTION DU ROLE
GRANT 'admin_role' TO 'utilisateur1'@'%';
GRANT 'admin_role' TO 'utilisateur1'@'%';
GRANT 'admin_role' TO 'utilisateur1'@'%';
SET DEFAULT ROLE 'admin_role' FOR 'utilisateur1'@'%';
SET DEFAULT ROLE 'admin_role' FOR 'utilisateur1'@'%';
SET DEFAULT ROLE 'admin_role' FOR 'utilisateur1'@'%';
-- BASE DE DONNEES
CREATE DATABASE DBMEDOCLAB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- PRIVILEGES
GRANT ALL PRIVILEGES ON DBMEDOCLAB.* TO 'admin_role';
GRANT SELECT, INSERT, UPDATE, DELETE ON DBMEDOCLAB.* TO 'student'@'%';
FLUSH PRIVILEGES;

5. Configuration réseau

Les utilisateurs étant créés en @’%’, MariaDB doit être configuré pour accepter les connexions distantes.

Modifier la configuration

Fichier : /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
bind-address = 0.0.0.0

Redémarrer et vérifier

systemctl restart mariadb
ss -tlnp | grep 3306

Ouvrir le port pare-feu si UFW est actif

ufw allow 3306/tcp

6. Vérifications

Lister les utilisateurs

SELECT User, Host FROM mysql.user;

Droits d’un utilisateur

SHOW GRANTS FOR 'utilisateur1'@'%';
SHOW GRANTS FOR 'student'@'%';

Droits du rôle

SHOW GRANTS FOR 'admin_role';

Lister les bases

SHOW DATABASES;

Tester la connexion depuis un client distant

mysql -u utilisateur1 -p -h <IP_DU_SERVEUR>
SELECT CURRENT_ROLE;

7. Opérations courantes

Ajouter un nouvel administrateur

CREATE USER 'nouveau'@'%' IDENTIFIED BY '[mot de passe masqué]';
GRANT 'admin_role' TO 'nouveau'@'%';
SET DEFAULT ROLE 'admin_role' FOR 'nouveau'@'%';
FLUSH PRIVILEGES;

Retirer le rôle admin sans supprimer le compte

REVOKE 'admin_role' FROM 'utilisateur1'@'%';

Supprimer un utilisateur

DROP USER 'utilisateur1'@'%';

Changer un mot de passe

ALTER USER 'utilisateur1'@'%' IDENTIFIED BY '<nouveau_mot_de_passe>';