Correction Phase 3

CREATE OR REPLACE VIEW customer_transaction_summary AS
SELECT 
    c.customer_id,
    c.name,
    c.email,
    COUNT(t.transaction_id) as total_transactions,
    SUM(t.amount) as total_amount,
    MAX(t.transaction_date) as last_transaction_date,
    ROUND(AVG(t.amount)::numeric, 2) as average_amount
FROM customers c
JOIN transactions t ON c.customer_id = t.customer_id
GROUP BY c.customer_id, c.name, c.email
ORDER BY last_transaction_date DESC;

-- 1. Création du rôle
CREATE ROLE support_agent;

-- 2. Attribution du droit de lecture sur la vue uniquement
GRANT USAGE ON SCHEMA game TO support_agent;
GRANT SELECT ON customer_transaction_summary TO support_agent;

-- 3. Révocation explicite des droits sur les tables sources
REVOKE ALL ON customers FROM support_agent;
REVOKE ALL ON transactions FROM support_agent;
REVOKE ALL ON transaction_details FROM support_agent;
REVOKE ALL ON merchant_products FROM support_agent;
REVOKE ALL ON products FROM support_agent;

-- 4. Attribution du rôle à l'utilisateur
GRANT support_agent TO user_tp ;

-- Se connecter en tant que support_agent
SET ROLE support_agent;

-- Cette requête devrait fonctionner
SELECT * FROM customer_transaction_summary LIMIT 1;

-- Cette requête devrait échouer avec une erreur de permission
SELECT * FROM customers LIMIT 1;

CREATE OR REPLACE VIEW customer_transaction_summary AS
SELECT 
    c.customer_id,
    c.name,
    c.email,
    COUNT(t.transaction_id) as total_transactions,
    COALESCE(SUM(t.amount), 0) as total_amount,
    MAX(t.transaction_date) as last_transaction_date,
    COALESCE(ROUND(AVG(t.amount)::numeric, 2), 0) as average_amount
FROM customers c
LEFT JOIN transactions t ON c.customer_id = t.customer_id
GROUP BY c.customer_id, c.name, c.email
ORDER BY last_transaction_date DESC NULLS LAST;