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
--faccio una query sulla tabella TestTable con SQLCMD per testare l'audit
--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)
Risorse aggiuntive :