Audit d’instructions SELECT sur un serveur SQL Server

Introduction

Bien que les instructions SELECT ne soient pas destructives par nature, et qu’elles ne peuvent pas non plus altérer les données ou les schémas, il y a certains cas qui requièrent leur surveillance sur un serveur SQL Server.

Les instructions SELECT exécutées peuvent indiquer différents problèmes actuels ou potentiels. Il est donc important de savoir quand c’est arrivé et pourquoi.

En général, deux cas majeurs nécessitent l’enregistrement des instructions SELECT:

• S’informer sur qui a accédé à vos données et à quel moment, ceci pour résoudre des problèmes par rapport à la sécurité, aux applications ou à la performance;
• Pour répondre aux exigences requises (pour se conformer à l’HIPAA ou autres réglementations de conformité)

Il y a plusieurs solutions natives au sein de SQL Server pour auditer des requêtes SELECT

Les procédures stockées et les fonctions

Conserver l’information sur “qui a vu quoi” peut être réalisé par l’implémentation de procédures stockées et fonctions spécifiques à son besoin. Afin d’utiliser cette méthode d’audit, l’accès à la base de données doit être limité à l’utilisation de ces procédures stockées (en permettant uniquement l’utilisation des instructions EXEC et en interdisant toutes les requêtes et les opérations DML). Le résultat d’une requête est renvoyé via une procédure stockée, pendant que, au même moment, la procédure stockée enregistre l’accès dans une table de dépôt d’audit avec des informations additionnelles de la demande (par exemple, l’heure et la date de l’exécution).

Cependant, cette méthode d’audit requiert de la programmation T-SQL et de la maintenance additionnelle des procédures stockées et fonctions (par exemple en cas de changement du schéma de la base de données). De plus, les requêtes ad-hoc exécutée par des membres de confiance (exemple membre du groupe SYSADMIN) outrepasse facilement la règle suivante : « les requêtes SELECT doivent être analysées par une procédure stockée », et dès lors ne peuvent être tracés. Cela rend l’utilisation des procédures stockées et fonctions inacceptables comme méthode d’audit pour les réglementations de conformité.

La technologie de traçabilité de SQL Server

SQL Server fournit un monitoring d’évènements sélectionnés grâce à la technologie de traçabilité. Cela est accessible via l’interface de programmation d’applications (API – Application Programmer Interface), via l’interface graphique SQL Server Profiler, afin d’effectuer un suivi en temps-réel, ou en accédant à des fichiers de sauvegarde d’une trace antérieure.

Ci-dessous l’explication pour réaliser l’audit de l’exécution de la commande SELECT sur une base de données spécifique:

1. Démarrez l’application SQL Server Profiler et fournissez les informations d’indentification nécessaires à la connexion au serveur SQL Server

2. Tapez le nom de la traçabilité que vous allez réaliser (par exemple : TraceSELECTs)

3. Choisissez l’option BLANK dans la ligne Use the template du menu de sélection. L’option BLANK est ici sélectionnée car nous voulons faire un traçage uniquement des instructions SELECT

   

4. Vérifier l’option Save to table et fournissez les informations nécessaires à l’enregistrement de l’audit. La table peut être localisée sur un autre serveur SQL Server que celui qui est audité. Dans cet exemple, nous utiliserons le même serveur

   

Nous utiliserons la base de données AUDITDB (une base de donnée vierge) et nous spécifierons une table de dépôts appropriée qui sera crée (TraceSELECTsTable)

5. Maintenant, utiliser l’onglet Event Selection, trouver et vérifier le paramètre SQL :StmtStarting dans la colonne Events.

   

6. Cliquer sur Column Filters pour ouvrir la boîte de dialogue Edit Filter

7. Sélectionnez le filtre TextData, et entrez “SELECT%” comme valeur pour la valeur souhaitée du filtre LIKE. Voir image ci-dessous

   

8. Si comme dans notre exemple vous souhaitez examiner seulement une base de données spécifique, il est nécessaire d’ajouter un filtre supplémentaire. Sélectionner le filtre DatabaseName et entrer comme valeur souhaitée (LIKE) le nom de la base de données. “ACMEDB” dans notre exemple

   

9. Cliquer sur OK pour confirmer les changements liés aux filtres configurés

10. Sélectionner l’option Run pour démarrer l’audit défit précédemment configuré TraceSELECTS (étape 2)

Toutes les commandes SELECT exécutées sur la base de données ACME seront identifiées et enregistrées dans la table TraceSELECTsTable. Ci-dessous un aperçu des informations obtenues:

Les informations auditées contiennent les informations pertinentes requises pour ce type d’audit (par exemple, le nom de la base de données et le nom d’utilisateur, l’heure et la date de l’exécution de la commande ainsi que l’état exact de l’exécution de la commande). Cependant, il y a certains inconvénients lors de l’utilisation de la technologie de traçabilité de SQL Server.

Cette méthode d’audit n’est pas une solution prête à l’emploi. Elle requiert une configuration manuelle en utilisant des filtres. Cela ne fournit pas une solution contre la falsification lorsque les manipulations sont réalisées par les membres de confiance (avec plus haut niveau de permission qui leur permettre d’outrepasser le traçage) sans compter qu’aucune possibilité d’archivage n’est envisageable. En plus, la méthode d’audit nécessite des connaissances T-SQL pour exporter les données et créer des rapports d’audit appropriés.

Les fonctionnalités d’audit de SQL Server

La fonctionnalité d’audit a été introduite dans la version 2008 de SQL Server. Elle utilise la technologie d’événements étendus (Extended Events) et réalise un audit à la fois des événements du serveur mais aussi de la base de données. Cependant, l’audit de la base de données n’est supporté que dans les versions Enterprise et Developer.

L’utilisation de la fonctionnalité d’audit de SQL Server pour tracer l’exécution des commandes SELECT demande moins de ressources que la technologie de traçabilité, mais en fonction de l’utilisation de la base de données un impact sur les performances du serveur peut se faire sentir.

Nous pouvons utiliser des requêtes T-SQL ou SQL Server Management pour paramétrer les fonctions d’audit de SQL Server. Dans cet article, nous nous concentrerons sur le SQL Server Management Studio. Vous pouvez aisément créer et voir la correspondance avec les scripts T-SQL en utilisant l’option CREATE TO dans le SQL Server Management Studio.

Pour auditer l’exécution des commandes SELECT sur une base de données spécifique:

1. Développez le dossier Security

2. Sélectionnez New Audit et paramétrer Audit name (par exemple, Audit SELECTsServerSpecification) et File path par exemple, C:\AUDITs) dans la fenêtre Create Audit

   

   Un fichier correspondant .sqlaudit sera généré dans le dossier spécifié plus haut (C:\AUDITs). Ce dernier sera utilisé comme répertoire d’audit

3. Confirmez la création la création de l’objet d’audit SQL Server en cliquant sur OK

4. Faites un clic-droit sur l’audit créé et sélectionnez l’option Enable Database Audit Specification (activer l’audit)

Les étapes suivantes décrivent comment créer un audit de base de données sur mesure. Cela requiert la création préalable d’un objet d’audit de serveur SQL (AuditSELECTsServerSpecification). C’est la spécification de l’audit de la base de données qui va affiner la collecte des commandes SELECT.

5. Développez la base de données que vous souhaitez auditer dans l’Object Explorer

6. Développez le dossier Security

7. Faites un clic-droit sur le dossier Database Audit Specification et sélectionner New Database Audit Specification

8. Entrez le nom de la nouvelle spécification de l’audit de la base de données (par exemple, AuditSELECTsDatabaseSpecification) et sélectionnez l’objet préalablement créé de l’objet d’audit SQL Server en utilisant le menu contextuel Audit. Dans le but d’affiner l’audit, remplissez les champs comme dans l’image ci-dessous:

   1. Audit Action Type: SELECT

   2. Object Class: DATABASE

   3. Object Name: ACMEDB

   4. Principal: public – Les audits enregistreront seulement les instructions SELECT issues de connexions établies en tant que le login spécifié. Si l’on souhaite auditer toutes les commandes SELECT sans tenir compte de qui exécute ces commandes, le nom de la rubrique principal dans être “public”

      

9. Confirmez la création de la spécification de l’audit de la base de données en cliquant sur OK

10. Pour finir, faites un clic-droit sur cette spécification et sélectionnez l’option Enable Database Audit Specification

Après que l’objet de l’audit ainsi que la spécification de l’audit de la base de données aient été paramétrés et activés, toutes les instructions SELECT sur toutes les tables, seront auditées et enregistrées dans le fichier .sqlaudit.

Pour visualiser les commandes SELECT auditées:

1. Faites un clic-droit sur l’audit AuditSELECTsServerSpecification et sélectionnez l’option du menu contextuel View Audit Logs pour ouvrir la boite de dialogue contenant les évènements

2. La boite de dialogue vous affichera les informations à propos des instructions SELECT effectuées sur cette base de données spécifique

   

Bien entendu, ceci n’est pas non plus une méthode pratique, ni un bon format pour fournir des données auditées. L’autre moyen d’exporter et de fournir les données capturées est via la fonction fn_get_file_audit. La fonction fn_get_file_audit lit les fichiers .sqlaudit créés par la fonctionnalité d’audit de SQL Server.

Le script suivant permet de récupérer les occurrences capturées de commandes SELECT exécutées sur la base de données ACMEDB:

SELECT
       event_time ,
       session_server_principal_name AS UserName ,
       server_instance_name ,
       database_name ,
       object_name ,
       statement
  FROM sys.fn_get_audit_file('C:\AUDITs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE
      action_id = 'SL'
  AND
       database_name = 'ACMEDB';

La fonctionnalité d’audit de SQL Server a moins d’impact sur les performances du serveur et fournit des informations plus affinées sur l’audit que la technologie de traçabilité.

Cependant cette fonctionnalité, n’est disponible que sur deux éditions de SQL Server. Il n’y a pas d’archivage ou méthode simple de centralisation. Sa mise en route requiert une intervention manuelle pour chaque instance de serveur et chaque base de données. Le T-SQL est indispensable pour des analyses et un reporting plus approfondis.

La solution prête à l’emploi

ApexSQL Audit est un programme d’audit pour SQL Server et un outil de vérification de conformité qui trace et rapporte tous les événements survenant sur une instance SQL Server en auditant les accès et les changements apportés à cette instance ainsi qu’à ses objets. Cet outil fournit une série de rapports intégrés, ainsi qu’un outil de création de rapports permettant de réaliser des rapports personnalisés en utilisant la technique du glisser-déposer.

Pour réaliser l’audit de l’exécution des instructions SELECT sur des bases de données spécifiques et sur plusieurs instances SQL Server:

1. Démarrez l’interface graphique ApexSQL Audit

2. Sélectionnez l’instance de SQL Server, vérifiez la base de données que vous souhaitez auditer pour les instructions SELECT et vérifiez l’option Query

3. De plus, vérifiez les tables qui peuvent faire partie de l’audit des instructions SELECT – si vous ne souhaitez pas auditer les instructions SELECT sur des tables spécifiques, vous pouvez les exclure ici en sélectionnant le bouton radio Exclude objects dans la zone dans la zone Objects de l’opération Query. L’exclusion permet de préserver l’espace disque occupé par la base de données du référenciel central d’ApexSQL:

   

4. Confirmez votre sélection en appuyant sur l’option Apply dans le ruban jaune

   

Les rapports intégrés de l’outil d’audit ApexSQL Audit peuvent fournir des rapports avec les instructions SELECT capturées et des informations complètes sur la traçabilité ainsi qu’un historique des accès

Pour voir les instructions SELECT, cliquez sur le bouton View Reports sur la barre d’outil principale de l’outil ApexSQL Audit et utilisez n’importe quel rapport listé précédemment

Il est également possible d’utiliser des rapports personnalisés (Custom Reports) pour n’afficher dans le rapport les informations de façon plus granulaire:

Dans le cadre de l’audit d’instructions SELECT, l’outil ApexSQL Audit fournit les informations suivantes:

• Configuration des audits du type pointer-cliquer
• Faire de l’audit et du reporting sans connaissance ou utilisation du langage T-SQL
• Support de toutes les éditions de SQL Server excepté l’édition SQL Server Express
• Monitoring et enregistrement automatiques des actions utilisateurs dont l’exécution des instructions SELECT peu importe leur rôle (niveau de permission)
• Identification de l’impact des risques de sécurité et de la performance en rapportant toutes les instructions SELECT dans leur forme T-SQL d’origine
• Un dossier d’audit centralisé avec la fonction d’archivage
• Des rapports évidents, précis et complets pour les contrôles de sécurité

L’audit des instructions SELECT peut être accompli en utilisant les moyens natifs de SQL Server ou encore à l’aide d’outils tiers tel que ApexSQL Audit. Bien que les méthodes natives soient intégrées (mais pas dans toutes les éditions de SQL Server), elles n’offrent pas des données inviolables et requiert un niveau de connaissance avancé pour paramétrer et faire le reporting.

En revanche, l’outil ApexSQL Audit fournit une méthode facile d’utilisation et efficace pour réaliser des audits et créer des rapports sur les instructions SELECT. Ceci avec tous les bénéfices additionnels listés préalablement.

Ressources utiles
Auditing SELECT Statements in SQL Server 2008 Standard using SQL Trace
Auditing in SQL Server 2008
MSDN – SQL Server Audit (Database Engine)
MSDN – SQL Server Audit Action Groups and Actions
MSDN – SQL Server Audit Records
MSDN – Create a Server Audit and Server Audit Specification