Respect au niveau SQL des cardinalités 1,n du MCD merisien

**CinePhil** · 10/03/2024, 01h32

Envoyé par fsmrel

Aïe, aïe ! MySQL refuse les triggers sur des vues ! C’est quand même malheureux, bridage intentionnel de la part de l’éditeur ?

J'avais écrit plus haut :

Envoyé par CinéPhil

Il me semble que ce que tu proposes pour SQL Server n'est pas possible encore pour MySQL et MariaDb. Sauf méconnaissance des dernières évolutions de ma part, il est impossible sur ces SGBD d'écrire un insert sur une vue.

En cherchant un peu, je trouve ici ce texte :

Envoyé par zestedesavoir

Modification des données d'une vue

On a tendance à penser que les vues ne servent que pour la sélection de données. En réalité, il est possible de modifier, insérer et supprimer des données par l'intermédiaire d'une vue. Les requêtes sont les mêmes que pour insérer, modifier et supprimer des données à partir d'une table (si ce n'est qu'on met le nom de la vue au lieu du nom de la table bien sûr).

Cependant, pour qu'une vue ne soit pas en "lecture seule", elle doit répondre à une série de conditions.
Conditions pour qu'une vue permette de modifier des données (requêtes UPDATE)
Jointures

Il est possible de modifier des données à partir d'une vue définie avec une jointure, à condition de ne modifier qu'une seule table.

Il y a d'autres conditions un peu plus bas pour l'insertion et la suppression de données.

Concernant les triggers sur les vues, la doc de MariaDB est claire : c'est impossible !

Envoyé par MariaDB

You cannot associate a trigger with a TEMPORARY table or a view.

**CinePhil** · 10/03/2024, 02h44

Envoyé par Paprick

Bonsoir à tous,

Voici un sujet particulièrement intéressant

!!!
En effet, lors du passage du MCD au MLD/LDD, on ne fait guère cas de la différence entre une cardinalité 1,n ou 0,n

...
J'aimerais ramener le sujet à un exemple plus simple et particulièrement significatif. En effet, autant il est fréquent qu'il soit acceptable de négliger cette différence (par exemple, créer un client avant de lui créer ses factures, ou créer un enseignant avant de lui affecter ses cours, ...), autant il n'est pas envisageable qu'une facture puisse exister sans au moins une ligne de facture.
J'aimerai donc traiter cette situation avec mes étudiants, et comme vous vous êtes lancés de manière efficace sur le sujet, pourriez-vous me proposer une solution la plus simple et la plus générique possible (et donc acceptable, entre autre, par MySQL) pour le cas standard suivant :
Pièce jointe 651835
Looping génère le LDD suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE Facture(
   NumFac SMALLINT,
   DateF DATE,
   PRIMARY KEY(NumFac)
);

CREATE TABLE Produit(
   Réf CHAR(3),
   Désignation VARCHAR(50),
   Prix DECIMAL(19,4),
   PRIMARY KEY(Réf)
);

CREATE TABLE LigneFac(
   NumFac SMALLINT,
   Réf CHAR(3),
   Quantité INT,
   PRIMARY KEY(NumFac, Réf),
   FOREIGN KEY(NumFac) REFERENCES Facture(NumFac) ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY(Réf) REFERENCES Produit(Réf) ON DELETE CASCADE ON UPDATE CASCADE);

La CASCADE en cas de suppression de la facture est bien prise en compte par Looping, mais l'inverse reste à faire, et surtout l'insertion obligatoire d'une ligne n'est pas traitée.
Bref, on est en plein dans votre sujet, mais avec un seul côté à traiter, car un produit peut exister même s'il n'existe plus dans la facture.
J'ai donc rajouter une règle associée à LigneFac au sein de laquelle du code SQL peut être saisi afin que Looping l'intègre ensuite dans le LDD.

Je suis donc en quête d'une solution la plus simple et pédagogique possible pour nos jeunes étudiants avides de savoir (si, si, on peut rêver

), mais sans grande maîtrise de la bête SQL

?
Merci !

J'étais sur le point d'aller me coucher mais vous m'avez réveillé avec vos histoires de cardinalités 1,n - 1,n !

Paprick, j'ai pris ton DDL généré par Looping et l'ai injecté dans ma base test sur MariaDB (Version du serveur : 10.11.7-MariaDB - Mageia MariaDB Server).

Tout d'abord, comme conseillé plus haut, je crée un user spécifique puis je ne lui attribue pour commencer qu'un privilège SELECT afin qu'Hilarion, muni du mot de passe de ce user, ne puisse pas contourner ce que nous allons développer :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
CREATE USER 'test'@'localhost' IDENTIFIED BY '***';
GRANT SELECT ON test.* TO 'test'@'localhost';

Comme MySQL/MariaDB est relativement limité, je propose donc de créer une procédure qui sera le seul moyen d'insérer la facture avec sa première ligne dans la base de données.
Puisqu'on doit faire le plus simple possible, je ne vais pas programmer la génération du numéro de facture (qui sera donc un paramètre d'entrée de la procédure) ni le contrôle de cohérence des données.

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
DELIMITER //
CREATE OR REPLACE PROCEDURE pr_creation_facture
(
    IN numFacture SMALLINT, -- Numéro de la facture => Pourrait être généré automatiquement dans la procédure
    IN refProduit CHAR(3), -- Référence du produit apparaissant sur la première ligne de la facture
    IN designationProduit VARCHAR(50), -- Désignation du produit apparaissant sur la première ligne de la facture
    IN prixUnitaireProduit DECIMAL(19,4), -- Prix du produit
    IN quantiteProduit INTEGER -- Quantité facturée du produit
)
/************************************************************************************************
Base :			test
Objet :			Procédure
Auteur :		Philippe Leménager
Description : 	Enregistre une nouvelle facture et sa première ligne
Utilise :		Facture - Table des factures
				Produit - Table des produits
				LigneFac - Table des lignes de facture
Historique :	
V 1.0 - plemenager - 2024-03-10 - Création en mode "la plus simple"
************************************************************************************************/
BEGIN
	DECLARE nb INTEGER DEFAULT 0;
 
    -- Enregistrement de la facture
    INSERT INTO Facture (NumFac, DateF)
    VALUES (numFacture, CURRENT_DATE);
 
    -- Recherche si la référence du produit existe déjà
    SELECT COUNT(*) INTO nb
    FROM Produit 
    WHERE Réf = refProduit;
 
    IF nb = 0 THEN
    	-- Le produit n'existe pas encore dans la BDD => On l'insère
        INSERT INTO Produit (Réf, Désignation, Prix)
        VALUES (refProduit, designationProduit, prixUnitaireProduit);
    END IF;
 
    -- Insertion de la première ligne de facture 
    INSERT INTO LigneFac (NumFac, Réf, Quantité)
    VALUES (numFacture, refProduit, quantiteProduit);
END; //

Pour que le user test puisse utiliser la procédure, il faut lui accorder le privilège EXECUTE :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

GRANT EXECUTE ON `test`.* TO 'test'@'localhost';

Je me suis reconnecté à phpMyAdmin avec le user test puis...
1) Tentative d'insertion directe d'une facture par Hilarion le pirate :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO `Facture` (`NumFac`, `DateF`) VALUES ('1', '2024-03-08')

=>

MySQL a répondu :
#1142 - La commande 'INSERT' est interdite à l'utilisateur: 'test'@'localhost' sur la table `test`.`Facture`

2) Utilisation de la procédure pour enregistrer la première facture :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

CALL pr_creation_facture (1, 'PR1', 'Produit 1', 199.90, 2);

=> Ça fonctionne.

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT * FROM Facture;
1 	2024-03-10
 
SELECT * FROM Produit;
PR1 	Produit 1 	199.9000
 
SELECT * FROM LigneFac;
1 	PR1 	2

3) Autre test ; je crée une seconde facture avec la même référence de produit mais une autre description et un autre prix :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
CALL pr_creation_facture (2, 'PR1', 'Autre désignation bidon', 1.50, 10);
 
SELECT * FROM `Facture` ;
1 	2024-03-10
2 	2024-03-10
 
SELECT * FROM `Produit` ;
PR1 	Produit 1 	199.9000 <== La référence du produit existant déjà, pas de nouveau produit créé et l'existant n'est pas modifié.
 
SELECT * FROM `LigneFac` ;
1 	PR1 	2
2 	PR1 	10

Bien sûr, tout ça est largement améliorable !

Remarque : Les noms de colonnes avec des lettres accentuées, c'est pas bien !

**fsmrel** · 10/03/2024, 21h16

Hello !

Envoyé par CinePhil

J'étais sur le point d'aller me coucher mais vous m'avez réveillé avec vos histoires de cardinalités 1,n - 1,n !

En tout cas, bravo Philippe !

Beau boulot. J’ai repris ton code, ce qui me permet de ne pas réinventer l’eau chaude...

J’ai demandé à Hilarion de secouer les factures, il n’a rien pu faire, le système l’a envoyé aux pelotes.

Merci à toi !

**escartefigue** · 11/03/2024, 09h24

Envoyé par CinePhil

J'étais sur le point d'aller me coucher mais vous m'avez réveillé avec vos histoires de cardinalités 1,n - 1,n !

Je me suis reconnecté à phpMyAdmin avec le user test puis...
1) Tentative d'insertion directe d'une facture par Hilarion le pirate :
=>

2) Utilisation de la procédure pour enregistrer la première facture :
=> Ça fonctionne.

Bien joué ~~Callaghan~~

Philippe !

**fsmrel** · 14/03/2024, 23h55

Salve !

@Philippe

Selon ton approche, pour qu’Hilarion ne viole pas la règle (1,n) tu l’habilites bien entendu à consulter sans modération le contenu des tables, mais tu le forces à utiliser la procédure pr_creation_facture s’il a besoin de créer une facture.

Question : Avant envoi de la facture c’est-à-dire quand elle est encore en cours de fabrication, si l’on veut modifier une donnée non vitale de cette facture, ou ajouter une ligne, que de précautions à prendre ! (et surtout à prévoir, sous l’oeil attentif des comptables !)

Comme l’écrit le Capitaine (cf. son post ici, du point de vue technique, les factures sont à manipuler avec bien des précautions, c’est de la dynamite...

En fouillant, j’ai retrouvé ton post d’il y a 14 ans et les échanges qui s’ensuivirent.

Vu tout ça, prudemment, je préfère basculer du côté des commandes, a priori moins sujettes à polémique.... Supposons donc que l’on puisse modifier certaines données d’une commande, voire ajouter et supprimer des lignes de commande : des procédures ad-hoc sont à prévoir.

Quoi qu’il en soit, il faudra habiliter les collègues d’Hilarion pour l’utilisation de la procédure, mais comment procède-t-on avec MySQL ? GRANT au cas par cas ? GRANT global ?

Je note en passant qu’Hilarion et ses collègues ne peuvent que consulter les tables de la base de données (schéma MySQL), quelles qu’elles soient. Comment habiliter tout ce joli monde pour la mise à jour des tables ?

Etant totalement ignare en matière d’habilitation en général et mysqlement

en particulier, quel serait ton avis ?

En tout cas, merci Philippe, je te trouve bien courageux.

Pour mes tests, j’ai créé le MCD suivant :

Code SQL des tables correspondantes (base de données "commandes") :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
create table Client
(
   clientId smallint not null
 , clientNumero int not null
 , clientNom varchar(24) not null
 , constraint Client_PK primary key(clientId)
 , constraint Client_AK unique(clientNumero)
) ;
create table Produit
(
   produitId smallint not null
 , produitCode char(4) not null
 , produitNom varchar(24) not null
 , produitMontant smallint not null
 , constraint Produit_PK primary key(produitId)
 , constraint Produit_AK unique(produitCode)
) ;
create table Commande
(
   commandeId int not null default 0
 , commandeNumero varchar(10) not null
 , commandeDate date not null
 , clientId smallint not null
 , constraint Commande_PK primary key(commandeId)
 , constraint Commande_AK unique(commandeNumero)
 , constraint Commande_Client_FK foreign key(clientId) references Client(clientId)
);
 
create table LigneCommande
(
   commandeId int not null
 , ligneCommandeId smallint not null
 , quantite smallint not null
 , produitId smallint not null
 , constraint LigneCommande_PK primary key(commandeId, ligneCommandeId)
 , constraint LigneCommande_Commande_FK foreign key(commandeId) 
     references Commande(commandeId) on delete cascade
 , constraint LigneCommande_Produit_FK foreign key(produitId) 
     references Produit(produitId)
) ;

Pour la suite, je me suis inspiré de ce que tu as proposé.

La procédure pour mettre à jour les tables :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
use commandes ;
drop procedure if exists clients_proc ;
delimiter //
create procedure clients_proc 
(
 
  in cliNumero int,
  in cliNom varchar(24),
  in cdeNumero varchar(10),
  in qte smallint,
  in prodCode char(4)
)
 
begin
  declare cliId int ;
  declare cdeId int ;
  declare ligneId int ;
  declare prodId int ;
 
  set cliId = (select max(clientId) + 1 from Client) ;
 
  if cliId is null then 
    set cliId = 1 ;
  end if ;
 
  insert into Client (clientId, clientNumero, clientNom)
    values (cliId, cliNumero, cliNom)
  ; 
 
  set cdeId = (select max(commandeId) + 1 from Commande) ;
 
  if cdeId is null then 
    set cdeId = 1 ;
  end if ;
 
  insert into Commande (commandeId, commandeNumero, commandeDate, clientId)
    values (cdeId, cdeNumero, current_date, cliId)
  ; 
 
  set ligneId = 1 ;
 
  set prodId = (select produitId from Produit where produitCode = prodCode) 
  ; 
  insert into ligneCommande (commandeId, ligneCommandeId, quantite, produitId)
    values (cdeId, ligneId, qte, prodId)
  ;
 
end ; //

j’habilite par exemple mimile pour la base de données "commandes" :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
grant select on commandes.* TO 'mimile'@'localhost'; 
 
grant execute on commandes.* TO 'mimile'@'localhost';

Création par mimile d’un client, d’une commande et d’une ligne de commande :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
call clients_proc 
  (
      8          -- numéro de client
    , 'Fernand'  -- nom du client
    , 1          -- numéro de commande 
    , 140        -- quantité
    , 'p002'     -- code produit
  );

Maintenant, je mets mon casque lourd...

**Paprick** · 15/03/2024, 01h25

Bonsoir,

Tout à fait d'accord : ne melons pas les affaires comptables à notre problématique. Le plus simple consiste alors à parler de devis.
De plus, comme je l'ai indiqué plus haut, ne nous prenons pas la tête avec les histoires d'identifiants non significatifs : le numéro de devis et la référence du produit sont des identifiants, certes signifiants, mais néanmoins pérennes.
Je vous propose donc de nous recadrer sur le modèle suivant :

Nom : MCD Facturation - Respect Contraintes.jpg
Affichages : 186
Taille : 12,7 Ko

Voici le MLD correspondant :

Nom : MLD Facturation - Respect Contraintes.jpg
Affichages : 184
Taille : 17,2 Ko

Et le LDD :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE Devis(
   NumDevis SMALLINT,
   DateD DATE,
   PRIMARY KEY(NumDevis)
);

CREATE TABLE Produit(
   RefProduit CHAR(3),
   Designation VARCHAR(50),
   Prix DECIMAL(19,4),
   PRIMARY KEY(RefProduit)
);

CREATE TABLE LigneDevis(
   NumDevis SMALLINT,
   RefProduit CHAR(3),
   Quantite INT,
   PRIMARY KEY(NumDevis, RefProduit),
   FOREIGN KEY(NumDevis) REFERENCES Devis(NumDevis) ON DELETE CASCADE,
   FOREIGN KEY(RefProduit) REFERENCES Produit(RefProduit) ON DELETE CASCADE
);

Philippe : fini les accents dans le MLD et le LDD

!

J'imagine plusieurs éléments à prendre en compte pour ce "petit" problème :

La gestion des suppressions en CASCADE des lignes de devis en cas de suppression d'un devis ou d'un produit : normalement, c'est réglé par le ON DELETE CASCADE dans le CREATE TABLE de LigneDevis.
Sans gérer les accès au niveau de l'administration, n'est-il pas possible de définir des TRIGGER qui "détournerait" un INSERT d'un devis vers la procédure stockée définie par Philippe ?
Interdire la suppression d'une ligne Devis si c'est la seule.

Bref, essayer d'être vraiment exhaustif sur un cas le plus simple possible.
Cela vous parait jouable

?

**CinePhil** · 15/03/2024, 02h29

Salut les noctambules !

Je réponds vite fait parce qu'il est encore 1h passée et je reviendrai ultérieurement pour répondre plus complètement.

Envoyé par fsmrel

Vu tout ça, prudemment, je préfère basculer du côté des commandes, a priori moins sujettes à polémique.... Supposons donc que l’on puisse modifier certaines données d’une commande, voire ajouter et supprimer des lignes de commande : des procédures ad-hoc sont à prévoir.

Quoi qu’il en soit, il faudra habiliter les collègues d’Hilarion pour l’utilisation de la procédure, mais comment procède-t-on avec MySQL ? GRANT au cas par cas ? GRANT global ?

Je reprends mon GRANT :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

GRANT SELECT ON test.* TO 'test'@'localhost';

test.* signifie "tous les objets de la BDD test".
On peut très bien limiter l'attribution de privilège à un objet :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
GRANT SELECT ON test.facture TO 'hilarion'@'localhost';
GRANT EXECUTE ON test.pr_creation_facture TO 'hilarion'@'localhost';

Envoyé par Paprick

J'imagine plusieurs éléments à prendre en compte pour ce "petit" problème :

La gestion des suppressions en CASCADE des lignes de devis en cas de suppression d'un devis ou d'un produit : normalement, c'est réglé par le ON DELETE CASCADE dans le CREATE TABLE de LigneDevis.

Autant la CASCADE sur la clé étrangère NumDevis référençant le devis dans la table LigneDevis est logique (une ligne de devis ne peut pas exister sans un devis), autant je mettrais plutôt un RESTRICT sur la clé étrangère RefProduit référençant le produit dans la ligne de devis : un produit qui a été mis dans un devis ne doit normalement plus être supprimé.

À la rigueur, ont pourrait imaginer (j'ai fait un cas similaire sur un autre sujet) que le produit puisse être supprimé s'il n'apparaît plus dans aucun devis. Par exemple si on supprime au bout d'un certain temps les vieux devis qui n'ont pas donné lieu à une commande et par voie de conséquence les produits qui n'ont jamais été vendus. Mais il faudra alors prévoir dans la procédure :
- La suppression de la ligne de devis ;
- puis le comptage du produit objet de la ligne de devis supprimée et, si le résultat est zéro ;
- alors suppression du produit devenu inutile ou obsolète.

Sans gérer les accès au niveau de l'administration, n'est-il pas possible de définir des TRIGGER qui "détournerait" un INSERT d'un devis vers la procédure stockée définie par Philippe ?

Je n'en suis pas sûr mais peut-être. Il faudra que je teste ça.

Interdire la suppression d'une ligne Devis si c'est la seule.

Il me semble qu'il y a une restriction technique chez MySQL / MariaDB qui empêche d'interroger une table en cours de mise à jour dans un trigger. À vérifier.

Bonne nuit !

**fsmrel** · 15/03/2024, 22h59

Salve,

Envoyé par Paprick

Sans gérer les accès au niveau de l'administration, n'est-il pas possible de définir des TRIGGER qui "détournerait" un INSERT d'un devis vers la procédure stockée définie par Philippe ?

Si le comprends bien, tu souhaiterais qu’un trigger intercepte les inserts dans la table Devis, et appelle la procédure définie par Philippe.

Toutes choses égales, tu observeras que l’appel à cette procédure utilise le paramètre ''quantiteProduit'' :
[

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE OR REPLACE PROCEDURE pr_creation_facture
(
    IN numFacture SMALLINT, -- Numéro de la facture => Pourrait être généré automatiquement dans la procédure
    IN refProduit CHAR(3), -- Référence du produit apparaissant sur la première ligne de la facture
    IN designationProduit VARCHAR(50), -- Désignation du produit apparaissant sur la première ligne de la facture
    IN prixUnitaireProduit DECIMAL(19,4), -- Prix du produit
    IN quantiteProduit INTEGER -- Quantité facturée du produit
)

Autrement dit, lors de l’appel à la procédure à mettre en oeuvre, le trigger doit lui fournir la quantité, donc qu’on aille la pêcher dans la table LigneDevis... : le serpent se mord la queue...

**fsmrel** · 15/03/2024, 23h14

Au fait, les choses seraient tellement plus simples si MySQL savait gérer les triggers sur les vues, le problème de la quantité serait vite réglé

**SQLpro** · 16/03/2024, 10h42

Entre nous la façon dont MySQL gère les privilèges est délirante et catastrophique d'un point de vue sécurité... Pour information, et la norme SQL est très claire sur le sujet, un utilisateur qui possède le privilège le lire une vue se trouvera bloqué de lire cette vue s'il ne dispose pas des privilèges de lire les tables sous-jacente à la vue, même partiellement. Il en sera de même avec les routines (fonctions, procédures et déclencheurs...)
or MySQL/MariaDB n'applique pas ce principe élémentaire de sécurité.
Bref l'exemple donné est proprement débile et à éviter !

À titre d'exemple, Peter Gulutzan dans son livre "SQL-99 complete really" (p 274) donne cet exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE VIEW Joe_View AS
SELECT date_1, date2 
FROM Sally_dates;

GRANT SELECT ON Joe_View TO Joe;

Qui ne peut fonctionner que si l'on donne le, privilège SELECT sur les colonnes date_1 et date_2 à Joe :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

GRANT SELECT ON Sally_dates (date_1, date_2) TO Joe;

Quand je vous dit que MySQL / MariaDB sont une seule et même daube que je nomme MSQLmerde et MerdiaDB...

Le plus drôle dans l'histoire est que Peter Gulutzan après avoir écrit le SGBDR Ocelot collant à 99% de la norme SQL:1999... est devenu le mentor technique de MySQmerde et maintenant MerdiaDB et n'arrive pas à faire appliquer les éléments essentiel à rendre ces pseudo SGBD non relationnels conforme au standard miniums de la norme ni a les rendre performants... !

A +

**SQLpro** · 16/03/2024, 10h46

Envoyé par fsmrel

Au fait, les choses seraient tellement plus simples si MySQL savait gérer les triggers sur les vues, le problème de la quantité serait vite réglé

Et pas que ça, respecter le principes des privilèges.... MySQmerde est une passoire sécuritaire à, ce niveau !

Pour information, la très grande majorité des ransomware sur les SGBD concernent des bases MySQL et MariaDB... Très rarement des bases SQL Server ou Oracle (sauf sous Linux ou là aussi c'est une passoire....);

En ce moment je ne fais presque plus que cela... mais confidentialité oblige, je n'ai pas le droit d'en parler !

A +

**fsmrel** · 16/03/2024, 17h41

Salve,

Envoyé par SQLpro

Entre nous la façon dont MySQL gère les privilèges est délirante et catastrophique d'un point de vue sécurité... Pour information, et la norme SQL est très claire sur le sujet, un utilisateur qui possède le privilège le lire une vue se trouvera bloqué de lire cette vue s'il ne dispose pas des privilèges de lire les tables sous-jacentes à la vue, même partiellement.

J’ai donc fait le test suivant :

(1) Création des tables proposées par Paprick dans son dernier post :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
use devis_paprick ;
 
CREATE TABLE Produit
(
   RefProduit CHAR(3),
   Designation VARCHAR(50) NOT NULL,
   Prix SMALLINT NOT NULL,
   CONSTRAINT Produit_PK PRIMARY KEY(RefProduit),
   CONSTRAINT Produit_AK UNIQUE(Designation)
);
 
CREATE TABLE Devis
(
   NumDevis SMALLINT,
   DateDevis DATE NOT NULL,
   CONSTRAINT Devis_PK PRIMARY KEY(NumDevis)
);
 
CREATE TABLE LigneDevis
(
   RefProduit CHAR(3),
   NumDevis SMALLINT,
   Quantite SMALLINT NOT NULL,
   CONSTRAINT LigneDevis_PK PRIMARY KEY(RefProduit, NumDevis),
   CONSTRAINT LigneDevis_Produit_FK FOREIGN KEY(RefProduit) REFERENCES Produit(RefProduit) ON DELETE CASCADE,
   CONSTRAINT LigneDevis_Devis_FK FOREIGN KEY(NumDevis) REFERENCES Devis(NumDevis) ON DELETE CASCADE
);

(2) L’utilisateur fred cherche à consulter la table Devis, mais il n’est pas habilité :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

select * from Devis ;

Bien entendu MySQL le lui interdit :

Error Code: 1142. SELECT command denied to user 'fred'@'localhost' for table 'devis'

(3) Création d’une vue à partir des tables précédemment créées :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
create view Devis_Produit_vue as
select x.NumDevis, y.RefProduit, Quantite
from Devis as x
join LigneDevis as y on x.NumDevis = y.NumDevis
join Produit as z on y.RefProduit = z.RefProduit ;

(4) Habilitation de l’utilisateur fred pour la consultation de la vue :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

GRANT SELECT ON devis_paprick.Devis_Produit_vue TO 'fred'@'localhost' ;

(5) L’utilisateur fred consulte la vue :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

select * from Devis_Produit_vue ;

Au résultat, ça fonctionne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
NumDevis RefProduit Quantite
1        p01        150

(6) fred cherche à nouveau à consulter la table Devis :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

select * from Devis ;

Une fois de plus, MySQL le lui interdit :

Error Code: 1142. SELECT command denied to user 'fred'@'localhost' for table 'devis'

Moralité, MySQL n’est pas conforme à la norme...

Pour l’anecdote, à la page 575 de son ouvrage, Peter Gulutzan évoque le produit cartésien, à partir d’une partie de cartes interminable entre René Descartes et Madame du Barry. Petit problème, le premier est mort en 1650 et la seconde est née en 1743...

Il va falloir interroger la bonne base de données pour montrer l’incongruité. Avec MySQL ça paraît jouable...

**SQLpro** · 16/03/2024, 19h38

Envoyé par fsmrel

[...]Pour l’anecdote, à la page 575 de son ouvrage, Peter Gulutzan évoque le produit cartésien, à partir d’une partie de cartes interminable entre René Descartes et Madame du Barry. Petit problème, le premier est mort en 1650 et la seconde est née en 1743...

Il va falloir interroger la bonne base de données pour montrer l’incongruité. Avec MySQL ça paraît jouable...

MySQmerde est peut être rentré dans la 4e dimension et les univers parallèles !!!!

A +

**fsmrel** · 17/03/2024, 23h48

Salve,

Rappel du souhait de Paprick :

Envoyé par Paprick

• Sans gérer les accès au niveau de l'administration, n'est-il pas possible de définir des TRIGGER qui "détournerait" un INSERT d'un devis vers la procédure stockée définie par Philippe ?
• Interdire la suppression d'une ligne Devis si c'est la seule.

Bref, essayer d'être vraiment exhaustif sur un cas le plus simple possible.
Cela vous parait jouable :koi

Sans gérer les accès au niveau de l’administration ? Hum !

Rappel du MCD :

Comme je l’ai déjà précisé pour le cas MySQL :

Envoyé par fsmrel

Lors de l’appel à la procédure à mettre en oeuvre, le trigger doit lui fournir la quantité, donc qu’on aille la pêcher dans la table LigneDevis... : le serpent se mord la queue...

Pour varier, que se passe-t-il avec SQL Server ?

(1) Création des tables :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE Produit
(
   RefProduit CHAR(3),
   Designation VARCHAR(50) NOT NULL,
   Prix SMALLINT NOT NULL,
   CONSTRAINT Produit_PK PRIMARY KEY(RefProduit),
   CONSTRAINT Produit_AK UNIQUE(Designation)
);
 
CREATE TABLE Devis
(
   NumDevis SMALLINT,
   DateDevis DATE NOT NULL,
   CONSTRAINT Devis_PK PRIMARY KEY(NumDevis)
);
 
CREATE TABLE LigneDevis
(
   RefProduit CHAR(3),
   NumDevis SMALLINT,
   Quantite SMALLINT NOT NULL,
   CONSTRAINT LigneDevis_PK PRIMARY KEY(RefProduit, NumDevis),
   CONSTRAINT LigneDevis_Produit_FK FOREIGN KEY(RefProduit) REFERENCES Produit(RefProduit) ON DELETE CASCADE,
   CONSTRAINT LigneDevis_Devis_FK FOREIGN KEY(NumDevis) REFERENCES Devis(NumDevis) ON DELETE CASCADE
);

(2) Un trigger pour interdire la création d’un devis sans ligne de devis :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
create trigger Devis_after_insert on Devis after insert as
begin
  declare @n int ;
  declare @tableDevisInserted table (NumDevis smallint) ; 
  declare @errId as int = 314115  -- numéro de l'erreur à afficher
  declare @errTexte as varchar (255) -- message d'erreur
  declare @NumDevis as smallint
 
  insert into @tableDevisInserted (NumDevis)
    select distinct NumDevis
    from inserted
    where NumDevis not in (select NumDevis from LigneDevis) ;
 
  set @n = (select count(NumDevis) from @tableDevisInserted) ;
  if @n > 0
    begin
      set @NumDevis = (select top (1) NumDevis from @tableDevisInserted)
      set @errTexte = 
          char(13)
        + 'Un devis doit avoir au moins une ligne.' 
        + char(13)
        + 'Le devis '''
        + cast(@NumDevis as  varchar) 
        + ''' ne répond pas à cette obligation.' 
        + char(13)
      ; throw @errId, @errTexte, 16
    end ;
end

(3) Une tentative d’infraction :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
insert into Devis (NumDevis, DateDevis)
  values
    (1, '2000-01-01') ;

La tentative échoue :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Msg 314115, Niveau 16, État 16, Procédure Devis_after_insert, Ligne 26 [Ligne de départ du lot 0]

Un devis doit avoir au moins une ligne.
Le devis '7' ne répond pas à cette obligation.

(4) Création d’une vue comportant la jointure des tables Devis et LigneDevis :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
create view DevisVue
  (NumDevis, DateDevis, RefProduit, Quantite)
as 
  select x.NumDevis, x.DateDevis, y.RefProduit, y.Quantite  
  from Devis as x 
     join LigneDevis as y on x.NumDevis = y.NumDevis
;

(5) Un trigger pour intercepter les inserts dans la vue et les ventiler dans les tables :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
create trigger DevisVue_instead_Trigger on DevisVue instead of insert as
 
begin
  declare @nDevis as smallint ;
  declare @nLigne as smallint ;
  declare @Quantite as smallint
 
  declare @tableDevis table 
          ( 
              NumDevis smallint
            , DateDevis date
          ) ; 
  declare @tableLigne table 
          ( 
              NumDevis smallint not null
            , RefProduit char(3) not null
            , Quantite smallint not null
          ) ; 
 
  insert into @tableDevis
    select distinct i.NumDevis, i.DateDevis
    from inserted as i 
    --  join Devis as d on i.NumDevis = d.NumDevis
 
  set @nDevis = (select count(x.NumDevis) 
                 from Devis as x join @tableDevis as y on x.NumDevis = y.NumDevis
                ) ;
 
  if @nDevis = 0
    begin ;
      disable trigger Devis_after_insert on Devis ;
      insert into Devis
        select distinct NumDevis, DateDevis 
        from inserted ;
      enable trigger Devis_after_insert on Devis ;
    end
 
  insert into LigneDevis
    select distinct NumDevis, RefProduit, Quantite 
    from inserted ;
 
end

(6) Création de produits :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
insert into Produit (RefProduit, Designation, Prix)
 values
    ('p01', 'ananas', 100) 
  , ('p02', 'bonbons', 200)
  , ('p03', 'caramels', 300)
  , ('p04', 'nougats', 400)

(7) Insert dans la vue :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
insert into DevisVue (NumDevis, DateDevis, RefProduit, Quantite)
  values
    (1, '2000-01-01', 'p01', 100) 
    (1, '2000-01-01', 'p02', 150)
  , (2, '2000-01-02', 'p01', 200)
;

(8) Au résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
NumDevis  RefProduit  Quantite
1         p01         100
1         p02         150
2         p01         200

(9) Suppression de toutes les lignes du devis 1 :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

delete from LigneDevis where NumDevis = 1

Pas de problème, ou plutôt si ! car le produit existe bien, mais sans aucune ligne : la contrainte (1,n) n’est pas respectée…

Qu’à cela ne tienne, on crée un trigger pour interdire cela :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
create trigger LigneDevis_after_delete on LigneDevis after delete as
begin
  declare @n int ;
  declare @tableLigneDevis table (NumDevis smallint, RefProduit char(3)) ; 
  declare @errId as int = 314117  -- numéro de l'erreur à afficher
  declare @errTexte as varchar (255) -- message d'erreur
  declare @NumDevis as smallint
  declare @RefProduit as char(3)
 
  insert into @tableLigneDevis (NumDevis, RefProduit)
    select distinct NumDevis, RefProduit
    from deleted
    where NumDevis in (select NumDevis from Devis) ;
 
  set @n = (select count(*) 
            from @tableLigneDevis as x
            join LigneDevis as y on x.NumDevis = y.NumDevis) ;
  print '@n = ' + cast(@n as varchar) ;
  if @n = 0 
    begin
      set @NumDevis = (select top (1) @NumDevis 
                       from @tableLigneDevis)
      set @errTexte = 
          char(13)
        + 'Un devis doit avoir au moins une ligne.' 
        + char(13)
        + 'Supprimer sa dernière ligne, n''est donc pas possible.'
        + char(13)
      ; throw @errId, @errTexte, 16
    end ;
end

(10) Prudemment on tente la suppression d’une des deux lignes du devis 1 :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

delete from LigneDevis where NumDevis = 1 and RefProduit = 'p01' ;

=> Pas de problème. La contrainte (1,n) n’est pas enfreinte.

(11) Suppression de la 2e et dernière ligne du devis 1 :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

delete from LigneDevis

Et cette fois-ci, ça réagit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Msg 314117, Niveau 16, État 16, Procédure LigneDevis_after_delete, Ligne 29 [Ligne de départ du lot 0]
 
Un devis doit avoir au moins une ligne.
Supprimer sa dernière ligne, n'est donc pas possible.

Paprick, je te prie de noter qu’il n’est plus possible de supprimer quelque devis que ce soit, car désormais le trigger LigneDevis_after_delete l’interdit dès qu’il a affaire à la dernière ligne d’un devis.

Pour supprimer un devis et ses lignes, on peut en revenir à un montage du genre de celui que j’ai mis en oeuvre ici...

A suivre !

**Paprick** · 18/03/2024, 01h05

Bonsoir,
Merci à tous : je trouve cette discussion particulièrement passionnante

; en effet, après 30 ans de modélisation, je me rends compte que cette cardinalité 1,N ne peut être raisonnablement traitée qu'au niveau de la partie programmation de l'application, et que sa prise en compte au niveau du LDD est démentielle si l'on veut l'intégrer dans une IHM conviviale.
Et tout cela me pousse à raison garder !
En effet, alors que ce cas d'école est le plus simple possible, la débauche d'énergie pour contrôler ce 1,N n'est pas raisonnable, et ne trouvera pas sa place dans une application.
Alors, faut-il oublier ? Faut-il laisser nos fiers programmeurs s'assurer du respect de cette contrainte ?

C'est en tous cas ce qui se passe aujourd'hui dans 99,99999% (j'ai peut-être oublié quelques 9

).
Ne peut-on pas alors trouver un compromis acceptable en faisant en sorte que noter LDD alerte l'application, mais tout en lui laissant la main sur l'IHM ?
Un gentil petit TRIGGER après insertion d'un devis envoyant un message (un numéro d'erreur non bloquant) à l'application lui rappelant la nécessité de créer au moins une ligne (compatible avec tous les SGBD).
Idem si on supprime la dernière ligne d'un devis.
Qu'en pensez-vous ?

**CinePhil** · 18/03/2024, 02h29

Je pense que le couple de cardinalités 1,n - 1,n est, dans la pratique, très rare.

Autant pour un devis, qu'une commande et à plus forte raison une facture, il est quasi systématique que le produit soit créé avant le devis. Au pire, il est créé en même temps que la ligne du premier devis dans lequel il est inclus, pour un nouveau produit qui n'a encore jamais été vendu. Mais obliger un produit à être inclus dans au moins un devis, c'est se mettre une contrainte inutile.

Je sais qu'il est (encore) tard mais, à cet instant, je ne vois pas de cas réel où le 1,n - 1,n serait absolument justifié. Dans l'écrasante majorité des cas, on trouvera du 0,n - 0,n ou bien du 0,n - 1,n.

Par contre, quelles que soient les cardinalités, l'utilisation adéquate des privilèges et des procédures peut régler n'importe quel cas. Laisser ça aux programmeurs de l'application, c'est prendre le risque d'avoir un jour des données incohérentes. Parce qu'on va peut-être greffer d'abord une application qui verrouillera correctement l'accès aux données puis, un jour ou l'autre, on en greffera une autre sur la BDD qui n'aura pas le même degré d'exigences que la première ; laquelle risque alors d'afficher des données bizarres.

**Paprick** · 18/03/2024, 04h18

Envoyé par CinePhil

Je pense que le couple de cardinalités 1,n - 1,n est, dans la pratique, très rare.
Autant pour un devis, qu'une commande et à plus forte raison une facture, il est quasi systématique que le produit soit créé avant le devis. Au pire, il est créé en même temps que la ligne du premier devis dans lequel il est inclus, pour un nouveau produit qui n'a encore jamais été vendu. Mais obliger un produit à être inclus dans au moins un devis, c'est se mettre une contrainte inutile.
Je sais qu'il est (encore) tard mais, à cet instant, je ne vois pas de cas réel où le 1,n - 1,n serait absolument justifié. Dans l'écrasante majorité des cas, on trouvera du 0,n - 0,n ou bien du 0,n - 1,n.

On est bien d'accord ; c'est la raison pour laquelle je propose cet exemple : autant il est normal d'exiger qu'un devis possède au moins une ligne, autant un produit peut très bien exister sans être proposé dans un devis.

Par contre, quelles que soient les cardinalités, l'utilisation adéquate des privilèges et des procédures peut régler n'importe quel cas. Laisser ça aux programmeurs de l'application, c'est prendre le risque d'avoir un jour des données incohérentes. Parce qu'on va peut-être greffer d'abord une application qui verrouillera correctement l'accès aux données puis, un jour ou l'autre, on en greffera une autre sur la BDD qui n'aura pas le même degré d'exigences que la première ; laquelle risque alors d'afficher des données bizarres.

Certes, mais il n'est pas envisageable que les procédures stockées s'occupent de l'IHM. C'est pourquoi, l'idée de renvoyer à l'application un message (d'erreur) pour lui signifier la conduite à tenir me parait être une approche acceptable.
Par ailleurs, l'exemple proposé est simpliste et nous voyons la difficulté de le gérer proprement... je n'ose pas imaginer ce que ce serait dans des cas bien plus complexes...

**fsmrel** · 18/03/2024, 16h16

Bonjour les oiseaux de nuit !

Envoyé par CinePhil

Je pense que le couple de cardinalités 1,n - 1,n est, dans la pratique, très rare.

Autant pour un devis, qu'une commande et à plus forte raison une facture, il est quasi systématique que le produit soit créé avant le devis. Au pire, il est créé en même temps que la ligne du premier devis dans lequel il est inclus, pour un nouveau produit qui n'a encore jamais été vendu. Mais obliger un produit à être inclus dans au moins un devis, c'est se mettre une contrainte inutile.

Je sais qu'il est (encore) tard mais, à cet instant, je ne vois pas de cas réel où le 1,n - 1,n serait absolument justifié. Dans l'écrasante majorité des cas, on trouvera du 0,n - 0,n ou bien du 0,n - 1,n.

On est bien d’accord et Paprick a bien fait de proposer l’exemple des devis, où les cardinalités sont effectivement (0,n/1,1) côté Produit.

Envoyé par CinePhil

Par contre, quelles que soient les cardinalités, l'utilisation adéquate des privilèges et des procédures peut régler n'importe quel cas.

Le problème est que la mise en oeuvre des privilèges est orthogonale à celle des tables (et des vues), et je trouve ça très, très moyen. Que les privilèges soient incontournables avec MySQL parce qu’il refuse la mise à jour des vues est un subterfuge, en fait un handicap sévère, alors que ce type de mise à jour est évidemment possible avec des poids lourds comme DB2, Oracle, SQL Server, voire avec des SGBD comme Postgres et d’autres (SQLite, Apache, Firebird, Informix, Sybase,...)
Après avoir consulté l’article de Fadace, Quel SGBD choisir ?, j’ai le sentiment que MySQL est bien seul à interdire les triggers ayant les vues pour objet

Volonté délibérée ?

**SQLpro** · 18/03/2024, 18h49

Envoyé par fsmrel

Bonjour les oiseaux de nuit ![...]
Le problème est que la mise en oeuvre des privilèges est orthogonale à celle des tables (et des vues), et je trouve ça très, très moyen. Que les privilèges soient incontournables avec MySQL parce qu’il refuse la mise à jour des vues est un subterfuge, en fait un handicap sévère, alors que ce type de mise à jour est évidemment possible avec des poids lourds comme DB2, Oracle, SQL Server, voire avec des SGBD comme Postgres et d’autres (SQLite, Apache, Firebird, Informix, Sybase,...)

PostGreSQL est lui aussi très restreint sur les mises à jour des vues, puisqu'il ne les supporte pas dès qu'il y a une jointure...

Après avoir consulté l’article de Fadace, Quel SGBD choisir ?, j’ai le sentiment que MySQL est bien seul à interdire les triggers ayant les vues pour objet

Volonté délibérée ?

Même avec Oracle, ce SGBD est d'une part volontairement bridé pour ne pas faire de l'ombre à Oracle Database et d'autre part le développement de MySQL/MariaDB a toujours été "on fait tout comme les grand" mais dans la réalité c'est ni fait ni à faire et en général inexploitable.... Voir par exemple, la réplication (tellement restrictif et bugué), le partitionnement (aucun gain de perf....)...

A +