Examen admin tuning bd master ssi 2012 2013 bases de données
Télécharger PDFGestion d'une Base de Données d'Hypermarché : Administration, Contraintes et Sécurité
Cet article explore la gestion d'une base de données pour un hypermarché fictif nommé ARDIS. Nous aborderons la configuration des privilèges, la mise en place de contraintes d'intégrité, l'automatisation des tâches via des triggers, l'optimisation des requêtes SQL et la sécurisation des accès.
Schéma Logique de la Base de Données ARDIS
La base de données est composée de quatre tables principales :
Produit(CodeP, Nom_Produit, TypeProduit, Emballage, Prix)Client(CodeC, NomC, PrénomC, Date_NaissanceC, VilleC)Ventes(CodeC*, CodeP*, Date, Quantité)Stock(CodeP*, Date, Quantité_Stock)
Les clés primaires sont soulignées et les clés étrangères sont suivies d'une étoile (*).
Configuration des Accès et Privilèges Utilisateur
Un administrateur de base de données (BD) possède tous les privilèges. Pour des raisons de sécurité et de gestion des rôles, un utilisateur spécifique, ici "Agent1", est créé avec des privilèges limités. Pour permettre à "Agent1" de créer des tables, l'administrateur doit lui accorder le privilège approprié.
GRANT CREATE TABLE TO Agent1;Définition et Gestion des Contraintes d'Intégrité
Les contraintes d'intégrité sont essentielles pour maintenir la cohérence et la validité des données dans la base. Elles incluent les clés primaires, les clés étrangères et les contraintes de vérification (CHECK).
Pour ajouter des contraintes d'intégrité aux tables existantes (créées sans contraintes initialement) :
ALTER TABLE Produit ADD CONSTRAINT pk_produit PRIMARY KEY (CodeP);
ALTER TABLE Client ADD CONSTRAINT pk_client PRIMARY KEY (CodeC);
ALTER TABLE Ventes ADD CONSTRAINT pk_ventes PRIMARY KEY (CodeP, CodeC, Date);
ALTER TABLE Stock ADD CONSTRAINT pk_stock PRIMARY KEY (CodeP, Date);
ALTER TABLE Ventes ADD CONSTRAINT fk_ventes_produit FOREIGN KEY (CodeP) REFERENCES Produit (CodeP);
ALTER TABLE Ventes ADD CONSTRAINT fk_ventes_client FOREIGN KEY (CodeC) REFERENCES Client (CodeC);
ALTER TABLE Stock ADD CONSTRAINT fk_stock_produit FOREIGN KEY (CodeP) REFERENCES Produit (CodeP);De plus, une règle de gestion stipule que la quantité en stock de n'importe quel produit ne doit pas être inférieure à 5. Cette contrainte est appliquée à la table Stock.
ALTER TABLE Stock ADD CONSTRAINT ck_stock_quantite CHECK (Quantité_Stock >= 5);Consultation des Contraintes
L'administrateur peut interroger la métabase (ou dictionnaire de données) pour connaître toutes les contraintes créées par un utilisateur spécifique.
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM ALL_CONSTRAINTS
WHERE OWNER = 'AGENT1';Automatisation des Opérations avec les Triggers
Les triggers sont des blocs de code SQL exécutés automatiquement en réponse à certains événements (INSERT, UPDATE, DELETE) sur une table. Ils permettent d'automatiser des tâches et d'appliquer des logiques métier complexes.
Ajout d'une Colonne pour le Montant de la Vente
Pour enregistrer le montant total de chaque vente, une colonne Montant de type flottant est ajoutée à la table Ventes.
ALTER TABLE Ventes ADD Montant FLOAT;Calcul Automatique du Montant de la Vente
Pour automatiser le calcul et le remplissage de la colonne Montant après chaque insertion dans la table Ventes, un trigger est nécessaire. L'administrateur doit d'abord accorder le privilège à "Agent1" pour créer des triggers.
GRANT CREATE TRIGGER TO Agent1;Le trigger suivant calcule le montant de la vente en multipliant le prix du produit par la quantité vendue et met à jour la ligne de vente correspondante.
CREATE OR REPLACE TRIGGER trg_ins_auto_montant_vente
AFTER INSERT ON Ventes
FOR EACH ROW
DECLARE
v_prix PRODUIT.Prix%TYPE;
BEGIN
SELECT Prix INTO v_prix FROM Produit WHERE CodeP = :new.CodeP;
UPDATE Ventes
SET Montant = v_prix * :new.Quantité
WHERE CodeP = :new.CodeP AND CodeC = :new.CodeC AND Date = :new.Date;
END;Vérification de la Quantité en Stock avant Vente
Une contrainte métier importante est que pour qu'une vente soit possible, la quantité en stock doit être suffisante, et il doit rester un minimum de 5 unités après la vente. Un trigger BEFORE INSERT sur la table Ventes peut appliquer cette règle.
CREATE OR REPLACE TRIGGER trg_verif_quantite_stock
BEFORE INSERT ON Ventes
FOR EACH ROW
DECLARE
v_quantite_stock STOCK.Quantité_Stock%TYPE;
BEGIN
SELECT Quantité_Stock INTO v_quantite_stock
FROM Stock
WHERE CodeP = :new.CodeP AND Date = :new.Date;
IF (v_quantite_stock < (:new.Quantité + 5)) THEN
-- Utilise RAISE_APPLICATION_ERROR pour empêcher l'insertion et retourner un message d'erreur
RAISE_APPLICATION_ERROR(-20001, 'Stock insuffisant pour cette vente. Un minimum de 5 unités doit rester.');
END IF;
END;Mise à Jour Automatique du Stock après Vente
Après chaque vente réussie, la quantité en stock doit être automatiquement décrémentée. Un trigger AFTER INSERT sur la table Ventes est approprié pour cela.
CREATE OR REPLACE TRIGGER trg_maj_stock_apres_vente
AFTER INSERT ON Ventes
FOR EACH ROW
BEGIN
UPDATE Stock
SET Quantité_Stock = Quantité_Stock - :new.Quantité
WHERE CodeP = :new.CodeP AND Date = :new.Date;
END;Optimisation des Requêtes SQL
L'optimisation des requêtes est cruciale pour la performance des bases de données. Considérons une requête qui calcule la somme du montant des ventes pour des clients habitant Alger et achetant des produits de type "Alimentaire".
Requête SQL : Somme des Ventes Ciblées
SELECT SUM(V.Montant)
FROM Ventes V
JOIN Produit P ON V.CodeP = P.CodeP
JOIN Client C ON V.CodeC = C.CodeC
WHERE C.Ville = 'Alger' AND P.TypeProduit = 'Alimentaire';Plan d'Exécution d'une Requête
Le Système de Gestion de Base de Données (SGBD) génère un plan d'exécution pour chaque requête afin de déterminer la manière la plus efficace de l'exécuter. Ce plan spécifie l'ordre des opérations (accès aux tables, jointures, filtres) et les algorithmes utilisés.
En l'absence d'index spécifiques sur les colonnes utilisées dans la clause WHERE ou les jointures, le SGBD pourrait opter pour des balayages complets (full scans) des tables et des algorithmes de jointure par hachage (Hash Join).
Un plan d'exécution possible pour la requête ci-dessus, sans index, pourrait ressembler à ceci (représentation simplifiée) :
SELECT STATEMENT(Sélectionner la somme du montant)HASH JOIN(Jointure par hachage entre le résultat de la précédente jointure et la table Client filtrée)TABLE ACCESS FULL CLIENT(Balayage complet de la table Client, avec filtre sur Ville = 'Alger')
HASH JOIN(Jointure par hachage entre la table Ventes et la table Produit filtrée)TABLE ACCESS FULL PRODUIT(Balayage complet de la table Produit, avec filtre sur TypeProduit = 'Alimentaire')TABLE ACCESS FULL VENTES(Balayage complet de la table Ventes)
Afficher le Plan d'Exécution sous SQL*Plus
Pour afficher automatiquement le plan d'exécution d'une requête dans l'environnement SQL*Plus (un outil client Oracle), la commande suivante peut être utilisée avant d'exécuter la requête :
SET AUTOTRACE ON;Sécurité des Bases de Données : L'Injection SQL
La sécurité est une préoccupation majeure, en particulier lors de l'interaction entre les applications web et les bases de données. Une menace courante dans les applications utilisant des formulaires de connexion est l'injection SQL.
Qu'est-ce que l'Injection SQL ?
L'injection SQL est une technique d'exploitation d'une faille de sécurité dans une application interagissant avec une base de données. Elle consiste à insérer ou "injecter" des fragments de requêtes SQL non prévus par le développeur dans des champs de saisie (comme un nom d'utilisateur ou un mot de passe). Si l'application ne nettoie pas correctement ces entrées, le SGBD exécute le code SQL malveillant, ce qui peut contourner les mécanismes de sécurité (comme l'authentification par mot de passe), révéler des données sensibles, ou même modifier/supprimer des données.
Par exemple, en saisissant ' OR '1'='1 comme mot de passe, un attaquant peut transformer une requête d'authentification en une condition toujours vraie, lui donnant ainsi accès au système sans connaître le mot de passe réel.
Remèdes contre l'Injection SQL
Plusieurs mesures peuvent être prises pour se prémunir contre les injections SQL :
- Utiliser des requêtes préparées (Prepared Statements) avec des paramètres liés : C'est la méthode la plus recommandée et la plus efficace. Elle sépare le code SQL des données utilisateur, garantissant que les entrées sont toujours traitées comme des valeurs et non comme des instructions SQL.
- Échapper les caractères spéciaux : Pour les cas où les requêtes préparées ne sont pas applicables (bien que rares), toutes les entrées utilisateur doivent être "échappées" pour neutraliser les caractères spéciaux qui pourraient être interprétés comme du code SQL. Les langages de programmation et les drivers de base de données offrent des fonctions spécifiques à cet effet.
- Valider et filtrer les entrées utilisateur : Appliquer des validations strictes sur le type, le format et la longueur des données attendues. Limiter la taille des champs de saisie peut également réduire la portée d'une attaque.
- Principe du moindre privilège : Les comptes de base de données utilisés par les applications ne devraient avoir que les privilèges strictement nécessaires à leur fonctionnement.
FAQ
Q1 : Pourquoi est-il important de définir des contraintes d'intégrité sur les tables ?
Les contraintes d'intégrité garantissent la validité et la cohérence des données dans une base de données. Elles empêchent l'insertion de données incorrectes (par exemple, valeurs nulles pour une clé primaire), assurent la liaison correcte entre les tables (clés étrangères) et appliquent des règles métier (contraintes CHECK), ce qui est fondamental pour la fiabilité des informations.
Q2 : Quel est l'intérêt d'utiliser des triggers en gestion de base de données ?
Les triggers permettent d'automatiser des actions en réponse à des événements spécifiques (INSERT, UPDATE, DELETE) sur les tables. Ils sont utiles pour maintenir la cohérence des données, appliquer des règles métier complexes qui ne peuvent pas être gérées par de simples contraintes, auditer les modifications de données ou propager des changements entre des tables liées.
Q3 : En quoi les requêtes préparées sont-elles efficaces contre l'injection SQL ?
Les requêtes préparées fonctionnent en séparant la structure de la requête SQL des valeurs des données. La requête est d'abord envoyée au SGBD et compilée sans les données utilisateur. Ensuite, les données sont envoyées séparément. Le SGBD sait alors que ces données sont des valeurs et non des parties de la requête, empêchant ainsi l'exécution de code SQL malveillant injecté dans les entrées utilisateur.