Les audits avec SQL Server 2008

Commentez Donner une note à l'article (4)

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction

Dans des environnements complexes où il existe différents types d'acteurs qui interagissent tous de différentes manières avec la base de données, le besoin d'auditer certains événements devient primordial pour une entreprise.

Mais que doit auditer exactement un administrateur de bases de données ? La réponse n'est pas si évidente à priori et doit faire l'objet d'un véritable travail en amont car ceci dépend bien entendu des besoins et du contexte client. SQL Server 2008 propose une nouvelle fonctionnalité d'audit unifiée qui permet de mettre en place facilement ces audits ce qui permet à un administrateur de bases de données de se concentrer uniquement sur le véritable enjeu : trouver les audits qui seront en adéquation avec les besoins de l'entreprise.

II. Quels avantages à utiliser ce système d'audit ?

Les audits ne sont pas une nouveauté sur SQL Server. Depuis la version 2000 il existe différents outils qui permettent d'auditer plus ou moins précisément les événements sur le serveur de bases de données. Cependant, jusqu'à la version 2005 de SQL Server, il fallait utiliser plusieurs de ces outils pour pouvoir auditer différentes catégories d'événements. Par exemple l'implémentation des triggers de type DML peuvent servir à enregistrer des instructions DML dans des tables d'historisation. Des triggers de type DDL, quant à eux, peuvent enregistrer n'importe quelle instruction DDL. Enfin le profiler, les notifications d'événements ou les alertes de type WMI peuvent enregistrer d'autres événements comme les changements de configuration de serveur ou les changements de droits sur les comptes de connexion ou les utilisateurs.

Le nouveau système d'audit proposé avec SQL Server 2008 facilite le travail de l'administrateur de bases de données car il ne propose qu'un seul outil à utiliser pour l'ensemble des catégories d'événements à auditer.

Un autre aspect important de cette nouvelle fonctionnalité est la sécurité qui n'est pas implémenté avec les autres outils proposés par SQL Server. En effet, les changements qui pourraient intervenir sur les audits eux mêmes sont enregistrés, ce qui limite fortement les possibilités de contournements par une personne mal intentionnée. Il va de soi que les fichiers d'audit ne soient accessibles que par un nombre restreint de personnes.

Enfin, l'impact sur les performances du serveur est minimisé avec ce nouveau système pour plusieurs raisons :

  • Il est possible d'écrire de façon asynchrone les événements dans les cibles d'audit
  • En adoptant une stratégie de limitation au strict nécessaire des événements à surveiller on minimise l'impact sur les performances du serveur
  • Le choix du type de cible est également déterminant. Les performances sont améliorées si l'on choisit les fichiers plats comme type de cible

III. Quels sont les inconvénients ?

Cependant si cette fonctionnalité possède beaucoup d'avantages il faut également tenir compte de certains inconvénients :

  • Elle n'est disponible qu'à partir de la version Entreprise
  • Elle ne peut être implémentée qu'au niveau d'une instance. On ne peut donc pas utiliser cette fonctionnalité de manière centralisée sur une batterie de serveurs.
  • Il n'existe pas d'outil de Reporting en natif.

IV. Architecture du système d'audit

L'architecture de la nouvelle fonctionnalité d'audit utilise les événements étendus de SQL Server 2008. Ces événements étendus sont également une nouveauté de cette version. Cette infrastructure permet de corréler des données d'événements provenant de SQL Server mais aussi du système d'exploitation, voire même de certaines applications de bases de données. Nous ne rentrerons pas dans le détail des événements étendus. Ils feront l'objet d'un autre article.

Revenons-en aux audits. Plusieurs types d'objets composent cette architecture. Chaque type d'objet a un rôle bien spécifique et permet de gérer les audits à différents niveaux. En effet, il est possible d'auditer des événements au niveau de l'instance SQL Server, au niveau d'une base de données ou encore au niveau d'un schéma d'une base de données spécifique. Chaque audit peut être enregistré dans plusieurs types de cibles qui peuvent être des fichiers plats ou des journaux d'événements.

L'architecture générale peut donc être représentée de la manière suivante :

image


Des objets de spécification d'audit de serveur ou de bases de données sont à l'écoute d'événements ou groupes d'événements en se basant sur l'infrastructure d'événements étendus. Ceux-ci sont ensuite transmis aux instances d'objets d'audit serveur. Selon le paramétrage défini, les événements sont ensuite enregistrés de manière synchrone ou asynchrone dans leurs cibles respectives.

IV-A. Objet d'audit SQL Server

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érieri 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 4MB 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énement 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.

IV-B. Cibles

Les cibles sont des containers dans lesquels sont enregistrées les données relatives aux événements audités. Il existe 3 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 3ème 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).

IV-C. Objet de spécification d'audit serveur

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éée 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.

IV-D. Object de spécification d'audit de bases de données

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.

IV-E. Vues de gestion des audits

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

IV-E-1. Vues systèmes

sys.server_audits : Chaque ligne de cette vue correspond à une instance d'objet d'audit d'audit SQL Server.

sys.server_audit_specifications : Chaque ligne de cette correspond à une instance d'objet de spécification d'audit serveur.

sys.server_audit_specification_details : Chaque ligne de cette vue correspond à un groupe d'événements audité pour chaque instance d'objet de spécification d'audit serveur. Il peut y avoir plusieurs lignes pour une instance d'objet de spécification d'audit serveur étant donné que celle-ci peut auditer plusieurs groupes d'événements à la fois.

sys.database_audit_specifications : Chaque ligne de cette vue correspond à une instance d'objet de spécification d'audit de bases de données.

sys.database_audit_specification_details : Chaque ligne de cette vue correspond à un groupe d'événements ou un événement audité pour chaque instance d'objet de spécification d'audit de bases de données. De la même manière que pour les instances d'objets de spécification d'audit serveur, il peut exister plusieurs lignes pour une instance d'objet de spécification d'audit de bases de données.

IV-E-2. DMV

sys.dm_server_audit_statut : Cette vue permet de connaître l'état de chaque instance d'objet d'audit SQL Server.

sys.dm_audit_actions : Cette vue permet de lister les événements ou groupes d'événements pouvant être enregistrés dans un fichier de log. Il est également possible de connaître leur niveau de configuration (bases de données ou serveur) et à quel groupe d'événements ils appartiennent.

sys.dm_audit_class_type_map : Cette vue retourne une table de correspondance entre les valeurs d'une colonne « class_type » d'un fichier d'audit et d'une colonne « class_desc » de la DMV sys.dm_audit_actions.

sys.server_file_audits : Chaque ligne de cette vue correspond à un fichier d'audit créé sur une instance SQL Server. On y retrouve notamment des informations de paramétrage comme le nom et le chemin du fichier, sa taille maximum, le type d'écriture (synchrone ou asynchrone), sa date de création etc .

IV-E-3. DMF

fn_get_audit_file() : Cette fonction permet d'extraire les informations d'un fichier d'audit. Celle-ci permet également de lire plusieurs fichiers en même temps. Cette fonction ne peut être utilisée que pour les cibles de type fichier plat.

V. Cas d'étude

Ce cas d'étude répond à trois besoins que vous pouvez rencontrer en entreprise :

  • Auditer et enregistrer tous les changements intervenant sur les comptes de connexion du serveur de bases de données.
  • Auditer et enregistrer les changements d'état du serveur de bases de données.
  • Auditer l'accès à certains objets d'une base de données pour connaître son utilisation réelle.

Nous utiliserons 3 types de cibles pour enregistrer les différents audits. La politique de sécurité de l'entreprise impose que les audits relatifs aux changements intervenant sur les comptes de connexion se fassent de manière synchrone et dans le journal de sécurité Windows. Si un problème quelconque ne permettait pas d'enregistrer ces changements alors le serveur de bases de données doit être arrêté. Les audits des changements d'état ou de configuration du serveur de bases de données ainsi que tous les changements relatifs à l'accès des objets d'une base de données nommée DB_AUDIT se feront dans un fichier plat d'une taille de 100Mo maximum, ce qui permettra de garder un historique convenable en concordance avec les exigences de l'entreprise. Les écritures dans le fichier se feront de manière asynchrone avec un délai maximum d'une seconde pour ne pas altérer de manière significative les performances du serveur.

Nous utiliserons le langage TSQL pour créer nos objets d'audits. Il est cependant possible d'arriver au même résultat avec la console graphique de gestion des bases de données SQL Server Management Studio.

 
Sélectionnez

CREATE DATABASE DB_AUDIT;
GO
 
USE DB_AUDIT;
GO
 
CREATE TABLE dbo.access
(
 id INT PRIMARY KEY,
 name_emp VARCHAR(50) NOT NULL
);
GO

V-A. Création des instances d'objets d'audit SQL Server

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

 
Sélectionnez

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 3 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 de des droits utilisateurs » et choisir le paramètre « Générer des audits de sécurité » 

image


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

image


Dans un deuxième temps il faut également activer les audits d'accès sur les objets. Ceci ce 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 ».

image


Il faut activer les audits pour les 2 types d'accès : Réussite et Echec

image


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 !!!

V-B. Création des objets de spécification d'audit 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.

 
Sélectionnez

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énement  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.

 
Sélectionnez

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 );

V-C. Création des objets de spécification d'audit de bases de données

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 .

 
Sélectionnez

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

V-D. Visualisation des enregistrements d'audit

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 égale à 33205. Voici un exemple de ce que l'on trouve dans le journal après avoir exécuté les scripts TSQL ci-dessus :

image

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

image

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

image

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

image

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

image

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 fichierplat nous utiliserons la DMF fn_get_audit_file, les vues systèmes 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 :

image


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 :

image


Chaque ligne d'audit correspond à un événement. On peut remarquer pour les instructions UPDATE et DELETE il existe 2 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.

V-E. Visualisation de la configuration des audits

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 :
 
Sélectionnez

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
 
Sélectionnez

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
image
  • Visualisation du statut des différentes cibles d'audits :
 
Sélectionnez

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;
image
  • Visualisation de la configuration des instances d'audits SQL Server concernées par le type de cible fichier :
 
Sélectionnez

SELECT
	name, 
	on_failure,
	max_file_size,
	max_rollover_files,
	log_file_path,
	log_file_name  
FROM  sys.server_file_audits
image
  • Visualisation du détail de la configuration d'une spécification d'audit pour une base de données :
 
Sélectionnez

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;
image
  • 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.
 
Sélectionnez

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
image

VI. Conclusion

SQL Server 2008 possède un outil d'audit extrêmement puissant. Cette nouvelle fonctionnalité permet de répondre parfaitement aux aspects sécurité, performance et gestion. Dommage que cette fonctionnalité ne soit disponible qu'en version entreprise ... Cependant si votre entreprise possède déjà un outil d'audit des bases de données SQL Server, cela vaut la peine de tester cette nouvelle fonctionnalité et d'établir un benchmarking des fonctionnalités !!

VII. Webographie

Documentation msdn

VIII. Bibliographie

70-432 : Microsoft SQL Server 2008--Implementation and Maintenance : Training Kit

IX. Remerciements

Je remercie tout particulièrement Elsuket pour avoir contribué à l'élaboration de cet article.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2010 David BARBARIN. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.