276 lines
13 KiB
SQL
276 lines
13 KiB
SQL
-- ============================================================================
|
|
-- PTA V7.11 — Accès par jeton, comptes gérés par la DSI et journal d'activité
|
|
-- ============================================================================
|
|
-- Trois apports, indissociables :
|
|
-- 1. un compte d'accès par utilisateur (compte_acces), identifié par un JETON
|
|
-- unique dont la base ne conserve qu'une empreinte SHA-256 ;
|
|
-- 2. un quatrième rôle DSI, seul habilité à créer, régénérer et désactiver
|
|
-- ces comptes, et à lire le journal ;
|
|
-- 3. un journal d'activité (journal_activite) alimenté par le code PHP.
|
|
--
|
|
-- POURQUOI « v7_9e » ALORS QUE C'EST LA V7.11 : les migrations sont jouées dans
|
|
-- l'ORDRE ALPHABÉTIQUE (docker/db/apply-migrations.sh). Un fichier nommé
|
|
-- « migration_v7_11_… » passerait AVANT « migration_v7_8_… », donc avant la
|
|
-- création des tables de droits qu'il doit étendre. Le préfixe suit donc la
|
|
-- dernière migration en date (v7_9d) plutôt que le numéro de version
|
|
-- fonctionnelle. Voir CLAUDE.md, section « Commandes ».
|
|
--
|
|
-- Prérequis : migration_v7_8_securite_acces.sql (tables acces_role_*).
|
|
-- ============================================================================
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- 1. LE RÔLE DSI ENTRE DANS LA MATRICE DES DROITS
|
|
-- ----------------------------------------------------------------------------
|
|
-- Un MODIFY d'ENUM est idempotent : rejouer la migration ne change rien.
|
|
ALTER TABLE acces_role_page
|
|
MODIFY COLUMN role ENUM('AGENT', 'RESPONSABLE_STRUCTURE', 'SERVICE_ENFANCE', 'DSI') NOT NULL;
|
|
|
|
ALTER TABLE acces_role_permission
|
|
MODIFY COLUMN role ENUM('AGENT', 'RESPONSABLE_STRUCTURE', 'SERVICE_ENFANCE', 'DSI') NOT NULL;
|
|
|
|
-- Les vues sont recréées après l'ALTER : leurs métadonnées de colonnes sont
|
|
-- figées à la définition, un ENUM élargi ne s'y propage pas tout seul.
|
|
CREATE OR REPLACE VIEW v_acces_role_page AS
|
|
SELECT role, page, ordre_affichage
|
|
FROM acces_role_page;
|
|
|
|
CREATE OR REPLACE VIEW v_acces_role_permission AS
|
|
SELECT role, permission, autorise
|
|
FROM acces_role_permission;
|
|
|
|
-- La DSI n'a qu'une page : la sienne. Elle n'accède NI à la saisie, NI aux
|
|
-- référentiels métier — c'est une administration technique, pas fonctionnelle.
|
|
INSERT INTO acces_role_page (role, page, ordre_affichage) VALUES
|
|
('DSI', 'dsi', 10)
|
|
ON DUPLICATE KEY UPDATE ordre_affichage = VALUES(ordre_affichage);
|
|
|
|
-- Deux permissions nouvelles, refusées explicitement aux trois rôles métier :
|
|
-- une ligne à FALSE documente le refus, là où une ligne absente le laisse
|
|
-- deviner (une permission inconnue vaut de toute façon « refusé »).
|
|
INSERT INTO acces_role_permission (role, permission, autorise) VALUES
|
|
('DSI', 'can_manage_access', TRUE),
|
|
('DSI', 'can_read_journal', TRUE),
|
|
('DSI', 'read_only', FALSE),
|
|
('DSI', 'can_edit_draft', FALSE),
|
|
('DSI', 'can_edit_cross_structure', FALSE),
|
|
('DSI', 'can_validate', FALSE),
|
|
('DSI', 'can_reopen', FALSE),
|
|
('DSI', 'can_use_templates', FALSE),
|
|
('DSI', 'can_copy_full_week', FALSE),
|
|
('DSI', 'can_view_pending', FALSE),
|
|
('DSI', 'can_manage_coverage', FALSE),
|
|
('DSI', 'can_manage_agents', FALSE),
|
|
('DSI', 'can_manage_structures', FALSE),
|
|
('DSI', 'can_manage_vacations', FALSE),
|
|
('DSI', 'can_manage_teams', FALSE),
|
|
('DSI', 'can_manage_pta', FALSE),
|
|
('DSI', 'can_forecast_next_year', FALSE),
|
|
|
|
('AGENT', 'can_manage_access', FALSE),
|
|
('AGENT', 'can_read_journal', FALSE),
|
|
('RESPONSABLE_STRUCTURE', 'can_manage_access', FALSE),
|
|
('RESPONSABLE_STRUCTURE', 'can_read_journal', FALSE),
|
|
('SERVICE_ENFANCE', 'can_manage_access', FALSE),
|
|
('SERVICE_ENFANCE', 'can_read_journal', FALSE)
|
|
ON DUPLICATE KEY UPDATE autorise = VALUES(autorise);
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- 2. COMPTES D'ACCÈS — un jeton par utilisateur, jamais de mot de passe
|
|
-- ----------------------------------------------------------------------------
|
|
-- Le jeton complet n'est JAMAIS stocké : la base ne garde qu'une empreinte
|
|
-- SHA-256 et les huit premiers caractères, qui servent uniquement à reconnaître
|
|
-- une ligne dans la liste. Un jeton perdu ne se retrouve pas, il se régénère.
|
|
-- Une fuite du dump SQL ne donne donc aucun accès.
|
|
--
|
|
-- L'empreinte n'est pas salée, et c'est délibéré : le jeton est tiré au sort sur
|
|
-- 120 bits par le serveur, il n'est pas devinable par force brute comme le
|
|
-- serait un mot de passe choisi par un humain. L'absence de sel est ce qui
|
|
-- permet de retrouver le compte en une lecture d'index à la connexion.
|
|
--
|
|
-- id_agent / id_structure sont UNIQUE tout en étant NULLables : MariaDB tolère
|
|
-- autant de NULL que voulu dans un index unique. Un agent ne peut donc avoir
|
|
-- qu'un seul compte, un lieu qu'un seul compte responsable, tandis que les
|
|
-- comptes SERVICE_ENFANCE et DSI (les deux colonnes à NULL) sont libres en
|
|
-- nombre.
|
|
CREATE TABLE IF NOT EXISTS compte_acces (
|
|
id_compte INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
role ENUM('AGENT', 'RESPONSABLE_STRUCTURE', 'SERVICE_ENFANCE', 'DSI') NOT NULL,
|
|
libelle VARCHAR(180) NOT NULL COMMENT 'Titulaire du jeton, tel qu''affiché dans la liste',
|
|
id_agent INT UNSIGNED NULL COMMENT 'Obligatoire pour un compte AGENT',
|
|
id_structure INT UNSIGNED NULL COMMENT 'Obligatoire pour un compte RESPONSABLE_STRUCTURE',
|
|
jeton_empreinte CHAR(64) NOT NULL COMMENT 'SHA-256 hexadécimal du jeton',
|
|
jeton_prefixe VARCHAR(12) NOT NULL COMMENT 'Début du jeton, pour le reconnaître sans le révéler',
|
|
actif BOOLEAN NOT NULL DEFAULT TRUE,
|
|
commentaire VARCHAR(255) NULL,
|
|
date_creation DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
date_rotation DATETIME NULL COMMENT 'Dernière régénération du jeton',
|
|
date_derniere_connexion DATETIME NULL,
|
|
nombre_connexions INT UNSIGNED NOT NULL DEFAULT 0,
|
|
|
|
CONSTRAINT uk_compte_empreinte UNIQUE (jeton_empreinte),
|
|
CONSTRAINT uk_compte_agent UNIQUE (id_agent),
|
|
CONSTRAINT uk_compte_structure UNIQUE (id_structure),
|
|
INDEX idx_compte_role_actif (role, actif),
|
|
CONSTRAINT fk_compte_agent FOREIGN KEY (id_agent)
|
|
REFERENCES agent (id_agent) ON DELETE CASCADE,
|
|
CONSTRAINT fk_compte_structure FOREIGN KEY (id_structure)
|
|
REFERENCES structure (id_structure) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- 3. COMPTE DSI D'INSTALLATION
|
|
-- ----------------------------------------------------------------------------
|
|
-- Problème de l'œuf et de la poule : les jetons ne sont distribuables que par la
|
|
-- DSI, qui a elle-même besoin d'un jeton pour entrer. Cette ligne est la porte
|
|
-- d'entrée initiale.
|
|
--
|
|
-- JETON D'INSTALLATION : PTA-INSTALLATION-DSI
|
|
--
|
|
-- Il est public — il est écrit ici, dans un fichier versionné. La page DSI
|
|
-- affiche un avertissement rouge tant que ce compte porte encore cette
|
|
-- empreinte : LE RÉGÉNÉRER EST LA PREMIÈRE ACTION À FAIRE APRÈS L'INSTALLATION.
|
|
--
|
|
-- L'identifiant 1 est forcé pour que le rejeu de la migration ne recrée pas un
|
|
-- second compte d'installation après régénération du jeton (l'empreinte, elle,
|
|
-- aura changé). Supprimer la ligne 1 puis rejouer la migration est d'ailleurs la
|
|
-- procédure de secours documentée en cas de perte du jeton DSI.
|
|
INSERT INTO compte_acces
|
|
(id_compte, role, libelle, jeton_empreinte, jeton_prefixe, commentaire)
|
|
VALUES
|
|
(1, 'DSI', 'Compte DSI d''installation',
|
|
'7f61ffc8efed4ed46d581a98286115ad03bb0122ffc70db3398584348aca431d',
|
|
'PTA-INSTALL',
|
|
'Jeton d''installation public : à régénérer immédiatement.')
|
|
ON DUPLICATE KEY UPDATE id_compte = id_compte;
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- 4. JOURNAL D'ACTIVITÉ
|
|
-- ----------------------------------------------------------------------------
|
|
-- Alimenté exclusivement par App\Core\Journal, jamais par des déclencheurs SQL :
|
|
-- le journal doit nommer l'UTILISATEUR à l'origine de l'action, information que
|
|
-- la base n'a pas (la connexion MariaDB est la même pour tout le monde).
|
|
--
|
|
-- Sont tracés : les connexions et déconnexions, toutes les écritures en base et
|
|
-- tous les refus d'accès. Les lectures ne le sont pas — c'est un choix de
|
|
-- volume : elles représenteraient l'essentiel des lignes pour une valeur
|
|
-- d'audit très faible.
|
|
--
|
|
-- L'acteur est FIGÉ au moment de l'action (rôle, libellé) : renommer un agent ou
|
|
-- supprimer son compte ne doit pas réécrire l'histoire. C'est aussi pourquoi la
|
|
-- clé étrangère est en ON DELETE SET NULL et non en CASCADE.
|
|
CREATE TABLE IF NOT EXISTS journal_activite (
|
|
id_journal BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
date_evenement DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
categorie ENUM('CONNEXION', 'ECRITURE', 'ACCES_REFUSE', 'ADMINISTRATION', 'SYSTEME') NOT NULL,
|
|
action VARCHAR(60) NOT NULL COMMENT 'Code stable de l''action, ex. PLANNING_VALIDE',
|
|
resultat ENUM('SUCCES', 'REFUS', 'ERREUR') NOT NULL DEFAULT 'SUCCES',
|
|
id_compte INT UNSIGNED NULL,
|
|
role VARCHAR(30) NULL COMMENT 'Rôle au moment de l''action, figé',
|
|
acteur VARCHAR(180) NULL COMMENT 'Libellé de l''utilisateur au moment de l''action, figé',
|
|
cible_table VARCHAR(64) NULL COMMENT 'Table principale touchée par l''écriture',
|
|
cible_id INT UNSIGNED NULL,
|
|
description VARCHAR(500) NULL,
|
|
contexte JSON NULL COMMENT 'Détail structuré, ex. semaine et lieu concernés',
|
|
adresse_ip VARCHAR(45) NULL,
|
|
agent_utilisateur VARCHAR(255) NULL,
|
|
|
|
INDEX idx_journal_date (date_evenement),
|
|
INDEX idx_journal_categorie (categorie, date_evenement),
|
|
INDEX idx_journal_compte (id_compte, date_evenement),
|
|
INDEX idx_journal_cible (cible_table, cible_id),
|
|
INDEX idx_journal_ip (adresse_ip, date_evenement),
|
|
CONSTRAINT fk_journal_compte FOREIGN KEY (id_compte)
|
|
REFERENCES compte_acces (id_compte) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- 5. VUES
|
|
-- ----------------------------------------------------------------------------
|
|
-- Comme pour la V7.8, le PHP interroge des vues et non les tables : l'identité
|
|
-- réelle derrière un compte (nom de l'agent, nom du lieu) et son activité sont
|
|
-- résolues en SQL, une seule fois, au même endroit.
|
|
CREATE OR REPLACE VIEW v_acces_compte AS
|
|
SELECT
|
|
c.id_compte,
|
|
c.role,
|
|
c.libelle,
|
|
c.id_agent,
|
|
c.id_structure,
|
|
c.jeton_empreinte,
|
|
c.jeton_prefixe,
|
|
c.actif,
|
|
c.commentaire,
|
|
c.date_creation,
|
|
c.date_rotation,
|
|
c.date_derniere_connexion,
|
|
c.nombre_connexions,
|
|
a.matricule,
|
|
a.nom AS agent_nom,
|
|
a.prenom AS agent_prenom,
|
|
s.code AS structure_code,
|
|
s.nom AS structure_nom,
|
|
-- Libellé affiché : l'identité VIVE pour un agent ou un lieu (un agent
|
|
-- renommé doit apparaître sous son nouveau nom), le libellé saisi pour les
|
|
-- comptes Service Enfance et DSI, qui ne pointent vers rien.
|
|
CASE c.role
|
|
WHEN 'AGENT' THEN CONCAT(a.prenom, ' ', a.nom)
|
|
WHEN 'RESPONSABLE_STRUCTURE' THEN s.nom
|
|
ELSE c.libelle
|
|
END AS identite_libelle,
|
|
-- Un compte reste utilisable tant que l'identité qu'il porte est active :
|
|
-- désactiver un agent coupe son accès sans avoir à toucher à son compte.
|
|
CASE c.role
|
|
WHEN 'AGENT' THEN COALESCE(a.actif, 0)
|
|
WHEN 'RESPONSABLE_STRUCTURE' THEN COALESCE(s.actif, 0)
|
|
ELSE 1
|
|
END AS identite_active
|
|
FROM compte_acces c
|
|
LEFT JOIN agent a ON a.id_agent = c.id_agent
|
|
LEFT JOIN structure s ON s.id_structure = c.id_structure;
|
|
|
|
-- Identités actives encore dépourvues de compte : alimente le bouton
|
|
-- « générer les jetons manquants » de la page DSI, qui permet d'équiper d'un
|
|
-- coup tous les agents et tous les lieux lors de la mise en service.
|
|
CREATE OR REPLACE VIEW v_acces_compte_manquant AS
|
|
SELECT
|
|
'AGENT' AS role,
|
|
a.id_agent AS id_agent,
|
|
CAST(NULL AS UNSIGNED) AS id_structure,
|
|
CONCAT(a.prenom, ' ', a.nom) AS libelle
|
|
FROM agent a
|
|
LEFT JOIN compte_acces c ON c.id_agent = a.id_agent
|
|
WHERE a.actif = 1 AND c.id_compte IS NULL
|
|
UNION ALL
|
|
SELECT
|
|
'RESPONSABLE_STRUCTURE',
|
|
CAST(NULL AS UNSIGNED),
|
|
s.id_structure,
|
|
s.nom
|
|
FROM structure s
|
|
LEFT JOIN compte_acces c ON c.id_structure = s.id_structure
|
|
WHERE s.actif = 1 AND c.id_compte IS NULL;
|
|
|
|
-- Journal enrichi du compte encore existant, quand il existe. Le libellé figé
|
|
-- (`acteur`) reste la source d'affichage : la jointure ne sert qu'à retrouver le
|
|
-- préfixe du jeton et à filtrer par compte.
|
|
CREATE OR REPLACE VIEW v_journal_activite AS
|
|
SELECT
|
|
j.id_journal,
|
|
j.date_evenement,
|
|
j.categorie,
|
|
j.action,
|
|
j.resultat,
|
|
j.id_compte,
|
|
j.role,
|
|
j.acteur,
|
|
j.cible_table,
|
|
j.cible_id,
|
|
j.description,
|
|
j.contexte,
|
|
j.adresse_ip,
|
|
j.agent_utilisateur,
|
|
c.jeton_prefixe,
|
|
c.actif AS compte_actif
|
|
FROM journal_activite j
|
|
LEFT JOIN compte_acces c ON c.id_compte = j.id_compte;
|