stephan
f1e43c911b
This commit refactors the access control logic to replace references to 'inscriptions_mobile' with 'inscriptions_gestion', aligning the permission structure with the new management system. Changes include updates to SQL scripts, PHP functions, and documentation comments to ensure consistency in permission handling for event management. This enhances clarity and maintains the integrity of the access control system.
246 lines
10 KiB
SQL
246 lines
10 KiB
SQL
/*
|
|
MSIN - Acces evenement v2 (phase 2)
|
|
Multi-kits par compte/evenement + permissions a l'unite + granularite mobile.
|
|
|
|
Executer APRES :
|
|
MSIN-eve-acces-v2-phase1-tables.sql
|
|
MSIN-eve-acces-v2-phase1-seed.sql
|
|
MSIN-eve-acces-v2-bib-qr-seed.sql (si deja applique)
|
|
|
|
Safe en prod pilote : n'enleve aucun acces existant.
|
|
*/
|
|
|
|
SET NAMES utf8mb4;
|
|
|
|
|
|
/* ------------------------------------------------------------------
|
|
1. Niveau permission : page | action | api | report
|
|
------------------------------------------------------------------ */
|
|
/* Ajout idempotent : n'ajoute la colonne que si elle n'existe pas deja
|
|
(evite l'erreur 1060 - Duplicate column name si le script est relance). */
|
|
SET @col_exists := (
|
|
SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'inscriptions_eve_permissions'
|
|
AND COLUMN_NAME = 'perm_scope'
|
|
);
|
|
SET @ddl := IF(@col_exists = 0,
|
|
'ALTER TABLE `inscriptions_eve_permissions` ADD COLUMN `perm_scope` enum(''page'',''action'',''api'',''report'') NOT NULL DEFAULT ''api'' AFTER `perm_group`',
|
|
'DO 0');
|
|
PREPARE stmt FROM @ddl;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
UPDATE `inscriptions_eve_permissions` SET `perm_scope` = 'api'
|
|
WHERE `perm_group` = 'api';
|
|
|
|
UPDATE `inscriptions_eve_permissions` SET `perm_scope` = 'report'
|
|
WHERE `perm_group` = 'reports';
|
|
|
|
UPDATE `inscriptions_eve_permissions` SET `perm_scope` = 'page'
|
|
WHERE `perm_key` IN (
|
|
'inscriptions.view', 'inscriptions_gestion.view',
|
|
'dossards.view', 'epreuves.view', 'rabais.view', 'team.view'
|
|
);
|
|
|
|
UPDATE `inscriptions_eve_permissions` SET `perm_scope` = 'action'
|
|
WHERE `perm_key` IN (
|
|
'inscriptions.edit', 'dossards.manage', 'epreuves.edit_qte',
|
|
'rabais.manage', 'emails.send', 'registrations.scan',
|
|
'team.invite', 'team.manage', 'tools.qr_test'
|
|
);
|
|
|
|
|
|
/* ------------------------------------------------------------------
|
|
2. Multi-kits : plusieurs roles par compte x evenement
|
|
------------------------------------------------------------------ */
|
|
/* Retrait idempotent de l'ancien index unique (evite 1091 si deja retire). */
|
|
SET @idx_old := (
|
|
SELECT COUNT(*) FROM information_schema.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'inscriptions_eve_acces'
|
|
AND INDEX_NAME = 'uk_com_eve'
|
|
);
|
|
SET @ddl := IF(@idx_old > 0,
|
|
'ALTER TABLE `inscriptions_eve_acces` DROP INDEX `uk_com_eve`',
|
|
'DO 0');
|
|
PREPARE stmt FROM @ddl;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
/* Ajout idempotent du nouvel index unique (evite 1061 si deja present). */
|
|
SET @idx_new := (
|
|
SELECT COUNT(*) FROM information_schema.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'inscriptions_eve_acces'
|
|
AND INDEX_NAME = 'uk_com_eve_role'
|
|
);
|
|
SET @ddl := IF(@idx_new = 0,
|
|
'ALTER TABLE `inscriptions_eve_acces` ADD UNIQUE KEY `uk_com_eve_role` (`com_id`, `eve_id`, `role_id`)',
|
|
'DO 0');
|
|
PREPARE stmt FROM @ddl;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
|
|
/* ------------------------------------------------------------------
|
|
3. Permissions additionnelles a l'unite (hors kit)
|
|
------------------------------------------------------------------ */
|
|
CREATE TABLE IF NOT EXISTS `inscriptions_eve_acces_extra` (
|
|
`ae_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`com_id` int(10) unsigned NOT NULL,
|
|
`eve_id` int(10) unsigned NOT NULL,
|
|
`perm_key` varchar(64) NOT NULL,
|
|
`ae_statut` enum('actif','revoke') NOT NULL DEFAULT 'actif',
|
|
`ae_granted_by` int(10) unsigned DEFAULT NULL,
|
|
`ae_note` varchar(255) DEFAULT NULL,
|
|
`ae_created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`ae_updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
|
|
`ae_revoked_at` datetime DEFAULT NULL,
|
|
PRIMARY KEY (`ae_id`),
|
|
UNIQUE KEY `uk_com_eve_perm` (`com_id`, `eve_id`, `perm_key`),
|
|
KEY `idx_ae_eve_actif` (`eve_id`, `ae_statut`),
|
|
KEY `idx_ae_com_actif` (`com_id`, `ae_statut`),
|
|
CONSTRAINT `fk_ae_perm`
|
|
FOREIGN KEY (`perm_key`) REFERENCES `inscriptions_eve_permissions` (`perm_key`)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|
COMMENT='Grants permission unitaire v2 (complement des kits)';
|
|
|
|
|
|
/* ------------------------------------------------------------------
|
|
4. Permissions actions mobile (niveau fonction dans la fiche)
|
|
------------------------------------------------------------------ */
|
|
INSERT INTO `inscriptions_eve_permissions`
|
|
(`perm_key`, `perm_group`, `perm_scope`, `perm_label_fr`, `perm_label_en`,
|
|
`perm_description_fr`, `perm_description_en`, `perm_phase`, `perm_actif`, `perm_tri`)
|
|
VALUES
|
|
('inscriptions_gestion.statut_edit', 'inscriptions', 'action',
|
|
'Gestion inscription : statut', 'Registration mgmt: status',
|
|
'Modifier le statut course sur la fiche', NULL, 2, 1, 131),
|
|
('inscriptions_gestion.bib_edit', 'inscriptions', 'action',
|
|
'Gestion inscription : no dossard', 'Registration mgmt: bib number',
|
|
'Modifier le numero de dossard sur la fiche', NULL, 2, 1, 132),
|
|
('inscriptions_gestion.bib_remis', 'inscriptions', 'action',
|
|
'Gestion inscription : dossard remis', 'Registration mgmt: bib handed out',
|
|
'Marquer dossard remis sur la fiche', NULL, 2, 1, 133),
|
|
('inscriptions_gestion.edit', 'inscriptions', 'action',
|
|
'Gestion inscription : modifier', 'Registration mgmt: edit',
|
|
'Bouton modifier inscription sur la fiche', NULL, 2, 1, 134),
|
|
('inscriptions_gestion.cancel', 'inscriptions', 'action',
|
|
'Gestion inscription : annuler', 'Registration mgmt: cancel',
|
|
'Annuler une inscription depuis la fiche', NULL, 2, 1, 135),
|
|
('inscriptions_gestion.restore', 'inscriptions', 'action',
|
|
'Gestion inscription : retablir', 'Registration mgmt: restore',
|
|
'Retablir une inscription annulee depuis la fiche', NULL, 2, 1, 136),
|
|
('inscriptions_gestion.refund', 'inscriptions', 'action',
|
|
'Gestion inscription : remboursement', 'Registration mgmt: refund',
|
|
'Effectuer un remboursement PayPal depuis la fiche de gestion', NULL, 2, 1, 137)
|
|
ON DUPLICATE KEY UPDATE
|
|
`perm_group` = VALUES(`perm_group`),
|
|
`perm_scope` = VALUES(`perm_scope`),
|
|
`perm_label_fr` = VALUES(`perm_label_fr`),
|
|
`perm_label_en` = VALUES(`perm_label_en`),
|
|
`perm_description_fr` = VALUES(`perm_description_fr`),
|
|
`perm_phase` = VALUES(`perm_phase`),
|
|
`perm_actif` = VALUES(`perm_actif`),
|
|
`perm_tri` = VALUES(`perm_tri`);
|
|
|
|
|
|
UPDATE `inscriptions_eve_permissions` SET `perm_scope` = 'page'
|
|
WHERE `perm_key` = 'inscriptions_gestion.view';
|
|
|
|
|
|
/* ------------------------------------------------------------------
|
|
5. Kits : gestion_ops complet, gestion_statut (exemple granulaire)
|
|
------------------------------------------------------------------ */
|
|
INSERT INTO `inscriptions_eve_roles`
|
|
(`role_code`, `role_label_fr`, `role_label_en`, `role_description_fr`, `role_description_en`, `role_icone`, `role_systeme`, `role_actif`, `role_tri`)
|
|
VALUES
|
|
('gestion_statut', 'Gestion statuts seulement', 'Management status only',
|
|
'Fiche : consulter et modifier les statuts (pas le dossard)',
|
|
'Sheet: view and edit status only (not bib number)',
|
|
'fa-flag-checkered', 1, 1, 25)
|
|
ON DUPLICATE KEY UPDATE
|
|
`role_label_fr` = VALUES(`role_label_fr`),
|
|
`role_label_en` = VALUES(`role_label_en`),
|
|
`role_description_fr` = VALUES(`role_description_fr`),
|
|
`role_description_en` = VALUES(`role_description_en`),
|
|
`role_icone` = VALUES(`role_icone`),
|
|
`role_actif` = VALUES(`role_actif`),
|
|
`role_tri` = VALUES(`role_tri`);
|
|
|
|
|
|
DELETE erp FROM inscriptions_eve_role_permissions erp
|
|
INNER JOIN inscriptions_eve_roles r ON r.role_id = erp.role_id
|
|
WHERE r.role_code = 'owner';
|
|
|
|
INSERT INTO inscriptions_eve_role_permissions (role_id, perm_key)
|
|
SELECT r.role_id, p.perm_key
|
|
FROM inscriptions_eve_roles r
|
|
CROSS JOIN inscriptions_eve_permissions p
|
|
WHERE r.role_code = 'owner'
|
|
AND p.perm_actif = 1;
|
|
|
|
|
|
DELETE erp FROM inscriptions_eve_role_permissions erp
|
|
INNER JOIN inscriptions_eve_roles r ON r.role_id = erp.role_id
|
|
WHERE r.role_code = 'gestion_ops';
|
|
|
|
INSERT INTO inscriptions_eve_role_permissions (role_id, perm_key)
|
|
SELECT r.role_id, p.perm_key
|
|
FROM inscriptions_eve_roles r
|
|
INNER JOIN inscriptions_eve_permissions p
|
|
ON p.perm_key IN (
|
|
'events.list', 'events.view', 'registrations.view', 'registrations.scan',
|
|
'inscriptions_gestion.view',
|
|
'inscriptions_gestion.statut_edit', 'inscriptions_gestion.bib_edit',
|
|
'inscriptions_gestion.bib_remis', 'inscriptions_gestion.edit',
|
|
'inscriptions_gestion.cancel', 'inscriptions_gestion.restore'
|
|
)
|
|
AND p.perm_actif = 1
|
|
WHERE r.role_code = 'gestion_ops';
|
|
|
|
|
|
DELETE erp FROM inscriptions_eve_role_permissions erp
|
|
INNER JOIN inscriptions_eve_roles r ON r.role_id = erp.role_id
|
|
WHERE r.role_code = 'gestion_readonly';
|
|
|
|
INSERT INTO inscriptions_eve_role_permissions (role_id, perm_key)
|
|
SELECT r.role_id, p.perm_key
|
|
FROM inscriptions_eve_roles r
|
|
INNER JOIN inscriptions_eve_permissions p
|
|
ON p.perm_key IN (
|
|
'events.list', 'events.view', 'registrations.view', 'inscriptions_gestion.view'
|
|
)
|
|
AND p.perm_actif = 1
|
|
WHERE r.role_code = 'gestion_readonly';
|
|
|
|
|
|
DELETE erp FROM inscriptions_eve_role_permissions erp
|
|
INNER JOIN inscriptions_eve_roles r ON r.role_id = erp.role_id
|
|
WHERE r.role_code = 'gestion_statut';
|
|
|
|
INSERT INTO inscriptions_eve_role_permissions (role_id, perm_key)
|
|
SELECT r.role_id, p.perm_key
|
|
FROM inscriptions_eve_roles r
|
|
INNER JOIN inscriptions_eve_permissions p
|
|
ON p.perm_key IN (
|
|
'events.list', 'events.view', 'registrations.view', 'inscriptions_gestion.view',
|
|
'inscriptions_gestion.statut_edit', 'inscriptions_gestion.bib_remis'
|
|
)
|
|
AND p.perm_actif = 1
|
|
WHERE r.role_code = 'gestion_statut';
|
|
|
|
|
|
/* ------------------------------------------------------------------
|
|
6. Plus de vues SQL.
|
|
Les vues v_eve_acces_actif / v_eve_acces_permissions portaient un DEFINER
|
|
non copiable par Navicat / la releve. La logique (union kits multiples +
|
|
extras) est desormais en sous-requetes directement dans le code PHP
|
|
(php/inc_fx_eve_acces.php et v3_ci4/app/Libraries/EventAccess.php).
|
|
On supprime les vues si elles existent.
|
|
------------------------------------------------------------------ */
|
|
DROP VIEW IF EXISTS `v_eve_acces_permissions`;
|
|
DROP VIEW IF EXISTS `v_eve_acces_actif`;
|