Abbiamo parlato dei meccanismi di auditing in questo articolo Meccanismi di Auditing e nella sessione dedicata durante l’ultimo workshop UGISS a Torino. Riprendo l’argomento per rispondere ad una domanda che alcuni di voi mi hanno sottoposto durante l'evento.
"Come è possibile fare user auditing anche quando la gestione delle login (e degli utenti) risiede nell’applicazione e non nel database ?"
Il caso tipico è rappresentato da un’applicazione WEB o Win32 che gestisce in autonomia i primi due livelli di sicurezza (autenticazione e autorizzazione) per l’accesso ai dati di un DB SQL Server. Al momento della connessione tutti gli utenti applicativi (autenticati e autorizzati) si presentano al database con la medesima login (ad esempio “ERPLogin”). In questa situazione, il database non conosce la *vera* identità dell’utente perché mascherata dalla login applicativa. Abbiamo tuttavia la necessità di conoscere chi fa che cosa e quando sul database applicativo.
La soluzione più pulita sarebbe quella di creare, sul DB, una login/utente per ogni utente applicativo assegnando correttamente ruoli e diritti a livello di database, in questo modo *vera* identità dell’utente non verrebbe mascherata dalla login applicativa. In alcune situazioni però non è possibile (almeno nel breve periodo) spostare la gestione delle login (e degli utenti) dall’applicazione al database. Pensiamo ad esempio ad un software gestionale che implementa, lato applicativo, l’intera gestione degli utenti e dei relativi permessi di accesso alle funzioni.
Una soluzione alternativa è rappresentata dall’implementazione di una tabella di associazione SIPD/utente applicativo. Subito dopo aver eseguito la connessione al database l’applicazione conoscerà sia lo SPID ovvero l’ID della sessione relativa al processo utente, che l’utente applicativo utilizzato per fare login. Queste due informazioni con l’aggiunta della data/ora di login potranno essere memorizzate (dall’applicazione stessa) nella tabella di associazione dbo.userforSPID. Di seguito i comandi T-SQL per il setup delle tabelle dbo.userforSPID e dbo.auditing. Nella tabella dbo.auditing, rispetto alla precedente versione, è stata aggiunta la colonna externaluser:
use [AdventureWorks];
go
-- Drop table dbo.userforSPID if exists
if (object_id('userforSPID') is not null)
drop table dbo.userforSPID;
go
-- Create table dbo.userforSPID
-- Memorizza l'utente esterno (user name) associato ad un
-- determinato SPID in un determinato momento
create table dbo.userforSPID
(id int identity(1, 1),
SPID int not null default @@SPID,
externaluser nvarchar(128) not null,
login_time datetime not null,
logout_time datetime default null
primary key(id)
)
go
-- Drop table dbo.auditing if exists
if (isnull(object_id('auditing'), 0) > 0)
drop table [dbo].[auditing]
go
-- Create table dbo.auditing if exists
create table [dbo].[auditing]
(id int identity (1, 1) not null,
rowid int null,
eventclass int null,
textdata nvarchar(max) null,
databaseid int null,
ntusername nvarchar(128) null,
ntdomainname nvarchar(128) null,
hostname nvarchar(128) null,
clientprocessid int null,
applicationname nvarchar(128) null,
loginname nvarchar(128) null,
spid int null,
duration bigint null,
starttime datetime null,
objectid int null,
objectname nvarchar(128) null,
databasename nvarchar(128) null,
dbusername nvarchar(128) null,
login_time datetime null,
oldvalue nvarchar(128) null,
newvalue nvarchar(128) null,
fieldname nvarchar(128) null,
externaluser nvarchar(128) null
)
on [primary]
Sarà necessario implementare anche la tabella dbo.lastimport per registrare la data/ora dell’ultima importazione, dal file fisico (traccia SQL) alla tabella dbo.auditing.
use [AdventureWorks];
go
-- Drop table dbo.lastimport if exists
if (object_id('lastimport') is not null)
drop table dbo.lastimport;
go
-- Create table dbo.lastimport
create table dbo.lastimport
(id int identity(1, 1),
lastexecution datetime not null
primary key(id)
)
Subito dopo aver eseguito la connessione, l’applicazione potrà registrare l’associazione SPID/utente applicativo semplicemente con:
-- Subito dopo la connessione al DB
insert into dbo.userforSPID
(
externaluser,
login_time
)
select
'ERPLogin',
(select login_time from sys.sysprocesses where SPID=@@SPID);
Dove “ERPLogin” rappresenta l’utente applicativo utilizzato per fare login; consultando la tabella di associazione si otterrà:
L’implementazione della traccia di auditing descritta nel precedente articolo riporta, per ogni evento, l’identificativo della sessione relativa al processo utente (SPID), tale ID avrà una corrispondenza nella tabella di associazione dbo.userforSPID.
Le attività registrate potranno essere associate all’utente applicativo attraverso lo SPID, la data/ora di login e la data/ora di esecuzione dell’evento registrato (attività tracciata), semplicemente con una join rivolta alla tabella di associazione allestita al momento della connessione.
Per importare le attività registrate, dal file fisico (traccia SQL) alla tabella dbo.auditing, si può utilizzare la seguente stored procedure schedulandone l’esecuzione attraverso un job:
use [AdventureWorks];
go
-- Drop procedure dbo.usp_trace_gettable_import_file if exists
if (object_id('usp_trace_gettable_import_file') is not null)
drop procedure [dbo].[usp_trace_gettable_import_file]
go
-- Create procedure dbo.usp_trace_gettable_import_file
create procedure dbo.usp_trace_gettable_import_file
as
begin
-- Aggiornamento dbo.lastimport
if (select count(id) from dbo.lastimport) = 0
insert into dbo.lastimport
(
lastexecution
)
values
(
'19000101'
);
-- Importazione dei dati raccolti da file (.trc) --> tabella dbo.auditing
Insert Into [dbo].[auditing]
(
EventClass,
TextData,
DatabaseID,
NTUserName,
NTDomainName,
HostName,
ClientProcessID,
ApplicationName,
LoginName,
Login_Time,
SPID,
Duration,
StartTime,
ObjectID,
ObjectName,
DatabaseName,
DBUserName,
externaluser
)
select
T.EventClass,
replace(cast(T.TextData as nvarchar(max)), char(13) + char(10), ''),
T.DatabaseID,
T.NTUserName,
T.NTDomainName,
T.HostName,
T.ClientProcessID,
T.ApplicationName,
T.LoginName,
(select
s.login_time
from
sys.sysprocesses s
where
(s.spid=T.SPID)
) as Login_Time,
T.SPID,
T.Duration,
T.StartTime,
T.ObjectID,
T.ObjectName,
T.DatabaseName,
T.DBUserName,
uSPID.externaluser
from
::fn_trace_gettable('C:\Audit_Trace\audit_trace_file.trc', default) as T
left outer join
dbo.userforSPID uSPID on (uSPID.SPID=T.SPID) and
(T.StartTime >= uSPID.login_time)
where
(T.TextData is not null) and
(T.StartTime >= (select lastexecution from dbo.lastimport));
if (@@error=0)
update
dbo.lastimport
set
lastexecution = getdate();
if (@@error=0)
-- Cancellazione per SPID login_time e logout_time
-- nella tabella dbo.userforSPID
delete
uSPID
from
dbo.userforSPID uSPID
join
dbo.auditing au on (au.SPID=uSPID.SPID) and
(au.starttime >= uSPID.login_time) and
(uSPID.logout_time is not null);
end
Subito dopo aver chiuso la connessione, l’applicazione potrà registrare la data/ora di logout nella tabella di l’associazione SPID/utente applicativo, alla prossima esecuzione della stored procedure dbo.usp_trace_gettable_import_file verranno importate le registrazioni mancanti e successivamente verranno eliminate le associazioni SPID/utente applicativo con data/ora login diversa da NULL.
Le registrazioni importate sono state arricchite della login applicativa utilizzata, siamo quindi in grado di conoscere quale utente applicativo che ha eseguito una determinata attività:
select
SPID,
externaluser,
starttime,
textdata,
objectname,
databaseid
from
dbo.auditing;
