Tables de dénormalisation

Jean-Nicolas BERGER a écrit:

Je cherche dans mon modèle de données à maintenir des tables de
dénormalisation par des triggers.

Bonjour,

Tu peux songer aux triggers, mais selon ton volume tu auras des impacts de
performance. Pour éviter la mise à jour directe, il te suffit de ne laisser
aucun privilège sur les tables.
Pourquoi ne pas songer à créer plutôt des vues indexées ?

--
Rudi Bruchez
Consultant independant, MCDBA, MCITP, MCT
http://www.babaluga.com/
http://rudi.developpez.com/

>
Tu peux songer aux triggers, mais selon ton volume tu auras des impacts de
performance. Pour éviter la mise à jour directe, il te suffit de ne laisser
aucun privilège sur les tables.
Pourquoi ne pas songer à créer plutôt des vues indexées ?

bonjour,
Les autres solutions ont été envisagées, mais les triggers semblent la seule
solution possible.
Les vues indexées ont été écartées car il y a des jointures réflexives, des
fonctions de type ROW_NUMBER() et des CTE.
Les performances ne devraient pas trop s'en ressentir car les MAJ seraient
principalement unitaires, tandis que le besoin du résultat du calcul existe
de manière ensembliste, comme critère de recherche.

Au niveau de la gestion de l'accès via les droits, une bonne solution
serait-elle un EXECUTE AS dans les triggers, sachant que les procédures
stockées des développeurs doivent pouvoir agir sur ces tables dénormalisées,
mais seulement de manière indirecte (via les triggers...)

Merci.
JN.

Jean-Nicolas BERGER a écrit:

Les autres solutions ont été envisagées, mais les triggers semblent la seule
solution possible.
Les vues indexées ont été écartées car il y a des jointures réflexives, des
fonctions de type ROW_NUMBER() et des CTE.

Je ne vois pas vraiment ce que ça change. La vue indexée matérialise ses
données, tout comme une table alimentée par un trigger.

En ce qui concerne la sécurité des triggers, si les deux tables
appartiennent au même user (donc sont dans le même schéma, ou dans des
schémas qui ont le même propriétaire), le chaînage de propriété s'applique.
Donc tu n'as pas à faire d'EXECUTE AS. Le trigger aura des privilèges
d'insertion dans ta table dénormalisée même si le contexte d'exécution de
l'INSERT sur la table normalisée ne le permet pas.

--
Rudi Bruchez
Consultant independant, MCDBA, MCITP, MCT
http://www.babaluga.com/
http://rudi.developpez.com/

Rudi Bruchez a écrit :

Jean-Nicolas BERGER a écrit:

Les autres solutions ont été envisagées, mais les triggers semblent la seule
solution possible.
Les vues indexées ont été écartées car il y a des jointures réflexives, des
fonctions de type ROW_NUMBER() et des CTE.

et c'est beaucoup plus fin et moins gourmand en ressource... mais bon,
on peut aimer se faire mal !!!

Je ne vois pas vraiment ce que ça change. La vue indexée matérialise ses
données, tout comme une table alimentée par un trigger.

En ce qui concerne la sécurité des triggers, si les deux tables
appartiennent au même user (donc sont dans le même schéma, ou dans des
schémas qui ont le même propriétaire), le chaînage de propriété s'applique.
Donc tu n'as pas à faire d'EXECUTE AS. Le trigger aura des privilèges
d'insertion dans ta table dénormalisée même si le contexte d'exécution de
l'INSERT sur la table normalisée ne le permet pas.

A +

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************

>
Je ne vois pas vraiment ce que ça change. La vue indexée matérialise ses
données, tout comme une table alimentée par un trigger.

En ce qui concerne la sécurité des triggers, si les deux tables
appartiennent au même user (donc sont dans le même schéma, ou dans des
schémas qui ont le même propriétaire), le chaînage de propriété s'applique.
Donc tu n'as pas à faire d'EXECUTE AS. Le trigger aura des privilèges
d'insertion dans ta table dénormalisée même si le contexte d'exécution de
l'INSERT sur la table normalisée ne le permet pas.

Bonjour,

La création d'une vue indexée implique (voir BOL) que la vue ne doit pas
contenir, entre autres :
- Une expression de table commune.
- La clause OVER, qui inclut des fonctions de classement ou d'agrégation de
fenêtre.
- Des jointures externes ou réflexives.

Et comme fonctionnellement, je n'ai pas le choix...

Par contre, le chainage des droits ne semble pas être mon allié sur ce
coup-là. Le but est de me prémunir des développeurs qui mettraient dans le
code de leurs procédures stockées une instruction DML sur les tables
dénormalisées; Par contre, ces mêmes développeurs, dans les mêmes procédures
stockées, pourraient tout à fait modifier des tables de base. Comment
voyez-vous le chainage et les propriétaires des procédures, des tables de
base et des tables dénormalisées? Je doit avouer que je n'arrive pas à
concevoir une chaîne fiable...

Merci.
JN.

Jean-Nicolas BERGER a écrit:

La création d'une vue indexée implique (voir BOL) que la vue ne doit pas
contenir, entre autres :

Pardon, j'avais mal compris, je n'avais pas capté que vous génériez la
table à l'aide de ces instructions. En effet, comme dit Fred Brouard, dans
un trigger, vous allez souffrir.

stockées, pourraient tout à fait modifier des tables de base. Comment
voyez-vous le chainage et les propriétaires des procédures, des tables de
base et des tables dénormalisées? Je doit avouer que je n'arrive pas à

Si le propriétaire de la table dénormalisée est le même que le propriétaire
de la table qui contient le trigger, l'utilisateur de la session n'a pas
besoin d'avoir des privilèges sur la table dénormalisée.

Exemple :

---
/* -------------------------------------------------
test de chaînage de propriété sur un trigger
------------------------------------------------- */

USE Master
GO

CREATE LOGIN testtrigger_dev WITH password = 'pwd'
CREATE LOGIN testtrigger_secret WITH password = 'pwd'
GO

CREATE DATABASE TestTrigger
GO

USE TestTrigger
GO

CREATE USER testtrigger_dev FROM LOGIN testtrigger_dev
CREATE USER testtrigger_secret FROM LOGIN testtrigger_secret
GO

CREATE TABLE dbo.MainTable (id int)
GO
CREATE TABLE dbo.SecretTable (id int)
GO

GRANT INSERT ON dbo.MainTable TO testtrigger_dev
GRANT INSERT ON dbo.SecretTable TO testtrigger_secret
GO

CREATE TRIGGER atr$i$MainTable$InsertIntoSecretTable
ON dbo.MainTable AFTER INSERT
AS BEGIN
IF @@ROWCOUNT = 0 RETURN
SET NOCOUNT ON

INSERT INTO dbo.SecretTable (id)
SELECT id FROM inserted
END
GO

-- -------- TEST ----------

-- insertion via Trigger
EXECUTE AS USER = 'testtrigger_dev'
SELECT CURRENT_USER
SELECT principal_id, sid, name, type, usage FROM sys.user_token;
INSERT INTO dbo.MainTable (id) VALUES (1)
REVERT
GO
SELECT CURRENT_USER
SELECT * FROM dbo.SecretTable

-- insertion directe
EXECUTE AS USER = 'testtrigger_dev'
SELECT CURRENT_USER
SELECT principal_id, sid, name, type, usage FROM sys.user_token;
INSERT INTO dbo.SecretTable (id) VALUES (1)
REVERT
GO
SELECT CURRENT_USER

SELECT * FROM dbo.SecretTable

-- test avec autre schémas
CREATE SCHEMA un
CREATE TABLE un.SecretTable (id int)
GO

CREATE SCHEMA deux AUTHORIZATION testtrigger_secret
CREATE TABLE deux.SecretTable (id int)
GO

ALTER TRIGGER atr$i$MainTable$InsertIntoSecretTable
ON dbo.MainTable AFTER INSERT
AS BEGIN
IF @@ROWCOUNT = 0 RETURN
SET NOCOUNT ON

INSERT INTO un.SecretTable (id)
SELECT id FROM inserted

INSERT INTO deux.SecretTable (id)
SELECT id FROM inserted

END
GO

-- insertion via Trigger
EXECUTE AS USER = 'testtrigger_dev'
SELECT CURRENT_USER
SELECT principal_id, sid, name, type, usage FROM sys.user_token;
INSERT INTO dbo.MainTable (id) VALUES (1)
REVERT
GO
SELECT CURRENT_USER
SELECT * FROM dbo.SecretTable


-- -------- NETTOYAGE ----------
USE Master
GO
DROP DATABASE TestTrigger
DROP LOGIN testtrigger_dev
DROP LOGIN testtrigger_secret
---

--
Rudi Bruchez
Consultant independant, MCDBA, MCITP, MCT
http://www.babaluga.com/
http://rudi.developpez.com/

Jean-Nicolas BERGER a écrit :

Je ne vois pas vraiment ce que ça change. La vue indexée matérialise ses
données, tout comme une table alimentée par un trigger.

En ce qui concerne la sécurité des triggers, si les deux tables
appartiennent au même user (donc sont dans le même schéma, ou dans des
schémas qui ont le même propriétaire), le chaînage de propriété s'applique.
Donc tu n'as pas à faire d'EXECUTE AS. Le trigger aura des privilèges
d'insertion dans ta table dénormalisée même si le contexte d'exécution de
l'INSERT sur la table normalisée ne le permet pas.

Bonjour,

La création d'une vue indexée implique (voir BOL) que la vue ne doit pas
contenir, entre autres :
- Une expression de table commune.
- La clause OVER, qui inclut des fonctions de classement ou d'agrégation de
fenêtre.
- Des jointures externes ou réflexives.

Oui, mais rien n'empêche de réaliser une première vue indexée avec ses
limitations là sur laquelle les calculs CTE et les fonctions de
fenêtrage vont se porter...

Et comme fonctionnellement, je n'ai pas le choix...

Par contre, le chainage des droits ne semble pas être mon allié sur ce
coup-là. Le but est de me prémunir des développeurs qui mettraient dans le
code de leurs procédures stockées une instruction DML sur les tables
dénormalisées; Par contre, ces mêmes développeurs, dans les mêmes procédures
stockées, pourraient tout à fait modifier des tables de base. Comment
voyez-vous le chainage et les propriétaires des procédures, des tables de
base et des tables dénormalisées? Je doit avouer que je n'arrive pas à
concevoir une chaîne fiable...

Le plus simple serait de faire une vue WITH SCHEMA BINDING. C'est
jutement fait pour cela : empêcher la restructuration des tables sous
jacentes.

Merci.
JN.

A +

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************

>
Oui, mais rien n'empêche de réaliser une première vue indexée avec ses
limitations là sur laquelle les calculs CTE et les fonctions de fenêtrage
vont se porter...

J'ai effectivement fait celà, mais j'avais besoin de boucler le dossier...
:-)
Merci.
JN.

Merci pour cette superbe étude.
JN.