Mettre en place SQL Server Audit : connexions, modifications de permissions et accès aux tables

Commande

USE master;
CREATE SERVER AUDIT [Audit_DBA]
TO FILE (FILEPATH = N'D:\Audit\', MAXSIZE = 256 MB, MAX_ROLLOVER_FILES = 20, RESERVE_DISK_SPACE = OFF)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT [Audit_DBA] WITH (STATE = ON);

-- Server-wide (covers all databases):
CREATE SERVER AUDIT SPECIFICATION [AuditSpec_Server]
FOR SERVER AUDIT [Audit_DBA]
    ADD (FAILED_LOGIN_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
    ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
    ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
    ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
    ADD (SERVER_PERMISSION_CHANGE_GROUP),
    ADD (DATABASE_PERMISSION_CHANGE_GROUP),
    ADD (AUDIT_CHANGE_GROUP)
WITH (STATE = ON);

-- Database: access to a sensitive table:
USE [Musteri];
CREATE DATABASE AUDIT SPECIFICATION [AuditSpec_Musteri]
FOR SERVER AUDIT [Audit_DBA]
    ADD (SELECT, INSERT, UPDATE, DELETE ON OBJECT::[dbo].[Siparis] BY [public])
WITH (STATE = ON);

-- Read:
SELECT event_time, action_id, succeeded, server_principal_name, database_name,
       schema_name, object_name, LEFT(statement, 200) AS stmt, client_ip, application_name
FROM   sys.fn_get_audit_file(N'D:\Audit\Audit_DBA_*.sqlaudit', DEFAULT, DEFAULT)
WHERE  event_time >= DATEADD(HOUR, -24, SYSUTCDATETIME())
ORDER BY event_time DESC;

Ce que fait la commande

Crée un server audit qui écrit dans un fichier ; enregistre au niveau serveur les échecs de connexion, les appartenances aux rôles, les modifications de principaux et de permissions, et au niveau base les accès SELECT/DML à une table choisie, puis lit les enregistrements avec T-SQL.

Quand l'utiliser

Pour les exigences d'audit/conformité (RGPD, PCI, SOX), pour les questions "qui a lu/modifié cette table", ou en cas de suspicion d'élévation de privilèges.

Attention

L'audit des SELECT sur une table très sollicitée génère des enregistrements très vite et coûte en performances ; restreignez-le par objet et principal. ON_FAILURE = SHUTDOWN arrête le serveur quand l'audit ne peut pas être écrit ; ne l'utilisez que si c'est imposé. Le dossier d'audit doit être accessible en écriture au compte de service SQL Server et non supprimable par des non-DBA. event_time est en UTC. Une database audit specification est restaurée avec la base mais reste orpheline si le GUID du server audit ne correspond pas.