UGISS Community

Durante l'ultimo workshop tenutosi a Torino, Sergio Govoni mi disse che era possibile tracciare l'attvità di un utente a partire dal suo SPID.

Io dovrei fare questo applicandolo a SQL2005.

Come posso fare ?

 Grazie.

Ciao Luca,

se non ricordo male la situazione è questa:

hai un’applicazione WEB che gestisce (autentica e autorizza) lato applicativo una serie di utenti. Al momento della connessione ad DB, però, tutti gli utenti applicativi si presentano al database con la stessa login, ad esempio “Login1”. Hai necessità di registrare le attività di un determinato utente WEB.

In questa situazione, il database non conosce la *vera* identità dell’utente perché è stata mascherata dalla login “Login1”.

La soluzione più pulita sarebbe quella di creare una login/utente per ogni utente applicativo (utente WEB) assegnando correttamente ruoli e diritti a livello di database, in questo modo gli utenti WEB non sarebbero mascherati dalla "Login1".

Un’alternativa quick and dirty potrebbe essere questa: subito dopo aver eseguito la connessione al DB potesti utilizzare la funzione @@SPID per conoscere l’identificativo del processo utente, ottenuto lo SPID, l’applicazione potrebbe salvarlo all’interno di una tabella di associazione SPID/utente WEB, come questa ad esempio:

if (object_id('UserForSPID') is not null)
  drop table dbo.UserForSPID
go

create table dbo.UserForSPID
(
  id int identity(1, 1),
  SPID int not null,
  userWEB sysname not null
  primary key(id)
)
go

insert into dbo.UserForSPID
(
  SPID,
  UserWEB
)
values
(
  @@spid,
  'luca'
)

go

select
  *
from
  dbo.UserForSPID

 

id          SPID    userWEB
----------- ----------- --------
1           51        luca

Potrai utilizzare lo SPID come filtro per la traccia di auditing, ad esempio, se l’ID del processo utente (SPID) fosse 51, potresti utilizzare sp_trace_setfilter in questo modo (vedi demo nell'area download):

-- Set the Filters
declare @intfilter int
set @intfilter = 51
exec sp_trace_setfilter @TraceID, 12, 1, 0, @intfilter 

Le attività registrate, attraverso lo SPID, potranno essere associate all’utente WEB passando attraverso la tabella di associazione allestita al momento della connessione. Terminata la connessione potrai importare le attività all’interno di una tabella di auditing e successivamente eliminare la riga relativa allo SPID corrente dalla tabella di associazione.

delete
from
  dbo.UserForSPID
where
  (SPID=@@spid)

select
  u.userWEB,
  t.*
from
  ::fn_trace_gettable('C:\Audit_Trace\audit_trace_file.trc', default) as t
join
  dbo.UserForSPID u on u.SPID=t.SPID

Buon divertimento

Ciao

Ti ringrazio moltissimo.

Ora inizio a lavorarci su.

Ho provato a vedere più attentamente il tuo script.

Come posso fare per tracciare le istruzioni (insert, update, select) eseguite dall'utente loggato ?

Grazie e ciao

Ciao Luca,

lfrance:

Come posso fare per tracciare le istruzioni (insert, update, select) eseguite dall'utente loggato ?

Potresti utilizzare la stored procedure [dbo].[usp_trace_audit_create] (vedi script demo - workshop) che permette di definire una nuova traccia SQL in grado di registrare il verificarsi dell’evento audit object permission (event number 114). La stored procedure dello script demo non applica però il filtro per SPID a te necessario per registrare solo le istruzioni di un determinato utente.

Il filtro per SPID può essere facilmente applicato aggiungendo alla stored procedure questo codice:
  ..
  begin
    ...
    ...
    set @permission = 8192
    exec sp_trace_setfilter @traceid, 19, 1, 0, @permission
  end

  aggiungo codice qui -->

  -- Set the Filters
  declare @intfilter int

  set @intfilter = 51  -- SPID (ti consiglio di aggiungere un parametro alla stored procedure per passare lo SPID che desideri filtrare, se zero non applicherai il filtro)

  exec sp_trace_setfilter @TraceID, 12, 1, 0, @intfilter

  <-- fine codice aggiunto

  -- sp_trace_setfilter per ObjectID e ObjectName
  set @cmd = ''
  select
    @cmd = @cmd + 'exec sp_trace_setfilter ' + cast(@traceid as varchar) + ', 22, 0, 0, ' + cast(id as varchar) + '; ',
    @cmd = @cmd + 'exec sp_trace_setfilter ' + cast(@traceid as varchar) + ', 34, 0, 6, ' + name + '; '
  from
    sysobjects
  where
    charindex((',' + ltrim(rtrim(upper(sysobjects.name))) + ','),
              (',' + replace(@tablelist, ' ', '') + ',')) > 0  
  ...
  ...

Ciao!

Ciao Luca,

prova a dare un'occhiata a questo post: http://community.ugiss.org/blogs/sgovoni/archive/2009/07/01/user-auditing.aspx che spero possa esserti utile

Ciao!