SCENE 7 : Les Vues et la Sécurité - Les Murs et les Miroirs

-- Vue simple : les clients actifs
CREATE VIEW vue_clients_actifs AS
SELECT client_id, nom, prenom, email, ville
FROM client
WHERE actif = true
  AND date_derniere_commande > CURRENT_DATE - INTERVAL '1 year';

-- Utilisation : comme une table
SELECT * FROM vue_clients_actifs WHERE ville = 'Paris';

-- Vue pour le dashboard commercial
CREATE VIEW vue_commandes_detaillees AS
SELECT 
    c.commande_id,
    c.date_commande,
    c.montant_total,
    cl.nom AS client_nom,
    cl.ville,
    e.nom AS employe_nom,
    COUNT(lc.produit_id) AS nb_produits
FROM commande c
JOIN client cl ON c.client_id = cl.client_id
LEFT JOIN employe e ON c.commercial_id = e.employe_id
LEFT JOIN ligne_de_commande lc ON c.commande_id = lc.commande_id
GROUP BY c.commande_id, cl.nom, cl.ville, e.nom;
-- Maintenant, le commercial voit tout d'un coup.

-- Pour un rapport quotidien lourd
CREATE MATERIALIZED VIEW mv_stats_quotidiennes AS
SELECT 
    date_commande::DATE as jour,
    COUNT(*) as nb_commandes,
    SUM(montant_total) as ca_total,
    AVG(montant_total) as panier_moyen
FROM commande
WHERE date_commande > CURRENT_DATE - INTERVAL '30 days'
GROUP BY date_commande::DATE
WITH DATA; -- Remplit immédiatement

-- Consultation ultra-rapide
SELECT * FROM mv_stats_quotidiennes ORDER BY jour DESC;

-- Rafraîchissement (à faire périodiquement)
REFRESH MATERIALIZED VIEW mv_stats_quotidiennes;
-- Coûteux. À faire la nuit.

CREATE VIEW vue_clients_parisiens AS
SELECT * FROM client WHERE ville = 'Paris'
WITH CHECK OPTION;

-- Ça marche :
UPDATE vue_clients_parisiens SET email = 'nouveau@paris.fr' WHERE client_id = 42;

-- Ça échoue (le client deviendrait invisible) :
UPDATE vue_clients_parisiens SET ville = 'Lyon' WHERE client_id = 42;
-- "row violates check option for view"

-- Créer un rôle (PostgreSQL)
CREATE ROLE lecteur_ventes;
CREATE ROLE redacteur_ventes;
CREATE USER pierre WITH PASSWORD 'motdepassecomplexe';

-- MySQL
CREATE USER 'pierre'@'localhost' IDENTIFIED BY 'motdepassecomplexe';
CREATE ROLE 'redacteur_ventes';

-- Donner le droit de SELECT sur une vue
GRANT SELECT ON vue_commandes_detaillees TO lecteur_ventes;

-- Donner plusieurs droits sur une table
GRANT SELECT, INSERT, UPDATE ON client TO redacteur_ventes;

-- Donner tous les droits (attention !)
GRANT ALL PRIVILEGES ON commande TO administrateur;

-- Donner le droit de donner les droits (encore plus dangereux)
GRANT SELECT ON client TO pierre WITH GRANT OPTION;
-- Maintenant pierre peut donner ce droit à d'autres.

-- Retirer un droit spécifique
REVOKE INSERT ON client FROM redacteur_ventes;

-- Retirer tous les droits
REVOKE ALL PRIVILEGES ON commande FROM ancien_employe;

-- Retirer l'option GRANT
REVOKE GRANT OPTION FOR SELECT ON client FROM pierre;

-- Autoriser seulement sur certaines colonnes
GRANT SELECT (nom, prenom, email) ON client TO support;

-- Le support ne verra pas l'adresse, le téléphone, le salaire.
-- Une fenêtre étroite.

-- PostgreSQL
CREATE ROLE employe_base;
GRANT SELECT ON vue_clients_actifs TO employe_base;

CREATE ROLE commercial;
GRANT employe_base TO commercial; -- Héritage
GRANT SELECT, INSERT, UPDATE ON commande TO commercial;

-- MySQL
CREATE ROLE 'employe_base';
GRANT SELECT ON societe.vue_clients_actifs TO 'employe_base';
CREATE ROLE 'commercial';
GRANT 'employe_base' TO 'commercial';
GRANT SELECT, INSERT, UPDATE ON societe.commande TO 'commercial';

-- Voir les privilèges sur une table (PostgreSQL)
SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name = 'client';

-- Voir ses propres privilèges
SELECT * FROM information_schema.table_privileges 
WHERE grantee = CURRENT_USER;

-- Table réelle (inaccessible aux utilisateurs normaux)
CREATE TABLE salaries (
    employe_id INT PRIMARY KEY,
    salaire DECIMAL(10,2),
    prime DECIMAL(10,2),
    notes_evaluation TEXT
);

-- Vue pour l'employé (ne voit que son propre salaire)
CREATE VIEW vue_mon_salaire AS
SELECT employe_id, salaire, prime
FROM salaries
WHERE employe_id = CURRENT_USER_ID(); -- Fonction magique

-- Vue pour le manager (voit son équipe)
CREATE VIEW vue_salaires_equipe AS
SELECT s.*
FROM salaries s
JOIN employe e ON s.employe_id = e.employe_id
WHERE e.manager_id = CURRENT_USER_ID();

-- Grant
GRANT SELECT ON vue_mon_salaire TO PUBLIC;
GRANT SELECT ON vue_salaires_equipe TO ROLE manager;
REVOKE ALL ON salaries FROM PUBLIC; -- Personne ne touche à la table mère

-- PostgreSQL : fonction avec droits élevés mais accessible aux utilisateurs
CREATE FUNCTION creer_compte_client(nom TEXT, email TEXT) 
RETURNS INTEGER
SECURITY DEFINER -- S'exécute avec MES droits
AS $$
BEGIN
    INSERT INTO client (nom, email) VALUES (nom, email);
    RETURN LASTVAL();
END;
$$ LANGUAGE plpgsql;

-- Maintenant un utilisateur basique peut appeler cette fonction
GRANT EXECUTE ON FUNCTION creer_compte_client TO redacteur_ventes;
-- Il peut insérer dans client sans avoir le droit direct sur la table.

-- Activer RLS sur la table
ALTER TABLE commande ENABLE ROW LEVEL SECURITY;

-- Politique : un commercial ne voit que SES commandes
CREATE POLICY politique_commercial ON commande
FOR ALL -- SELECT, INSERT, UPDATE, DELETE
TO commercial
USING (commercial_id = CURRENT_USER_ID()); -- La magie

-- Politique : les managers voient tout
CREATE POLICY politique_manager ON commande
FOR ALL
TO manager
USING (true); -- Pas de filtre

-- Maintenant, même un SELECT * FROM commande filtrera automatiquement.

Utilisateurs : anon, client, admin

-- Vue publique (produits en stock)
CREATE VIEW vue_catalogue_public AS
SELECT id, nom, description, prix
FROM produit
WHERE en_stock = true AND actif = true;

-- Vue client (avec ses infos)
CREATE VIEW vue_mes_commandes AS
SELECT c.*, cl.nom
FROM commande c
JOIN client cl ON c.client_id = cl.client_id
WHERE cl.email = CURRENT_USER_EMAIL(); -- Fonction de l'application

-- Grants
GRANT SELECT ON vue_catalogue_public TO PUBLIC;
GRANT SELECT ON vue_mes_commandes TO ROLE client_web;
GRANT ALL ON vue_mes_commandes TO ROLE admin;
REVOKE ALL ON produit, commande, client FROM PUBLIC; -- Tables cachées

-- Vue "nettoyée" pour les analystes
CREATE VIEW vue_ventes_analytics AS
SELECT 
    c.commande_id,
    c.date_commande,
    c.montant_total,
    cl.region,
    EXTRACT(YEAR FROM c.date_commande) as annee,
    EXTRACT(MONTH FROM c.date_commande) as mois
FROM commande c
JOIN client cl ON c.client_id = cl.client_id
WHERE c.montant_total > 0 
  AND c.statut = 'terminee';

-- Masquer les clients spécifiques (RGPD)
CREATE VIEW vue_clients_anonymises AS
SELECT 
    client_id,
    CONCAT('Client_', client_id) as identifiant_anonyme,
    region,
    date_inscription
FROM client;

-- Grants stricts
GRANT SELECT ON vue_ventes_analytics TO ROLE analyste;
GRANT SELECT ON vue_clients_anonymises TO ROLE analyste;
-- Pas d'accès aux tables brutes. Jamais.

-- Toutes les données dans une table, séparées par tenant_id
CREATE TABLE donnees_tenants (
    tenant_id INTEGER NOT NULL,
    donnee_id SERIAL,
    contenu TEXT
);

-- Vue automatiquement filtrée par tenant
CREATE VIEW vue_mes_donnees AS
SELECT * FROM donnees_tenants 
WHERE tenant_id = CURRENT_TENANT_ID(); -- Variable de session

-- RLS encore mieux
ALTER TABLE donnees_tenants ENABLE ROW LEVEL SECURITY;
CREATE POLICY politique_tenant ON donnees_tenants
FOR ALL
TO PUBLIC
USING (tenant_id = CURRENT_SETTING('app.current_tenant')::INT);
-- Chaque connexion définit son tenant_id dans la session.

-- PostgreSQL post-install
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE ma_base FROM PUBLIC;

-- Créer un rôle d'application avec droits restreints
CREATE ROLE mon_app LOGIN PASSWORD 'complexe';
GRANT CONNECT ON DATABASE ma_base TO mon_app;
GRANT USAGE ON SCHEMA public TO mon_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON TOUTES_LES_TABLES_NEEDED TO mon_app;
-- Jamais ALL, jamais TRUNCATE, jamais DROP.

-- deploy_security.sql
BEGIN;

-- 1. Créer les rôles
CREATE ROLE lecteur;
CREATE ROLE redacteur;
CREATE ROLE admin_app;

-- 2. Créer les vues
CREATE VIEW vue_analytics AS ...;
CREATE VIEW vue_mes_donnees AS ...;

-- 3. Donner les droits sur les VUES, pas les tables
GRANT SELECT ON vue_analytics TO lecteur;
GRANT SELECT, INSERT, UPDATE ON vue_mes_donnees TO redacteur;
GRANT ALL ON vue_analytics, vue_mes_donnees TO admin_app;

-- 4. Retirer les droits sur les tables
REVOKE ALL ON client, commande, produit FROM PUBLIC;
REVOKE ALL ON client, commande, produit FROM lecteur, redacteur;

-- 5. Sécuriser le schéma
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 6. Configurer RLS si nécessaire (PostgreSQL)
ALTER TABLE donnees_sensibles ENABLE ROW LEVEL SECURITY;
CREATE POLICY politique_restrictive ON donnees_sensibles ...;

COMMIT;