Home > Meccanismi di auditing

Meccanismi di auditing

Sergio Govoni 17 Settembre 2008 Senza categoria 3,129 Views

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

      ‘manager@sales.it’,

      — @MailMessage

      ‘Il campo ListPrice della tabella Production.Product è stato modificato.’,

      — @MailCopy_Recipients

      ‘manager@sales.it’,

      — @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.

click to enlarge

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

click to enlarge

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:

 Click to enlarge

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…)

Tags

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

1nn0va Saturday 2024 – Agenda pubblicata!

L’agenda dell’evento 1nn0va Saturday 2024 è stata pubblicata ed è disponibile qui. Ospitato nelle aule …

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

twelve − = four

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.

User Group Italiano SQL Server - Via Del Capanno 26, Lodi - P.IVA: 03015800968
Tutti i contenuti (c) UGISS, 2024