Datafly.pro

Comment fonctionne l'audit de SQL Server ?

L'audit dans SQL Server permet d'assurer le suivi des actions effectuées sur les bases de données. D'une manière générale, cela permet aux administrateurs de surveiller et d'enregistrer une série d'activités et d'événements du système.

SQL Server propose deux niveaux d'audit :

• Audit au niveau du serveur : Suivi des actions au niveau du serveur, comme les changements de configuration ou les tentatives de connexion.
• Audit au niveau de la base de données : Surveillance des actions spécifiques à une base de données, telles que les modifications de schéma ou l'accès aux données : select, insert, update, delete, etc..

Pour configurer l'audit dans SQL Server, vous devez d'abord :

• Configurer l'audit avec la commande CREATE SERVER AUDIT, en spécifiant la destination (généralement un fichier, mais il est possible d'envoyer l'audit vers l'event log windows applications ou sécurité). Dans le cas d'une destination fichier, vous pouvez préciser ses caractéristiques : taille, nombre, rotation.
• Créer une ou plusieurs spécifications d'audit : Définissez ce que vous voulez auditer, au niveau du serveur ou de la base de données, en précisant vers quel audit serveur (créé à l'étape précédente) doivent être envoyées les données.

Une fois l'audit configuré, vous pouvez activer ou désactiver à volonté l'audit ou une spécification d'audit : vous avez la flexibilité de démarrer ou d'arrêter l'audit selon vos besoins.

Pour lire les données d'audit, vous pouvez passer par SQL Server Management Studio, ou lire le fichier avec la fonction SQL fn_get_audit_file.

Attention : l'audit peut avoir un impact sur les performances du serveur, surtout s'il est configuré pour auditer un grand nombre d'événements. Limitez l'audit aux actions et aux événements les plus critiques.

Exemple

Voici un exemple de configuration à adapter à votre cas de figure : mes développeurs sont membre d'un rôle de bases de données 'Developpers_role', il va donc être plus facile de les tracer.

Je configure d'abord l'audit, vers une destination de fichiers dans le répertoire log de SQL Server, avec 5 fichiers de 100 MB en rotation.

USE master;
GO
DECLARE @errorlogpath NVARCHAR(1000)
      , @sql NVARCHAR(MAX);
SELECT @errorlogpath = LEFT(path, LEN(path) - 1)
FROM sys.dm_os_server_diagnostics_log_configurations;
-- Create server audit
SET @sql
    = N'CREATE SERVER AUDIT [Developpers_Audit]
TO FILE
(    FILEPATH = ''' + @errorlogpath
      + N'''
    ,MAXSIZE = 100 MB
    ,MAX_ROLLOVER_FILES = 5
    ,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);';
EXEC (@sql);
-- Enable the server audit.  
ALTER SERVER AUDIT Developpers_Audit   
WITH (STATE = ON) ;  
GO  

Je vais maintenant configurer la spécification d'audit dans la base de données à surveiller : je capture les SELECT, UPDATE, INSERT, DELETE et EXECUTE sur tous les objets du schéma dbo, uniquement pour les développeurs. Facultativement, je peux aussi capturer les CREATE, ALTER ou DROP de tous les utilisateurs.

-- Target database
USE AdventureWorks2019;  
GO  
-- Create the database audit specification.  
CREATE DATABASE AUDIT SPECIFICATION DB_Developpers_Audit  
FOR SERVER AUDIT Developpers_Audit  
-- actions filtrées sur les développeurs
ADD (SELECT , UPDATE, INSERT , DELETE, EXECUTE
     ON Schema::dbo BY Developpers_role )
-- si nécessaire pour tous les create/alter/drop
-- , ADD  (SCHEMA_OBJECT_CHANGE_GROUP)  
WITH (STATE = ON) ;  
GO 

Et maintenant, voici comment lire mon fichier d'audit en SQL (je peux bien sûr envoyer cela vers une table...)

-- Read audit file
SELECT event_time
     , action_id
     , server_principal_name
     , database_principal_name
     , database_name
     , object_name
     , statement
     , duration_milliseconds
     , response_rows
     , affected_rows
FROM fn_get_audit_file('Developpers_audit*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id IN ( 'SL', 'UP', 'DL', 'EX','CR','AL','DR');

Bon audit, et n'oubliez pas de gronder les développeurs pour leurs requêtes gourmandes sur la production...