UGISS Community

Ciao a tutti,

ho la necessità di tracciare le modifiche (INSERT, UPDATE, DELETE, TRUNCATE, ecc...) che vengono eseguite sui dati di alcune tabelle. Ogni operazione di modifica dovrà essere inserita in una tabella di traccia (pensavo ad una tabella unica).

Per alcune tabelle è sufficiente tracciare solo le modifiche che avvengono su una colonna, al contrario per altre tabella devo tracciare tutto.

Sto utilizzando SQL Server 2000, e pensavo di implementare dei trigger per tracciare le modifiche (se non ricordo male SQL Server 2005 fornisce delle primitive per fare auditing su una determinata tabella).

Secondo voi la soluzione via trigger è buona oppure è meglio usare altro ?

Grazie

Sergio

Ciao Sergio.

Con SQL Server 2000, in effetti, se vuoi poter tracciare modifiche ai tuoi dati non hai altra strada che utilizzare i trigger.

Potresti anche utilizzare il SQL Profiler opportunamente configurato per registrare solo quello che ti serve sul db che ti serve (anche se l'applicazione non nasce per fare auditing).... ma il fatto di poterlo fare non significa che sia una buona cosa... ;-)

Potresti anche dare un'occhiata alle capacità di fare auditing C2. Vedi, se può interessarti, qui:

http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx

Ciao!

Grazie Andrea,

proseguo con lo sviluppo dei trigger. Mi consigli d'implementare una tabella unica oppure una tabella di traccia per ogni tabella tracciata ?

Ciao!

Sergio

Ciao Sergio

i trigger possono anche andare bene (se hai un basso carico di lavoro) ma non potrai mai tracciare in questo modo il comando TRUNCATE in quanto, essendo partially logged, non provoca l'esecuzione del trigger. Infatti non esiste un trigger "ON TRUNCATE" ed il trigger "ON DELETE" proprio per il motivo del logging parziale, non è di aiuto in questo contesto.

Per fare una cosa ben fatta il mio consiglio è quello di tracciare i comandi inviati al tuo database utilizzando le funzionalità di tracing di SQL Server (sp_trace_create & amici) e poi di importare tali dati in un database nella quale poter fare analisi (magari ogni ora, in modo da non far crescere troppo i file di tracing)

Il tutto ti porterà via un pò di lavoro....ma i 2 o 3 gg dovresti riuscire a fare tutto :-) 

Ah dimenticavo, poi ci faresti anche una bella sessione ad UGISS su questa cosa!!!!

Molto volentieri (per me sarebbe un onore).

Seguo il tuo consiglio e mi metto al lavoro... vi tengo informati.

Grazie, ciao

Sergio

dmauri:

Per fare una cosa ben fatta il mio consiglio è quello di tracciare i comandi inviati al tuo database utilizzando le funzionalità di tracing di SQL Server (sp_trace_create & amici) e poi di importare tali dati in un database nella quale poter fare analisi (magari ogni ora, in modo da non far crescere troppo i file di tracing)

Domanda: ma mantenere il tracing attivo  rallenta anche un po' l'attivita', giusto ?

    Franco

orsocurioso:

Domanda: ma mantenere il tracing attivo  rallenta anche un po' l'attivita', giusto ?

Si ma in modo praticamente inifluente. Sto parlando di utilizzare il tracing SENZA usare il profiler ovviamente, ossia inviado i dati di traccia ad un file.

Qui Linchi ha fatto un pò di test (beato lui che può giocare con un sacco di giocattoli!!! )

http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx

Ciao,

ho creato una traccia con sp_trace_create e vorrei configurare i filtri in modo da monitorare solo una tabella: ho impostato il filtro per ObjectID ma non viene applicato perchè il campo risulta essere sempre vuoto. Anche eseguendo una traccia da SQL Profiler, il campo ObjectID è vuoto! Cosa devo impostare perchè venga valorizzato il campo ObjectID ?

Grazie

Sergio

Il campo ObjectId viene valorizzato solo per certi eventi, non per tutti.

Con SQL 2005 ti conviene caricare TUTTE le righe che produce il trace in una tabella di analisi e poi li farci su un pò di pulizia (magari aiutandoti anche con il FULL-TEXT Search) per lasciare solo le righe con i dati che ti interessano (INSERT, UPDATE, DELETE e TRUNCATE nella tuo caso)

E' un lavoro non pulitissimo ma l'unico fattibile che soddisfa tutte le tue esigenze IMHO. Ovviamente se non ti interessa tenere traccia del TRUNCATE puoi tranuillamente usare i trigger :-)

In SQL 2008 ci sarà il support nativo per l'auditing, ma fino ad allora dobbiamo utilizzare per forza di cose il tracing.

Oppure poi leggere il transaction log (usando il comando non documentato DBCC LOG) dove ci sono dentro tutti i dati che ti servono. Non ti consiglio però di farlo a mano, ma ti suggerisco di acquistare un tool come l'Apex SQL Log (http://www.apexsql.com/sql_tools_log.asp) che fa egregiamente questo lavoro.

Mi è venuto in mente un barbatrucco per poter far prima :-)

Puoi tracciare solamente gli eventi di tipo "Audit Schema Object Access Event" (nel profiler trovi questa voce sotto il gruppo Security Audit).

Questo evento traccia il controllo dei permessi che SQL Server fa per ogni accesso ad ogni oggetto.

Ho fatto una prova e tutte le colonne che servono sono valorizzate correttamente (objectid e object name (per avere quest'ultimo devi includere anche database_id e servername.

La colonna "permission" assume un valore che rappresenta:

• 1=SELECT ALL
• 2=UPDATE ALL
• 4=REFERENCES ALL
• 8=INSERT
• 16=DELETE
• 32=EXECUTE (procedures only)
  

il valore 16 (DELETE) viene utilizzato anche nel caso di un TRUNCATE!

Dovresti quindi essere a cavallo :-)

dmauri:

Puoi tracciare solamente gli eventi di tipo "Audit Schema Object Access Event" (nel profiler trovi questa voce sotto il gruppo Security Audit).

Questo evento traccia il controllo dei permessi che SQL Server fa per ogni accesso ad ogni oggetto.

Semplicemente fantastico! Questo evita parecchio codice per pulire i dati prima di fare analisi. Procedo in questa direzione...

GRAZIE

Sergio

Bene bene, me lo sono segnato :-)

Fammi sapere quando hai concluso che programmiamo un intervento al prossimo evento UGISS!

OK, non vedo l'ora

Grazie

Sergio