Auditing in Sql Server 2008

Le funzionalità di auditing di Sql Server 2008 permettono, in alcuni scenari, di ottenere con più semplicità informazioni sull'utilizzo del DB Engine rispetto a Sql Profiler, che comunque rimane ed è sempre valido. Di seguito trovate un semplice esempio su come tracciare le attività di selezione per un utente di test su una tabella. Gli statement sono commentati in modo da chiarire il perchè dei vari passaggi.

--creo un database di test

CREATE DATABASE TestDatabase

GO

USE [TestDatabase]

GO

--creo una tabella di test

CREATE TABLE [dbo].[TestTable](

      [table_id] [int] NOT NULL,

      [field1] [varchar](50) NULL,

      [field2] [varchar](50) NULL,

 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED

(

      [table_id] ASC

)

) ON [PRIMARY]

GO

--inserisco dei dati di test

INSERT INTO TestTable VALUES (1, 'paolino', 'paperino')

INSERT INTO TestTable VALUES (2, 'nonna', 'papera')

INSERT INTO TestTable VALUES (3, 'zio', 'paperone')

--creo la login e l'utente TestUser3 per il quale voglio fare audit

USE master

GO

CREATE LOGIN [nomeserver\TestUser3] FROM WINDOWS

GO

USE TestDatabase

GO

CREATE USER [TestUser3] FROM LOGIN [nomeserver\TestUser3]

GO

GRANT SELECT ON TestTable TO [TestUser3]

--creo il server audit

USE master

GO

CREATE SERVER AUDIT [TestServerAudit]

TO FILE

(

      FILEPATH = N'C:\Audit\', --il file di log verrà salvato in questa cartella

      MAXSIZE = 0 MB

)

WITH

(

      QUEUE_DELAY = 1000,

      ON_FAILURE = SHUTDOWN

)

--creo il database audit

USE TestDatabase

GO

CREATE DATABASE AUDIT SPECIFICATION [TestDatabaseAudit]

FOR SERVER AUDIT [TestServerAudit]

ALTER DATABASE AUDIT SPECIFICATION [TestDatabaseAudit]

ADD (SELECT ON dbo.TestTable BY TestUser3) --voglio monitorare tutte le istruzioni select effettuate dall'utente TestUser3

--abilito il server audit

USE master

GO

ALTER SERVER AUDIT [TestServerAudit]

      WITH (STATE=ON)

GO

--abilito l'audit del database da Sql Server Management Studio

enable_audit

--faccio una query sulla tabella TestTable con SQLCMD per testare l'audit

SQLCMD

--uso la funzione fn_get_audit_file per vedere il report sull'audit

select event_time,action_id,session_id,session_server_principal_name,[statement] from fn_get_audit_file('c:\audit\*',null,null)

--il risultato della query contiene lo statement eseguito dall'utente (la parte evidenziata in giallo)

 

printscreen3

Risorse aggiuntive :

https://technet.microsoft.com/en-us/library/cc280386.aspx