Auditing implementeren

[Standeman]

Ik ga binnenkort beginnen met het bouwen van een nieuw software pakket waar een wens aan hangt om te kunnen zien wie wat wanneer heeft gewijzigd. Eigen een soort van auditing / versiebeheer van de data dus. Aangezien dit de eerste keer voor mij is dat ik iets dergelijks ga implementeren vraag ik me af wat de beste aanpak is.

Ik gebruik even hypothetisch voorbeeldje waar bijgehouden moet worden welke user een bepaald product aanpast en op welk tijdstip dit gebeurt.

Product

productNumber, (prim key)

supplierId

name

price

stock

Data ziet er dan als volgt uit:

productNumber	supplierId	name	price	stock
2348953	2	Zwitsal babysalf	2.99	13

Ik zie eigenlijk twee verschillende mogelijkheden om dit op te lossen in een RDBMS:

1) Records dupliceren en metadata toevoegen zoals user, en timestamp.

key	productNumber	supplierId	name	price	stock	userid	timestamp
2	2348953	2	Zwitsal babysalf	1.99	13	633	2011-07-05 16:49:45
3	2348953	2	Zwitsal babyzalf	1.99	13	25	2011-07-05 16:51:29

Om de huidige versie van het product op te halen kan je volstaan met een selectquery met een max(timestamp) in de where clause. Het komt er ook op neer dat je in principe nooit een update doet op de product tabel, maar alleen inserts. Het nadeel is wel dat je er veel data dupliceert. Elke wijziging resulteert in een nieuw record in de db, waardoor de data behoorlijk groeit. Aan de andere kant is diskspace goedkoop.

2) audit / change info opslaan in een aparte tabel.

Audit

id

table

column

key

previousValue

userId

timestamp

id	table	column	key	previousValue	userid	timestamp
1	Product	price	2348953	2.99	633	2011-07-05 16:49:45
1	Product	name	2348953	Zwitsal babysalf	25	2011-07-05 16:49:45

Het nadeel van deze aanpak is dat wanneer kolomnamen of tabelnamen wijzigen je ook gelijk de audit tabel inhoudelijk moet gaan bewerken. Verder vind ik het wat minder transparant. Als je als devver in een tabel zit te kijken heb je niet direct het overzicht welke wijzigingen er allemaal zijn en lijkt met me dus een stuk lastiger om het geheel te debuggen.

Mijn voorkeur gaat in principe uit naar oplossing 1. Dit vind ik namelijk een stuk simpeler en duidelijker dan oplossing twee en minder bewerkelijk qua onderhoud omdat je nergens rekening mee hoeft te houden als je bijvoorbeeld een kolom dropped of een nieuwe maakt. Maar mogelijk mis ik nog een paar voordelen / nadelen van beide methodes of is er nog een andere manier om dit te implementeren.
Ik hoor graag ideeën en ervaringen over dit soort implementaties.

[CodeCaster]

Zie hier een paar ideeën.

Ik vind zelf het creëren van een history-tabel per tabel wel een aardige oplossing, wanneer gecombineerd met triggers. Eventueel wegschrijven naar een andere database om het overzicht te bewaren. Alleen heb ik er op hobbyprojecten na geen ervaring mee, dus ik wacht rustig de rest van de reacties af.

Tvp dus.

[Voor 11% gewijzigd door CodeCaster op 06-07-2011 10:42]

[Anoniem: 42791]

Het is volgens mij in principe een een op veel relatie. Je hebt je Ding (1) en je Dingrevisies (veel). Ik zou twee tabellen gebruiken. De velden waarvan je geen historie hoeft bij te houden (owner, herkomst product, weet Ik et) steek je dan in de 1 kant en de velden die per revisie kunnen wijzigen in de veel kant.

Mijn ervaring is dat het met queryen makkelijker is als he een enkele ingang per ding hebt.

[Anoniem: 14124]

Beter bekend als "audit trail" of "journal"

Een diff op twee versies is voldoende om de changes aan het licht te brengen. Individuele changes hoef je niet op te slaan.

Ik zou dus alleen je versies opslaan, en de informatie per versie. Per versie sla je metadata op met betrekking tot de auteur, datum, tijdstippen.

[Janoz]

Als je Java gerbuikt moet je zeker even naar Envers kijken. Als je geen java gebruikt zou je ook even bij Envers kunnen kijken hoe zij het doen. Op zich komt het ongeveer neer op je eerste idee, maar dan zitten de timestamp en de userid in een aparte revision tabel (waar je zelf ook nog spullen aan toe kunt voegen). Alle wijzigingen binnen 1 transactie horen bij een specifieke revision.

[fleppuhstein]

Misschien een rare vraag, maar waarom moet dit in de database ? Is het een vereiste dat er met grote eenvoud terug gedraaid moet kunnen worden ? Of een actueel overzicht van de historie kunnen weergeven ?

IK kan me zo voorstellen dat een plain tekst file met wijzigingen in een vast formaat op te slaan voldoende zou moeten zijn. Dit kan je dan met iets als een stukje te ontwikkelen tooling wel weer goed weergeven / inlezen in een audit database.

De eerste versie lijkt mij niet geheel handig bij grote volumes, en je hebt een primary key die over twee colommen loopt.

Een audit log heeft een aantal security implicaties, met betrekking tot wijzigen van een wachtwoord van een gebruiker enz. De basis implementatie zou wel redelijk eenvoudig moeten zijn als je het Active record pattern gebruikt, icm getters en setters voor elke property. Voor elke setter de audit trail entity opzetten. En bij een update van het model de audit trail entities opslaan naar DB/txt

[RobIII]

Als je SQL Server 2008 of hoger kunt gebruiken moet je eens hier kijken.

[Davio]

In sommige databases kun je auditing aanzetten en hoef je zelf het wiel dus opnieuw uit te vinden.

Van Progress (v.10+?) weet ik het, van SQL Server heb ik er wel eens over gehoord en kun je de link in de vorige post gebruiken.

Auditing kan soms een vereiste zijn voor bijvoorbeeld Sarbanes-Oxly compliancy.

[dingstje]

Ik gebruik hiervoor Envers. Uitstekende integratie met Hibernate, en je kan makkelijk zelf bepalen welke metadata je opslaat. Zoals al werd aangehaald, gebruikt Envers een tabel met de data van alle revisies per object, en één centrale revisietabel. Dat is belangrijk, zodat je samenhangende wijzigingen aan verschillende objecten kan centraliseren. Je hebt er ook meteen support bij voor auditing van collections zonder je daar zelf te moeten van aantrekken. Er zijn ook zeer brede query mogelijkheden: zowel horizontaal (geef mij de volledige object graph op revisie x of op tijdstip y) als verticaal (geef mij alle revisies van object y), waarbij je ook nog eens kan filteren op alle metadata die je erbij hebt opgeslagen.

[Anoniem: 42791]

Gaat Envers ook werken als je revisies wil bijhouden van complexe, samengestelde entiteiten?

Stel dat je een "functie" hebt. En een functie bestaat uit "taken". En taken bestaan uit "competenties". En je wil aan functies beoordelingen hangen. Dan moet zo'n beoordeling aan een revisie van een bepaalde functie hangen en blijven hangen. Want een functie kan veranderen in de tijd, maar een beoordeling en een revisie van een functie moeten bij elkaar blijven horen.

Kort gezegd, een beoordeling moet niet aan een functie gekoppeld zijn, maar aan een functierevisie.

Kan Envers dit soort situaties opvangen?

[Janoz]

Envers houdt de revisies van je complete database bij en je kunt, gegeven een revisienummer (of timestamp) exact de staat van de database op dat moment in tijd terug halen. Ik vraag me echter af of ik je probleem omschrijving helemaal begrepen heb. Als een beoordeling niet meer bij een functie hoort, dan wis je die gewoon. In de audit history is vervolgens gewoon terug te zien dat voor dat moment de beoordeling nog wel bestond en daarna niet meer.

Ik heb echter het vermoeden dat je het over een beoordeling van een medewerker hebt die een bepaalde functie heeft. Die is ondertussen misschien aangepast, maar de beoordeling wil je wel gewoon bewaren. Dit is echter een ander probleem dan audithistory. Audit history is alleen bedoeld om een 'papertrail' achter te laten over hoe je database er ooit uit gezien heeft. Het is niet bedoeld als een manier om de veranderende wereld op te slaan. Dat klinkt lastig, maar misschien wordt het duidelijker met een voorbeeldje:

Stel, je hebt een database waarin je adressen hebt staan. De enige vraag die die database kan beantwoorden is:

"Waar woont, volgens onze database, pietje?"

Door nu een audittrail toe te voegen (met bv Envers) is de enige extra vraag die kunt beantwoorden:

"Waar woont, volgens onze database 2 weken terug, pietje?"

De vraag die nog steeds niet beantwoord kan worden is:

"Waar woonde pietje 2 weken terug?"

Als je ook die vraag wilt kunnen beantwoorden heb je geen audittrail nodig, maat temporal properties.

[Anoniem: 42791]

Janoz schreef op zaterdag 09 juli 2011 @ 09:00:
Envers houdt de revisies van je complete database bij en je kunt, gegeven een revisienummer (of timestamp) exact de staat van de database op dat moment in tijd terug halen. Ik vraag me echter af of ik je probleem omschrijving helemaal begrepen heb. Als een beoordeling niet meer bij een functie hoort, dan wis je die gewoon. In de audit history is vervolgens gewoon terug te zien dat voor dat moment de beoordeling nog wel bestond en daarna niet meer.

Ik heb echter het vermoeden dat je het over een beoordeling van een medewerker hebt die een bepaalde functie heeft. Die is ondertussen misschien aangepast, maar de beoordeling wil je wel gewoon bewaren. Dit is echter een ander probleem dan audithistory. Audit history is alleen bedoeld om een 'papertrail' achter te laten over hoe je database er ooit uit gezien heeft. Het is niet bedoeld als een manier om de veranderende wereld op te slaan. Dat klinkt lastig, maar misschien wordt het duidelijker met een voorbeeldje:

Stel, je hebt een database waarin je adressen hebt staan. De enige vraag die die database kan beantwoorden is:

"Waar woont, volgens onze database, pietje?"

Door nu een audittrail toe te voegen (met bv Envers) is de enige extra vraag die kunt beantwoorden:

"Waar woont, volgens onze database 2 weken terug, pietje?"

De vraag die nog steeds niet beantwoord kan worden is:

"Waar woonde pietje 2 weken terug?"

Als je ook die vraag wilt kunnen beantwoorden heb je geen audittrail nodig, maat temporal properties.

Ja, ok. Dat is inderdaad het antwoord wat ik zocht. Wij hebben net een applicatie gebouwd die onder andere de geschiedenis van een medewerker kan bijhouden. Zowel de medewerker verandert soms van functie en functies veranderen zelf qua inhoud, waarbij "inhoud' dan betekent de taken en de competenties en het belang/zwaarte van competenties binnen een taak. Dat is dus geen case voor het bijhouden van audit history.

Ik vroeg me sowieso af of dit soort wijzigingen in de tijd zich lenen voor een oplossing die je van de plank kan trekken. Ik krijg de indruk van niet (en dus dat we op zich het juiste pad hebben bewandeld bij de bouw van die applicatie).

[Voor 9% gewijzigd door Anoniem: 42791 op 09-07-2011 19:31]