Sécurité BD, M2/SR/IAT
SÉCURITÉ
DES BASES DE DONNÉES
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
Objectifs du cours
Comprendre les principes de sécurité des bases de données.
Maîtriser la gestion des utilisateurs et des permissions.
Appréhender les mécanismes de contrôle des transactions.
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
Chapitre 1 : Introduction à la sécurité des bases de données
La sécurité des bases de données est un enjeu majeur pour toute organisation. En identifiant les
menaces et en mettant en place des mesures de protection, il est possible de garantir l’intégrité
et la confidentialité des données. Ce chapitre introductif pose les bases pour approfondir les
mécanismes spécifiques de gestion des droits d’accès et d’audit.
1.1 Importance de la sécurité des données
Objectifs :
Expliquer pourquoi la sécurité est essentielle dans le contexte des bases de données.
Identifier les risques associés à la négligence des pratiques de sécurité.
Contenu :
1. Protéger les informations sensibles :
o La sécurité des bases de données vise à garantir la confidentialité, l'intégrité
et la disponibilité des données.
o Exemples d'informations sensibles :
Données personnelles : noms, adresses, numéros de téléphone.
Informations financières : détails des paiements, cartes de crédit.
Propriété intellectuelle : brevets, designs.
o Conséquences d’une faille :
Perte de données critiques.
Atteinte à la réputation.
Sanctions légales ou financières.
2. Assurer la conformité réglementaire :
o Les entreprises doivent se conformer aux normes locales et internationales.
o Exemples de régulations :
RGPD (Europe) : Réglemente le traitement des données personnelles.
HIPAA (USA) : Protège les données de santé des patients.
PCI DSS : Définit des normes pour la sécurité des paiements
électroniques.
o Impact en cas de non-conformité :
Amendes sévères.
Perte de la confiance des clients.
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
3. Prévenir les cyberattaques :
o Les bases de données sont des cibles privilégiées pour les attaquants.
o Types d'attaques fréquentes :
Piratage par force brute.
Espionnage des données (écoute clandestine).
Ransomwares bloquant l’accès aux systèmes.
1.2 Menaces courantes et vulnérabilités des bases de données
Objectifs :
Identifier les menaces les plus fréquentes pour les bases de données.
Comprendre les vulnérabilités exploitées par les attaquants.
Contenu :
1. Accès non autorisés :
o Problème :
Utilisation de mots de passe faibles ou partagés.
Absence de contrôle strict sur les droits d'accès.
o Exemple :
Un employé accède à des données confidentielles sans autorisation,
entraînant des fuites.
2. Injection SQL :
o Technique :
Exploitation des champs de saisie utilisateur pour injecter des
commandes SQL malveillantes.
o Exemple d'injection :
SELECT * FROM utilisateurs WHERE nom_utilisateur =
'admin' OR '1'='1';
o Conséquences :
Accès illimité aux données.
Corruption ou suppression des informations.
3. Failles de configuration :
o Mauvaise configuration des privilèges ou services.
o Exemple :
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
Utilisateurs bénéficiant de privilèges DBA non nécessaires.
o Solutions :
Configurer les paramètres par défaut de manière sécurisée.
Vérifier régulièrement les autorisations.
4. Absence de chiffrement :
o Données sensibles stockées en texte clair.
o Impact :
Les pirates peuvent lire directement les informations volées.
o Solution :
Chiffrer les données sensibles au repos et en transit.
Exemple d'utilisation de chiffrement en Oracle :
ALTER TABLE clients MODIFY (num_carte_credit
ENCRYPT);
5. Logiciels non mis à jour :
o Les bases de données obsolètes peuvent comporter des failles de sécurité.
o Importance des mises à jour régulières :
Réduction des vulnérabilités.
Protection contre les nouvelles menaces.
1.3 Mesures de protection de base
Objectifs :
Présenter des solutions pratiques pour sécuriser les bases de données.
Mettre en œuvre des bonnes pratiques de sécurité.
Contenu :
1. Authentification forte :
o Imposer des mots de passe robustes.
o Utiliser des mécanismes d’authentification multi-facteurs (MFA).
2. Gestion des privilèges :
o Appliquer le principe du moindre privilège :
Accorder uniquement les droits nécessaires à chaque utilisateur.
o Exemple :
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
GRANT SELECT ON clients TO analyste;
REVOKE UPDATE ON clients FROM analyste;
3. Journalisation et audits :
o Activer l’audit des activités utilisateur.
o Exemple en Oracle :
AUDIT INSERT, UPDATE, DELETE ON commandes BY ACCESS;
4. Sécurisation des données :
o Utiliser le chiffrement pour protéger les données sensibles.
o Sauvegarder régulièrement les données.
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
Chapitre 2 : Gestion des utilisateurs
2.1 Création et gestion des comptes utilisateurs
Objectif :
Apprendre à gérer le cycle de vie des utilisateurs, de leur création à leur suppression,
avec une attention particulière à la sécurité.
2.1.1 Création d’un utilisateur
Syntaxe de base :
CREATE USER utilisateur IDENTIFIED BY mot_de_passe;
Exemples pratiques :
o Création d'un utilisateur simple :
CREATE USER alice IDENTIFIED BY Password123!;
o Création avec un espace de tables par défaut et temporaire :
CREATE USER bob IDENTIFIED BY Secure#2025
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
2.1.2 Modification d’un utilisateur
Modifier les paramètres d'un compte utilisateur :
ALTER USER utilisateur IDENTIFIED BY nouveau_mot_de_passe;
Scénario pratique :
o Alice doit mettre à jour son mot de passe :
ALTER USER alice IDENTIFIED BY NewSecure#2025;
2.1.3 Suppression d’un utilisateur
Supprimer un compte utilisateur :
DROP USER utilisateur [CASCADE];
o L'option CASCADE supprime les objets appartenant à l'utilisateur.
Exemple :
o Suppression sécurisée d'Alice après avoir vérifié son inutilité :
DROP USER alice CASCADE;
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
2.2 Politiques de mots de passe
Objectif :
Renforcer la sécurité des comptes utilisateurs grâce à des politiques robustes.
2.2.1 Définition de règles de mot de passe
Activer une fonction de vérification de mot de passe :
CREATE OR REPLACE FUNCTION verify_password
RETURN BOOLEAN IS
BEGIN
-- Logique de vérification personnalisée
END;
Associer cette fonction au profil par défaut :
ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_password;
2.2.2 Expiration et renouvellement des mots de passe
Forcer un renouvellement régulier :
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 90;
Empêcher la réutilisation des mots de passe récents :
ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 5;
2.3 Rôles et profils
Objectif :
Simplifier la gestion des permissions et des ressources par l’utilisation de rôles et de
profils.
2.3.1 Gestion des rôles
Création d’un rôle :
CREATE ROLE analyst_role;
Attribution de privilèges à un rôle :
GRANT SELECT, UPDATE ON ventes TO analyst_role;
Assignation du rôle à un utilisateur :
GRANT analyst_role TO bob;
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
2.3.2 Gestion des profils
Création d’un profil limitant les ressources :
CREATE PROFILE dev_profile LIMIT
SESSIONS_PER_USER 3
CONNECT_TIME 120;
Attribution d’un profil à un utilisateur :
ALTER USER bob PROFILE dev_profile;
2.3.3 Automatisation avec des scripts
Création d’un script pour configurer rapidement des utilisateurs :
BEGIN
EXECUTE IMMEDIATE 'CREATE USER dev_team IDENTIFIED BY Team#2025';
EXECUTE IMMEDIATE 'GRANT analyst_role TO dev_team';
EXECUTE IMMEDIATE 'ALTER USER dev_team PROFILE dev_profile';
END;
Recommandations pratiques
1. Auditez régulièrement les comptes utilisateurs.
o Utilisez la vue DBA_USERS pour identifier les utilisateurs inactifs ou expirés :
SELECT username, account_status, lock_date FROM dba_users;
2. Testez vos politiques de sécurité.
o Simulez des scénarios pour vérifier la robustesse des politiques de mots de
passe et des rôles.
3. Documentez les privilèges.
o Maintenez une liste des rôles et permissions accordés pour éviter les privilèges
excessifs.
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
Travail Pratique : Gestion des utilisateurs dans Oracle
Objectifs du TP :
1. Créer, modifier, et supprimer des utilisateurs dans une base de données Oracle.
2. Configurer des rôles et profils pour gérer les privilèges et les ressources.
3. Appliquer des politiques de mots de passe pour renforcer la sécurité.
4. Tester et auditer la configuration des utilisateurs.
Prérequis :
Un environnement Oracle Database configuré (Oracle Database Express Edition suffit).
Outils pour interagir avec la base de données (SQL*Plus, SQL Developer, ou Oracle APEX).
Étape 1 : Création d'utilisateurs
Exercice 1.1 : Créer des comptes utilisateurs
1. Créez deux utilisateurs avec des mots de passe respectant une politique de sécurité
(minimum 8 caractères, contenant des majuscules, des chiffres et des caractères spéciaux) :
o user_dev avec le mot de passe Dev@2025.
o user_test avec le mot de passe Test123!.
Commande :
CREATE USER user_dev IDENTIFIED BY Dev@2025;
CREATE USER user_test IDENTIFIED BY Test123!;
2. Configurez un espace de tables par défaut pour user_dev :
CREATE USER user_dev IDENTIFIED BY Dev@2025
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
Exercice 1.2 : Vérifiez les utilisateurs créés
Utilisez la vue système pour lister les utilisateurs existants :
SELECT username, account_status FROM dba_users;
Étape 2 : Gestion des mots de passe
Exercice 2.1 : Modifier les mots de passe
1. Modifiez le mot de passe de user_dev en NewDev#2025 :
ALTER USER user_dev IDENTIFIED BY NewDev#2025;
2. Essayez de définir un mot de passe non conforme (par exemple, 123456) et observez l'erreur.
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
Exercice 2.2 : Expiration et renouvellement
1. Configurez une politique pour expirer les mots de passe après 30 jours :
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 30;
2. Testez l’expiration en simulant une tentative de connexion après expiration.
Étape 3 : Configuration des rôles
Exercice 3.1 : Créer et attribuer un rôle
1. Créez un rôle nommé role_dev avec les privilèges suivants :
o Lecture et écriture sur une table projects.
CREATE ROLE role_dev;
GRANT SELECT, INSERT ON projects TO role_dev;
2. Attribuez le rôle role_dev à user_dev :
GRANT role_dev TO user_dev;
Exercice 3.2 : Vérifiez les privilèges
1. Connectez-vous en tant que user_dev et testez les privilèges sur la table projects :
CONNECT user_dev/Dev@2025;
SELECT * FROM projects; -- Lecture
INSERT INTO projects (id, name) VALUES (1, 'New Project'); -- Écriture
Étape 4 : Configuration des profils
Exercice 4.1 : Créer un profil
1. Créez un profil profile_dev avec les limites suivantes :
o Nombre maximum de sessions simultanées : 3.
o Temps de connexion maximum : 120 minutes.
CREATE PROFILE profile_dev LIMIT
SESSIONS_PER_USER 3
CONNECT_TIME 120;
2. Attribuez le profil à user_test :
ALTER USER user_test PROFILE profile_dev;
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
Exercice 4.2 : Testez les limites
1. Connectez-vous plusieurs fois en tant que user_test et observez l'effet de la limite sur le
nombre de sessions.
Étape 5 : Audits et nettoyage
Exercice 5.1 : Auditez les utilisateurs
1. Listez tous les utilisateurs et leurs statuts :
SELECT username, account_status, profile FROM dba_users;
2. Vérifiez les privilèges accordés à un utilisateur spécifique (user_dev) :
SELECT * FROM dba_role_privs WHERE grantee = 'USER_DEV';
SELECT * FROM dba_tab_privs WHERE grantee = 'USER_DEV';
Exercice 5.2 : Nettoyez les utilisateurs
1. Supprimez les utilisateurs créés (user_dev et user_test) avec leurs objets associés :
DROP USER user_dev CASCADE;
DROP USER user_test CASCADE;
Résultats attendus :
À la fin de ce TP, vous aurez :
Créé et géré des utilisateurs.
Appliqué des politiques de mots de passe.
Configuré et testé des rôles et profils.
Réalisé un audit des utilisateurs et nettoyé la base.
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
Chapitre 3 : Gestion des droits d’accès dans Oracle
3.1 Introduction à la gestion des droits d’accès
La gestion des droits d’accès est essentielle pour garantir la sécurité et le bon fonctionnement d’une
base de données. Elle permet de contrôler qui peut accéder aux données et quelles actions peuvent
être effectuées.
Principes fondamentaux :
1. Droits d’objet : Contrôlent les actions sur des objets spécifiques (tables, vues, procédures,
etc.).
2. Droits système : Concernent les actions globales liées au système de base de données.
3. Rôles : Facilitent la gestion des autorisations en regroupant plusieurs privilèges.
4. Audits : Permettent de suivre et d’enregistrer les activités des utilisateurs pour des raisons
de traçabilité.
3.2 Droits d’objet
Qu’est-ce que les droits d’objet ?
Les droits d’objet sont des privilèges qui permettent d’effectuer des actions spécifiques sur des
objets de la base de données, tels que des tables, des vues ou des procédures.
Exemples de droits d’objet :
SELECT : Lire les données d'une table ou d'une vue.
INSERT : Ajouter de nouvelles données dans une table.
UPDATE : Modifier des données existantes.
DELETE : Supprimer des données d'une table.
EXECUTE : Exécuter une procédure stockée.
Syntaxe :
Pour accorder un droit d’objet :
GRANT <privilège> ON <objet> TO <utilisateur|rôle>;
Pour révoquer un droit d’objet :
REVOKE <privilège> ON <objet> FROM <utilisateur|rôle>;
Exemple pratique :
GRANT SELECT, INSERT ON commandes TO user1;
REVOKE INSERT ON commandes FROM user1;
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
3.3 Droits système
Qu’est-ce que les droits systèmes ?
Les droits systèmes permettent d’exécuter des actions au niveau global du système de base de
données, comme créer des utilisateurs, des tables ou des vues.
Exemples de droits système :
CREATE SESSION : Permet de se connecter à la base de données.
CREATE TABLE : Autorise la création de nouvelles tables.
CREATE VIEW : Permet de créer des vues.
ALTER SYSTEM : Autorise la modification des paramètres système.
Syntaxe :
Pour accorder un droit système :
GRANT <privilège> TO <utilisateur|rôle>;
Pour révoquer un droit système :
REVOKE <privilège> FROM <utilisateur|rôle>;
Exemple pratique :
GRANT CREATE SESSION, CREATE TABLE TO user1;
REVOKE CREATE TABLE FROM user1;
3.4 Rôles
Qu’est-ce qu’un rôle ?
Un rôle est un groupe de privilèges qui peut être attribué à un ou plusieurs utilisateurs. Il permet de
simplifier la gestion des autorisations, surtout dans les environnements avec de nombreux
utilisateurs.
Créer un rôle :
CREATE ROLE <nom_du_rôle>;
Attribuer des privilèges à un rôle :
GRANT <privilège> ON <objet> TO <rôle>;
Attribuer un rôle à un utilisateur :
GRANT <rôle> TO <utilisateur>;
Exemple pratique :
CREATE ROLE sales_team;
GRANT SELECT, INSERT ON commandes TO sales_team;
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
GRANT sales_team TO user1;
3.5 Audits et traçabilité
Pourquoi les audits sont-ils nécessaires ?
Les audits permettent de surveiller les actions des utilisateurs et de garantir la traçabilité des accès.
Cela est crucial pour détecter les activités malveillantes et s’assurer de la conformité avec les
politiques de sécurité.
Configurer un audit :
Pour surveiller des actions spécifiques sur des objets :
AUDIT <action> ON <objet> BY <utilisateur>;
Pour surveiller toutes les actions d’un utilisateur :
AUDIT ALL BY <utilisateur>;
Analyser les journaux d’audit :
Les journaux d’audit contiennent des informations sur les actions effectuées dans la base de données
:
Utilisateur.
Type d’action (SELECT, INSERT, etc.).
Heure et date.
Objet concerné.
Exemple pratique :
AUDIT SELECT, INSERT ON commandes BY user1;
SELECT username, action_name, timestamp
FROM dba_audit_trail
WHERE username = 'USER1';
3.6 Meilleures pratiques
1. Appliquer le principe du moindre privilège : n'accorder que les droits nécessaires pour
accomplir les tâches.
2. Utiliser des rôles pour regrouper les autorisations similaires.
3. Surveiller régulièrement les journaux d’audit pour détecter les anomalies.
4. Révoquer les privilèges non utilisés pour réduire les risques de sécurité.
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
Résumé
Type de privilège Exemple Commande SQL
Droits d’objet SELECT sur table GRANT SELECT ON commandes
TO user1;
Droits système CREATE TABLE GRANT CREATE TABLE TO
user1;
Rôle Groupe de droits GRANT SELECT ON commandes
TO sales_team;
Audit Suivi des actions AUDIT SELECT, INSERT ON
commandes BY user1;
Travail Pratique 2 : Gestion des droits d’accès dans Oracle
Objectifs du TP :
1. Accorder et révoquer des droits d'accès à des utilisateurs et des rôles.
2. Configurer des droits systèmes et des droits d'objet.
3. Mettre en place et analyser des audits pour surveiller les actions des utilisateurs.
Prérequis :
Une base de données Oracle opérationnelle.
Outils d'interaction comme SQL*Plus, SQL Developer ou Oracle APEX.
Une table commande contenant des données pour les tests :
CREATE TABLE commandes (
id_commande NUMBER PRIMARY KEY,
produit VARCHAR2(50),
quantite NUMBER,
prix NUMBER
);
INSERT INTO commandes VALUES (1, 'Laptop', 2, 1500);
INSERT INTO commandes VALUES (2, 'Smartphone', 5, 800);
COMMIT;
Étape 1 : Gestion des droits d’objet
Exercice 1.1 : Accorder des privilèges
1. Créez un utilisateur user_sales avec le mot de passe Sales@2025 :
CREATE USER user_sales IDENTIFIED BY Sales@2025;
2. Accordez à user_sales les privilèges suivants sur la table commandes :
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
o Lecture (SELECT).
o Insertion de nouvelles commandes (INSERT).
GRANT SELECT, INSERT ON commandes TO user_sales;
3. Connectez-vous en tant que user_sales et testez les privilèges :
CONNECT user_sales/Sales@2025;
SELECT * FROM commandes; -- Lecture
INSERT INTO commandes (id_commande, produit, quantite, prix) VALUES (3, 'Tablet', 3, 400);
-- Insertion
Exercice 1.2 : Révoquer des privilèges
1. Révoquez le privilège INSERT de user_sales :
REVOKE INSERT ON commandes FROM user_sales;
2. Connectez-vous en tant que user_sales et essayez de faire une insertion pour vérifier que le
privilège a bien été révoqué :
INSERT INTO commandes (id_commande, produit, quantite, prix) VALUES (4, 'Headphones',
10, 50); -- Devrait échouer
Étape 2 : Gestion des droits système
Exercice 2.1 : Accorder des droits système
1. Accordez à user_sales les privilèges suivants :
o CREATE SESSION : Permettre la connexion à la base de données.
o CREATE TABLE : Autoriser la création de tables.
GRANT CREATE SESSION, CREATE TABLE TO user_sales;
2. Connectez-vous en tant que user_sales et créez une nouvelle table pour tester :
CREATE TABLE test_table (
id NUMBER PRIMARY KEY,
name VARCHAR2(50)
);
Exercice 2.2 : Révoquer des droits système
1. Révoquez le privilège CREATE TABLE de user_sales :
REVOKE CREATE TABLE FROM user_sales;
2. Essayez à nouveau de créer une table avec user_sales pour vérifier que l’opération est
bloquée.
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
Étape 3 : Gestion des rôles
Exercice 3.1 : Création et gestion d’un rôle
1. Créez un rôle role_sales avec les privilèges suivants :
o SELECT et UPDATE sur la table commandes.
CREATE ROLE role_sales;
GRANT SELECT, UPDATE ON commandes TO role_sales;
2. Attribuez le rôle role_sales à user_sales :
GRANT role_sales TO user_sales;
3. Connectez-vous en tant que user_sales et testez les privilèges :
SELECT * FROM commandes; -- Lecture
UPDATE commandes SET prix = prix * 1.1 WHERE id_commande = 1; -- Mise à jour
Étape 4 : Audits et traçabilité
Exercice 4.1 : Configurer des audits
1. Activez l’audit sur les actions SELECT et INSERT sur la table commandes pour l'utilisateur
user_sales :
AUDIT SELECT, INSERT ON commandes BY user_sales;
2. Connectez-vous en tant que user_sales et effectuez les actions suivantes :
o Lisez les données de la table commandes.
o Essayez d'insérer une nouvelle commande (devrait échouer en raison du privilège
révoqué précédemment).
Exercice 4.2 : Analyser les journaux d’audit
1. Connectez-vous en tant qu'administrateur et affichez les journaux d’audit pour vérifier les
actions de user_sales :
SELECT username, action_name, timestamp
FROM dba_audit_trail
WHERE username = 'USER_SALES';
Résultats attendus :
Les privilèges ont été accordés et révoqués avec succès.
Les rôles ont permis de centraliser la gestion des droits.
Les audits ont enregistré les actions effectuées par user_sales et les journaux ont été
consultés.
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
Chapitre 4 : Gestion des transactions
La gestion des transactions est essentielle pour garantir l'intégrité et la fiabilité des opérations dans
une base de données. Les concepts ACID, les mécanismes de contrôle de concurrence et les stratégies
de reprise après incident sont au cœur d'un système robuste et résilient.
4.1 Concepts des transactions ACID
Objectif :
Comprendre les propriétés fondamentales ACID (Atomicité, Cohérence, Isolation, Durabilité)
pour assurer l'intégrité des transactions dans une base de données.
Contenu :
1. Atomicité :
o Une transaction est une unité indivisible : elle s'exécute entièrement ou pas du tout.
o Exemple :
BEGIN TRANSACTION;
UPDATE comptes SET solde = solde - 100 WHERE id = 1;
UPDATE comptes SET solde = solde + 100 WHERE id = 2;
IF aucune_erreur THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
2. Cohérence :
o Une transaction doit amener la base de données d'un état valide à un autre état
valide, respectant toutes les contraintes (intégrité référentielle, unique, etc.).
o Exemple :
Maintenir une contrainte de clé étrangère :
CREATE TABLE commandes (
id INT PRIMARY KEY,
client_id INT,
montant DECIMAL(10, 2),
FOREIGN KEY (client_id) REFERENCES clients(id)
);
3. Isolation :
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
o Garantit que les transactions concurrentes n'interfèrent pas entre elles. Les niveaux
d'isolation définissent le degré de visibilité des données non validées.
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
o Niveaux d'isolation :
Read Uncommitted : Les transactions peuvent lire des données non validées
(risque élevé).
Read Committed : Lecture uniquement des données validées.
Repeatable Read : Les mêmes lectures donnent toujours les mêmes
résultats.
Serializable : Le plus strict, simulant une exécution séquentielle.
o Exemple :
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT quantité FROM inventaire WHERE produit_id = 101;
UPDATE inventaire SET quantité = quantité - 5 WHERE produit_id = 101;
COMMIT;
4. Durabilité :
o Une fois validée (commit), une transaction devient permanente, même en cas de
panne.
o Exemple :
COMMIT;
-- Garantit la persistance des modifications
4.2 Contrôle de concurrence
Objectif :
Prévenir les conflits entre transactions concurrentes et maintenir la cohérence des données.
Contenu :
1. Verrouillage pessimiste :
o Empêche d'autres transactions d'accéder aux ressources verrouillées.
o Exemple :
SELECT * FROM comptes WHERE id = 1 FOR UPDATE;
2. Verrouillage optimiste :
o Permet les accès concurrents et vérifie les conflits avant de valider.
o Exemple :
UPDATE produits
SET quantité = quantité - 10
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
WHERE produit_id = 200 AND version = 3;
3. Gestion des contentions :
o Techniques pour réduire les conflits de verrouillage :
Utilisation de requêtes non bloquantes.
Délais d’attente ou retries automatiques en cas de verrouillage.
4.3 Reprise après incident
Objectif :
Garantir la continuité et la récupération des données après une panne ou une erreur.
Contenu :
1. Stratégies de sauvegarde :
o Types de sauvegardes :
Complète : Copie de toute la base de données.
BACKUP DATABASE mydb TO DISK = 'C:\sauvegardes\backup_full.bak';
Différentielle : Copie des données modifiées depuis la dernière sauvegarde
complète.
BACKUP DATABASE mydb TO DISK = 'C:\sauvegardes\backup_diff.bak' WITH
DIFFERENTIAL;
Incrémentale : Copie des modifications depuis la dernière sauvegarde, qu'elle
soit complète ou incrémentale.
2. Procédures de restauration :
o Restauration à partir d'une sauvegarde pour récupérer la base de données.
o Exemple :
RESTORE DATABASE mydb FROM DISK = 'C:\sauvegardes\backup_full.bak';
3. Journaux de transactions :
o Les journaux de transactions permettent de rejouer ou d'annuler des transactions en
cas de panne.
o Exemple :
RESTORE LOG mydb FROM DISK = 'C:\sauvegardes\trans_log.bak';
4. Plan de reprise après sinistre (Disaster Recovery) :
o Planifier et tester les procédures pour minimiser les pertes de données et les temps
d'arrêt.
o Exemples de bonnes pratiques :
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
Réplication des données vers un site distant.
Tests réguliers des procédures de sauvegarde/restauration.
4.4 Transactions distribuées
Objectif :
Coordonner les transactions impliquant plusieurs bases de données.
Contenu :
1. Deux-Phase Commit (2PC) :
o Utilisé pour garantir l'intégrité des transactions distribuées.
o Phases :
Préparation : Vérifie si toutes les bases de données sont prêtes à valider.
Validation : Confirme ou annule la transaction.
o Exemple :
o COMMIT TRANSACTION DISTRIBUÉE;
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
TP : Sécurité et Gestion des Transactions dans une Base de Données Oracle
Objectifs
1. Comprendre et appliquer la gestion des utilisateurs et des privilèges.
2. Manipuler les transactions ACID pour assurer l’intégrité des données.
3. Mettre en œuvre des audits pour renforcer la sécurité et la traçabilité.
Partie 1 : Gestion des utilisateurs et des rôles
Exercice 1 : Création et gestion des utilisateurs
1. Créer un rôle nommé analyste_role avec les privilèges nécessaires :
CREATE ROLE analyste_role;
GRANT SELECT ON ventes TO analyste_role;
GRANT SELECT ON employes TO analyste_role;
2. Créer un utilisateur nommé anna_smith avec un mot de passe sécurisé et attribuer-lui ce
rôle :
CREATE USER anna_smith IDENTIFIED BY Welcome2025;
GRANT analyste_role TO anna_smith;
3. Modifier le mot de passe de anna_smith pour qu’il devienne Secure@2025 et vérifier les
privilèges attribués :
ALTER USER anna_smith IDENTIFIED BY Secure@2025;
SELECT * FROM dba_role_privs WHERE grantee = 'ANNA_SMITH';
4. Supprimer l’utilisateur anna_smith après vérification :
DROP USER anna_smith;
Exercice 2 : Attribution et révocation de privilèges
1. Créez un utilisateur john_doe et accordez-lui des privilèges SELECT et INSERT sur la table
clients :
CREATE USER john_doe IDENTIFIED BY John1234;
GRANT SELECT, INSERT ON clients TO john_doe;
2. Révoquez ensuite le privilège INSERT de john_doe :
REVOKE INSERT ON clients FROM john_doe;
3. Vérifiez les privilèges restants :
SELECT * FROM user_tab_privs WHERE grantee = 'JOHN_DOE';
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
Partie 2 : Transactions ACID et contrôle de concurrence
Exercice 3 : Implémentation des transactions ACID
1. Préparation des données :
Créez les tables produits et commandes avec des relations entre elles :
CREATE TABLE produits (
produit_id INT PRIMARY KEY,
nom VARCHAR(50),
stock INT
);
CREATE TABLE commandes (
commande_id INT PRIMARY KEY,
produit_id INT,
quantité INT,
FOREIGN KEY (produit_id) REFERENCES produits(produit_id)
);
2. Ajout de données initiales :
INSERT INTO produits (produit_id, nom, stock) VALUES (1, 'Laptop', 50);
INSERT INTO produits (produit_id, nom, stock) VALUES (2, 'Smartphone', 100);
3. Transaction complexe :
Implémentez une transaction pour :
o Réduire le stock de 3 laptops.
o Ajouter une commande correspondante.
o Valider ou annuler la transaction en cas d’erreur.
BEGIN TRANSACTION;
UPDATE produits SET stock = stock - 3 WHERE produit_id = 1;
INSERT INTO commandes (commande_id, produit_id, quantité) VALUES (103, 1, 3);
-- Valider la transaction
COMMIT;
-- Si une erreur survient
-- ROLLBACK;
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
Sécurité BD, M2/SR/IAT
Exercice 4 : Contrôle de concurrence
1. Configurez le niveau d’isolation des transactions :
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2. Essayez de simuler deux transactions concurrentes accédant aux mêmes données pour
observer les effets des niveaux d’isolation (READ COMMITTED, REPEATABLE READ, etc.).
Partie 3 : Audits et sécurité des données
Exercice 5 : Configuration et utilisation des audits
1. Activez l’audit des actions SELECT sur la table commandes par john_doe :
AUDIT SELECT ON commandes BY john_doe;
2. Connectez-vous en tant que john_doe et effectuez une requête SELECT sur la table
commandes :
SELECT * FROM commandes;
3. Vérifiez les journaux d’audit pour observer les actions effectuées :
SELECT * FROM dba_audit_trail WHERE user_name = 'JOHN_DOE';
Exercice 6 : Détection des actions suspectes
1. Ajoutez un audit pour surveiller les suppressions sur la table produits :
AUDIT DELETE ON produits;
2. Essayez de supprimer un produit en tant qu’administrateur et vérifiez les journaux d’audit
pour détecter cette action.
Ing. YAHAYA Abdoul-Razak
Spécialiste en Réseaux de données et Sécurité
Expert en Régulation Numérique
