L’audit SQL Server consiste à tracer des événements qui se produise au niveau du serveur ou d’une base de données dans un fichier. Ces audits constitue une traçabilité des actions et permettent de remonter des informations comme la date, l’heure, l’action, l’objet, le compte de connexion, l’utilisateur…

Il existe principalement deux niveaux d’audit : l’audit niveau serveur (par exemple pour tracer des connexions) et l’audit de bases de données (par exemple pour savoir quelles commandes SQL ont été lancées sur telle ou telle table).

Les traces d’un audit sont stockées soit dans un fichier, dans le journal des événements de sécurité de Windows ou le journal des événements d’applications de Windows. néanmoins, tous les utilisateurs n’ont pas les droits leur permettant de générer des entrées dans les journaux systèmes, c’est pourquoi je vous recommande d’utiliser le mode fichier.

Attention, dès qu’une base de données est audité, il est important de prévoir la manière de la rattacher ou de la mettre en miroir. Dans ces deux cas, il convient de spécifier un tag identifiant le mécanisme d’audit avec un GUID lors de la création de ce dernier.

0 РPr̩paration

-- création d'un répertoire pour stockage des fichiers d'audit 

EXEC xp_cmdshell 'MKDIR "C:\AUDITSQL\"'; 

GO

1 – création de l’espace de stockage de l’audit

CREATE SERVER AUDIT ASV_1               --> le nom que vous voulez 

   TO FILE                              --> mais aussi APPLICATION_LOG, SECURITY_LOG  

      (FILEPATH = 'C:\AUDITSQL\',       --> le répertoire de stockage des fichiers d'audit 

       MAXSIZE = 50 MB,                 --> taille max des fichiers 

       MAX_ROLLOVER_FILES = 200,        --> nombre de fichier avant reprise circulaire 

       RESERVE_DISK_SPACE = ON)         --> pré réservation de l'espace 

   WITH  

      (QUEUE_DELAY = 1000,              --> délai max des opérations asynchrones de la file d'attente 

       ON_FAILURE = CONTINUE,           --> mais aussi SHUTDOWN si plus d'audit ! 

       AUDIT_GUID = '9D88D3BA-47E3-460C-906E-679DD81E2C39');

2 – mise en place des événements à auditer

-- audit de niveau serveur des sauvegardes et restaurations 

CREATE SERVER AUDIT SPECIFICATION ASP_BAKUP 

FOR SERVER AUDIT ASV_1 

    ADD (BACKUP_RESTORE_GROUP);

-- audit de niveau base pour SELECT sur table des adresses des personnes quelque soit l'utilisateur (PUBLIC = tout utilisateur) 

USE AdventureWorks2008;     

CREATE DATABASE AUDIT SPECIFICATION ASP_SELECT_ADV2008 

FOR SERVER AUDIT ASV_1 

    ADD (SELECT ON Person.Address BY PUBLIC);

3 – démarrage de l’audit

USE master;    

ALTER SERVER AUDIT SPECIFICATION ASP_BAKUP 

WITH (STATE = ON); 

 

USE AdventureWorks2008; 

ALTER DATABASE AUDIT SPECIFICATION ASP_SELECT_ADV2008 

WITH (STATE = ON); 

 

USE master;    

ALTER SERVER AUDIT ASV_1 

WITH (STATE = ON);

4 – tests

USE AdventureWorks2008; 

GO 

 

SELECT Top 10 *  

FROM    Person.Address AS A 

        INNER JOIN Person.BusinessEntityAddress AS BE 

              ON A.AddressID = BE.AddressID 

GO 

 

SELECT * 

FROM    Person.Address 

GO

5 – Dépouillement de l’audit

USE master 

GO 

-- voir le contenu de tous les fichiers de cet audit : 

SELECT *  

FROM   fn_get_audit_file('C:\AUDITSQL\*_9D88D3BA-47E3-460C-906E-679DD81E2C39*', NULL, NULL) 

-- Notez les joker * pour obtenir les informations de tous les fichiers 

 

-- voir les SELECT de cet audit 

SELECT a.name AS "ACTION", c.class_type_desc AS TYPE_OBJET, event_time AS DATE_HEURE, 

       f.server_principal_name AS Connexion,  

       f.schema_name AS NOM_SCHEMA, f.OBJECT_NAME AS NOM_OBJET, f.statement AS ORDRE_SQL 

FROM   sys.server_file_audits  as sfa 

       CROSS APPLY fn_get_audit_file('C:\AUDITSQL\*_9D88D3BA-47E3-460C-906E-679DD81E2C39*', NULL, NULL) AS f 

       INNER JOIN sys.dm_audit_class_type_map c  

             ON f.class_type = c.class_type 

       INNER JOIN sys.dm_audit_actions a  

             ON f.action_id = a.action_id  

                AND c.securable_class_desc = a.class_desc 

WHERE  a.name = 'SELECT' 

ORDER  BY event_time DESC, sequence_number

-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *