Correction Phase 3
Phase 3 : Vues et contrôle d’accès
Énigme 6 : Sécurisation des accès du support client
Objectif 1 [Code réponse 118
]
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;
Objectif 2 [Code réponse InsufficientPrivilege
]
-- 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 ;
Pour tester la configuration :
-- 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;
Énigme 7 :
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;