-
Titre
Techniques d'audit d'une base de données SQL Server -
Description
Techniques d'audit d'une base de données SQL Server
-
Résolution
L'audit des bases de données SQL Server n'est pas seulement utilisé pour répondre aux exigences de conformité. C'est quelque chose qui est devenu nécessaire pour analyser les actions effectuées sur la base de données, essayer de résoudre des problèmes ou encore investiguer des activités suspectes ou malveillantes. Il peut également être utile pour empêcher les utilisateurs d'effectuer certaines actions - comme si vous aviez un système CCTV sur vos bases de données.
Il existe plusieurs techniques d'audit sous SQL Server:
- Audit manuel - peut être créé pour répondre à des besoins ou des prérequis spécifiques, mais prend beaucoup de temps et n'est pas forcément exempt d'erreurs
- Via les SQL Server Extended Events - facile à configurer, un large panel d'actions peuvent être surveillées, mais il ne fournit pas la possibilité conserver l'information sur quelle donnée a été insérée ou supprimée ou quelles étaient les valeurs avant et après une mise à jour; un audit détaillé peut causer des problèmes de performance
- Via les SQL Server triggers - faciles à mettre en place, mais peuvent causer des problèmes de performance dans les bases de données très transactionnelles
- En lisant le transaction log - pas de capture supplémentaire à effectuer vu que SQL Server conserve déjà tous les changements. Par contre, un espace de stockage est nécessaire et malheureusement certaines actions (comme les commandes EXECUTE) ne sont pas auditées
- En utilisant SQL Server Profiler et SQL Server Traces - flexible et complexe. Difficultés pour lire et pour filtrer les enregistrements
Le choix de la technique parmi celles citées ci-dessus qui vous conviendrait le mieux dépend de votre environnement, de ce que vous voulez auditer, de l'emplacement où vous comptez stocker les actions capturées et de quelle manière vous souhaitez vos rapports.
L'audit manuel
Les bases de données SQL Server peuvent être auditée en utilisant des procédures stockées et des fonctions développées tout spécialement afin de conserver les changements tant aux niveaux des données qu'au niveau des objets. Cela nous permet d'obtenir une solution flexible qui requière beaucoup de code et de développement et de facto augmente le coût et le temps nécessaire à sa mise en oeuvre.
Via les SQL Server Extended Events
A partir de la version 2008 de SQL Server sont apparus les SQL Server Audits, une fonctionnalité de SQL Server faisant appel aux Extended Events pour auditer des actions dans SQL Server. Il permet l'audit de différentes actions en fournissant beaucoup de granularité lors du processus d'installation et couvre la majeure partie des activités au sein de SQL Server.
Pour créer un nouvel objet SQL Server Audit:
- Dans SQL Server Management Studio (SSMS), développez Security (Sécurité) et cliquez droit sur Audits
-
Sélectionner New Audit (Nouvel audit)
-
Donnez un nom à votre audit et un emplacement de destination. Celui-ci peut être l'Application Event Log, le Security Event Log ou l'emplacement d'un fichier
- Cliquer sur OK et votre audit apparait dans le noeud Audit de l'observateur d'objets de SSMS
-
Par défaut, cet événement est désactivé, ce que nous pouvons voir grâce à la flèche rouge. Pour l'activer, cliquez droit sur cet audit et sélectionnez Enable Audit (Activer)
- Choisir si vous avez besoin d'auditer au niveau de l'instance ou pour une base de données en particulier. Pour ce faire, vous utiliserez respectivement une Server Audit Specification ou une Database Audit specification
-
Pour créer une Database Audit specification, développer le noeud de la base de données que vous voulez auditer, aller dans la partie Security (Sécurité), cliquer droit sur la zone Database Audit Specification et sélectionner New Database Audit Specification...
-
Dans la boite de dialogue de création d'une Database Audit Specification, donner un nom à la spécification, associer la spécification avec l'objet Audit créé dans l'étape #1 et spécifier l'activité qui sera auditée dans la zone Audit Action Type. Pour l'audit de base de données, spécifiez une base de données, un objet ou un schéma dans la liste Object Class (Classe d'objet). Reste alors à entrer le nom de l'objet audité et l'utilisateur à auditer
Vous trouverez ci-dessous toutes les actions qui peuvent être auditées avec SQL Server Audit dans la liste déroulante Audit Action Type
Les Principals que vous sélectionnez dans cette boite de dialogue sont en fait les comptes utilisateurs qui seront surveillés
Comme pour l'objet Audit, une Database Audit Specification est désactivée par défaut. Pour l'activer, sélectionner cette option dans le menu contextuel
A présent, toutes les requêtes DELETE exécutées sur la table Person.BusinessEntityAddress seront auditées et insérées dans les fichiers dont le nom commence par Audit- comme Audit-AW2012Test_9D93CA4A-8B90-40B8-8B0B-FCBDA77B431D_0_130161593310500000.sqlaudit sauvegardé sur le disque E:\
Comme il peut y avoir beaucoup d'actions capturées sur une base de données très sollicitée, il est recommandé de sauver les informations d'audit dans un fichier. Le fichier SQLAudit ne peut être ouvert avec un éditeur de texte ou hexadécimal. Il faut utiliser Reporting Services ou la fonction Transact SQL fn_get_audit_file pour analyser les données.
Par exemple:
SELECT event_time,action_id,statement,database_name,server_principal_name FROM fn_get_audit_file( 'E:\Test\Audit-*.sqlaudit' , DEFAULT , DEFAULT);
nous donne le résultat suivant:
Même lorsque vous sélectionnez toutes les colonnes, vous ne trouverez pas d'information sur ce qui a été supprimé mais vous trouverez quand même qui a effectué l'action et à quel moment. C'est un désavantage à cette méthode.
D'autres désavantages:
- Comme les SQL Server Audits font appel aux ressources de SQL Server pour récupérer les informations d'audit, il peut y avoir un effet sur les performances
- Il n'y pas d'option existante pour gérer de façon centralisée les SQL Server Audit sur plusieurs instances de SQL Server
- L'analyse et l'archivage des données d'audit (dans un fichier ou des journaux) implique des opérations manuelles pour l'importation, la création de rapports ou l'archivage
- Etre disponible uniquement à partir de la version 2008 de SQL Server
- l'audit au niveau base de données n'est disponible qu'en versions Enterprise, Developer et d'évaluation
Via les SQL Server triggers
Les triggers ou déclencheurs sont activés automatiquement sitôt qu'un certain événement survient. Les triggers sont en fait des procédures stockées exécutées automatiquement lorsqu'une condition est remplie. Les triggers sur manipulation de données, aussi appelés triggers DML, peuvent être utilisés pour tracer les requêtes de type INSERT, UPDATE, DELETE. Vous pouvez créer ces triggers un à un pour chaque table et chaque type d'opération que vous voulez auditer. Vous devez aussi considérer l'espace où stocker ces informations d'audit - par exemple une table où le moment de la transaction, le nom d'utilisateur, le type de transaction,(...) seront insérées. Lorsque nous traçons les requêtes de type UPDATE, il peut s'avérer utile de conserver tant les valeurs avant que les valeurs après modification
En guise d'exemple, voici le code d'un trigger qui sera déclenché dès qu'un enregistrement est inséré dans la table Person.Person et stockera les informations suivantes dans une table appelée dbo.Repository: nom de la table modifiée, moment (date et timing) de l'insertion de cet enregistrement et le nom de l'utilisateur qui a effectué l'insertion:
CREATE TRIGGER PersonPerson_I ON Person.Person AFTER INSERT AS INSERT INTO dbo.repository ( TABLE_NAME, TABLE_SCHEMA, AUDIT_ACTION_ID, MODIFIED_BY, MODIFIED_DATE, [DATABASE] ) values( 'Person', 'Person', 'Insert', SUSER_SNAME(), GETDATE(), 'AdventureWorks2012' ) GO
Avant qu'un ou plusieurs triggers de ce type soient mis en place, il faut impérativement avoir au préalable créé la ou les tables de destination des données d'audit.
Cette méthode n'est pas exempte d'erreur vu que la majeure partie du travail consiste en des opérations manuelles.
ApexSQL Trigger est un outil d'audit de base de données qui capture les modifications au niveau du schéma et des données qui ont eu lieu dans une base de données. Il permet de savoir qui a effectué la modification, sur quels objets et à quel moment. De plus, il collecte non seulement les informations sur l'identifiant SQL Server avec lequel l'utilisateur s'est connecté mais aussi l'application avec laquelle il s'est connecté et le nom du poste utilisé pour effectuer ces changements. L'outil stocke les informations capturées dans un dépôt central et les exporte dans des formats exploitables. Pour créer les triggers, il suffit de sélectionner les tables et les types d'opération à auditer
-
Démarrer ApexSQL Trigger
-
Se connecter à la base de données à auditer
-
Dans la grille principale, sélectionner les tables que vous voulez auditer
-
Dans le panneau Columns (Colonnes), sélectionner les colonnes à surveiller
-
Sélectionner les transactions à auditer : INSERT - UPDATE - DELETE
-
Répéter les étapes 3 à 5 pour toutes les tables que vous voulez surveiller
-
Dans le menu, cliquer sur Create Triggers (Créer déclencheurs)
-
Le script qui génère les triggers spécifiés est visible dans la boite de dialogue Script. Vérifier son code puis exécuter en pressant sur la touche F5
Une fois que les triggers sont créés, ils seront lancés pour chaque requête INSERT, DELETE, UPDATE sur la table et les détails de l'opération sont stockés dans les tables AUDIT_LOG_DATA et AUDIT_LOG_TRANSACTIONS
Vous pouvez accéder à leur contenu facilement en utilisant les rapports prédéfinis d'ApexSQL Trigger ou en créant des requêtes T-SQL
Bien que les triggers permettent un audit granulaire et un stockage facilement accessible, leur plus gros désavantage est qu'ils engendrent une charge supplémentaire sur les bases de données.
En lisant le transaction log
Chaque changement au niveau du schéma ou des données d'une base de données SQL Server est ajouté au transaction log comme enregistrement du journal (log record). Nous pouvons donc lire ces enregistrements dans un contexte d'audit. Mais, il n'est pas simple d'ouvrir et de lire un transaction log qu'il soit en ligne, détaché ou issu d'une sauvegarde de base de données. Une des options pour y parvenir est d'utiliser des fonctions non documentées comme fn_dblog, fn_dump_dblog, et DBCC PAGE.
En plus de la complexité et de problèmes liés à la reconstruction des requêtes UPDATE/BLOB, leur plus gros désavantage est que ces logs présentent des valeurs hexadécimales que nous devons déchiffrer.
Il est donc préférable d'utiliser un lecteur de transaction logs comme ApexSQL Log. . Cet outil audit, annule ou rejoue les changements au niveau des objets et des données qui ont été effectués sur une base de données, et ce même pour les changements effectués avant l'installation d'ApexSQL Log. Il capture également les informations sur l'utilisateur, l'application et le poste utilisés pour effectuer les changements.
- Démarrer ApexSQL Log
-
Se connecter à la base de données à auditer
-
Dans l'étape Select SQL Logs to analyze (Sélectionner les journaux SQL Server à analyser), ajouter les backups de transaction log et les transaction logs détachés que vous souhaitez lire. A noter qu'ils doivent former une chaine complète pour que l'audit soit un succès
-
Utiliser les options de filtrage (Filter setup) pour diminuer le nombre de résultats sur base des critères suivants: moment, type d'opération, nom de table
- Cliquer sur Open
-
Les résultats sont visibles dans la grille principale et nous pouvons créer des scripts d'annulation et d'application d'un changement, exporter le changement vers un fichier CSV, HTML, XML, SQL pour le sauver sur disque
Les avantages de cette méthode sont multiples: il n'y a pas de trigger, pas de traitement additionnel pour capturer les informations d'audit qui affecte les performances de l'instance SQL Server. Nous avons accès à l'historique des transactions même avant l'installation de l'outil, contrairement aux solutions basées sur les triggers ou sur les Extended Events.
Les désavantages de cette méthode sont:
- un espace de stockage plus important est nécessaire pour contenir les sources de données, comme une base de données doit être dans le modèle de récupération "FULL" et qu'une chaine complète de transaction logs doit exister
- toutes les actions de l'utilisateur que vous pourriez désirer surveiller ne sont pas écrites dans le transaction log. Par exemple, les requêtes SELECT ne se retrouvent pas dans le transaction log
En utilisant SQL Server Profiler et SQL Server Traces
L'utilisation de SQL Server Profiler et des SQL Server Traces comme solution d'audit est très complexe avec énormément de travail à faire soi-même et donc assez bien de risques d'erreurs.
ApexSQL Audit est un outil d'audit construit pour utiliser les SQL Server Traces qui fournissent les informations sur "Qui a vu quoi?". Il permet l'audit de la tolérance aux pannes et la génération de rapports de façon centralisée. Son interface graphique est intuitive pour la mise en place d'audits sur plus de 230 opérations. Il utilise un dépôt central sécurisé pour le stockage des données d'audit enregistrées et des configurations de ces audits. Ces configurations sont construites sur base des paramètres entrés par l'utilisateur ou de sa configuration par défaut qui couvre la majeure partie des demandes d'audit.
- Démarrer ApexSQL Audit
-
Cliquer sur le bouton Add Server (Ajouter serveur) pour sélectionner le serveur à auditer
-
Cliquer sur le bouton Add Database (Ajouter base de données) pour choisir la base de données à auditer et sélectionner les opérations sur le serveur ou la base de données qui doivent faire partie de l'audit
Une autre manière de faire consiste à utiliser les filtres avancés:
Dès lors, un enregistrement dans la base de données de dépôt central, appelée ApexSQLCrd, sera créé pour tout lancement d'une des opérations que vous avez sélectionnées
Pour accéder aux données collectées par l'audit, vous pouvez utiliser les rapports déjà définis dans l'outil ou la fonctionnalité de rapports web.
Les avantages d'ApexSQL Audit tiennent dans la facilité de mise en oeuvre d'un audit, le grand nombre d'opérations pouvant faire être surveillées, la centralisation des données d'audit de manière sécurisée, la fonctionnalité de création de rapports web de sorte que les utilisateurs peuvent accéder à aux données d'audit à distance et enfin une administration centralisée.
Technique Avantages Inconvénients Est le plus approprié lorsque Audit manuel Flexibilité Coûts de développement et temps d'implémentation importants Une solution d'audit spécifique est nécessaire et aucun outil disponible n'est prévu pour le faire SQL Server Audit Flexibilité. Un grand nombre d'actions sont auditées. Facile à mettre en place. Pas de coût additionnel Les valeurs des données touchées par d'une modification au travers des requêtes INSERT, UPDATE ou DELETE ne sont pas conservées. Peut affecter les performances du serveur. N'est pas disponible dans toutes les versions et toutes les éditions de SQL Server Une édition Enterprise, Developer ou Evaluation de SQL Server est installée, qu'un audit détaillé et aucune information sur les enregistrements affectés par la modification ne sont nécessaires Via les SQL Server triggers Faciles à mettre en place. Peuvent conserver les informations sur les transactions pour des tables spécifiques choisies par leur concepteur. Espace de stockage flexible N'est pas garanti sans erreurs lorsque ces triggers et l'espace de stockage associés sont créés manuellement. Peut engendrer une surcharge sur un serveur hautement sollicité Toutes les tables et tous les types d'opérations DML n'ont pas besoin d'être surveillés; les données d'audit doivent pouvoir être accédées au travers de requêtes T-SQL En lisant le transaction log Pas d'outil supplémentaire à installer pour capturer les données d'audit. Les modifications DML et DDL peuvent être surveillées. Permet de mettre en avant les enregistrements touchés par une modification donnée. Pas de charge supplémentaire induite sur le serveur Un espace de stockage plus important est nécessaire. Difficultés pour lire le contenu d'un fichier journal sans l'utilisation d'un outil de lecture adapté. Certaines actions ne peuvent faire l'objet d'une surveillance au travers de ce procédé (sécurité, requêtes pour l'accès à une donnée, connexions...) Nous sommes face à un environnement avec un taux de transactions très important où il doit être possible de voir les changements apportés aux données et de revenir à la version précédent En utilisant SQL Server Profiler et SQL Server Traces Flexible
Déjà disponible avec SQL Server
Complexe et non garanti sans erreurs lorsque cette solution est utilisée manuellement Il y a un large éventail d'actions au sein de SQL Server qui peuvent être auditées. Il est recommandé de disposer d'un outil de lecture des traces qui filtre les résultats et génère des rapports Comme présenté ci-dessus, il y a plusieurs techniques à disposition pour auditer l'activité au sein de SQL Server. Ces techniques utilisent des fonctionnalités différentes de SQL Server. Cela va de la création d'une méthode manuelle, qui n'est pas garanties sans erreurs, à des interfaces graphiques conviviales. Certaines de ces solutions peuvent avoir un impact sur les performances sur l'instance SQL Server tandis que d'autres n'induisent aucune charge supplémentaire sur le serveur. Déterminez le type d'opérations que vous devez surveiller et choisissez l'outil d'audit ApexSQL le plus adapté et qui vous permettra de mettre en place l'audit et de créer des rapports en quelques clics.
Téléchargements
Vous trouverez l'ensemble des scripts associés à cet article au bout du lien suivant thou sur notre dépôt GitHub.
N'hésitez pas à nous contacter si vous rencontrez un problème ou si vous avez des questions concernant ces scripts.
