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.

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

Commencez par ouvrir le notebook phase_3_Configuration.ipynb.

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.

Les vues permettent de :

• Simplifier l’accès aux données en masquant la complexité des requêtes
• Standardiser la présentation des informations
• Gérer des droits différenciés

Voici un exemple de création de vue :

-- Cette vue analyse les prix et marges des marchands
CREATE OR REPLACE 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 de l'accès à un schéma
GRANT USAGE ON SCHEMA nom_schema TO exemple_role;

-- Attribution du droit de lecture sur une vue spécifique (ATTENTION au `TO`)
GRANT SELECT ON ma_vue TO exemple_role;

-- Suppresion de tous les droits sur une table (ATTENTION au `FROM`)
REVOKE ALL ON nom_table FROM exemple_role;

-- Attribution du rôle à un utilisateur
GRANT exemple_role TO user ;

Et pour tester :

-- Test des droits
SET ROLE exemple_role;  -- attribution du rôle
SELECT * FROM ma_vue ;  -- devrait fonctionner
SELECT * FROM table_source;     -- devrait échouer

-- Retour à son rôle d'origine
RESET role;

Le notebook actuel phase_3_Configuration.ipynb doit vous connecter à la base de données en tant d’administrateur. Quel est le nom de compte l’administrateur (c’est celui par défaut sous Postgresql) ?

Pour tester votre configuration utilisez le notebook phase_3_Tests.ipynb qui vous connecte sous le compte utilisateur user_tp.

SELECT * FROM customers;

Validez vos réponses