Vues.sql

-- Vue offrant une vision complète pour l'administrateur

CREATE OR REPLACE VIEW V_Gestion_Totale AS
SELECT
    V.CVE_Id, 
    V.Description AS Desc_Vuln, 
    V.Score_CVSS,
    V.Date_pub AS Date_pub_vuln,
    V.Gravite,
    P.Nom AS Nom_Produit, 
    VP.Version,
    F.Nom AS Nom_Fournisseur, 
    F.Siteweb AS Site_Fournisseur,
    E.Id_exploit, 
    E.Description AS Desc_Exploit, 
    E.PoC, 
    E.Tag,
    AU.Nom AS Nom_Auteur, 
    AU.Email
FROM VULNERABILITE V
JOIN AFFECTE A ON V.CVE_Id = A.CVE_Id
JOIN VERSION_P VP ON A.Id_version = VP.IdVP
JOIN PRODUIT P ON VP.Id_produit = P.Id_produit
JOIN FOURNISSEUR F ON P.Id_fournisseur = F.Id_fournisseur
LEFT JOIN EXPLOIT E ON V.CVE_Id = E.CVE_Id
LEFT JOIN AUTEUR AU ON E.Id_auteur = AU.Id_auteur;




-- Vue offrant tout les détails sur les produits d'un fournisseur


CREATE OR REPLACE VIEW V_vuln_fournisseur AS
    SELECT 
        F.Nom AS Nom_Fournisseur, 
        F.Siteweb, 
        F.Description AS Fournisseur_desc, 
        F.NB_employer,
        P.Id_produit, 
        P.Nom AS Nom_produit, 
        P.Description AS Produit_desc, 
        P.vuln_count, 
        P.Date_pub AS Date_pub_produit,
        VP.IdVP, 
        VP.Date_pub AS Date_pub_VP, 
        VP.Version,
        V.CVE_Id, 
        V.Description AS Vuln_desc, 
        V.Score_CVSS, 
        V.Date_pub AS Date_pub_vuln, 
        V.Date_MAJ AS Date_MAJ_vuln, 
        V.Gravite,
        E.Id_exploit, 
        E.Description AS Exploit_desc, 
        E.PoC, 
        E.Tag, 
        E.Date_pub AS Date_pub_exploit, 
        E.Date_MAJ AS Date_MAJ_Exploit,
        AU.Id_auteur, 
        AU.Nom AS Nom_auteur, 
        AU.Affiliation, 
        AU.Email
    FROM Fournisseur F JOIN Produit P ON F.id_fournisseur = P.id_fournisseur
    JOIN Version_P VP ON P.id_produit = VP.id_produit
    LEFT JOIN Affecte A ON VP.IdVP = A.id_version
    LEFT JOIN VULNERABILITE V ON A.CVE_Id = V.CVE_Id
    LEFT JOIN Exploit E ON V.CVE_Id = E.CVE_Id
    LEFT JOIN Auteur AU ON E.id_auteur = AU.id_auteur;





-- Vue offrant des informations internes sur les produits d'un fournisseur}~


CREATE OR REPLACE VIEW V_entretien_fournisseur AS
    SELECT 
        F.Siteweb, 
        F.Nom AS Nom_fournisseur, 
        F.Description AS description_fournisseur, 
        F.NB_employer, 
        P.Nom AS Nom_produit, 
        P.Description AS desc_produit,
        VP.Version
    FROM Fournisseur F JOIN Produit P ON F.id_fournisseur = P.id_fournisseur
    JOIN Version_P VP ON P.id_produit = VP.id_produit;



-- Vue offrant toute les informations d'un auteur

CREATE OR REPLACE VIEW V_contrib_auteur AS
    SELECT 
        AU.Nom, 
        AU.Affiliation, 
        AU.Email, 
        AU.Nb_exploits,
        E.Description, 
        E.PoC, 
        E.Tag, 
        E.Date_pub, 
        E.Date_MAJ, 
        E.CVE_Id
    FROM Auteur AU JOIN Exploit E ON AU.id_auteur = E.id_auteur;




-- Vue offrant un résumé d'une vulnérabilité

CREATE OR REPLACE VIEW V_vuln_basic_info AS
    SELECT 
        V.CVE_Id, 
        V.Description AS description_vuln,
        Score_CVSS, 
        V.Date_pub,
        E.Description AS description_exploit, 
        E.Tag, 
        AU.nom
    FROM VULNERABILITE V 
    LEFT JOIN EXPLOIT E ON V.CVE_Id = E.CVE_Id
    LEFT JOIN AUTEUR AU ON E.id_auteur=AU.id_auteur;
    



-- Vue offrant des statistiques sur les produits d'un fournisseur

CREATE OR REPLACE VIEW V_Stats_Secu_Fournisseur AS
SELECT 
    F.Nom AS Fournisseur,
    COUNT(DISTINCT P.Id_produit) AS Nb_Produits_Total,
    COUNT(V.CVE_Id) AS Total_Vulnerabilites,
    ROUND(AVG(V.Score_CVSS), 2) AS Gravite_Moyenne,
    MAX(V.Score_CVSS) AS Score_Pire_Vuln
FROM FOURNISSEUR F
JOIN PRODUIT P ON F.Id_fournisseur = P.Id_fournisseur
JOIN VERSION_P VP ON P.Id_produit = VP.Id_produit
JOIN AFFECTE A ON VP.IdVP = A.Id_version
JOIN VULNERABILITE V ON A.CVE_Id = V.CVE_Id
GROUP BY F.Nom;