Activation et influence sur les performances des files d’audit SQL Server 2008 - Audit trail

Introduction

SQL 2008 introduit un nouvel outil souple permettant d’auditer les opérations en base en complément du Profiler : SQL Server Audit.

Voici quelques clés pour l’utiliser ainsi que quelques benchmarks sur les dégradations de performances engendrées par la mise en place d’un audit systématique. Fonctionnalité à utiliser avec précaution.

Fonctionnement

Principes de base

Un objet parent nommé 'Audit' est créé au niveau de l’instance . Il définit le fonctionnement général de la session d’audit comme la destination des traces. 2 types d’objets de spécification peuvent lui être associés selon la nature de la collecte : Serveur ou Base de données (Database).

Dans une instance, il est possible de créer plusieurs objets d’audit et plusieurs spécifications peuvent s’accumuler au sein du même objet. Chaque composant peut être activé ou désactivé indépendamment des autres.

Les informations obtenues sont enregistrées, soit dans un fichier, soit dans le gestionnaire d’évènements Windows ( Security ou Application ).

La brique technique de base est 'Extended Events', un gestionnaire d’évènements couplé à un bus de messages interne.

Création des objets Audit : CREATE SERVER AUDIT

Audit est un objet serveur, déclaré dans la base master par la commande CREATE SERVER AUDIT.

Il permet de spécifier la destination (FILE, APPLICATION_LOG, SECURITY_LOG) mais aussi, entre autres, l’option ON_FAILURE qui indique le comportement attendu en cas d’erreur dans l’écriture de la trace : la valeur CONTINUE ignore l’erreur, la valeur SHUTDOWN stoppe le serveur SQL.

Si la destination est de type fichier (FILE), SQL Server ouvre un fichier binaire à l’emplacement défini. Des options permettent de limiter leur taille. La nomenclature du fichier est en deux parties : audit et guid.

L’instance d’audit est toujours créée inactive. Il faut appliquer une commande ALTER SERVER AUDIT pour l’activer.

Exemple 1 : création d’une file d’audit hello world

use master
go
CREATE SERVER AUDIT [audit_helloworld]
TO FILE ( FILEPATH = N'E:\audit\')
WITH ( ON_FAILURE = CONTINUE )
go

ALTER SERVER AUDIT [audit_helloworld]
with (STATE=ON)

Exemple 2 : création d’une file d’audit avec gestion des fichiers. La gestion sur le long terme des fichiers d’audits est proposée dans la commande CREATE SERVER AUDIT (déplacement et/ou compression des fichiers générés, rétention, taille maximale…).

CREATE SERVER AUDIT [audit_adminfiles]
TO FILE (
        FILEPATH = N'E:\audit\'
        ,MAXSIZE = 100 MB
        ,MAX_ROLLOVER_FILES = UNLIMITED
        ,RESERVE_DISK_SPACE = ON
)
WITH (ON_FAILURE = CONTINUE ) 
go

SI MAX_ROLLOVER_FILES a une valeur définie, alors les fichiers les plus anciens sont supprimés pour respecter cette limite.

La vue système sys.server_audits permet de consulter les instances d’audit créées

select  audit_id, 
        name, 
        create_date, 
        type_desc, 
        on_failure_desc,
        'enabled'=case is_state_enabled when 1 then 'ON' else 'OFF' end
FROM    sys.server_audits
go

Une autre vue, sys.server_file_audits, permet de visualiser le détail des chemins utilisés pour les files d’audit.

Création des spécifications d’audit SERVER : CREATE SERVER AUDIT SPECIFICATION

La commande CREATE SERVER AUDIT SPECIFICATION met en place les traces d’audit pour des évènements systèmes au niveau de l’instance MS SQL Server. La spécification s’appuie sur un objet d’audit existant préalablement créée.

La liste des traces d’audit de type SERVER est disponible sur la documentation en ligne de SQL Server 2008 : SQL Server Audit Action & Groups - Server Level Audit Action Groups. On peut s’intéresser par exemple aux évènements suivants :

• FAILED_LOGIN_GROUP : trace les connexions refusées.
• LOGIN_CHANGE_PASSWORD_GROUP : trace les changements de mot de passe.
• BACKUP_RESTORE_GROUP : trace les opérations de backup/restore.

Exemple:

CREATE SERVER AUDIT SPECIFICATION audit_srvspec_1
FOR SERVER AUDIT audit_adminfiles
        ADD (FAILED_LOGIN_GROUP)
        ,ADD (BACKUP_RESTORE_GROUP)
with ( STATE=ON )
go

Deux vues, sys.server_audit_specifications et sys.server_audit_specification_details permettent de consulter les spécifications de type SERVER pour un objet d’audit.

select          a.name 'Audit', 
                a.on_failure_desc, 
                a.is_state_enabled 'AuditActive',
                b.name 'SrvSpec', 
                b.is_state_enabled 'SpecActive',
                c.audit_action_name
FROM            sys.server_audits                       a
inner join      sys.server_audit_specifications         b on b.audit_guid=a.audit_guid
inner join      sys.server_audit_specification_details  c on c.server_specification_id = b.server_specification_id

Création des spécifications d’audit DATABASE : CREATE DATABASE AUDIT SPECIFICATION

La commande CREATE DATABASE AUDIT SPECIFICATION fonctionne comme pour les spécifications serveurs, les classes de traces étant cette fois spécifiques aux bases de données. La trace peut être très fine, allant jusqu’à l’objet ou même la colonne.

La liste des traces d’audit de type DATABASE est également disponible sur la documentation en ligne de SQL Server 2008 : SQL Server Audit Action & Groups - Database Level Audit Action Groups. Parmi quelques exemples de groupes ou actions :

• DATABASE_ROLE_MEMBER_CHANGE_GROUP : trace les ajouts/suppression de user.
• DATABASE_PERMISSION_CHANGE_GROUP : trace les commandes GRANT, REVOKE ou DENY.
• SELECT : trace les commande de sélections.
• UPDATE, INSERT , DELETE : trace les opérations d’écriture.
• EXECUTE : trace les appels de procédures.

Exemple : trace toutes les exécutions de procédures et l’accès en lecture à la table Sales par le compte user dans la base pubs.

use pubs
go
CREATE DATABASE AUDIT SPECIFICATION audit_dbspec_1
FOR SERVER AUDIT audit_adminfiles 
    ADD (EXECUTE ON DATABASE::pubs BY PUBLIC) 
    ,ADD (SELECT on SCHEMA::table by user)
WITH (STATE = ON) 
GO 

Deux vues, sys.database_audit_specifications et sys.database_audit_specification_details permettent de consulter ces spécifications.

Lecture des traces : fn_get_audit_file

Une méthode SQL accompagne la fonctionnalité d’audit afin de lire le contenu des traces : fn_get_audit_file.

SELECT  event_time, sequence_number ,
        server_principal_name,
        schema_name +'.'+ object_name,
        statement
FROM    fn_get_audit_file('E:\audit\audit_adminfiles*.sqlaudit',null,null)
ORDER BY event_time DESC,sequence_number 

Dans l’exemple ci-dessus, fn_get_audit_file lit tous les fichiers correspondant au modèle ou 'pattern' indiqué (audit_adminfiles*.sqlaudit).

La liste des informations retournées par fn_get_audit_file() est décrite dans la documentation en ligne SQL Server 2008 : Microsoft SQL Server 2008 BOL: fn_get_audit_file

2 informations importantes retournées par fn_get_audit_file() :

• action_id : nature de l’opération, à croiser avec la vue sys.dm_audit_actions.
• succeeded 0|1 : échec | succès de la commande

Benchmarks de performances et commentaires

Impact sur les performances de l’audit

L’impact de ce type de solution n’est pas anodin et ne doit être mis en place qu’à très bon escient avec justifications réelles et uniquement pour des applications très stratégiques. Microsoft avance que cette méthode est jusqu’à 2 fois plus rapide que les méthodes d’audit impliquant le profiler.

Certes, mais plus rapide ne signifie pas sans impact !

Les quelques résultats de tests résumés dans le tableau suivant montrent que la nature de la trace influe très sensiblement tant sur la performance brute de chaque traitement que sur la montée en charge.

Le test est composé d’une succession de commandes SELECT uniquement. Ces requêtes sont exécutées unitairement (PAR1) puis par 2 utilisateurs simultanés (PAR2)… Une première série de mesures est réalisée sans audit, une deuxième avec un audit base sur les commandes SELECT et une troisième, toujours en audit base, sur les commandes SELECT + INSERT, UPDATE, DELETE, EXEC.

En moyenne, l’audit des commandes SELECT rend les opérations 25% plus lentes, et l’audit complet près de 30%.

Quelques variations sur les paramètres concernant la taille des fichiers d’audit ou queue_delay n’ont pas montré d’impacts significatifs sur les performances.

Étant donné la nature de la collecte, réalisée à l’instruction, plus l’accès à la base sera 'propre', c’est à dire ensembliste, moins l’impact sera important. À contrario, les applications basées sur des framework ligne à ligne (MOSS, Entity Framework, NHIbernate) seront fortement pénalisées. Pour information sur les différences de performances entre traitements ensembliste et traitements ligne à ligne : Performances SQL - Traitements ensemblistes et ligne à ligne, la métaphore de la boulangère.

Tailles des fichiers d’audit

Un autre élément à considérer : la taille du fichier. Il est stocké dans un format propriétaire et contient des structures lourdes, de type unicode, incluant outre l’instruction, ses dates d’exécution, des guid identifiant le serveur et autres informations comme le nom de l’objet, le propriétaire…

En conséquence, la taille du fichier généré est environ 5 à 6 fois plus volumineux que le texte sql passé au serveur.

Lors de tests, 1270141 d’instructions SQL occupant 94 Mb de texte ont généré une trace de 620 Mb.

Les fichiers inactifs sont fermés par MS SQL Server et peuvent être ainsi compressés et/ou déplacés.