Audit des déclencheurs dans les bases de données SQL Server – Bien choisir son serveur d impression

L’un des sujets essentiels de la sécurité de SQL Server est de savoir qui a fait quelque chose, quoi et quand. Pouvoir fournir un historique des données à diverses fins d'audit, dont certaines sont imposées par les lois américaines par le biais de réglementations de conformité, est une tâche ardue pour tout administrateur de base de données. Étant donné que les politiques commerciales ou les réglementations en matière de conformité imposent un audit des modifications de données dans une base de données, diverses techniques d'adaptation aux exigences sont utilisées pour effectuer un audit de base de données. Le plus courant consiste à utiliser des déclencheurs d'audit dans des bases de données SQL Server.

Un déclencheur est un type spécial d'objet de base de données qui est automatiquement exécuté sous certaines conditions – par ex. actions effectuées par l'utilisateur. Il existe actuellement quatre types de déclencheurs dans SQL Server, les deux premiers étant les plus couramment utilisés:

• Les déclencheurs DDL (audit CREATE, ALTER, DROP et plusieurs procédures stockées définies par le système qui effectuent des opérations DDL) sont souvent utilisés pour des tâches administratives telles que la régulation et l'audit des opérations de base de données.

• Déclencheurs DML – nous mettrons l'accent sur ce type de déclencheurs, car ils permettent l'audit des modifications de données. Ce type de déclencheurs d'audit se déclenche sur les instructions DML (telles que INSERT, UPDATE et DELETE) ainsi que sur les procédures stockées qui exécutent des opérations de type DML.

• Les déclencheurs CLR sont un type spécial de déclencheurs d'audit qui permettent à la technique de déclenchement d'être utilisée directement dans les langages .NET. Ils prennent en charge SQL Server 2008 et les versions ultérieures.

• Les déclencheurs de connexion se déclenchent lorsqu'un événement SQL Server LOGON se produit. Il peut être utilisé pour suivre avec succès l’authentification, le contrôle et l’audit des sessions de serveur.

Il existe un grand nombre de cas d'utilisation où les déclencheurs peuvent être utilisés, et ils sont particulièrement utiles pour appliquer les stratégies commerciales. Par exemple, certaines entreprises peuvent avoir une règle selon laquelle, chaque fois qu'elles obtiennent un nouveau client, ce client doit être affecté à un consultant au sein d'une entreprise. Lorsque cette règle de gestion est traduite en bases de données, cela signifie que chaque fois qu’un nouveau client est ajouté à la base de données (à l’aide de l’opération INSERT), un déclencheur peut être utilisé pour affecter le client au consultant ayant le nombre le plus bas de clients ou de clients. n’est actuellement pas occupé ou n’importe quelle politique d’entreprise exige.

Dans cet article, nous allons nous concentrer sur leurs capacités d’audit de données. Ce que les déclencheurs d'audit doivent essentiellement fournir lors de l'audit des modifications de données répondent aux questions suivantes:

1. Qui a changé les données?

2. Quelle était la date et l'heure du changement?

3. Quel logiciel client a été utilisé pour accéder aux données?

4. Dans le cas où la demande concernait la modification de données (l'instruction UPDATE), quelle était la valeur de données avant et après la modification?

Cela signifie que lorsqu'un déclencheur est activé, toutes ces informations doivent être collectées à partir d'une base de données ou d'une instance de SQL Server et stockées dans un référentiel d'audit.

Par exemple, un déclencheur est déclenché lorsqu'un enregistrement est inséré dans la devise. La table Sales est créée à l'aide du code SQL suivant:

CRÉER DÉCLENCHEUR Ventes.tr_i_AUDIT_Currency
SUR Ventes.Devise
    POUR INSÉRER
    NE PAS POUR Réplication
COMME

Ensuite, le déclencheur doit insérer le nom de l'ordinateur sur lequel INSERT a été exécuté, le nom d'utilisateur de la personne qui a inséré l'enregistrement et le nom de l'application utilisée pour insérer l'enregistrement. Les déclencheurs exploitent les fonctions SQL Server intégrées pour obtenir ces valeurs:

• HOST_NAME () renvoie le nom du poste de travail.

• APP_NAME () renvoie un nom de l'application dans la session en cours

• SUSER_SNAME () – renvoie le nom de connexion

• GETDATE () – renvoie l'horodatage du système pour la base de données actuellement connectée à

Ces valeurs sont stockées dans une table prédéfinie utilisée comme référentiel de données d'audit:

INSÉRER
DANS AdventureWorks2012.dbo.AUDIT_LOG_TRANSACTIONS
(
     NOM DE LA TABLE,
     TABLE_SCHEMA,
     AUDIT_ACTION_ID,
     HOST_NAME,
     NOM DE L'APPLICATION,
     MODIFIÉ PAR,
     DATE MODIFIÉE,
     [DATABASE]



)
VALEURS(
'Devise',
'Ventes',
2, CAS
QUAND
       LEN(HOST_NAME())
       <
       1 ENSUITE ''
    AUTRE HOST_NAME()
FIN,
CAS
QUAND
       LEN(NOM DE L'APPLICATION())
       <
       1 ENSUITE ''
    AUTRE NOM DE L'APPLICATION()
FIN,
SUSER_SNAME(),
AVOIR UN RENDEZ-VOUS(),
'AdventureWorks2012'
);

Organisation des déclencheurs d'audit

Dans la mesure où les déclencheurs d’audit font essentiellement partie d’une instance de production SQL Server, ils se déclenchent et s’exécutent en temps réel avec les modifications apportées à la base de données. Par nature, les déclencheurs entraînent une utilisation supplémentaire des ressources SQL Server, ce qui implique une planification.

Les déclencheurs d'audit doivent être organisés de manière efficace pour auditer des modifications suffisantes sans toutefois générer de temps système inutiles pour SQL Server. Ceci peut être réalisé grâce à une planification minutieuse et à l'analyse des actions précédentes des utilisateurs de la base de données. Toute vérification inutile doit être évitée afin de simplifier le processus de création et de maintenance du déclencheur et d'éviter toute utilisation inutile des ressources SQL Server. Malheureusement, SQL Server ne fournit aucun moyen visuel d'aider l'audit à déclencher une organisation et une vue d'ensemble. Finalement, l’explorateur d’objets de SQL Server Management Studio peut être utilisé pour une vue d’ensemble des déclencheurs sous la forme d’une arborescence.

Avantages et inconvénients de l'utilisation de déclencheurs d'audit

L'utilisation de déclencheurs pour l'audit des modifications de la base de données présente plusieurs avantages:

• L'utilisation de déclencheurs d'audit permet un développement plus rapide des applications, car celles-ci sont scriptées dans une base de données; par conséquent, il n'est pas nécessaire de coder les actions de déclenchement pour chaque application qui les utilise

• Une fois le déclencheur défini, il sera réutilisé par toute application modifiant les données de la base de données, appliquant ainsi des règles métier définies.

• Si une stratégie de données d’entreprise est modifiée, il suffit de modifier le déclencheur d’audit approprié au lieu de modifier toutes les applications.

• Les performances de l’environnement client / serveur ne sont pas affectées (aucun impact sur le trafic réseau) car l’audit est effectué localement sur le serveur de base de données.

D'autre part, les performances de SQL Server peuvent avoir un impact important car les déclencheurs d'audit peuvent utiliser les ressources de la même manière que les opérations effectuées par l'utilisateur. C’est le principal inconvénient des déclencheurs – la surcharge de SQL Server. Par exemple, l'audit des modifications d'un champ d'enregistrement effectuera en outre au moins une modification supplémentaire lors de la sauvegarde des données capturées.

Sauvegarde des informations capturées

Les réglementations de conformité exigent des informations d'audit fiables et lisibles par l'homme, à la demande. La pratique la plus courante consiste à utiliser SQL Server en tant que stockage. De cette manière, des techniques similaires (T-SQL, par exemple) sont utilisées pour l’audit, le stockage et la maintenance des informations capturées. De plus, les informations vérifiées étant enregistrées sous une forme de tables / bases de données SQL Server, les avantages que SQL Server offre pour toutes les informations stockées sont également disponibles pour la gestion des données capturées.

Bien entendu, dans certains cas particuliers, d'autres formats de stockage des données capturées sont utilisés. Par exemple, si aucune requête de données supplémentaire n'est requise, des formats de fichier standard sont utilisés, tels que XLSX ou PDF. Cela peut sûrement fournir des rapports d'audit facilement accessibles, mais tout traitement ou interrogation supplémentaire pourrait ne pas être possible du tout.

Auditer les déclencheurs et ApexSQL Trigger

Trigger ApexSQL est un outil d'audit SQL Server qui capture les données et les modifications de schéma survenues dans une base de données, notamment les informations sur l'auteur des modifications, les objets affectés, la date de création, ainsi que les informations relatives à la connexion, à l'application et à l'hôte SQL. utilisé pour faire le changement. Il stocke toutes les informations capturées dans un référentiel central et les exporte dans des formats faciles à imprimer.

La technologie qui Trigger ApexSQL utilise pour l'audit est basé sur des déclencheurs. Celles-ci sont créées à l'aide de son interface visuelle, sans aucune connaissance préalable de T-SQL. Simplement, après la connexion à une base de données, l'outil répertorie toutes les tables utilisateur existantes dans les tables utilisateur de la base de données avec leur statut d'audit actuel.

La grille indique clairement quelle table est auditée et pour quelle opération – INSERT, UPDATE, DELETE ou toutes ces opérations. De plus, le Filtre row peut être utilisé pour réduire la liste. le Préparé colonne indique s'il existe une sélection appropriée de colonnes vérifiées pour une table donnée. Les détails concernant les colonnes et leur statut d’audit sont indiqués dans Colonnes panneau:

Grâce à cet aperçu visuel, les déclencheurs d’audit peuvent être facilement créés, modifiés et supprimés. Lors de la finalisation de la sélection des tables et de leurs lignes à auditer pour une opération particulière, simplement en utilisant Créer dans la barre d’outils de l’application principale, des déclencheurs d’audit sont créés.

Trigger ApexSQL vous permet de consulter et de modifier un script de création de déclencheur avant son exécution. Outre la possibilité de modifier le script qui génère des déclencheurs, il est également possible de personnaliser le modèle. Trigger ApexSQL utilise pour les générer.

Des améliorations supplémentaires des données capturées peuvent être obtenues en utilisant Watches and Lookups.

Une fois créés, les déclencheurs d’audit peuvent être désactivés, activés ou supprimés par sélection ou tous en même temps à l’aide du module de gestion des déclencheurs. Cela n’exige pas non plus de script manuel:

Lorsqu’il s’agit de présenter les données capturées, le module de rapport intégré offre un filtrage complet des données capturées tout en exportant des rapports au format CSV, TXT, PDF, Microsoft Word et Microsoft Excel.

Enfin, abordons le sujet de la sauvegarde des données capturées à l’aide de déclencheurs d’audit. Trigger ApexSQL stocke les informations capturées dans trois tableaux supplémentaires:

• La table AUDIT_LOG_DATA est utilisée pour stocker les informations sur les données qui ont été modifiées – la valeur de données initiale avant une modification et la valeur de données après que la modification a eu lieu.

• La table AUDIT_LOG_TRANSACTIONS stocke les informations sur la transaction, telles que le nom de connexion, le nom de l'application, le nom de la table ainsi que la date et l'heure de validation de la transaction.

• La table AUDIT_LOG_DDL stocke les informations sur les modifications du schéma de base de données

Toutes les tables sont créées dans chaque base de données auditée. Cela peut être personnalisé afin que toutes les données capturées de toutes les bases de données auditées soient enregistrées dans une base de données distincte.

Ainsi, les informations capturées étant enregistrées dans la base de données SQL Server, elles ne sont pas seulement disponibles via le module de génération de rapports et ses exportations, mais également en tant que source de la requête supplémentaire via des scripts SQL utilisateur.

Téléchargements

Veuillez télécharger les scripts associés à cet article sur notre référentiel GitHub.

Veuillez nous contacter pour tout problème ou question avec les scripts.

26 juin 2013