Administration : users, permissions, backup

🎯 Objectif : À la fin de ce chapitre, tu sauras sécuriser une base SQL en production — gestion des utilisateurs, permissions granulaires, sauvegardes et restauration. ⏱️ Durée estimée : 50 minutes | Niveau : Avancé

Pourquoi l’administration SQL est critique

Une base de données en production sans administration, c’est une bombe à retardement. Données accessibles à tout le monde, pas de backup, zéro traçabilité — le genre de setup qui finit en postmortem un vendredi soir.

L’administration SQL couvre trois piliers :

• Qui a accès → gestion des utilisateurs
• Qui peut faire quoi → permissions et rôles
• Comment on survit à un crash → sauvegardes et restauration

💡 Ces concepts s’appliquent à PostgreSQL, MySQL et SQL Server. Les syntaxes varient légèrement, mais la logique reste la même.

Gestion des utilisateurs

Créer et gérer des comptes

Chaque personne ou service qui accède à la base doit avoir son propre compte. Jamais de compte partagé en production.

-- PostgreSQL : créer un utilisateur
CREATE USER app_backend WITH PASSWORD 'S3cur3P@ss!';

-- MySQL : créer un utilisateur
CREATE USER 'app_backend'@'10.0.1.%' IDENTIFIED BY 'S3cur3P@ss!';

-- Modifier un mot de passe
ALTER USER app_backend WITH PASSWORD 'N3wP@ssw0rd!';

-- Supprimer un utilisateur
DROP USER IF EXISTS app_backend;

🔥 En MySQL, le @'10.0.1.%' limite la connexion à un réseau précis. En production, ne jamais utiliser @'%' sauf derrière un VPN.

Les rôles : gérer les permissions à l’échelle

Attribuer des permissions utilisateur par utilisateur, ça ne scale pas. Les rôles regroupent des permissions qu’on assigne ensuite aux utilisateurs.

-- Créer des rôles métier
CREATE ROLE readonly;
CREATE ROLE app_readwrite;
CREATE ROLE dba_admin;

-- Assigner un rôle à un utilisateur
GRANT readonly TO stagiaire_paul;
GRANT app_readwrite TO app_backend;
GRANT dba_admin TO dany;

-- Retirer un rôle
REVOKE app_readwrite FROM ancien_dev;

💡 Pense aux rôles comme des groupes Linux. Tu gères les permissions sur le groupe, pas sur chaque personne.

Permissions : GRANT et REVOKE

Permissions sur les tables

Le principe du moindre privilège s’applique : chaque compte ne reçoit que les droits strictement nécessaires.

-- Lecture seule sur toutes les tables du schéma public
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Lecture + écriture pour l'application
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;

-- Tous les droits pour le DBA
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dba_admin;

-- Révoquer un privilège
REVOKE DELETE ON TABLE customers FROM app_readwrite;

Permissions sur les schémas et bases

-- Autoriser l'usage d'un schéma
GRANT USAGE ON SCHEMA analytics TO readonly;

-- Autoriser la création d'objets
GRANT CREATE ON SCHEMA staging TO app_readwrite;

-- PostgreSQL : permissions par défaut sur les futures tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly;

⚠️ Sans ALTER DEFAULT PRIVILEGES, les nouvelles tables créées ne sont pas automatiquement accessibles aux rôles existants. C’est un piège classique en PostgreSQL.

Vérifier les permissions

-- PostgreSQL : qui a accès à quoi
SELECT grantee, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'public'
ORDER BY grantee, table_name;

-- MySQL : permissions d'un utilisateur
SHOW GRANTS FOR 'app_backend'@'10.0.1.%';

Sauvegardes : le filet de sécurité

Stratégies de backup

Trois approches, souvent combinées :

Backup avec pg_dump et mysqldump

-- PostgreSQL : backup complet en format custom (compressé)
-- pg_dump -Fc -h localhost -U dba_admin -d production > backup_2026-03-30.dump

-- PostgreSQL : restauration
-- pg_restore -h localhost -U dba_admin -d production backup_2026-03-30.dump

-- MySQL : backup complet avec snapshot cohérent
-- mysqldump --single-transaction -u dba_admin -p production > backup.sql

-- MySQL : restauration
-- mysql -u dba_admin -p production < backup.sql

🔥 L’option --single-transaction est essentielle pour les tables InnoDB : elle garantit un snapshot cohérent sans verrouiller les tables.

Automatiser les sauvegardes

Un backup manuel, c’est un backup oublié. Automatise avec cron :

#!/bin/bash
# /opt/scripts/backup-db.sh
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups/postgres"
RETENTION_DAYS=30

pg_dump -Fc -h localhost -U backup_user -d production \
  > "${BACKUP_DIR}/prod_${TIMESTAMP}.dump"

# Supprimer les backups de plus de 30 jours
find "$BACKUP_DIR" -name "*.dump" -mtime +$RETENTION_DAYS -delete

# Vérifier la taille (alerte si trop petit = backup vide)
SIZE=$(stat -c%s "${BACKUP_DIR}/prod_${TIMESTAMP}.dump" 2>/dev/null || echo 0)
if [ "$SIZE" -lt 1000 ]; then
  echo "ALERTE: backup suspect (${SIZE} bytes)" | mail -s "Backup Alert" ops@example.com
fi

🎯 Règle d’or : un backup qui n’a jamais été testé en restauration n’est pas un backup. Planifie des tests de restore mensuels.

Cas entreprise : sécuriser une base e-commerce

Contexte : une startup lance sa plateforme e-commerce. La base PostgreSQL contient clients, commandes et paiements.

Architecture des accès :

-- Rôles métier
CREATE ROLE ro_support;          -- Support client : lecture seule
CREATE ROLE rw_application;      -- Backend API : CRUD
CREATE ROLE admin_dba;           -- DBA : tous droits

-- Permissions support : voir clients et commandes, jamais les paiements
GRANT SELECT ON customers, orders TO ro_support;
-- Pas de GRANT sur la table payments !

-- Permissions application
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO rw_application;
REVOKE DELETE ON payments FROM rw_application;

-- Le DELETE sur payments ne passe que par le DBA
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_dba;

-- Créer les utilisateurs et assigner les rôles
CREATE USER support_marie WITH PASSWORD 'Sup0rt2026!';
GRANT ro_support TO support_marie;

CREATE USER api_prod WITH PASSWORD 'Api_Pr0d_S3cure!';
GRANT rw_application TO api_prod;

💡 Le support ne voit jamais les données de paiement. L’API ne peut pas supprimer de paiements. Seul le DBA peut intervenir sur les données sensibles. C’est le principe du moindre privilège en action.

Les pièges classiques

⚠️ Compte root/postgres exposé — Ne jamais utiliser le superuser pour l’application. Crée un compte dédié avec des droits limités.

⚠️ Backup sans test de restore — 40% des entreprises découvrent que leur backup est corrompu au moment où elles en ont besoin. Teste chaque mois.

⚠️ Oublier ALTER DEFAULT PRIVILEGES — En PostgreSQL, GRANT SELECT ON ALL TABLES ne couvre que les tables existantes. Les futures tables restent inaccessibles.

⚠️ Mots de passe en clair dans les scripts — Utilise des variables d’environnement ou un gestionnaire de secrets (Vault, AWS Secrets Manager). Jamais de mot de passe dans un fichier .sh versionné.

⚠️ GRANT ALL à tout le monde — C’est tentant en dev, mais ça finit toujours par fuiter en prod. Commence restrictif, ouvre au cas par cas.

Résumé

L’administration SQL repose sur trois piliers indissociables :

• Utilisateurs et rôles → CREATE USER, CREATE ROLE, GRANT role TO user
• Permissions granulaires → GRANT / REVOKE sur tables, schémas, bases
• Sauvegardes fiables → pg_dump / mysqldump, automatisées, testées régulièrement

🎯 La sécurité d’une base de données n’est pas un feature — c’est un prérequis. En production, chaque accès doit être justifié, chaque modification traçable, et chaque donnée récupérable.

➡️ La suite : Chapitre suivant