Escape Game

Phase 3 : Vues et contrôle d’accès

Mission

Suite aux événements des phases précédentes, la direction souhaite mettre en place une meilleure organisation de l’accès aux données pour l’équipe du support client. Actuellement, chaque agent doit écrire ses propres requêtes SQL pour consulter les transactions des clients, ce qui est à la fois inefficace et risqué.

Votre mission est de créer une vue adaptée aux besoins du support client et de mettre en place les droits d’accès appropriés.

Commencez par créer un notebook, copiez/collez le code suivant dans la première cellule et exécutez-la :

%reload_ext sql
%sql postgresql://user_tp:ue_info_reseaux_db@localhost/ue_info_reseaux_db
%config SqlMagic.displaylimit = None

Énigme 6 : Sécurisation des accès du support client

Objectif 1 : Création d’une vue

Situation : Les agents du support client passent beaucoup de temps à écrire des requêtes complexes pour obtenir une vision complète des transactions d’un client. Ils ont besoin de voir dans une seule vue : les informations du client, le montant total de ses achats, sa dernière transaction et le nombre total de ses transactions.

Voici un exemple simple de création de vue avec agrégation :

-- Cette vue analyse les prix et marges des marchands
CREATE VIEW exemple_vue_marchands AS
SELECT 
    mp.merchant_id,                   -- Identifiant du marchand
    p.product_name,                   -- Nom du produit
    mp.price as prix_vente,          -- Prix fixé par le marchand
    mp.available_quantity as stock,   -- Stock disponible
    p.base_price as prix_catalogue,   -- Prix catalogue recommandé
    -- Calcul de la marge en pourcentage, arrondie à 2 décimales
    ROUND(((mp.price - p.base_price) / p.base_price * 100)::numeric, 2) as marge_pourcentage
FROM merchant_products mp
JOIN products p ON mp.product_id = p.product_id
WHERE mp.available_quantity > 0;      -- Ne montre que les produits en stock

Cette vue est particulièrement utile pour l’analyse des prix : elle permet de comparer rapidement les prix pratiqués par chaque marchand par rapport au prix catalogue et de calculer leur marge. Elle ne montre que les produits en stock pour faciliter la gestion des approvisionnements.

Pour utiliser cette vue :

SELECT * FROM exemple_vue_marchands ORDER BY merchant_id;

SELECT * FROM customer_transaction_summary 
WHERE total_amount > 260000 
ORDER BY last_transaction_date DESC 
LIMIT 1;

Objectif 2 : Configuration des droits d’accès

Situation : Maintenant que la vue est créée, nous devons nous assurer que les agents du support client y ont accès, tout en restreignant leur accès aux tables sources pour des raisons de sécurité.

Voici un exemple de création de rôle avec droits limités :

-- Création du rôle
CREATE ROLE exemple_role;

-- Attribution du droit de lecture sur une vue spécifique
GRANT SELECT ON ma_vue TO exemple_role;

-- Test des droits
SET ROLE exemple_role;
SELECT * FROM ma_vue LIMIT 1;  -- devrait fonctionner
SELECT * FROM table_source;     -- devrait échouer

Pour pouvoir modifiez les droits vous allez devoir vous reconnecter en tant qu’admin à la base de données en executant le code suivant :

%reload_ext sql
%sql postgresql://postgres@localhost/ue_info_reseaux_db
%config SqlMagic.displaylimit = None

SELECT * FROM customers LIMIT 1;

Validez vos réponses