Meccanismi di auditing
L’auditing dei dati rappresenta l’insieme delle attività mirate ad accertare la validità e l’affidabilità di un’informazione.
I meccanismi di auditing vengono utilizzati per tenere traccia di tutti gli eventi che riguardano la sicurezza del sistema database. SQL Server Profiler è un tool che permette di monitorare gli eventi del database engine, si basa sull’impostazione di tracce (o filtri di traccia). Una traccia (o filtro di traccia) è un set di eventi memorizzati di cui occorre creare un profilo.
SQL Server adotta un’architettura di sicurezza basata su tre differenti livelli di protezione:
- Autenticazione: l’utilizzo delle risorse di un database deve essere subordinato alla preventiva autenticazione degli utenti che avviene attraverso la fornitura di una password
- Autorizzazione: i permessi assegnati agli utenti regolano il diritto di accedere (o meno) ai vari oggetti del database (tabelle, viste, stored procedure, ecc…)
- Auditing: la fase di auditing è caratterizzata dalla scelta dei mezzi più idonei per:
o Identificare e riconoscere possibili abusi
o Assicurare l’integrità delle informazioni
o Mantenere (comunque) alto il livello delle prestazioni
Scenario
Lavorate come database administrator per la vostra organizzazione. Gli utenti dell’ufficio commerciale vi segnalano periodicamente delle situazioni anomale su alcuni dati memorizzati nel sistema informativo. In particolare lamentano prezzi di listino errati per i prodotti commerciali di largo consumo e la cancellazione di alcuni articoli obsoleti non movimentati nell’ultimo esercizio fiscale. Dopo aver aggiornato i prezzi errati, l’anomalia di ripresenta nuovamente ad una distanza di tempo ravvicinata. Il responsabile commerciale non si spiega il motivo di tali variazioni non autorizzate.
Sospettate un bug nella procedura di aggiornamento prezzi oppure un accesso non autorizzato alla tabella prodotti con conseguente aggiornamento abusivo del prezzo di listino e la cancellazione di alcuni articoli.
Il responsabile commerciale desidera conoscere la natura di tali aggiornamenti errati o abusivi con il dettaglio del tipo di evento che ha avuto luogo, le informazioni di connessione ed il testo del comando SQL eseguito. Desidera inoltre essere informato tempestivamente, con un messaggio di posta elettronica, al verificarsi un aggiornamento del prezzo di listino.
Soluzione
Con SQL Server 2008 il supporto all’auditing è nativo grazie al comando “CREATE AUDIT”, con SQL Server 2000 o 2005 dobbiamo invece farci una soluzione custom. Una possibile soluzione è rappresentata dall’implementazione di tracce SQL per monitorare l’utilizzo delle autorizzazioni (UPDATE, DELETE, ecc…) sull’anagrafica prodotti, oltre alla realizzazione di un trigger per il monitoraggio degli aggiornamenti eseguiti sul prezzo di listino.
L’utilizzo delle traccie è necessario perchè non sono intrusive come i trigger ed inoltre rappresentano l’unico modo per poter effettuare l’audit anche del comando “TRUNCATE TABLE” che altrimenti non potrebbe mai essere intercettato in quanto non scatena nessun trigger.
La soluzione è stata implementata sul database AdventureWorks ipotizzando che fosse il database del sistema informativo utilizzato nella nostra organizzazione. L’anagrafica prodotti è rappresentata dalla tabella Production.Product.
Oggetti implementati per la soluzione di auditing
Tabelle
- AUDITINGVALUE
- AUDITING
- AUDITINGMAIL
Stored Procedure
- USP_TRACE_AUDIT_CREATE
- USP_TRACE_GETTABLE_IMPORT_FILE
- USP_AUDIT_MAIL
- USP_AUDIT_INS_DATA
Trigger
- TRIU_PRODUCTION_PRODUCT_AUDIT
Iniziamo con la creazione delle tabelle di auditing sul database AdventureWorks.
USE AdventureWorks
GO
/*
AUDITING
Setup tabelle:
- AUDITINGVALUE
- AUDITING
- AUDITINGMAIL
*/
-- Drop table dbo.AUDITINGVALUE
IF (ISNULL(OBJECT_ID(‘AUDITINGVALUE’), 0) > 0)
DROP TABLE [dbo].[AUDITINGVALUE]
GO
-- Create table dbo.AUDITINGVALUE
CREATE TABLE [dbo].[AUDITINGVALUE]
(ID INT IDENTITY(1, 1) NOT NULL,
RowID INT NOT NULL,
SPID INT NOT NULL,
fOldValue NVARCHAR(128),
fNewValue NVARCHAR(128)
PRIMARY KEY(ID)
) ON [PRIMARY]
-- Drop table dbo.AUDITING
IF (ISNULL(OBJECT_ID(‘AUDITING’), 0) > 0)
DROP TABLE [dbo].[AUDITING]
GO
-- Create table dbo.AUDITING
CREATE TABLE [dbo].[AUDITING]
(ID int IDENTITY (1, 1) NOT NULL,
RowID int NULL,
EventClass int NULL,
TextData nvarchar(2000) 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
)
ON [PRIMARY]
GO
-- Creazione indice cluster sul campo dbo.AUDITING.StartTime
CREATE CLUSTERED INDEX [IDX__AUDITING_StartTime] ON [DBO].[AUDITING]
([StartTime] DESC) ON [PRIMARY]
GO
-- Creazione indice non-cluster sul campo dbo.AUDITING.ID
CREATE NONCLUSTERED INDEX [IDX__AUDITING_ID] ON [DBO].[AUDITING]
([ID] ASC) ON [PRIMARY]
GO
-- Creazione della chiave primaria tabella dbo.AUDITING
ALTER TABLE [dbo].[AUDITING] ADD CONSTRAINT PK__AUDITING_ID PRIMARY KEY(ID)
-- Drop table dbo.AUDITINGMAIL
IF (ISNULL(OBJECT_ID(‘AUDITINGMAIL’), 0) > 0)
DROP TABLE [dbo].[AUDITINGMAIL]
GO
-- Create table dbo.AUDITINGMAIL
CREATE TABLE [dbo].[AUDITINGMAIL]
(ID INT IDENTITY (1, 1) NOT NULL,
MailRecipients NVARCHAR(512),
MailMessage NVARCHAR(1024),
MailCopy_Recipients NVARCHAR(512),
MailSubject NVARCHAR(80),
MailError INT DEFAULT 0 NOT NULL
PRIMARY KEY (ID)
) ON [PRIMARY]
Procediamo con la creazione delle stored procedure sul database AdventureWorks.
USE AdventureWorks
GO
/*
AUDITING
Setup Stored Procedure:
- USP_TRACE_AUDIT_CREATE
- USP_TRACE_GETTABLE_IMPORT_FILE
- USP_AUDIT_MAIL
- USP_AUDIT_INS_DATA
*/
-- Drop procedure dbo.USP_TRACE_AUDIT_CREATE
IF (OBJECT_ID(‘USP_TRACE_AUDIT_CREATE’) IS NOT NULL)
DROP PROCEDURE [dbo].[USP_TRACE_AUDIT_CREATE]
GO
-- Create procedure dbo.USP_TRACE_AUDIT_CREATE per l’importazione dei files
-- di traccia SQL Server
CREATE PROCEDURE [dbo].[USP_TRACE_AUDIT_CREATE]
(@DataBaseID INT,
@FilePath NVARCHAR(128),
@StopTime DATETIME,
@maxfilesize BIGINT,
@TableList NVARCHAR(4000))
AS
BEGIN
/*
Descrizione:
Creazione tracce SQL Server
Parametri:
@DataBaseID = ID del database (filtro traccia)
@FilePath = UNC path file senza estensione
(Es. N’\\servername\out\audit_trace\audit_trace_file’)
@StopTime = Data ora di stop
@maxfilesize = Dimensione massima (in MB) dei file di traccia
@TableList = Lista tabelle (filtro traccia)
*/
-- Creazione della traccia con sp_trace_create
DECLARE @rc INT,
@TraceID INT,
@Major INT,
@MsgErr VARCHAR(128)
/*
@maxfilesize BIGINT,
@StopTime DATETIME
*/
/*
SET @maxfilesize = 1
SET @StopTime = (GETDATE() + 1)
SET @StopTime = DATEADD(minute, ((24*60)-5), GETDATE())
*/
SELECT @Major = substring(convert(varchar, ServerProperty(‘ProductVersion’)),
1,
charindex(’.’, convert(varchar, ServerProperty(‘ProductVersion’))) - 1)
PRINT @Major
EXEC @rc = sp_trace_create @TraceID OUTPUT,
-- @options (2 = TRACE_FILE_ROLLOVER)
2,
-- @tracefile
@FilePath,
-- @maxfilesize
@maxfilesize,
-- @stoptime
@StopTime
IF (@rc != 0) GOTO error
-- Impostazione eventi
declare @on bit
set @on = 1
-- Evento Audit Object Permission
exec sp_trace_setevent @TraceID, 114, 1, @on -- TextData
exec sp_trace_setevent @TraceID, 114, 3, @on -- DatabaseID
exec sp_trace_setevent @TraceID, 114, 6, @on -- NTUserName
exec sp_trace_setevent @TraceID, 114, 7, @on -- NTDomainName
exec sp_trace_setevent @TraceID, 114, 8, @on -- ClientHostName
exec sp_trace_setevent @TraceID, 114, 9, @on -- ClientProcessID
exec sp_trace_setevent @TraceID, 114, 10, @on -- ApplicationName
exec sp_trace_setevent @TraceID, 114, 11, @on -- SQLSecurityLoginName
exec sp_trace_setevent @TraceID, 114, 12, @on -- SPID
exec sp_trace_setevent @TraceID, 114, 13, @on -- Duration
exec sp_trace_setevent @TraceID, 114, 14, @on -- StartTime
exec sp_trace_setevent @TraceID, 114, 19, @on -- Permissions
exec sp_trace_setevent @TraceID, 114, 22, @on -- ObjectID
exec sp_trace_setevent @TraceID, 114, 34, @on -- ObjectName
exec sp_trace_setevent @TraceID, 114, 35, @on -- DatabaseName
exec sp_trace_setevent @TraceID, 114, 40, @on -- DatabaseUserName
-- Set the Filters
declare @ObjectId int
declare @Permissionbig bigint
declare @Permission int
declare @AppName nvarchar(128)
declare @ObjectName nvarchar(128)
declare @intfilter int
-- DataBaseID
set @intfilter = @DataBaseID
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter
set @AppName = ‘SQL Profiler%’
exec sp_trace_setfilter @TraceID, 10, 0, 7, @AppName
PRINT @Major
if (@Major >= 9) /* Versione >= SQL Server 2005 */
begin
-- Set Permissions
set @Permissionbig = 2
exec sp_trace_setfilter @TraceID, 19, 1, 0, @Permissionbig
set @Permissionbig = 8
exec sp_trace_setfilter @TraceID, 19, 1, 0, @Permissionbig
set @Permissionbig = 16
exec sp_trace_setfilter @TraceID, 19, 1, 0, @Permissionbig
set @Permissionbig = 32
exec sp_trace_setfilter @TraceID, 19, 1, 0, @Permissionbig
set @Permissionbig = 8192
exec sp_trace_setfilter @TraceID, 19, 1, 0, @Permissionbig
end
else begin /* Versione < SQL Server 2005 */
-- Set Permissions
set @Permission = 2
exec sp_trace_setfilter @TraceID, 19, 1, 0, @Permission
set @Permission = 8
exec sp_trace_setfilter @TraceID, 19, 1, 0, @Permission
set @Permission = 16
exec sp_trace_setfilter @TraceID, 19, 1, 0, @Permission
set @Permission = 32
exec sp_trace_setfilter @TraceID, 19, 1, 0, @Permission
set @Permission = 8192
exec sp_trace_setfilter @TraceID, 19, 1, 0, @Permission
end
declare CUR cursor for
Select Id, Name
From sysobjects
Where CHARINDEX((’,’ + LTRIM(RTRIM(UPPER(sysobjects.NAME))) + ‘,’),
(’,’ + REPLACE(@TableList, ’ ‘, ‘’) + ‘,’)) > 0
-- Set filter per ObjectID
open CUR
fetch next from CUR into @ObjectId, @ObjectName
while (@@fetch_status = 0)
begin
exec @rc = sp_trace_setfilter @TraceID, 22, 0, 0, @ObjectId
if (@rc != 0)
goto error
fetch next from CUR into @ObjectId, @ObjectName
end
close CUR
-- Set filter per ObjectName
open CUR
fetch next from CUR into @ObjectId, @ObjectName
while (@@fetch_status = 0)
begin
exec @rc = sp_trace_setfilter @TraceID, 34, 0, 6, @ObjectName
if (@rc != 0)
goto error
fetch next from CUR into @ObjectId, @ObjectName
end
close CUR
deallocate CUR
-- Trace start
exec sp_trace_setstatus @TraceID, 1
-- Visualizzazione trace id
select TraceID = @TraceID
goto finish
error:
begin
SET @MsgErr = ‘There was an error during creation the SQL trace. ErrorCode = ’ + LTRIM(RTRIM(STR(@rc)))
raiserror(@MsgErr, 16, 1)
select ErrorCode = @rc
end
finish:
select 1
END
GO
-- Drop procedure dbo.USP_TRACE_GETTABLE_IMPORT_FILE
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(@sTraceFile varchar(4000))
AS
BEGIN
/*
Descrizione:
Implementa la logica d’importazione dei file di traccia (.trc)
nella tabella dbo.AUDITING
Parametri:
@sTraceFile = Nome del file di traccia iniziale
*/
DECLARE
@nTraceFiles int,
@sSQL varchar(8000),
@bIsRunning smallint,
@sPath varchar(255),
@sFirstFile varchar(255),
@sLastFile varchar(255),
@sFileName varchar(255)
IF (RIGHT(@sTraceFile, 1) <> ‘\’)
SET @sTraceFile = @sTraceFile + ‘\’
-- Determinazione dei files da importare ordinati
-- per data + nome file senza informazioni di dettaglio
EXEC master..xp_sprintf @sSQL OUTPUT, ‘DIR “%s*.trc” /ODN /B’, @sTraceFile
-- Debug
--PRINT @sSQL
-- Impostazione directory di path
SELECT @sPath = SUBSTRING(@sTraceFile, 1, DATALENGTH(@sTraceFile) - CHARINDEX(’\’,REVERSE(@sTraceFile))) + ‘\’
-- Debug
--PRINT @sPath
SELECT @sFileName = RIGHT(@sTraceFile,CHARINDEX(’\’,REVERSE(@sTraceFile))-1)
-- Debug
--PRINT @sFileName
IF EXISTS(SELECT * FROM tempdb..sysobjects
WHERE ID = OBJECT_ID(’tempdb..#tmpFiles’))
DROP TABLE #tmpFiles
CREATE TABLE #tmpFiles(f_id int IDENTITY,
[filename] varchar(255))
INSERT #tmpFiles
EXEC master..xp_cmdshell @sSQL
-- Determino il numero totale dei files
SELECT @nTraceFiles = COUNT(*)
FROM #tmpFiles
WHERE [filename] LIKE @sFileName + ‘%’
-- Debug
--PRINT @nTraceFiles
-- Determino il primo file
SELECT @sFirstFile = @sPath +
(SELECT [filename]
FROM #tmpFiles
INNER JOIN (SELECT MIN(f_id) AS f_id
FROM #tmpFiles
WHERE [filename] LIKE @sFileName + ‘%’) [first] ON #tmpFiles.f_id = [first].f_id)
-- Debug
--PRINT @sFirstFile
-- Determino l’ultimo file
SELECT @sLastFile = @sPath +
(SELECT [filename]
FROM #tmpFiles
INNER JOIN (SELECT MAX(f_id) AS f_id
FROM #tmpFiles
WHERE [filename] LIKE @sFileName + ‘%’) [first] ON #tmpFiles.f_id = [first].f_id)
-- Debug
--PRINT @sLastFile
-- La traccia è in esecuzione ?
-- Se è in esecuzione non importo l’ultimo file
IF EXISTS(SELECT * FROM ::fn_trace_getinfo(default)
WHERE property = 2
AND ((value = @sLastFile) OR
(CAST(value AS varchar(4000)) = @sTraceFile))
)
SELECT @bIsRunning = 1
ELSE
SELECT @bIsRunning = 0
-- Importazione
SELECT @nTraceFiles = @nTraceFiles - @bIsRunning -- Sottraggo 1 se la traccia è in esecuzione
IF (@nTraceFiles > 0)
BEGIN
INSERT INTO [dbo].[AUDITING]
(EventClass,
TextData,
DatabaseID,
NTUserName,
NTDomainName,
HostName,
ClientProcessID,
ApplicationName,
LoginName,
SPID,
Duration,
StartTime,
ObjectID,
ObjectName,
DatabaseName,
DBUserName)
SELECT
T.EventClass,
REPLACE(CAST(T.TextData AS NVARCHAR(2000)), CHAR(13) + CHAR(10), ‘’),
T.DatabaseID,
T.NTUserName,
T.NTDomainName,
T.HostName,
T.ClientProcessID,
T.ApplicationName,
T.LoginName,
T.SPID,
T.Duration,
T.StartTime,
T.ObjectID,
T.ObjectName,
T.DatabaseName,
T.DBUserName
FROM ::fn_trace_gettable(@sFirstFile, @nTraceFiles) AS T
IF (@@ERROR = 0)
BEGIN
-- Cancellazione files importati
DECLARE curFiles CURSOR READ_ONLY FOR
SELECT [filename]
FROM #tmpFiles
WHERE ([filename] LIKE @sFileName + ‘%’)
AND (f_id <= @nTraceFiles)
OPEN curFiles
FETCH NEXT FROM curFiles INTO @sFileName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- EXEC master..xp_fileexist
EXEC master..xp_sprintf @sSQL OUTPUT, ‘DEL /Q “%s%s”’, @sPath, @sFileName
EXEC master..xp_cmdshell @sSQL
END
FETCH NEXT FROM curFiles INTO @sFileName
END
CLOSE curFiles
DEALLOCATE curFiles
END
END
DROP TABLE #tmpFiles
END
-- Drop procedure dbo.USP_AUDIT_MAIL
IF (OBJECT_ID(‘USP_AUDIT_MAIL’) IS NOT NULL)
DROP PROCEDURE dbo.USP_AUDIT_MAIL
GO
-- Drop procedure dbo.USP_AUDIT_MAIL
CREATE PROCEDURE dbo.USP_AUDIT_MAIL
AS BEGIN
/*
Descrizione:
Implementa la logica di invio delle email di notifica
*/
DECLARE @RowNumber INT,
@MailRecipients NVARCHAR(512),
@MailMessage NVARCHAR(1024),
@MailCopy_Recipients NVARCHAR(512),
@MailSubject NVARCHAR(80)
EXEC master..xp_startmail
IF (@@ERROR = 0)
BEGIN
WHILE (SELECT ISNULL(MIN(ID), 0) FROM AUDITINGMAIL) > 0
BEGIN
SELECT @MailRecipients = MailRecipients,
@MailMessage = MailMessage,
@MailCopy_Recipients = MailCopy_Recipients,
@MailSubject = MailSubject,
@RowNumber = ID
FROM AUDITINGMAIL
WHERE (ID = (SELECT MIN(ID) FROM AUDITINGMAIL))
-- Send email
EXEC master..xp_sendmail @Recipients = @MailRecipients,
@Message = @MailMessage,
@Copy_Recipients = @MailCopy_Recipients,
@Subject = @MailSubject
IF (@@ERROR = 0)
BEGIN
DELETE FROM AUDITINGMAIL WHERE (ID = @RowNumber)
END
ELSE BEGIN
UPDATE AUDITINGMAIL SET MailError = -1 WHERE (ID = @RowNumber)
END
END
END
EXEC master..xp_stopmail
END
GO
-- Drop procedure dbo.USP_AUDIT_INS_DATA
IF (OBJECT_ID(‘USP_AUDIT_INS_DATA’) IS NOT NULL)
DROP PROCEDURE dbo.USP_AUDIT_INS_DATA
GO
-- Create procedure dbo.USP_AUDIT_INS_DATA
CREATE PROCEDURE dbo.USP_AUDIT_INS_DATA
(@TextData NVARCHAR(2000),
@ObjectName NVARCHAR(128),
@FieldName NVARCHAR(128),
@SPID INT,
@SendMail INT,
@MailRecipients NVARCHAR(512),
@MailMessage NVARCHAR(1024),
@MailCopy_Recipients NVARCHAR(512),
@MailSubject NVARCHAR(80)
)
AS BEGIN
/*
Descrizione:
Implementa la logica d’inserimento dei dati di
auditing nella tabella AUDITING. La stored procedure
viene richiamata dai trigger
Parametri:
@TextData: Query text - Testo del comando SQL
@ObjectName: Object name - Nome oggetto/tabella
@FieldName: Field name - Nome del campo che ha subito l’aggiornamento
@SPID: Server process identifier (ID)
@SendMail: Enable or disable send mail
services for this alarm - Flag invio email
@MailRecipients: Is the semicolon-separated list
of the recipients of the mail - Destinarati email
@MailMessage: Is the message to be sent - Corpo del messaggio email
@MailCopy_Recipients: Is the semicolon-separated list
identifying the recipients of a copy of the mail (cc:‘ing) -
Destinatari in copia/conoscenza
@MailSubject: Is an optional parameter specifying the subject of the mail.
If subject is not specified, SQL Server Message is the default -
Oggetto messaggio email
*/
SET NOCOUNT ON
DECLARE @EventClass INT,
@DatabaseID INT,
@NTUserName NVARCHAR(128),
@NTDomainName NVARCHAR(128),
@HostName NVARCHAR(128),
@ClientProcessID INT,
@ApplicationName NVARCHAR(128),
@LoginName NVARCHAR(128),
@Duration BIGINT,
@StartTime DATETIME,
@ObjectID INT,
@DatabaseName NVARCHAR(128),
@DBUserName NVARCHAR(128),
@Login_Time DATETIME
SET @EventClass = 0
SET @DatabaseID = (SELECT dbid FROM master.dbo.sysprocesses WHERE spid=@SPID)
SET @NTUserName = (SELECT CASE WHEN (LTRIM(RTRIM(nt_username))=’’) THEN NULL ELSE nt_username END FROM master.dbo.sysprocesses WHERE spid=@SPID)
SET @NTDomainName = (SELECT CASE WHEN (LTRIM(RTRIM(nt_domain))=’’) THEN NULL ELSE nt_domain END FROM master.dbo.sysprocesses WHERE spid=@SPID)
SET @HostName = (SELECT CASE WHEN (LTRIM(RTRIM(hostname))=’’) THEN NULL ELSE hostname END FROM master.dbo.sysprocesses WHERE spid=@SPID)
SET @ClientProcessID = 0
SET @ApplicationName = (SELECT CASE WHEN (LTRIM(RTRIM(program_name))=’’) THEN NULL ELSE program_name END FROM master.dbo.sysprocesses WHERE spid=@SPID)
SET @LoginName = (SELECT CASE WHEN (LTRIM(RTRIM(loginame))=’’) THEN NULL ELSE loginame END FROM master.dbo.sysprocesses WHERE spid=@SPID)
SET @Duration = 0
SET @StartTime = GETDATE()
SET @ObjectID = OBJECT_ID(@ObjectName)
SET @DatabaseName = LTRIM(RTRIM(DB_NAME(@DatabaseID)))
SET @DBUserName = NULL
SET @Login_Time = (SELECT login_time FROM master.dbo.sysprocesses WHERE spid=@SPID)
INSERT INTO [dbo].[AUDITING]
(EventClass,
TextData,
DatabaseID,
NTUserName,
NTDomainName,
HostName,
ClientProcessID,
ApplicationName,
LoginName,
SPID,
Duration,
StartTime,
ObjectID,
ObjectName,
DatabaseName,
DBUserName,
Login_Time,
FieldName,
OldValue,
NewValue,
RowID)
SELECT
@EventClass,
LTRIM(RTRIM(@TextData)),
@DatabaseID,
LTRIM(RTRIM(@NTUserName)),
LTRIM(RTRIM(@NTDomainName)),
LTRIM(RTRIM(@HostName)),
@ClientProcessID,
LTRIM(RTRIM(@ApplicationName)),
LTRIM(RTRIM(@LoginName)),
@SPID,
@Duration,
@StartTime,
@ObjectID,
LTRIM(RTRIM(@ObjectName)),
LTRIM(RTRIM(@DatabaseName)),
LTRIM(RTRIM(@DBUserName)),
LTRIM(RTRIM(@Login_Time)),
LTRIM(RTRIM(@FieldName)),
LTRIM(RTRIM(ATV.fOldValue)),
LTRIM(RTRIM(ATV.fNewValue)),
ATV.RowID
FROM [dbo].[AUDITINGVALUE] ATV
WHERE (ATV.SPID = @SPID)
IF (@SendMail = -1)
BEGIN
INSERT INTO [dbo].[AUDITINGMAIL]
(MailRecipients,
MailMessage,
MailCopy_Recipients,
MailSubject,
MailError)
SELECT
@MailRecipients,
(@MailMessage +
’ UserName: ’ + RTRIM(LTRIM(ISNULL(@NTUserName, ‘’))) +
‘, HostName: ’ + RTRIM(LTRIM(ISNULL(@HostName, ‘’))) +
‘, RowID: ’ + RTRIM(LTRIM(STR(ISNULL(ATV.RowID, 0)))) +
‘, ObjectID: ’ + LTRIM(RTRIM(ISNULL(@ObjectID, 0))) +
‘, ObjectName: ’ + LTRIM(RTRIM(ISNULL(@ObjectName, ‘’))) +
‘, FieldName: ’ + LTRIM(RTRIM(ISNULL(@FieldName, ‘’))) +
‘, StartTime: ’ + ISNULL(CONVERT(VarChar, @StartTime, 113), ‘’) +
‘, OldValue: ’ + LTRIM(RTRIM(ISNULL(ATV.fOldValue, ‘’))) +
‘, NewValue: ’ + LTRIM(RTRIM(ISNULL(ATV.fNewValue, ‘’)))
),
@MailCopy_Recipients,
@MailSubject,
0
FROM [dbo].[AUDITINGVALUE] ATV
WHERE (ATV.SPID = @SPID)
END
SET NOCOUNT OFF
END
Procediamo con la creazione del trigger Production.TRIU_PRODUCTION_PRODUCT_AUDIT sulla tabella Production.Product. Il trigger implementa un meccanismo di auditing sul campo ListPrice. Ogni aggiornamento del campo ListPrice viene registrato sulla tabella AUDITING attraverso la stored procedure USP_AUDIT_INS_DATA.
USE AdventureWorks
GO
-- Drop trigger Production.TRIU_PRODUCTION_PRODUCT_AUDIT
IF (OBJECT_ID(‘Production.TRIU_PRODUCTION_PRODUCT_AUDIT’) IS NOT NULL)
DROP TRIGGER Production.TRIU_PRODUCTION_PRODUCT_AUDIT
GO
-- Create trigger Production.TRIU_PRODUCTION_PRODUCT_AUDIT
CREATE TRIGGER TRIU_PRODUCTION_PRODUCT_AUDIT ON Production.Product
FOR UPDATE AS
BEGIN
DECLARE
@TextData NVARCHAR(2000),
@ParentObj INT,
@ObjectName NVARCHAR(128)
-- ListPrice
IF UPDATE(ListPrice)
BEGIN
-- Comando SQL
CREATE TABLE #TraceInfo
(EventType NVARCHAR(30),
Parameters INTEGER,
EventInfo NVARCHAR(2000))
INSERT INTO #TraceInfo EXEC(‘DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS’)
SET @TextData = (SELECT Eventinfo FROM #TraceInfo)
-- Inserimento valori modificati fOldValue, FNewValue
INSERT INTO [dbo].[AUDITINGVALUE]
(RowID, SPID, fOldValue, fNewValue)
SELECT
d.ProductID,
@@SPID,
LTRIM(RTRIM(CAST(ISNULL(d.ListPrice, ‘’) AS NVARCHAR(128)))),
LTRIM(RTRIM(CAST(ISNULL(i.ListPrice, ‘’) AS NVARCHAR(128))))
FROM Inserted i
INNER JOIN Deleted d On d.ProductID=i.ProductID
WHERE (ISNULL(d.ListPrice, 0) <> ISNULL(i.ListPrice, 0))
SELECT @ParentObj = Parent_Obj FROM dbo.sysobjects WHERE (ID = @@PROCID)
SELECT @ObjectName = OBJECT_NAME(@ParentObj)
EXEC dbo.USP_AUDIT_INS_DATA
@TextData,
@ObjectName,
‘ListPrice’,
@@SPID,
-- @SendMail
-1,
-- @MailRecipients
-- @MailMessage
‘Il campo ListPrice della tabella Production.Product è stato modificato.’,
-- @MailCopy_Recipients
-- @MailSubject
‘SQL Server Message: Notifica di aggiornamento campo’
IF (@@ERROR = 0)
DELETE FROM [dbo].[AUDITINGVALUE] WHERE SPID = @@SPID
END
END
Per tracciare gli accessi alla tabella prodotti si è scelto di monitorare l’evento 114 Audit Schema Object Access Event generato quando si utilizza un’autorizzazione per gli oggetti (ad esempio SELECT) con esito sia positivo che negativo. Abbiamo applicato i filtri per database e per entità: tabella Product del database AdventureWorks.
Il file di traccia audit_trace_file.trc verrà generato nella directory C:\audit_trace\ ed avrà una dimensione massima di 10 MB, raggiunta la quale SQL Server provvederà alla generazione di un nuovo file di traccia con come audit_trace_file**_1**.trc nella directory C:\audit_trace\ (opzione TRACE_FILE_ROLLOVER). Non è stata impostata una data/ora di arresto per l’esecuzione della traccia SQL Server.
Per creare ed avviare la traccia SQL utilizziamo la stored procedure USP_TRACE_AUDIT_CREATE:
DECLARE
@DataBaseID INT,
@FilePath NVARCHAR(128),
@StopTime DATETIME,
@maxfilesize BIGINT
SELECT @DataBaseID = dbid
FROM master..sysdatabases
WHERE (NAME = ‘AdventureWorks’)
SET @FilePath = ‘C:\audit_trace\audit_trace_file’
SET @StopTime = NULL
SET @maxfilesize = 10
EXEC USP_TRACE_AUDIT_CREATE @DataBaseID, @FilePath, @StopTime, @maxfilesize, ‘Product’
Per controllare l’effettiva generazione della traccia SQL e del relativo file con estensione TRC si potrà utilizzare la funzione ::fn_trace_getinfo(default) con il seguente comando di SELECT:
SELECT * FROM ::fn_trace_getinfo(2)
TraceID = 2 rappresenta l’ID della traccia SQL appena creata.
Il meccanismo di auditing è da questo momento attivo, eseguiamo ora alcuni comandi DML sull’anagrafica prodotti: eliminiamo il prodotto con ProductID = 1006 e modifichiamo il prezzo di listino del prodotto con ProductID = 2.
DELETE FROM Production.Product WHERE ProductId=1006
UPDATE Production.Product SET ListPrice = 14 where ProductId=2
Interrompiamo (close) la traccia SQL con TraceID = 2
-- Close trace (status = 2)
DECLARE @RC int, @TraceID int
SET @TraceID = 2
EXEC @RC = sp_trace_setstatus @TraceID, 2
Terminiamo (stop) la traccia SQL con TraceID = 2
-- Stop trace (status = 0)
DECLARE @RC int, @TraceID int
SET @TraceID = 2
EXEC @RC = sp_trace_setstatus @TraceID, 0
Importiamo, nella tabella AUDITING, gli eventi memorizzati nei file di traccia (non in esecuzione) contenuti nella cartella C:\Audit_Trace. Per questa attività utilizziamo la stored procedure USP_TRACE_GETTABLE_IMPORT_FILE:
EXEC USP_TRACE_GETTABLE_IMPORT_FILE ‘C:\Audit_Trace’
Interrogando la tabella AUDITING possiamo determinare le informazioni richieste dal responsabile commerciale: la natura degli aggiornamenti eseguiti sull’anagrafica prodotti ed in particolare sul prezzo di listino, l’utente che ha eseguito tali aggiornamenti, l’applicazione utilizzata, la data e l’ora in cui è avvenuto l’aggiornamento, il prezzo di listino che aveva l’articolo prima dell’aggiornamento ed il prezzo di listino dopo l’aggiornamento. E’ stato possibile rilevare anche la cancellazione anagrafica di un prodotto.
SELECT * FROM AUDITING
La tabella AUDITINGMAIL contiene una riga per ogni notifica da inviare via posta elettronica ai destinatari indicati nei campi MailRecipients e MailCopy_Recipients. Per l’invio dei messaggi email è necessario configurare il servizio SQL Mail, successivamente si potrà utilizzare la stored procedure USP_AUDIT_MAIL.
Vediamo il contenuto della tabella AUDITINGMAIL:
Per l’invio delle email, dopo aver configurato SQL Mail sarà sufficiente eseguire
EXEC dbo.USP_AUDIT_MAIL
Questa soluzione si presta anche per eventuali altri scopi: modificando gli eventi monitorati dalla stored procedure USP_TRACE_AUDIT_CREATE sarà possibile ottenere informazioni dettagliate su:
- Eventi cursore
- Crescita o riduzione delle dimensioni del database
- Messaggi di errore e di avviso
- Blocchi acquisiti o rilasciati su oggetti SQL Server
- Eventi di accesso, come connessioni, disconnessioni e tentativi di accesso terminati con esito negativo
- Stato batch delle RPC
- Parallelismo di esecuzioni SQL
- Avvio e termine di una stored procedure
- Avvio e termine di un’istruzione SQL all’interno di una stored procedure
- Avvio e termine di un batch SQL
- Scansione di tabelle
- Istruzioni T-SQL (Select, Update, Insert ecc…)
