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