Correction TD6 — Indépendance des niveaux, vues, confidentialité

Partie A – Sur feuille

1. Architecture à trois niveaux

Schéma attendu (description textuelle)

• Niveau externe

  • Objets typiques dans le projet :

    • Vues de service : vue_sejours_client, vue_offre_publique, vue_clients_st_trop, vue_bilan_villages, etc.
    • Fonctions / procédures exposées : disponibilite(...), creer_village_confidentiel(...), purge_sejours(...), etc.

  • Rôles / utilisateurs :

    • client_app, employe_app, secretaire, etc.

• Niveau logique

  • Tables métier :

    • client(idc, nom, age, ...)
    • village(idv, ville, activite, prix, capacite, …)
    • sejour(ids, idc, idv, debut, fin, …)
    • archiveséjour(ids, idc, idv, debut, fin, date_archivage, …)

  • Contraintes :

    • PRIMARY KEY, FOREIGN KEY, CHECK, EXCLUDE USING gist, UNIQUE, etc.

  • Triggers / logique métier :

    • Triggers d’archivage, de contrôle de dates, de mise à jour de totaux, etc.

• Niveau physique

  • Fichiers sur disque contenant les pages de tables et d’index.
  • Index B-tree, éventuellement autres types.
  • Entrées du catalogue système pg_catalog (pg_class, pg_index, pg_attribute, …).
  • Statistiques (pg_statistic), structures internes, autovacuum.

Exemple de dépendance à éviter

• Éviter que le code applicatif dépende :

  • du nom d’un index ou de la façon dont les données sont physiquement stockées (partitionnement, cluster, etc.) ;
  • de colonnes internes qui pourraient être supprimées ou renommées.

• Exemple concret :

  • Mauvaise pratique : une application qui utilise SELECT * FROM pg_class WHERE relfilenode = … pour identifier une table.
  • Bonne pratique : l’application utilise uniquement les vues et API définies au niveau externe.

2. Vues et indépendance

Différence vue “logique” / vue matérialisée

• Vue logique (CREATE VIEW) :

  • Stocke uniquement la définition (la requête).
  • À chaque SELECT, PostgreSQL réévalue la requête sous-jacente.

• Vue matérialisée (CREATE MATERIALIZED VIEW) :

  • Stocke physiquement le résultat de la requête.
  • Les données ne sont mises à jour que lors d’un REFRESH MATERIALIZED VIEW.

Deux avantages d’utiliser une vue dans le projet “séjours”

Exemples de réponses correctes :

1. Masquer la complexité de la structure interne :

   • Une vue comme vue_sejours_client évite à l’application de connaître le détail des jointures entre client, sejour, village.

2. Assurer l’indépendance des niveaux :

   • On peut réorganiser les tables (ajouter/supprimer des colonnes, scinder une table) tant que la signature de la vue (colonnes exposées) reste stable.

Autres avantages possibles (acceptables) :

• Restreindre l’accès à certaines colonnes (confidentialité).
• Facteur commun de code SQL (moins de duplication).
• Pour les vues matérialisées : améliorer les performances sur des agrégats coûteux (ex. bilan par ville).

3. Catalogue système

Différence entre pg_catalog et information_schema

• pg_catalog :

  • Schéma spécifique à PostgreSQL, très complet.
  • Contient toutes les tables système : pg_class, pg_attribute, pg_index, pg_views, etc.
  • Interface “bas niveau”, non standard, mais plus riche.

• information_schema :

  • Schéma standard SQL, présent dans plusieurs SGBD.
  • Fournit une vue portable des métadonnées : information_schema.tables, information_schema.columns, information_schema.views, etc.
  • Moins complet, mais plus portable.

Exemple de requête listant toutes les vues du schéma public

Solution 1 (via information_schema) :

SELECT table_name AS view_name
FROM information_schema.views
WHERE table_schema = 'public'
ORDER BY table_name;

Solution 2 (via pg_catalog.pg_views) :

SELECT viewname, definition
FROM pg_catalog.pg_views
WHERE schemaname = 'public'
ORDER BY viewname;

Les deux sont correctes, la première est plus “standard”, la seconde fournit la définition complète.

Partie B – Sur machine

Exercice 1 – Vue de synthèse client

Objectif rappelé Construire une vue qui rassemble, pour chaque client, toutes les informations sur ses séjours et les villages associés : idc, nom, ids, ville, activite, debut, fin.

1. Analyse conceptuelle

• Niveau logique utilisé : client, sejour, village
• Relation attendue : chaque séjour relie un client (idc) à un village (idv)
• Opération : jointure naturelle (JOIN … USING) ou explicite (ON …)
• Type d’objet : vue logique, pas matérialisée.

2. Création

CREATE OR REPLACE VIEW vue_sejours_client AS
SELECT
    c.idc,
    c.nom,
    s.ids,
    v.ville,
    v.activite,
    s.debut,
    s.fin
FROM client  AS c
JOIN sejour  AS s USING (idc)
JOIN village AS v USING (idv)
ORDER BY c.idc, s.debut DESC;

3. Vérification

SELECT * FROM vue_sejours_client LIMIT 5;

Résultat attendu (exemple) :

4. Droits d’accès (option sécurité)

La vue peut être exposée à une application cliente sans donner accès direct aux tables :

REVOKE SELECT ON client, sejour, village FROM public;
GRANT  SELECT ON vue_sejours_client TO client_app;

Ainsi, client_app ne voit que la vue (niveau externe), pas les structures internes.

5. Points d’attention pédagogiques

• Si une colonne change de nom dans les tables logiques, la vue devra être recréée.
• Si on veut garantir la stabilité pour le client, on fixe la vue comme interface contractuelle.
• En cas de forte volumétrie, on pourra créer une vue matérialisée pour optimiser les lectures de reporting :

CREATE MATERIALIZED VIEW vue_sejours_client_mat AS
SELECT * FROM vue_sejours_client;

Partie B – Exercice 2 — Vue de service limitée

Énoncé résumé Créer une vue vue_offre_publique(ville, activite, prix) qui masque les colonnes internes (idv, capacite) de village, puis tester les droits :

• public n’a pas accès direct à village,
• mais a SELECT sur la vue.

1. Création de la vue

CREATE OR REPLACE VIEW vue_offre_publique AS
SELECT
    ville,
    activite,
    prix
FROM village
ORDER BY ville, prix DESC;

Caractéristiques :

• Colonnes exposées : seulement ville, activite, prix.
• idv et capacite restent internes, accessibles uniquement via village.

Test basique :

SELECT * FROM vue_offre_publique LIMIT 5;

2. Révocation des droits sur la table de base

On retire à tout le monde (rôle public) le droit de lecture directe sur village :

REVOKE SELECT ON village FROM PUBLIC;

Effet attendu :

• Un utilisateur sans privilège explicite sur village ne peut plus faire :

  SELECT * FROM village;
  

  → erreur du type : permission denied for table village.

3. Attribution des droits sur la vue

On autorise uniquement la vue pour public :

GRANT SELECT ON vue_offre_publique TO PUBLIC;

Conséquence :

• Un utilisateur sans droits sur village peut exécuter :

  SELECT * FROM vue_offre_publique;
  

  Résultat attendu (exemple) :

  ville	activite	prix
  Lisbonne	yoga	120.0
  Lisbonne	surf	95.0
  Rio	surf	90.0

• La structure interne (idv, capacite, autres colonnes) reste invisible.

4. Vérification “indépendance / confidentialité”

Cas à faire exécuter par un rôle sans privilèges sur village (ex. secretaire) :

1. Requête interdite :

   SELECT idv, ville, activite, prix, capacite FROM village;
   -- → permission denied
   

2. Requête autorisée :

   SELECT * FROM vue_offre_publique;
   -- → OK, mais seulement ville, activite, prix
   

Conclusion à faire expliciter par les étudiants :

• Indépendance externe/logique : l’interface exposée est la vue, pas la table.
• Confidentialité : la structure interne de village est masquée derrière vue_offre_publique.

Partie B – Exercice 3 — Vue matérialisée

Objectif Créer une vue matérialisée vue_prix_moyen donnant, par ville, le prix moyen des villages, et montrer comment la rafraîchir après modification.

1. Création de la vue matérialisée

CREATE MATERIALIZED VIEW vue_prix_moyen AS
SELECT
    ville,
    avg(prix) AS prix_moyen
FROM village
GROUP BY ville
ORDER BY ville;

Vérification :

SELECT * FROM vue_prix_moyen;

Exemple de résultat attendu :

2. Index (optionnel mais pertinent)

Pour accélérer les recherches par ville :

CREATE INDEX idx_vue_prix_moyen_ville
    ON vue_prix_moyen (ville);

3. Impact d’une modification dans village

Exemple : augmentation du prix d’un village à Nice.

UPDATE village
SET prix = prix + 10
WHERE ville = 'Nice' AND idv = 42;

Sans rafraîchir, la vue matérialisée ne voit pas cette modification :

SELECT * FROM vue_prix_moyen WHERE ville = 'Nice';
-- affiche encore l’ancien prix moyen

4. Rafraîchissement de la vue

REFRESH MATERIALIZED VIEW vue_prix_moyen;

Puis relecture :

SELECT * FROM vue_prix_moyen WHERE ville = 'Nice';
-- affiche le nouveau prix moyen après mise à jour

5. Point de compréhension

• CREATE VIEW : recalcul à chaque SELECT.
• CREATE MATERIALIZED VIEW : stockage sur disque, nécessite un REFRESH explicite pour refléter les changements de village.

Partie B – Exercice 4 — Vue dépendante d’une procédure (création confidentielle de village)

Objectif : encapsuler l’insertion dans village dans une procédure SECURITY DEFINER, puis exposer seulement cette procédure à un rôle employe, sans lui donner INSERT sur village.

1. Création de la procédure sécurisée

Procédure côté “niveau logique”, propriétaire = un rôle “technique” (par ex. admin_app ou votre rôle courant).

CREATE OR REPLACE PROCEDURE creer_village_confidentiel(
    p_ville    text,
    p_activite text,
    p_prix     numeric,
    p_capacite int
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    v_idv bigint;
BEGIN
    INSERT INTO village(ville, activite, prix, capacite)
    VALUES (p_ville, p_activite, p_prix, p_capacite)
    RETURNING idv INTO v_idv;

    RAISE NOTICE
        'Création village: idv=%, ville=%, activité=%, prix=%, capacité=%',
        v_idv, p_ville, p_activite, p_prix, p_capacite;
END;
$$;

Points clés :

• SECURITY DEFINER : la procédure s’exécute avec les droits de son propriétaire.
• L’utilisateur appelant n’a pas besoin d’avoir INSERT sur village, seulement EXECUTE sur la procédure.

Éventuellement sécuriser le search_path du propriétaire (bonne pratique), mais hors champ de l’exercice.

2. Création du rôle applicatif et gestion des droits

Créer un rôle employe sans privilèges directs sur village :

CREATE ROLE employe LOGIN PASSWORD 'motdepasse';

Retirer les droits directs (si besoin) :

REVOKE INSERT, UPDATE, DELETE, SELECT ON village FROM PUBLIC;
REVOKE INSERT, UPDATE, DELETE, SELECT ON village FROM employe;

Accorder uniquement l’exécution de la procédure :

GRANT EXECUTE ON PROCEDURE creer_village_confidentiel(text, text, numeric, int)
    TO employe;

Vérifier que employe ne peut pas insérer directement :

SET ROLE employe;

INSERT INTO village(ville, activite, prix, capacite)
VALUES ('TestVille', 'TestActivite', 100, 50);
-- → permission denied for table village (attendu)

Appel via la procédure :

CALL creer_village_confidentiel('TestVille', 'TestActivite', 100, 50);
-- → OK, insertion effectuée + RAISE NOTICE

3. Vérification du résultat

Revenir à un rôle avec droits (RESET ROLE; ou reconnecter) :

SELECT idv, ville, activite, prix, capacite
FROM village
WHERE ville = 'TestVille' AND activite = 'TestActivite';

Ligne présente → la procédure a bien encapsulé l’insertion.

Conclusion pour l’exercice

• Le rôle employe ne possède aucun droit direct sur la table village.
• Il peut pourtant créer de nouveaux villages via l’API interne creer_village_confidentiel, qui matérialise l’encapsulation et l’indépendance entre niveau externe (procédure) et logique (table).

Partie B – Exercice 5 — Audit du dictionnaire (vues)

Objectif :

1. Écrire une requête listant les vues et leur définition via pg_catalog.pg_views.
2. Créer une fonction liste_vues() qui renvoie (schemaname, viewname, definition).

1. Requête simple sur pg_views

Lister toutes les vues visibles, avec schéma, nom et définition :

SELECT
    schemaname,
    viewname,
    definition
FROM pg_catalog.pg_views
ORDER BY schemaname, viewname;

Limiter au schéma public si besoin :

SELECT
    schemaname,
    viewname,
    definition
FROM pg_catalog.pg_views
WHERE schemaname = 'public'
ORDER BY viewname;

2. Fonction liste_vues()

Version renvoyant un ensemble de lignes (RETURNS TABLE).

CREATE OR REPLACE FUNCTION liste_vues()
RETURNS TABLE (
    schemaname text,
    viewname   text,
    definition text
)
LANGUAGE sql
AS $$
    SELECT
        schemaname,
        viewname,
        definition
    FROM pg_catalog.pg_views
    ORDER BY schemaname, viewname
$$;

Utilisation :

SELECT * FROM liste_vues();

Variante filtrée sur public :

CREATE OR REPLACE FUNCTION liste_vues_public()
RETURNS TABLE (
    viewname   text,
    definition text
)
LANGUAGE sql
AS $$
    SELECT
        viewname,
        definition
    FROM pg_catalog.pg_views
    WHERE schemaname = 'public'
    ORDER BY viewname
$$;

Utilisation :

SELECT * FROM liste_vues_public();

Partie C — Scénario d’intégration

1. Indépendance externe / logique

But : montrer qu’une modification du schéma logique (suppression d’une colonne interne) ne casse pas une vue bien conçue.

On suppose qu’une colonne non utilisée note existe dans village (suite aux blocs précédents).

a) Suppression de la colonne interne

ALTER TABLE village
DROP COLUMN note;

La vue vue_offre_publique a été définie ainsi (correction Ex. 2) :

CREATE OR REPLACE VIEW vue_offre_publique AS
SELECT
    ville,
    activite,
    prix
FROM village
ORDER BY ville, prix DESC;

Elle ne dépend pas de note.

b) Vérification

SELECT * FROM vue_offre_publique LIMIT 5;

Résultat attendu : la vue fonctionne sans erreur, toujours avec les colonnes :

• ville
• activite
• prix

Conclusion : – On a modifié le schéma logique (village) sans casser le niveau externe (vue_offre_publique) → indépendance externe / logique respectée.

2. Encapsulation et droits : vue vue_clients_st_trop

But : un rôle secretaire ne doit voir que les clients ayant des séjours à Saint-Tropez, via une vue filtrée.

Dans le modèle du cahier des charges, la notion de “ville” est portée par village.ville. Un “client de Saint-Tropez” = un client ayant au moins un séjour dans un village dont ville = 'Saint-Tropez' (à adapter à vos données, éventuellement ILIKE 'St Trop%').

a) Création de la vue filtrée

CREATE OR REPLACE VIEW vue_clients_st_trop AS
SELECT DISTINCT
    c.idc,
    c.nom
FROM client  AS c
JOIN sejour  AS s USING (idc)
JOIN village AS v USING (idv)
WHERE v.ville ILIKE 'Saint-Tropez';

Variante si vous utilisez une graphie abrégée :

WHERE v.ville ILIKE 'St Trop%';

b) Création du rôle secretaire

CREATE ROLE secretaire LOGIN PASSWORD 'secret';

Retrait des droits directs sur les tables :

REVOKE SELECT ON client, sejour, village FROM PUBLIC;
REVOKE SELECT ON client, sejour, village FROM secretaire;

c) Attribution des droits sur la vue uniquement

GRANT SELECT ON vue_clients_st_trop TO secretaire;

d) Vérification

Se connecter ou basculer :

SET ROLE secretaire;

1. Accès direct interdit :

   SELECT * FROM client;
   -- → permission denied for table client
   

2. Accès via la vue autorisé :

   SELECT * FROM vue_clients_st_trop;
   -- → OK, seulement les colonnes idc, nom des clients ayant un séjour à Saint-Tropez
   

Conclusion : – secretaire n’a qu’une vue partielle du modèle, définie par la vue → encapsulation + confidentialité.

3. Sécurité exécutive : appel de creer_village_confidentiel par secretaire

But : vérifier que secretaire peut appeler la procédure creer_village_confidentiel(...) (définie SECURITY DEFINER), sans avoir de droits directs sur village.

On reprend la procédure de la Partie B, Ex. 4 :

CREATE OR REPLACE PROCEDURE creer_village_confidentiel(
    p_ville    text,
    p_activite text,
    p_prix     numeric,
    p_capacite int
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    v_idv bigint;
BEGIN
    INSERT INTO village(ville, activite, prix, capacite)
    VALUES (p_ville, p_activite, p_prix, p_capacite)
    RETURNING idv INTO v_idv;

    RAISE NOTICE
      'Création village: idv=%, ville=%, activité=%, prix=%, capacité=%',
      v_idv, p_ville, p_activite, p_prix, p_capacite;
END;
$$;

a) Droits à accorder

S’assurer que secretaire n’a pas de droits directs sur village (fait plus haut), puis lui donner seulement l’exécution :

GRANT EXECUTE ON PROCEDURE creer_village_confidentiel(text, text, numeric, int)
    TO secretaire;

b) Vérification côté secretaire

SET ROLE secretaire;

1. Tentative d’insertion directe (doit échouer) :

   INSERT INTO village(ville, activite, prix, capacite)
   VALUES ('Saint-Tropez', 'plage', 200, 80);
   -- → permission denied for table village
   

2. Appel via la procédure (doit réussir) :

   CALL creer_village_confidentiel('Saint-Tropez', 'plage', 200, 80);
   -- → OK, avec un RAISE NOTICE confirmant la création
   

c) Contrôle du résultat (avec un rôle ayant les droits)

RESET ROLE;

SELECT idv, ville, activite, prix, capacite
FROM village
WHERE ville = 'Saint-Tropez' AND activite = 'plage';

La ligne doit être présente.

Conclusion : – La création du village est encapsulée dans une procédure exécutée avec les droits du propriétaire → secretaire déclenche l’action sans voir ni manipuler directement la table.

4. Analyse du catalogue : rôles et vues

But : explorer le catalogue pour lister les rôles, leurs capacités, et les vues qu’ils possèdent.

a) Liste des rôles et propriétés principales

Depuis n’importe quel rôle avec droits suffisants :

SELECT
    rolname,
    rolcanlogin,
    rolsuper,
    rolcreatedb,
    rolcreaterole,
    rolreplication
FROM pg_roles
ORDER BY rolname;

Interprétation :

• rolname : nom du rôle (postgres, employe, secretaire, etc.).
• rolcanlogin : peut se connecter (LOGIN).
• rolsuper : super-utilisateur.
• rolcreatedb : peut créer des bases.
• rolcreaterole : peut créer des rôles.

On peut compléter par les droits sur les tables/vues via les vues standard :

SELECT
    grantee        AS role_name,
    table_schema,
    table_name,
    privilege_type
FROM information_schema.role_table_grants
ORDER BY role_name, table_schema, table_name, privilege_type;

Permet de voir, par exemple, que :

• secretaire possède SELECT sur vue_clients_st_trop,
• public possède SELECT sur vue_offre_publique, etc.

b) Vues et propriétaires

Lister les vues et leur propriétaire :

SELECT
    schemaname,
    viewname,
    viewowner
FROM pg_catalog.pg_views
ORDER BY schemaname, viewname;

Exemple de lignes attendues :

On peut filtrer par propriétaire :

SELECT
    schemaname,
    viewname
FROM pg_catalog.pg_views
WHERE viewowner = 'phparis'
ORDER BY viewname;

Conclusion :

• Les rôles sont décrits dans pg_roles (niveau logique / sécurité).
• Les vues et leurs propriétaires sont répertoriés dans pg_views.
• On peut ainsi vérifier quels rôles possèdent quoi et quels objets composent le niveau externe.

Partie D — Autonomie / Devoir maison

Rappel des items que j’avais proposés :

1. Vue d’administration vue_bilan_villages.
2. Fonction rafraichir_bilan().
3. Procédure revoke_tout().
4. Question ouverte : vue vs procédure vs trigger.

1. Vue d’administration vue_bilan_villages

Spécification Par ville :

• nb_villages
• nb_sejours
• prix_moyen

Création de la vue

CREATE OR REPLACE VIEW vue_bilan_villages AS
SELECT
    v.ville,
    COUNT(DISTINCT v.idv)           AS nb_villages,
    COUNT(s.ids)                    AS nb_sejours,
    AVG(v.prix)                     AS prix_moyen
FROM village v
LEFT JOIN sejour s
       ON s.idv = v.idv
GROUP BY v.ville
ORDER BY v.ville;

Commentaires :

• LEFT JOIN pour inclure les villes sans aucun séjour (nb_sejours = 0).
• COUNT(DISTINCT v.idv) pour éviter de compter plusieurs fois le même village.

Test :

SELECT * FROM vue_bilan_villages;

2. Fonction rafraichir_bilan()

Ici on suppose que vue_bilan_villages est matérialisée pour coller à la notion de “rafraîchissement”. On commence donc par la matérialiser :

DROP VIEW IF EXISTS vue_bilan_villages;

CREATE MATERIALIZED VIEW vue_bilan_villages AS
SELECT
    v.ville,
    COUNT(DISTINCT v.idv)           AS nb_villages,
    COUNT(s.ids)                    AS nb_sejours,
    AVG(v.prix)                     AS prix_moyen
FROM village v
LEFT JOIN sejour s
       ON s.idv = v.idv
GROUP BY v.ville
ORDER BY v.ville;

Fonction de rafraîchissement

CREATE OR REPLACE FUNCTION rafraichir_bilan()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  REFRESH MATERIALIZED VIEW vue_bilan_villages;
END;
$$;

Utilisation :

SELECT rafraichir_bilan();
SELECT * FROM vue_bilan_villages;

3. Procédure revoke_tout()

Spécification – Révoquer les droits SELECT sur les tables de base pour PUBLIC. – Ne laisser la lecture qu’au travers des vues “publiques” (exposées).

Version simple, centrée sur le schéma public :

CREATE OR REPLACE PROCEDURE revoke_tout()
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    -- Révoquer SELECT sur toutes les tables du schéma public pour PUBLIC
    FOR r IN
        SELECT tablename
        FROM pg_catalog.pg_tables
        WHERE schemaname = 'public'
    LOOP
        EXECUTE format('REVOKE SELECT ON TABLE public.%I FROM PUBLIC;', r.tablename);
    END LOOP;

    -- Exemple : accorder SELECT sur certaines vues publiques
    -- à adapter selon vos vues "API"
    PERFORM 1;  -- no-op pour éviter bloc vide
END;
$$;

Si on veut explicitement (ré)accorder sur certaines vues externes :

CREATE OR REPLACE PROCEDURE ouvrir_vues_publiques()
LANGUAGE plpgsql
AS $$
BEGIN
    GRANT SELECT ON vue_offre_publique    TO PUBLIC;
    GRANT SELECT ON vue_prix_moyen        TO PUBLIC;
    -- Ajouter les autres vues que vous considérez comme interface publique
END;
$$;

Séquence typique :

CALL revoke_tout();
CALL ouvrir_vues_publiques();

4. Comparaison vue / procédure / trigger

Exemple de correction synthétique :

• Vue

  • Rôle : interface de lecture au niveau externe.

  • Avantages :

    • Masque la complexité des jointures.
    • Masque les colonnes sensibles.
    • Assure l’indépendance externe/logique si sa “signature” reste stable.

  • Limites :

    • Pas d’effet de bord (pas de logique métier en écriture, sauf via INSTEAD OF sur vues spécifiques).
    • Performances parfois dépendantes de la requête sous-jacente (d’où vues matérialisées).

• Procédure

  • Rôle : encapsuler une action (écriture, séquence d’opérations).

  • Avantages :

    • Peut regrouper plusieurs requêtes et vérifier des pré/post-conditions.
    • S’intègre bien à la sécurité (GRANT EXECUTE, SECURITY DEFINER).
    • Sert d’API transactionnelle pour l’application.

  • Limites :

    • Ne protège rien si l’application accède aussi directement aux tables.
    • Complexité de maintenance si la logique métier est dispersée dans trop de procédures.

• Trigger

  • Rôle : logique métier déclenchée automatiquement sur INSERT/UPDATE/DELETE.

  • Avantages :

    • Garantit des invariants même si un développeur oublie d’appeler une procédure.
    • Centralise certaines règles structurales (archivage, cohérence).

  • Limites :

    • Moins visibles depuis l’extérieur (effets implicites).
    • Débogage plus difficile.
    • Ne constitue pas une API claire pour les clients, c’est un garde-fou interne.

Conclusion attendue :

• Pour l’indépendance des niveaux, la vue est l’outil principal côté interface.

• Pour la sécurité et la confidentialité, on combine :

  • vues filtrées,
  • procédures SECURITY DEFINER,
  • triggers pour les invariants internes.

• Pour la gestion des règles métier, privilégier :

  • des procédures et vues comme API explicite,
  • des triggers réservés aux invariants “non négociables” (intégrité, archivage).

Partie E — Synthèse réflexive

1. Définitions (une phrase chacune)

• Indépendance des niveaux Capacité d’un SGBD à faire évoluer un niveau (physique, logique ou externe) sans obliger à modifier les autres niveaux tant que l’interface exposée reste stable.

• Confidentialité Propriété qui consiste à contrôler quelles données sont accessibles à quels rôles, de façon à ce que chaque utilisateur ne voie que ce qui lui est autorisé.

• Encapsulation Technique qui consiste à exposer uniquement des opérations (vues, fonctions, procédures) pour manipuler les données, en masquant la structure interne des tables et la logique de bas niveau.

2. Pourquoi une application bien conçue ne doit pas accéder directement aux tables métier

Réponse structurée :

• Les tables métier représentent l’implémentation interne du modèle (niveau logique), amenée à évoluer (renommage, ajout/suppression de colonnes, refactoring de schéma).

• Si l’application accède directement aux tables, chaque changement interne implique de modifier tout le code applicatif (couplage fort, forte dette technique).

• En passant par des vues et des API SQL/PLpgSQL (fonctions, procédures), on fige une interface stable :

  • la base peut être réorganisée tant que cette interface est respectée ;
  • la sécurité est plus simple à gérer (droits sur vues/procédures plutôt que sur toutes les tables).

Donc : accès direct aux tables = couplage fort + fuites de détails internes ; interface via vues/procédures = indépendance des niveaux + meilleure sécurité.

3. Exemple d’évolution physique qui ne modifie ni les vues ni les applications clientes

Plusieurs exemples corrects ; un scénario complet :

• Ajout d’un index sur sejour(idc, debut) pour accélérer les requêtes “séjours par client, ordre chrono” :

  CREATE INDEX idx_sejour_idc_debut
      ON sejour(idc, debut);
  

Effet :

• Les tables logiques ne changent pas (mêmes colonnes, mêmes noms).
• Les vues (par ex. vue_sejours_client, vue_clients_st_trop) continuent à fonctionner sans modification.
• Le code applicatif ne change pas : mêmes requêtes externes, juste plus rapides.

Autres réponses acceptables :

• Passage d’une table non clusterisée à CLUSTER sur un index.
• Création d’index supplémentaires ou d’index partiels.
• Changement des paramètres d’autovacuum ou de stockage.

Dans tous les cas, tant que le schéma logique (noms de tables/colonnes, types, contraintes exposées) ne change pas, les vues et les applications restent compatibles : c’est précisément l’indépendance du niveau physique.