L’AUDIT dans SQL Server 2008

Par Christopher Maneu, posté le 06/10/2009

Profil : Développeur | Niveau : Intermédiaire (200)

Tags : SQL Server | Partager :

Ne vous est-il jamais arrivé de vous apercevoir par hasard que l'une des tables de la base de données qui contient la vie de l'entreprise a été modifiée en votre absence, ou que le compte utilisé par une application qui est censée ne plus être utilisée se connecte tous les jours ? Comment faire pour savoir ce qui se passe vraiment sur votre serveur ?

En quelque sorte, l'AUDIT est le twitter de votre serveur SQL. Il permet, entre autre, de répondre à la célèbre question « qui a fait quoi ? ». Nous allons voir tout au long de cet article quelles sont les usages de cette fonctionnalité, dans quels cas l'utiliser, comment la mettre en ouvre et comment exploiter les résultats.

Que peut-on faire avec l'audit ?

La fonctionnalité AUDIT de SQL Server 2008 vous permet de capturer des événements qui surviennent sur votre serveur. Ces actions peuvent être enregistrées dans trois endroits différents (une seule destination par audit) :

• · Un fichier de log,
• · Le journal d'événements système,
• · Le journal d'événements de l'application.

L'audit, un objet à part entière !
Les AUDIT sont des objets serveurs à part entière. Vous pouvez donc créer un AUDIT qui porte sur les audits !

L'ensemble des événements capturables possibles sont regroupées dans deux catégories : les actions au niveau du serveur (Server Audit Specifications) et les actions au niveau de la base de données (Database audit specifications).

L'audit serveur permet de connaître :

• · Les connexions réussies et échouées, les déconnexions et changements de mots de passe,
• · Les changements de rôles et des utilisateurs,
• · L'exécution d'une sauvegarde ou d'une restauration,
• · Les changements de paramètres, ressources, autorisations, et permissions
• · Les changements au niveau des audits,
• · Les créations, modifications et suppression de bases de données,
• · Les changements de schémas sur toutes les bases (voir liste audit bases de données),
• · Les actions d'impersonation.

L'audit bases de données quant à lui permet de capturer :

• · Les ordres SELECT, INSERT, UPDATE, DELETE effectués sur des objets du schéma (tables, vues, procédures stockées,.)
• · Les changements de rôles ou d'autorisations,
• · La création, modification ou suppression d'objets de la base de données,

Pour certaines de ces actions, vous aurez besoin de préciser un objet sur lequel appliquer la capture et/ou un principal name.

Dans quels cas utiliser l'AUDIT ?

Nous savons maintenant de quoi est capable la fonctionnalité d'audit de SQL 2008. Cependant, indépendamment des possibilités fournies, que peut-on faire avec ?

Vous pouvez tout d'abord réaliser des audits de sécurité, que ce soit pour vous conformer aux politiques de sécurité de votre entreprise, ou pour vérifier la sécurité de vos bases de données (droits des guests, les connexions qui échouent, les changements de mots de passes, .).

Il y a un autre cas d'utilisation intéressant de l'audit : surveillez l'intégrité de vos bases. Vous pouvez, par exemple, créer un audit qui capture tous les changements au niveau du schéma de vos bases de données ainsi que sur les droits. Pour savoir ce qui a changé, il vous suffira de relire le fichier de log pour savoir ce qui a été modifié en votre absence.

Il se peut que vous ayez dans votre environnement un important serveur SQL, avec un très grand nombre de tables, d'applications et d'utilisateurs connectés dessus. L'audit peut vous permettre de réaliser une cartographie de votre serveur : quelles applications accèdent à quelles informations et avec quels comptes ?

Mettre en place l'AUDIT

La première étape consiste à créer un AUDIT. Cet objet contient simplement l'emplacement dans lequel les données provenant de l'audit seront stockés. Vous devez ensuite préciser quoi auditer, ce sont les AUDIT SPECIFICATION. Il en existe deux types : les databases audits specification ou les server audits specification. Chaque AUDIT SPECIFICATION que vous créez contient des actions à auditer. Enfin, il faudra les associer à un AUDIT afin d'avoir un emplacement dans lequel enregistrer les données auditées. Chaque AUDIT peut contenir n'importe quelle combinaison d'AUDIT SPECIFICATION.

Figure 1: La composition d'un audit

Les AUDIT ne sont pas actifs dès leur création, vous devez les activer manuellement. Chaque brique étant indépendante, si vous souhaitez activer l'ensemble des composants de AUDIT, vous devez - dans l'exemple du schéma ci-dessus- activer l'audit puis activer les deux DB audit et le server audit.

Figure 2 Les étapes d'activation et de création d'un audit

Création d'un audit avec Management Studio

Que cela soit au niveau du serveur ou des bases de données, les audits se situent dans le dossier Security.

Etape 1 : Création de l'audit

Nous allons commencer par créer un audit ; pour se faire, allez dans [Votre serveur] > Security > Audits depuis l'object explorer.

La fenêtre de création d'audit vous permet d'indiquer un nom, une destination d'enregistrement pour l'audit (fichier ou journaux) ainsi que d'autres paramètres. Vous pouvez également faire en sorte que le serveur s'arrête si l'activation de l'audit échoue, et ainsi vous assurer que votre audit est toujours exécuté.

Etape 2 : Création d'un audit serveur (optionnel)

Si vous souhaitez auditer des événements serveurs, vous pouvez créer un audit serveur depuis [Votre serveur] > Security > Server Audit Specifications dans l'object explorer.

Dans la fenêtre de création, vous devez spécifier :

· Un nom,

· L'audit auquel le server audit doit être rattaché,

· La liste des événements à auditer.

Etape 3 : Création d'un audit bases de données (optionnel)

Si vous souhaitez auditer des événements sur l'une de vos bases de données, vous pouvez créer un audit database depuis [Votre serveur] > Databases > [Votre base de données] > Security > Database Audit Specifications dans l'object explorer.

Tout comme pour l'audit serveur, vous aurez besoin d'indiquer le nom ainsi que l'audit de rattachement, ainsi que les actions à auditer. Cependant, de nombreuses actions d'audit database nécessitent une classe d'objet, voire un principal name. C'est notamment le cas de l'audit des fonctions Select, Insert, Update, Delete ; qui ne peuvent s'appliquer que sur une table ou une base de données précise, ainsi que sur un utilisateur, DB role ou application role.

Etape 4: Activation de l'audit

A ce stade, vous avez décrit votre audit, cependant il n'est pas encore actif. Il faut tout d'abord activer l'audit lui-même, puis tous les audits serveurs et database qui lui sont rattachés.

Pour se faire, il suffit de faire un clic droit sur l'audit concerné et de choisir Enable Audit, Enable Server Audit Specification ou Enable Database Audit Specification selon le cas.

Votre audit est à présent activé, et il capture les données. Lorsque vous souhaitez arrêter la capture, il suffit de le désactiver de la même manière que vous l'avez activé.

Création d'un AUDIT en T-SQL

Voici les différents scripts qui vous permettent de constituer votre audit. Bien évidemment, le principe reste le même qu'au travers de Management Studio.

Etape 1 : Création de l'audit

   1:  CREATE SERVER AUDIT [MonAudit]

   2:  TO FILE (

   3:     FILEPATH = N'D:\'

   4:     ,MAXSIZE = 0 MB

   5:     ,MAX_ROLLOVER_FILES = 2147483647

   6:     ,RESERVE_DISK_SPACE = OFF

   7:  )

   8:  WITH (

   9:     QUEUE_DELAY = 1000

  10:     ,ON_FAILURE = CONTINUE

  11:  )

  12:  GO

Etape 2 : Création d'un audit serveur (optionnel)

   1:  CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20090914-094010]

   2:  FOR SERVER AUDIT [MonAudit]

   3:  ADD (

   4:     FAILED_LOGIN_GROUP

   5:  )

   6:  GO

Etape 3 : Création d'un audit bases de données (optionnel)

   1:  CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20090914-094145]

   2:  FOR SERVER AUDIT [MonAudit]

   3:  ADD (

   4:     INSERT ON OBJECT::[dbo].[MaTable] BY [db_datawriter]

   5:  )

   6:  GO

Etape 4: Activation de l'audit

   1:  ALTER SERVER AUDIT [MonAudit] WITH (STATE = ON)

   2:   

   3:  ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20090914-094010] WITH (STATE = ON)

   4:   

   5:  ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20090914-094145] WITH (STATE = ON)

Exploitation des données

Voir les logs depuis Management Studio

Par défaut, Management Studio vous propose de visualiser les logs dans le log viewer.

Cette vue vous permet de visualiser vos différents fichiers de log, cependant, il peut vite s'avérer fastidieux de rechercher une information dans cette vue.

Accéder aux logs en T-SQL

Vous avez également la possibilité de requêter les fichiers de logs en T-SQL, et ainsi construire vos propres requêtes afin de trouver l'information que vous souhaitez. Voici un exemple simple qui permet de requêter l'ensemble d'un fichier de log.

   1:  SELECT F.event_time, F.action_id, F.session_id, 

   2:   

   3:  F.server_instance_name, F.database_name, F.statement 

   4:   

   5:  FROM fn_get_audit_file('C:\tmp\*', default, default) F 

Figure 3 : Visualisation des logs via une requête

Aller plus loin avec les logs
Il existe des solutions beaucoup plus avancées pour analyser les logs provenant des audits. En les mettant en ouvre, il est possible d'auditer une ferme de serveurs et de produire des rapports simples sur l'état de la ferme. Cependant, les technologies nécessaires à cette mise en ouvre dépassent le cadre de cet article.

Erreurs courantes

Voici quelques erreurs courantes qui peuvent survenir lorsque vous mettez en place l'audit SQL sur un serveur :

- Vous avez spécifié d'écrire dans le journal d'événements système, cependant l'utilisateur qui a démarré le serveur SQL n'a pas les droits d'écrire dans ce journal,

- Vous avez spécifié Audit with shutdown on failure, votre serveur ne se démarre donc pas à cause d'une erreur au niveau de l'audit,

- Vous tentez d'attacher une base de données qui contient un audit,

- Vous avez mis en place le Database mirroring, dans ce cas, vous devez créer un audit en précisant un GUID afin que l'ensemble des serveurs aient le même. Vous pouvez utiliser l'ordre CREATE AUDIT WITH GUID = .

Commentaires