Les audits avec SQL Server 2008

L'objet d'audit SQL Server définit la cible dans laquelle les événements seront enregistrés. Chaque type de cible engendre une configuration des paramètres qui lui sont propres. Le mode d'écriture dans une cible peut être synchrone ou asynchrone par exemple (propriété QUEUE_DELAY). Il est également possible de contrôler le comportement de l'instance SQL Server lorsque celui-ci ne peut plus écrire dans la cible (propriété ON_FAILURE).
Il existe deux scénarios typiques.

L'instance SQL Server est arrêtée en cas d'échec d'écriture dans une cible. Dans ce premier cas, il peut être nécessaire de redémarrer le serveur en mode mono-utilisateur (option -m) ou avec une configuration minimale (option -f), de désactiver l'audit à l'origine du problème et enfin permettre un redémarrage normal du serveur. Une analyse du problème pourra se faire à postériori par un administrateur de bases de données.

Le serveur SQL n'est pas arrêté en cas d'échec d'écriture dans une cible. Dans ce deuxième cas, la mémoire tampon dédiée à l'objet d'audit SQL Server commence à se remplir. Par défaut, chaque objet d'audit SQL Server se voit allouer 4 MB d'espace mémoire). Que se passe-t-il si cette mémoire tampon est pleine ? Tant qu'il est impossible d'écrire dans la cible pour une raison quelconque, l'objet d'audit SQL Server bloquera tous les événements qu'il est censé enregistrer. Si le problème n'est pas résolu avant que le système d'exploitation renvoie lui-même une erreur (erreur d'écriture sur disque ou encore disque plein par exemple) l'objet d'audit SQL Server peut être désactivé et une erreur correspondante sera écrite dan le journal d'erreurs de SQL Server. Une fois le problème résolu, l'objet d'audit SQL Server devra être redémarré.

Chaque objet d'audit SQL Server ne peut collecter les données que d'un seul type d'objet de spécification d'audit. Les objets de spécification d'audit peuvent être créés à différents niveaux en fonction de l'événement ou du groupe d'événements que l'on désire surveiller. Il peut exister plusieurs objets d'audit SQL Server pour une instance. Enfin chaque objet d'audit SQL Server possède un état. Par défaut celui-ci est désactivé. Il faut l'activer pour pouvoir l'utiliser. Il faudra également désactiver un objet d'audit SQL Server si l'on veut modifier une de ses propriétés.

Les cibles sont des containers dans lesquels sont enregistrées les données relatives aux événements audités. Il existe trois types de cibles dans l'architecture d'audit SQL Server 2008. Deux concernent directement le journal d'événement Windows (APPLICATION_LOG ou SECURITY_LOG). Il est possible d'enregistrer les données d'événement d'audit soit dans le journal des applications soit dans le journal de sécurité. Pour ce dernier, certains paramétrages supplémentaires sont nécessaires au niveau du système d'exploitation. Nous y reviendrons plus tard dans l'article. La ^3e cible concerne les fichiers plats (FILE). Il est possible d'utiliser un ou plusieurs de ces fichiers en roulement (propriété MAX_ROLLOVER_FILES) pour ce type de cible à l'instar des traces SQL Profiler ou Perfmon. Il est donc nécessaire de paramétrer le chemin du ou des fichiers (propriété FILEPATH), le nombre de fichiers qui pourront être utilisés pour l'opération de roulement, et optionnellement de réserver directement l'espace total requis pour ces fichiers (propriété RESERVE_DISK_SPACE). Il est enfin nécessaire de créer le répertoire qui recevra les fichiers d'audit (le moteur ne le crée pas, mais vérifie son existence).

L'objet de spécification d'audit serveur est créé au niveau de l'instance. Celui-ci permet de transmettre différents événements ou actions au niveau de l'instance par le biais des événements étendus. Ces événements sont rassemblés en groupes d'événements classés par type. Le groupe d'événements est donc ici la granularité la plus fine qu'il est possible d'utiliser au niveau serveur. De la même manière qu'un objet d'audit serveur, un objet de spécification d'audit serveur est créé avec un état désactivé. Il faudra donc l'activer pour pouvoir l'utiliser. Il faudra également désactiver un objet de spécification d'audit serveur pour pouvoir modifier une de ses propriétés ou les événements à transmettre.

Un objet de spécification d'audit de bases de données fonctionne de la même manière qu'un objet de spécification d'audit de serveur à quelques différences près. Un objet de spécification d'audit de bases de données écoute et transmet les événements au niveau d'une base de données. De plus, le niveau de granularité des événements n'est pas le même. En effet, il est possible de récupérer des groupes d'événements ou un seul type événement.

Bien entendu comme pour la plupart des autres fonctionnalités, SQL Server fournit des vues système, des DMV (Dynamic Management View) et DMF (Dynamic Management Function) pour les audits. En voici la liste.

IV-E-1. Vues système▲

IV-E-2. DMV▲

IV-E-3. DMF▲

Commençons donc par créer trois instances d'objet d'audit SQL Server avec les trois types de cibles exigés.

USE master
GO 
 
-- Cible --> journal d'application Windows
CREATE SERVER AUDIT application_log_modif_status_server
TO FILE
(
 FILEPATH = 'E:\audit\',
 MAXSIZE = 100 MB,
 RESERVE_DISK_SPACE = ON
)
WITH
( 
 QUEUE_DELAY = 1000,
 ON_FAILURE = CONTINUE
);
 
 
-- Activitation de l'instance d'objet audit SQL Server
ALTER SERVER AUDIT application_log_modif_status_server
WITH ( STATE = ON );
 
-- Cible --> journal de sécurité Windows
CREATE SERVER AUDIT security_log_modif_security_login_user
TO SECURITY_LOG
WITH
( 
 QUEUE_DELAY = 0,
 ON_FAILURE = SHUTDOWN
);
 
-- Activation de l'instance d'objet audit SQL Server
ALTER SERVER AUDIT security_log_modif_security_login_user
WITH ( STATE = ON );
 
-- Cible --> Fichier plat
CREATE SERVER AUDIT file_modif_objects_database
TO FILE
(
 FILEPATH = 'E:\audit\',
 MAXSIZE = 100 MB,
 RESERVE_DISK_SPACE = ON
)
WITH
( 
 QUEUE_DELAY = 1000,
 ON_FAILURE = CONTINUE
);
 
-- Activation de l'instance d'objet audit SQL Server
ALTER SERVER AUDIT file_modif_objects_database
WITH ( STATE = ON );

Après avoir créé et activé les objets d'audit SQL Server pour les trois cibles concernées, il faut ensuite effectuer un paramétrage supplémentaire pour que SQL Server soit autorisé à écrire dans le journal de sécurité Windows. Pour cela nous utiliserons la console de gestion des stratégies locales du serveur, mais il est également possible d'utiliser les utilitaires en ligne de commande auditpol ou secpol. On accède à la console de gestion des stratégies locales du serveur à partir du Panneau de configuration qui est référencée dans le menu Outils d'administration.

La première étape consiste à autoriser le compte de service SQL Server à générer des audits de sécurité. Pour cela avec la console de gestion des stratégies locales, il faut se rendre sous le nœud « Attribut des droits utilisateurs » et choisir le paramètre « Générer des audits de sécurité » 

Il faut ajouter le compte de service du serveur SQL. Dans notre cas nous ajouterons le compte LOCAL SYSTEM.

Dans un deuxième temps il faut également activer les audits d'accès sur les objets. Ceci se fait aussi par la console de gestion des stratégies locales sous le nœud « Stratégie d'audit » et le paramètre « Auditer l'accès aux objets ».

Il faut activer les audits pour les deux types d'accès : Réussite et Échec

Bien entendu il faudra éventuellement veiller à ce qu'aucune stratégie descendue par GPO ne vienne modifier le comportement des paramètres configurés dans les stratégies locales. Attention à bien veiller à paramétrer ces accès surtout dans le cas où vous avez décidé de stopper le serveur SQL en cas d'échec d'écriture dans le journal de sécurité Windows. En effet lorsque vous activerez cet audit et si les accès ne sont pas corrects, l'audit stoppera automatiquement le serveur !!!

Il faut maintenant créer deux objets de spécification d'audit au niveau de l'instance SQL Server. Un objet de spécification d'audit sera à l'écoute des événements ou actions concernant les changements sur les comptes de connexion. Un deuxième objet de spécification d'audit sera, quant à lui, destiné aux changements d'état intervenant sur le serveur.

USE [master];
GO
 
-- Audit changement statut serveur
CREATE SERVER AUDIT SPECIFICATION audit_change_status_server
FOR SERVER AUDIT file_modif_objects_database
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (SERVER_OPERATION_GROUP)
WITH ( STATE = ON );
GO
 
-- Audit modification sécurité des comptes de connexion
CREATE SERVER AUDIT SPECIFICATION audit_modif_users_logins
FOR SERVER AUDIT security_log_modif_security_login_user
ADD (FAILED_LOGIN_GROUP), -- Connexion échouée
ADD (LOGIN_CHANGE_PASSWORD_GROUP),  -- Changement de mot passe
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), -- Changement rôle serveur
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP) -- Changement rôle base de données
WITH ( STATE = ON );

Nous allons ajouter à l'objet de spécification d'audit serveur nommé audit_modif_users_login le groupe d'événements SERVER_PRINCIPAL_CHANGE_GROUP qui permet de surveiller, entre autres, les événements de création, de suppression et de modification de compte de connexion. Pour rappel, il faudra, au préalable, le désactiver pour pouvoir le modifier.

ALTER SERVER AUDIT SPECIFICATION audit_modif_users_logins
WITH ( STATE = OFF );
 
-- Ajout d'un événement pour l'instance d'objet de spécification d'audit serveur
-- audit_modif_users_logins
ALTER SERVER AUDIT SPECIFICATION audit_modif_users_logins
ADD (SERVER_PRINCIPAL_CHANGE_GROUP); -- Evénement CREATE, DROP , ALTER;
 
ALTER SERVER AUDIT SPECIFICATION audit_modif_users_logins
WITH ( STATE = ON );

Après avoir créé les objets de spécification d'audit serveur, il nous faut créer maintenant un autre objet de spécification d'audit de bases de données pour surveiller les événements SELECT, INSERT, UPDATE, DELETE et RERENCES en provenance de l'utilisateur dbo pour la table dbo.access.

USE DB_AUDIT
GO
 
-- Audit d'accès pour la table dbo.access de la base de données
-- DB_AUDIT
CREATE DATABASE AUDIT SPECIFICATION file_modif_objects_database
FOR SERVER AUDIT file_modif_objects_database
ADD (SELECT, INSERT, UPDATE, DELETE, REFERENCES ON dbo.access BY public)
WITH (STATE = ON)
GO

Les outils de visualisation d'événements diffèrent selon le type de cible utilisé. On peut par exemple utiliser directement le journal des événements pour un type de cible se basant sur le journal de sécurité ou d'application. SQL Server met également à disposition une DMF qui permet de lire les données d'audit dans un fichier plat.

V-D-1. Audit de sécurité des comptes de connexion▲

Commençons par simuler des actions pouvant être effectuées sur des objets de type comptes de connexion :

 

Sélectionnez

-- Création compte de connexion test1
CREATE LOGIN test1
WITH PASSWORD = 'test1';
 
-- Création compte de connexion test2
CREATE LOGIN test2
WITH PASSWORD = 'test2';
 
-- Modification mot de passe compte de connexion test1
ALTER LOGIN test1
WITH PASSWORD = '%test1%';
 
-- Modification mot de passe compte de connexion test2
ALTER LOGIN test2
WITH PASSWORD = '%test2%';
 
-- Ajout du compte de connexion test1 au rôle fixe de serveur sysadmin
EXEC sp_addsrvrolemember 'test1', 'sysadmin';
 
-- Ajout du compte de connexion test2 au rôle fixe de serveur sysadmin
EXEC sp_addsrvrolemember 'test2', 'bulkadmin';
 
-- Suppression du compte de connexion test1
DROP LOGIN test1;
 
-- Suppression du compte de connexion test2
DROP LOGIN test2;

Il suffit maintenant de regarder dans le journal de sécurité de Windows. L'ID de l'événement généré par les audits SQL Server 2008 dans le journal de sécurité est égal à 33205. Voici un exemple de ce que l'on trouve dans le journal après avoir exécuté les scripts TSQL ci-dessus :

Le détail d'une ligne du journal concernant la création d'un des comptes de connexion :

Le détail d'une ligne du journal concernant le changement de mot de passe d'un des comptes de connexion :

Le détail d'une ligne du journal concernant l'affectation d'un des comptes de connexion au rôle de serveur fixe sysadmin :

Le détail d'une ligne du journal concernant la suppression d'un des comptes de connexion :

V-D-2. Audit des changements d'état du serveur SQL▲

Initions maintenant un changement dans les options de configuration du serveur. Nous autorisons la procédure xp_cmdshell.

 

Sélectionnez

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell' , 1;
GO
RECONFIGURE;

… et nous redémarrerons également notre serveur de bases de données :

 

Sélectionnez

net stop mssqlserver /y && net start mssqlserver && net start sqlserveragent

Pour pouvoir visualiser les enregistrements d'audits dans un fichier plat nous utiliserons la DMF fn_get_audit_file, les vues système sys.dm_audit_actions et sys.dm_audit_class_type de la manière suivante :

 

Sélectionnez

SELECT 
    f.session_id,
    a.name AS action_name,
    m.class_type_desc,
    a.covering_action_name,
    f.statement,
    f.server_principal_name
FROM fn_get_audit_file('E:\audit\application_log_modif_status_server*', DEFAULT, DEFAULT) AS f
INNER JOIN sys.dm_audit_class_type_map AS m
ON m.class_type = f.class_type
INNER JOIN sys.dm_audit_actions AS a
ON a.action_id = f.action_id 
ORDER BY event_time DESC

Le résultat est le suivant :

On peut voir les lignes d'audit en relation avec les actions de type « ALTER SETTINGS » et « SERVER_STARTED ».

V-D-3. Audit des accès à la table dbo.access▲

 

Sélectionnez

-- opération SELECT 
SELECT * 
FROM dbo.access
 
-- opération INSERT
INSERT dbo.access VALUES (1, 'emp1');
INSERT dbo.access VALUES (2, 'emp2');
 
-- opération UPDATE
UPDATE dbo.access
SET name_emp = 'emp1'
WHERE id = 1;
 
-- opération DELETE
DELETE FROM dbo.access
WHERE id = 2;
GO

Le résultat est le suivant :

Chaque ligne d'audit correspond à un événement. On peut remarquer pour les instructions UPDATE et DELETE il existe deux lignes d'audit. Quelle en est la raison ? Dans notre cas, la requête effectue des mises à jour ou des suppressions en utilisant un prédicat (clause WHERE). Cette action est possible à la condition de posséder le droit SELECT sur l'objet concerné (ici la table dbo.access). C'est la raison pour laquelle nous aurons, à chaque fois qu'une instruction UPDATE ou DELETE est effectuée en utilisation une clause WHERE, deux lignes correspondant aux actions SELECT et UPDATE ou DELETE.

Un certain nombre de DMV existe pour visualiser la configuration des différents composants de l'architecture d'audit SQL Server 2008. Voici quelques exemples d'utilisation de ces vues de gestion.

• Visualisation des différentes instances d'objets d'audits SQL Server, de leurs instances d'objets de spécification d'audit respectives, les événements ou groupes d'événements audités et les paramètres de fichier pour les types de cibles fichier plat :

SELECT 
    sa.name AS audit_name,
    sa.is_state_enabled,
    sa.on_failure_desc,
    sa.queue_delay,
    sa.type_desc,
    sas.name AS srv_spe_name,
    sas.is_state_enabled,
    (SELECT audit_action_name + ', '
     FROM sys.server_audit_specification_details AS sasd
     WHERE sasd.server_specification_id = sas.server_specification_id
     FOR XML PATH('')) AS list_actions,
    fa.log_file_path,
    fa.log_file_name,
    fa.max_file_size,
    fa.max_rollover_files
FROM sys.server_audit_specifications AS sas
INNER JOIN sys.server_audits AS sa
ON sa.audit_guid = sas.audit_guid
LEFT JOIN sys.server_file_audits AS fa
ON fa.audit_guid = sa.audit_guid

• Visualisation de la configuration des différentes instances d'objets d'audit de spécification de bases de données

SELECT 
    DISTINCT
    sa.name,
    sa.is_state_enabled,
    class_desc,
    (SELECT sasd.audit_action_name + ', '
     FROM sys.database_audit_specification_details AS sasd
     WHERE sasd.database_specification_id = sa.database_specification_id
     FOR XML PATH('')) AS list_actions 
FROM sys.database_audit_specification_details AS sas
INNER JOIN sys.database_audit_specifications AS sa
ON sas.database_specification_id = sa.database_specification_id

• Visualisation du statut des différentes cibles d'audits :

SELECT 
    name AS audit_name,
    status_desc,
    status_time,
    audit_file_path,
    audit_file_size /1024 AS size_in_Ko
FROM sys.dm_server_audit_status;

• Visualisation de la configuration des instances d'audits SQL Server concernées par le type de cible fichier :

SELECT
    name, 
    on_failure,
    max_file_size,
    max_rollover_files,
    log_file_path,
    log_file_name  
FROM  sys.server_file_audits

• Visualisation du détail de la configuration d'une spécification d'audit pour une base de données :

USE DB_AUDIT;
GO
 
SELECT 
    database_specification_id,
    audit_action_name,
    class_desc,
    audited_result,
    is_group
FROM sys.database_audit_specification_details AS das
WHERE das.database_specification_id = (SELECT database_specification_id 
                                    FROM sys.database_audit_specifications 
                                    WHERE name ='file_modif_objects_database')
ORDER BY class_desc;

• Visualisation des informations d'audits et de leurs sessions d'événements étendues respectives : remarquez que pour l'objet d'audit « security_log_modif_security_login_user » aucun buffer n'est alloué. Ceci est normal puisque nous avons demandé une écriture synchrone pour cet audit.

SELECT 
    a.name,
    x.name AS session_name,
    t.target_name,
    x.total_buffer_size 
FROM sys.dm_server_audit_status AS a
INNER JOIN sys.dm_xe_sessions AS x
ON a.event_session_address = x.address
INNER JOIN sys.dm_xe_session_events AS e
ON x.address = e.event_session_address
INNER JOIN sys.dm_xe_session_targets AS t
ON t.event_session_address = e.event_session_address