Discussion :

[Kropernic]

Bonjour,

J'aurais juste voulu savoir s'il existait un tool ou quelque chose d'autre qui permettrait de savoir qui a fait quoi dans une DB ?

J'ai bien tenté une recherche mais avec log comme mot clef, je ne tombe que sur des résultats concernant le journal de transaction.

Jusqu'ici, je procédais en ajoutant des colonnes dans les tables pour lesquelles je souhaitais avec les informations concernant la création d'une ligne et la dernière modification d'une ligne. Les informations en question étant l'utilisateur et la date, cela fait donc à chaque fois 4 colonnes en plus par table.

Y a-t-il un meilleur moyen de procéder ?

[mikedavem]

Salut,

Tout dépend la granularité de ce que tu veux.

Il existe les audits SQL Server (depuis 2008) qui te permettent de savoir quelles actions un utilisateur a effectué (modification du paramétrage d'un serveur, ajout d'un login, insert / update / delete dans une table etc ...)

Si en revanche tu souhaites savoir quelles données ont été modifiées par un utilisateur par exemple il faudra compléter avec du Change Data Capture de SQL Server.

Il est également possible de faire des audits par le biais des traces SQL Server mais l'impact est plus important en terme de performance.

++

[Kropernic]

Ah ouais, j'ai oublié de le préciser mais je suis encore sous 2005...

Sinon ce que je veux savoir ne se situe qu'au niveau des requêtes insert/update/delete.

Enfin... Il est prévu de passer sur 2008 d'ici la fin de l'année en même temps que de mettre de nouveaux serveurs (pas trop tôt !). Je vais rester avec mes colonnes jusque-là et on verra après.

Merci pour l'info !

P.S. : Au passage, y a des trucs spéciaux à faire pour migrer de 2005 à 2008 ou bien je peux juste prendre mes DB et les mettre sur le nouveau serveur ?

[SQLpro]

1) ce serait idiot de mettre en œuvre un truc lourd, long, complexe et peu performant à base de déclencheurs, alors qu'il existe dans la version 2008 3 moyens beaucoup moins intrusifs pour ce faire :

• CHANGE TRACKING
• CHANGE DATA CAPTURE
• DATABASE AUDIT.

2) détacher la base, la rattacher sur la nouvelle instance (moins d'une seconde)
puis :
1.1 - la mettre en version 2008 (ALTER DATABASE ??? SET COMPATIBILITY_LEVEL 100
1.2 - recalculer TOUTES les statistiques en FULL SCAN (sp_updatestats)

A +

[Kropernic]

Concernant le point 1, je suis bien d'accord avec vous mais je n'aurai pas la version 2008 avant la fin de cette année (voir même le début de la prochaine).

Concernant le point 2, un grand merci pour ces infos !

[SQLpro]

alors, à lire :
http://blog.developpez.com/sqlpro/p8...on_des_donnees

A +

[Kropernic]

J'avais déjà lu cet article il y a pas mal de temps maintenant.

Je ne suis intéressé que par l'historisation online. Et cela est assez facile à mettre en place donc je vais m'en tenir à l'ajout des colonnes.

Par contre j'ai une remarque. Dans votre article, on peut lire :

1.1 – l’historisation en ligne (IN LINE) :
Elle est constituée de colonnes rajoutées à la table afin de connaître deux groupes d’informations :
- la date/heure et le nom d’utilisateur SQL de celui qui à inséré la ligne;
- la date/heure et le nom d’utilisateur SQL de celui qui à modifié la ligne en dernier.
Et de déclencheurs associés pour mettre à jour ces données (triggers INSERT et UPDATE).

Personnellement, je renseigne les informations concernant celui qui a inséré la ligne en spécifiant une valeur par défaut à ces deux colonnes.
SYSTEM_USER pour la colonne contenant l'utilisateur et GETDATE() pour la colonne contenant la date et l'heure.

Y a-t-il une raison particulière pour avoir choisi d'utiliser un trigger ?

[Kropernic]

Encore une question...

Je suis dans le même temps en grande discussion sur le forum schéma concernant la modélisation d'un MCD pour une nouvelle DB que je développe.

Concernant ce log, on me suggère de ne pas polluer les tables devant en faire l'objet mais plutôt de créer une entité parallèle suffixé par "_log" (par exemple) qui contiendra les 4 colonnes pour l'historisation inline + la clef référençant la table principale.

Cela poserait-il un problème quelconque (performance, fragmentation, etc. ) ?

[Serguei_TARASSOV]

Kropernic,

Cette question est bien croisée avec celle concernant les types des clés.

Imagine, que tu a les ID homogènes. On part sur le concept "hyper-key" cela veut dire, l'ID est généré de même maniéré pour tous les objets.

Registry (ID registry, ID entity type)
tous les autres entités doivent référencer un objet correspondant de Registry, i.e.
Contact(ID contact, nom, prénom, ID registry)

On ajout le service d'audit :
AuditLog(ID log, ID registry, ID action, date&time, ID user)

Comme cela tu peux évoluer tes services sans changer la structure des entités.

[Kropernic]

Kropernic,

Cette question est bien croisée avec celle concernant les types des clés.

Imagine, que tu a les ID homogènes. On part sur le concept "hyper-key" cela veut dire, l'ID est généré de même maniéré pour tous les objets.

Registry (ID registry, ID entity type)
tous les autres entités doivent référencer un objet correspondant de Registry, i.e.
Contact(ID contact, nom, prénom, ID registry)

On ajout le service d'audit :
AuditLog(ID log, ID registry, ID action, date&time, ID user)

Comme cela tu peux évoluer tes services sans changer la structure des entités.

Donc si je comprends bien, tu proposes d'ajouter une colonne à chaque table devant être auditée. Colonne qui sera une clef étrangère de la table Registry.

Ensuite, une table AuditLog qui trace toutes les actions des users.


Pourquoi pas...

Question : Quels avantages/inconvénients par rapport à l'historisation "INLINE" comme expliqué dans l'article de sqlpro ? (manière que j'utilisais déjà dans le passé)

[Serguei_TARASSOV]

L'approche "Registry" permet rajouter les service en fonction d'évolution sans changer les entités. I.e. rajouter de même manière la sécurité fonctionnelle ou regroupement des objets.

L'historisation "INLINE" concerne la création et dernière modification, l'approche "audit" fait l'historique des modifications. C'est de l'architecte à choisir selon les besoins.