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

Objectifs d’apprentissage

• Comprendre la séparation externe / logique / physique dans un SGBD.
• Concevoir des vues stables masquant la structure interne.
• Gérer la confidentialité via droits et procédures encapsulées.
• Utiliser le catalogue système pour explorer la métabase.

Partie A — Sur feuille

1. Architecture à trois niveaux

   • Dessinez les trois niveaux (externe, logique, physique) de votre application.
   • Indiquez pour chaque niveau les objets concrets : externe → vues, fonctions ; logique → tables métier ; physique → fichiers, index, catalogue.
   • Citez un exemple de dépendance à éviter entre deux niveaux.

2. Vues et indépendance

   • Expliquez en une phrase la différence entre vue logique et vue matérialisée.
   • Citez deux avantages d’utiliser une vue dans le contexte du projet “séjours”.

3. Catalogue système

   • Quelle est la différence entre pg_catalog et information_schema ?
   • Donnez un exemple de requête pour lister toutes les vues du schéma public.

Partie B — Sur machine : Vues et niveaux d’accès

Exercice 1 — Vue de synthèse client

Créer une vue vue_sejours_client affichant :

idc, nom, ids, ville, activite, debut, fin

en joignant client, sejour, village.

Exercice 2 — Vue de service limitée

Créer une vue vue_offre_publique(ville, activite, prix) excluant les colonnes internes (idv, capacite). Tester qu’un utilisateur sans droits directs sur village peut lire la vue si vous :

REVOKE SELECT ON village FROM public;
GRANT SELECT ON vue_offre_publique TO public;

Exercice 3 — Vue matérialisée

Créer une vue matérialisée vue_prix_moyen donnant, par ville, le prix moyen des villages. Mettre à jour la vue après modification d’un prix :

REFRESH MATERIALIZED VIEW vue_prix_moyen;

Exercice 4 — Vue dépendante d’une procédure

Créer une procédure creer_village_confidentiel(p_ville text, p_activite text, p_prix numeric, p_capacite int) déclarée SECURITY DEFINER. – Elle insère un nouveau village. – Elle logge l’action avec RAISE NOTICE. – Donnez à un rôle employe uniquement le droit EXECUTE sur cette procédure. Vérifiez qu’il ne peut pas insérer directement dans village.

Exercice 5 — Audit du dictionnaire

Écrivez une requête listant les vues et leur définition à partir de pg_catalog.pg_views. Ajoutez une fonction liste_vues() renvoyant (schemaname text, viewname text, definition text).

Partie C — Scénario d’intégration

1. Indépendance externe / logique

   • Supprimez une colonne non utilisée de village (ex. note) et montrez que la vue vue_offre_publique reste fonctionnelle.

2. Encapsulation et droits

   • Un rôle secretaire ne doit voir que les clients de Saint-Tropez : Créez une vue vue_clients_st_trop filtrant destination ILIKE 'St Trop%' et ne lui accordez que le droit SELECT dessus.

3. Sécurité exécutive

   • Vérifiez que secretaire peut exécuter CALL creer_village_confidentiel(...) sans accès direct aux tables.

4. Analyse du catalogue

   • Listez dans pg_roles les rôles existants et leurs privilèges.
   • Identifiez les vues appartenant à chaque rôle via pg_views.

Partie D — Autonomie / Devoir maison

1. Créer une vue d’administration vue_bilan_villages : par ville → nb_villages, nb_sejours, prix_moyen.
2. Créer une fonction rafraichir_bilan() qui exécute REFRESH MATERIALIZED VIEW vue_bilan_villages;.
3. Créer une procédure revoke_tout() qui révoque tous les droits SELECT sur les tables et ne garde que les vues publiques.
4. Comparer : vue vs procédure vs trigger sur la question de l’indépendance et de la sécurité.

Partie E — Synthèse réflexive (sur feuille)

1. Définir en une phrase : indépendance des niveaux, confidentialité, encapsulation.
2. Expliquer pourquoi une application bien conçue ne doit jamais accéder directement aux tables métier.
3. Donner un exemple d’évolution physique (index, partitionnement) qui ne modifie ni les vues ni les applications clientes.