Sécurité et droits

Sécurité et droits#

Hide code cell source

 
import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.patches as mpatches
import numpy as np
import seaborn as sns

sns.set_theme(style="whitegrid", palette="muted", font_scale=1.1)

Le modèle de sécurité SQL#

La sécurité d’une base de données relationnelle repose sur trois concepts fondamentaux : les utilisateurs (ou rôles), les objets (tables, vues, schémas, séquences…) et les privilèges qui définissent quelles actions un utilisateur est autorisé à effectuer sur quels objets.

Définition 98

Le modèle de sécurité SQL est fondé sur le contrôle d’accès discrétionnaire (DAC — Discretionary Access Control) : le propriétaire d’un objet peut accorder et révoquer des droits sur cet objet à d’autres utilisateurs ou rôles. Les privilèges sont accordés via GRANT et révoqués via REVOKE.

Les principaux privilèges SQL sur les tables sont :

Privilège

Signification

SELECT

Lire les données

INSERT

Insérer des lignes

UPDATE

Modifier des lignes

DELETE

Supprimer des lignes

REFERENCES

Créer des clés étrangères vers la table

TRIGGER

Créer des triggers sur la table

ALL PRIVILEGES

Tous les privilèges

Remarque 56

Dans PostgreSQL, un utilisateur est un rôle avec la capacité de se connecter (LOGIN). Un rôle au sens large peut représenter un groupe d’utilisateurs. La gestion des utilisateurs SQLite n’existe pas : SQLite est un fichier local, la sécurité est gérée par le système de fichiers.

GRANT et REVOKE#

Accorder des privilèges#

Définition 99

GRANT accorde des privilèges à un utilisateur ou un rôle. La syntaxe générale est :

GRANT privilege [, ...]
ON   objet
TO   utilisateur [, ...] [WITH GRANT OPTION];

L’option WITH GRANT OPTION permet au bénéficiaire de transmettre à son tour le privilège à d’autres.

Exemples illustratifs (PostgreSQL) :

-- Autoriser alice à lire la table employes
GRANT SELECT ON employes TO alice;

-- Autoriser bob à lire et modifier
GRANT SELECT, UPDATE ON employes TO bob;

-- Accorder tous les droits sur un schéma
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_role;

-- Autoriser alice à transmettre le droit de lecture
GRANT SELECT ON commandes TO alice WITH GRANT OPTION;

Restreindre à certaines colonnes#

Remarque 57

Il est possible d’accorder des privilèges colonne par colonne, ce qui affine le contrôle d’accès :

-- alice ne peut lire que nom et prenom, pas le salaire
GRANT SELECT (nom, prenom) ON employes TO alice;

-- bob peut mettre à jour uniquement le statut
GRANT UPDATE (statut) ON commandes TO bob;

Cette granularité est utile pour respecter le principe de moindre privilège (least privilege).

Révoquer des privilèges#

Définition 100

REVOKE retire des privilèges précédemment accordés. Si le privilège avait été accordé avec WITH GRANT OPTION, on peut aussi révoquer la capacité de transmission avec CASCADE :

REVOKE SELECT ON employes FROM alice;
REVOKE SELECT ON commandes FROM alice CASCADE;

Rôles : CREATE ROLE et héritage#

Les rôles permettent de gérer des groupes de permissions. Au lieu d’attribuer les mêmes droits à dix utilisateurs un par un, on crée un rôle, on lui donne les droits, et on attribue le rôle aux utilisateurs.

Définition 101

Un rôle PostgreSQL est un objet de la base qui peut recevoir des privilèges et être attribué à d’autres rôles ou utilisateurs. La hiérarchie des rôles permet un héritage des privilèges : si alice possède le rôle lecteur, elle hérite de tous les privilèges accordés à lecteur.

Les blocs suivants supposent une connexion à un serveur PostgreSQL.

-- Créer un rôle sans droit de connexion (groupe)
CREATE ROLE lecteur;
CREATE ROLE editeur;
CREATE ROLE admin_bdd;

-- Accorder des droits au rôle
GRANT SELECT ON ALL TABLES IN SCHEMA public TO lecteur;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO editeur;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_bdd;

-- Créer des utilisateurs et leur attribuer des rôles
CREATE USER alice WITH LOGIN PASSWORD 'motdepasse_alice';
CREATE USER bob   WITH LOGIN PASSWORD 'motdepasse_bob';

GRANT lecteur TO alice;
GRANT editeur TO bob;

-- Héritage : alice hérite des SELECT de lecteur
-- bob hérite des SELECT, INSERT, UPDATE de editeur

Remarque 58

Par défaut dans PostgreSQL, un rôle hérite des privilèges des rôles qui lui sont attribués. On peut désactiver cet héritage avec NOINHERIT lors de la création du rôle. Dans ce cas, l’utilisateur doit explicitement SET ROLE lecteur; pour activer les droits du rôle parent.

Row-Level Security (RLS)#

La sécurité au niveau ligne (Row-Level Security, RLS) permet de restreindre les lignes qu’un utilisateur peut voir ou modifier, selon son identité.

Définition 102

La sécurité au niveau ligne (RLS, Row-Level Security) est une fonctionnalité PostgreSQL qui permet d’attacher des politiques à une table. Chaque politique définit une condition (USING) qui filtre les lignes visibles, et optionnellement une condition WITH CHECK pour les écritures. Tant que RLS n’est pas activé sur une table, les politiques n’ont aucun effet.

Exemple illustratif (PostgreSQL — requiert un serveur PostgreSQL) :

-- Activer RLS sur la table commandes
ALTER TABLE commandes ENABLE ROW LEVEL SECURITY;

-- Chaque utilisateur ne voit que ses propres commandes
CREATE POLICY politique_commandes_utilisateur
ON commandes
FOR ALL
TO PUBLIC
USING (client = current_user);

-- Les administrateurs voient tout (politique distincte)
CREATE POLICY politique_admin
ON commandes
FOR ALL
TO admin_bdd
USING (TRUE);

Remarque 59

Le propriétaire de la table contourne toujours les politiques RLS par défaut. Pour soumettre aussi le propriétaire aux politiques, on utilise ALTER TABLE ... FORCE ROW LEVEL SECURITY. Les superutilisateurs contournent toujours RLS.

Injection SQL : mécanisme et prévention#

L’injection SQL est l’une des vulnérabilités les plus répandues et les plus dangereuses dans les applications web.

Définition 103

Une injection SQL survient lorsqu’une application construit dynamiquement une requête SQL en concaténant des données provenant de l’utilisateur sans les valider ni les échapper. Un attaquant peut alors injecter du code SQL arbitraire dans la requête, modifiant son comportement : extraction de données confidentielles, contournement de l’authentification, modification ou destruction de données.

Démonstration d’une injection#

# Mise en place d'une petite base d'exemple
conn_demo = sqlite3.connect(":memory:")
cur_demo = conn_demo.cursor()
cur_demo.executescript("""
CREATE TABLE utilisateurs (
    id       INTEGER PRIMARY KEY,
    login    TEXT UNIQUE NOT NULL,
    mdp_hash TEXT NOT NULL,
    role     TEXT DEFAULT 'user'
);
INSERT INTO utilisateurs VALUES
    (1, 'alice', 'hachage_alice', 'user'),
    (2, 'bob',   'hachage_bob',   'user'),
    (3, 'admin', 'hachage_admin', 'admin');
""")
conn_demo.commit()

# CODE DANGEREUX — NE JAMAIS FAIRE EN PRODUCTION
# Simulation d'une fonction de connexion vulnérable
def connexion_vulnerable(login_saisi, mdp_saisi):
    """Construit la requête par concaténation — DANGEREUX."""
    requete = (
        f"SELECT id, login, role FROM utilisateurs "
        f"WHERE login = '{login_saisi}' AND mdp_hash = '{mdp_saisi}'"
    )
    print(f"  Requête générée : {requete}")
    rows = cur_demo.execute(requete).fetchall()
    return rows

print("=== Connexion normale ===")
print(connexion_vulnerable("alice", "hachage_alice"))

print("\n=== Injection SQL — bypass d'authentification ===")
# L'attaquant saisit : ' OR '1'='1' --
# La requête devient : WHERE login = '' OR '1'='1' -- ' AND ...
# Le commentaire -- annule la vérification du mot de passe
resultat = connexion_vulnerable("' OR '1'='1' --", "peu_importe")
print(f"  Lignes retournées : {resultat}")

=== Connexion normale ===
  Requête générée : SELECT id, login, role FROM utilisateurs WHERE login = 'alice' AND mdp_hash = 'hachage_alice'
[(1, 'alice', 'user')]

=== Injection SQL — bypass d'authentification ===
  Requête générée : SELECT id, login, role FROM utilisateurs WHERE login = '' OR '1'='1' --' AND mdp_hash = 'peu_importe'
  Lignes retournées : [(1, 'alice', 'user'), (2, 'bob', 'user'), (3, 'admin', 'admin')]

Remarque 60

Dans l’exemple ci-dessus, l’attaquant accède à la liste complète des utilisateurs sans connaître aucun mot de passe. Des variantes plus destructrices permettent de :

  • Exfiltrer des données avec UNION SELECT sur d’autres tables.

  • Modifier des données avec des requêtes UPDATE ou DELETE empilées.

  • Lister le schéma avec sqlite_master (SQLite) ou information_schema (autres SGBD).

Requêtes paramétrées : la solution#

Définition 104

Une requête paramétrée (parameterized query ou prepared statement) sépare la structure SQL des données. Les valeurs sont passées comme paramètres distincts — jamais interpolées dans la chaîne SQL. Le moteur SQL traite les paramètres comme des données littérales, pas comme du code ; l’injection devient structurellement impossible.

# BONNE PRATIQUE — requête paramétrée avec sqlite3
def connexion_securisee(login_saisi, mdp_saisi):
    """Utilise des marqueurs ? — les données ne sont jamais du code SQL."""
    requete = (
        "SELECT id, login, role FROM utilisateurs "
        "WHERE login = ? AND mdp_hash = ?"
    )
    rows = cur_demo.execute(requete, (login_saisi, mdp_saisi)).fetchall()
    return rows

print("=== Connexion normale ===")
print(connexion_securisee("alice", "hachage_alice"))

print("\n=== Tentative d'injection avec requête paramétrée ===")
# La chaîne malveillante est traitée comme une valeur littérale
resultat = connexion_securisee("' OR '1'='1' --", "peu_importe")
print(f"  Lignes retournées : {resultat}")
print("  (Résultat vide : l'injection a échoué)")

=== Connexion normale ===
[(1, 'alice', 'user')]

=== Tentative d'injection avec requête paramétrée ===
  Lignes retournées : []
  (Résultat vide : l'injection a échoué)

Remarque 61

Les ORM (SQLAlchemy, Django ORM, Hibernate) utilisent systématiquement des requêtes paramétrées. Ils éliminent ce risque pour les opérations CRUD classiques. En revanche, les requêtes construites manuellement avec du SQL brut dans un ORM peuvent réintroduire la vulnérabilité si elles concatènent des données utilisateur.

Trigger d’audit complet#

# Reprise de la démonstration avec un audit complet
conn_audit = sqlite3.connect(":memory:")
cur_audit = conn_audit.cursor()

cur_audit.executescript("""
CREATE TABLE comptes (
    id      INTEGER PRIMARY KEY,
    login   TEXT NOT NULL,
    solde   REAL DEFAULT 0
);

CREATE TABLE audit_acces (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    table_cible TEXT,
    operation   TEXT,
    login_modif TEXT,
    detail      TEXT,
    ts          TEXT DEFAULT (datetime('now'))
);

INSERT INTO comptes VALUES (1, 'alice', 1500.00);
INSERT INTO comptes VALUES (2, 'bob',   800.00);

-- Trigger d'audit sur UPDATE
CREATE TRIGGER audit_update_comptes
AFTER UPDATE ON comptes
FOR EACH ROW
BEGIN
    INSERT INTO audit_acces (table_cible, operation, login_modif, detail)
    VALUES (
        'comptes',
        'UPDATE',
        OLD.login,
        'solde : ' || OLD.solde || ' -> ' || NEW.solde
    );
END;

-- Trigger d'audit sur DELETE
CREATE TRIGGER audit_delete_comptes
AFTER DELETE ON comptes
FOR EACH ROW
BEGIN
    INSERT INTO audit_acces (table_cible, operation, login_modif, detail)
    VALUES (
        'comptes',
        'DELETE',
        OLD.login,
        'compte supprimé (solde=' || OLD.solde || ')'
    );
END;
""")
conn_audit.commit()

# Opérations
cur_audit.execute("UPDATE comptes SET solde = 2000 WHERE login = 'alice'")
cur_audit.execute("UPDATE comptes SET solde =  600 WHERE login = 'bob'")
cur_audit.execute("DELETE FROM comptes WHERE login = 'bob'")
conn_audit.commit()

print("=== Journal d'audit ===")
display(pd.read_sql("SELECT * FROM audit_acces", conn_audit))

=== Journal d'audit ===
id table_cible operation login_modif detail ts
0 1 comptes UPDATE alice solde : 1500.0 -> 2000.0 2026-03-31 22:47:13
1 2 comptes UPDATE bob solde : 800.0 -> 600.0 2026-03-31 22:47:13
2 3 comptes DELETE bob compte supprimé (solde=600.0) 2026-03-31 22:47:13

Chiffrement#

Remarque 62

Le chiffrement dans les bases de données s’applique à plusieurs niveaux :

  • Chiffrement au repos (at rest) : les fichiers de la base de données sont chiffrés sur le disque. PostgreSQL délègue cela au système de fichiers (LUKS, FileVault) ou à des extensions commerciales (Transparent Data Encryption dans PostgreSQL Enterprise). SQLite dispose de SQLCipher.

  • Chiffrement en transit (in transit) : les communications entre l’application et le serveur de base de données sont chiffrées via TLS/SSL. Dans PostgreSQL : ssl = on dans postgresql.conf ; la connexion utilise sslmode=require côté client.

  • Chiffrement de colonnes : certaines colonnes sensibles (numéros de carte bancaire, données de santé) sont chiffrées au niveau applicatif avant insertion. La base ne stocke et ne voit que le chiffré.

Exemple 37

Le chiffrement au niveau colonne en Python avec cryptography :

from cryptography.fernet import Fernet
import sqlite3

cle = Fernet.generate_key()
f   = Fernet(cle)

conn = sqlite3.connect("ma_base.db")
conn.execute("CREATE TABLE patients (id INTEGER PRIMARY KEY, num_secu TEXT)")

num_secu = "1 85 12 75 056 147 80"
chiffre  = f.encrypt(num_secu.encode()).decode()
conn.execute("INSERT INTO patients VALUES (1, ?)", (chiffre,))

# Lecture et déchiffrement
row = conn.execute("SELECT num_secu FROM patients WHERE id=1").fetchone()
original = f.decrypt(row[0].encode()).decode()
print(original)  # "1 85 12 75 056 147 80"

La base ne contient que le texte chiffré ; sans la clé, les données sont inaccessibles.

Audit : logs d’accès#

Remarque 63

Dans PostgreSQL, le module pgaudit permet de journaliser précisément les requêtes SQL exécutées. On peut configurer l’audit par objet, par utilisateur ou par type d’opération. Les logs sont écrits dans les journaux PostgreSQL (pg_log) et peuvent être envoyés vers un SIEM.

Configuration minimale dans postgresql.conf :

shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write, ddl'        -- journalise écritures et DDL
pgaudit.log_relation = on         -- précise la table concernée

SQLite ne dispose pas de gestion native des logs d’accès. L’audit est entièrement à implémenter via des triggers (comme démontré dans ce chapitre) ou au niveau applicatif.

Visualisation : hiérarchie des privilèges#

Hide code cell source

 
fig, ax = plt.subplots(figsize=(13, 8))
ax.set_xlim(0, 13)
ax.set_ylim(0, 9)
ax.axis('off')
ax.set_title("Hiérarchie des privilèges et rôles (modèle PostgreSQL)",
             fontsize=14, fontweight='bold', pad=12)

palette = sns.color_palette("muted", 6)
c_root   = palette[3]   # rouge — superuser
c_admin  = palette[0]   # bleu — admin
c_role   = palette[4]   # orange — rôles
c_user   = palette[2]   # vert — utilisateurs
c_obj    = '#7f8c8d'    # gris — objets

def box(ax, x, y, w, h, color, text, fontsize=9.5):
    rect = mpatches.FancyBboxPatch(
        (x - w/2, y - h/2), w, h,
        boxstyle="round,pad=0.12", linewidth=1.5,
        edgecolor='white', facecolor=color, alpha=0.88, zorder=3
    )
    ax.add_patch(rect)
    ax.text(x, y, text, ha='center', va='center', fontsize=fontsize,
            color='white', fontweight='bold', zorder=4, multialignment='center')

def fleche(ax, x1, y1, x2, y2, label='', ls='-', color='#555'):
    ax.annotate('', xy=(x2, y2), xytext=(x1, y1),
                arrowprops=dict(arrowstyle='->', color=color, lw=1.8,
                                linestyle=ls), zorder=2)
    if label:
        ax.text((x1+x2)/2 + 0.1, (y1+y2)/2, label, fontsize=8,
                color=color, va='center')

# Niveau 1 : superuser
box(ax, 6.5, 8.2, 3.0, 0.8, c_root, "superuser\n(contourne tout)")

# Niveau 2 : admin rôle
box(ax, 6.5, 6.8, 2.8, 0.8, c_admin, "admin_bdd\n(CREATEROLE, CREATEDB)")

# Niveau 3 : rôles fonctionnels
box(ax, 2.5, 5.3, 2.5, 0.8, c_role, "lecteur\nSELECT")
box(ax, 6.5, 5.3, 2.5, 0.8, c_role, "editeur\nSELECT, INSERT\nUPDATE")
box(ax,10.5, 5.3, 2.5, 0.8, c_role, "auditeur\nSELECT (audit_*)")

# Niveau 4 : utilisateurs
box(ax, 1.5, 3.5, 2.0, 0.8, c_user, "alice")
box(ax, 4.0, 3.5, 2.0, 0.8, c_user, "charles")
box(ax, 6.5, 3.5, 2.0, 0.8, c_user, "bob")
box(ax, 9.0, 3.5, 2.0, 0.8, c_user, "diana")
box(ax,11.5, 3.5, 2.0, 0.8, c_user, "eve")

# Niveau 5 : objets
box(ax, 2.5, 1.8, 2.5, 0.8, c_obj, "employes\ncommandes")
box(ax, 6.5, 1.8, 2.5, 0.8, c_obj, "factures\nclients")
box(ax,10.5, 1.8, 2.5, 0.8, c_obj, "audit_acces\naudit_commandes")

# Flèches hiérarchie
fleche(ax, 6.5, 7.8, 6.5, 7.2)
fleche(ax, 6.5, 6.4, 2.5, 5.7, "crée/accorde")
fleche(ax, 6.5, 6.4, 6.5, 5.7)
fleche(ax, 6.5, 6.4,10.5, 5.7)

# Utilisateurs → rôles (pointillés = appartient à)
for ux, rx in [(1.5, 2.5), (4.0, 2.5)]:
    fleche(ax, ux, 3.9, rx, 4.9, ls='dashed', color=palette[2])
for ux, rx in [(6.5, 6.5), (9.0, 6.5)]:
    fleche(ax, ux, 3.9, rx, 4.9, ls='dashed', color=palette[2])
fleche(ax, 11.5, 3.9, 10.5, 4.9, ls='dashed', color=palette[2])

# Rôles → objets (GRANT)
fleche(ax, 2.5, 4.9, 2.5, 2.2, color=palette[4])
fleche(ax, 6.5, 4.9, 6.5, 2.2, color=palette[4])
fleche(ax,10.5, 4.9,10.5, 2.2, color=palette[4])

# Légende
patches = [
    mpatches.Patch(color=c_root,  label='Superutilisateur'),
    mpatches.Patch(color=c_admin, label='Rôle administrateur'),
    mpatches.Patch(color=c_role,  label='Rôles fonctionnels'),
    mpatches.Patch(color=c_user,  label='Utilisateurs'),
    mpatches.Patch(color=c_obj,   label='Objets (tables)'),
]
ax.legend(handles=patches, loc='lower left', fontsize=9, framealpha=0.85)
plt.savefig("_build_securite_hierarchie.png", dpi=120, bbox_inches='tight')
plt.show()
_images/d21f67fc7a74d090054c287cb77d8f927b772679bbe73435852d872eb5755903.png

Résumé#

Remarque 64

Ce chapitre a couvert les mécanismes essentiels de sécurité SQL :

Contrôle d’accès :

  • GRANT accorde des privilèges sur des objets ; REVOKE les retire.

  • Les rôles permettent de gérer des groupes de permissions réutilisables.

  • Les privilèges peuvent être définis au niveau table, colonne ou schéma.

  • WITH GRANT OPTION permet la délégation de privilèges.

Sécurité avancée (PostgreSQL) :

  • La Row-Level Security (RLS) filtre les lignes accessibles selon la politique et l’identité de l’utilisateur.

  • Les politiques USING (lecture) et WITH CHECK (écriture) définissent les conditions d’accès.

Injection SQL :

  • L’injection survient quand des données utilisateur sont concaténées dans une requête SQL.

  • La parade universelle est la requête paramétrée : les données ne sont jamais du code.

  • Les ORM protègent nativement contre ce risque pour les opérations standard.

Chiffrement et audit :

  • Chiffrement at rest (fichiers), in transit (TLS) et de colonnes (niveau applicatif).

  • Les triggers d’audit tracent les modifications sensibles dans une table de log.

  • PostgreSQL propose pgaudit pour un audit fin des requêtes.

conn_demo.close()
conn_audit.close()
Contenu