auditer SQL 2008

7/30/2019 auditer SQL 2008

1/17

Les audits avec SQL Server 2008

parDavid BARBARIN

Date de publication : 13 juin 2010
http://blog.developpez.com/mikedavem/http://mikedavem.developpez.com/http://www.developpez.net/forums/u70376/mikedavem/http://www.developpez.com/

7/30/2019 auditer SQL 2008

2/17

Les audits avec SQL Server 2008 parDavid BARBARIN

- 2 -Les sources prsentes sur cette page sont libres de droits et vous pouvez les utiliser votre convenance. Par contre, la page de prsentationconstitue une uvre intellectuelle protge par les droits d'auteur. Copyright 2010 David BARBARIN. Aucune reproduction, mme partielle, nepeut tre faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vousencourez selon la loi jusqu' trois ans de prison et jusqu' 300 000 de dommages et intrts. Cette page est dpose la SACD.

http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/

I - Introduction..............................................................................................................................................................3

II - Quels avantages utiliser ce systme d'audit ?................................................................................................... 3

III - Quels sont les inconvnients ?.............................................................................................................................3

IV - Architecture du systme d'audit........................................................................................................................... 4

IV-A - Objet d'audit SQL Server............................................................................................................................ 4

IV-B - Cibles........................................................................................................................................................... 5

IV-C - Objet de spcification d'audit serveur.........................................................................................................5IV-D - Object de spcification d'audit de bases de donnes.................................................................................5

IV-E - Vues de gestion des audits.........................................................................................................................6

IV-E-1 - Vues systmes....................................................................................................................................6

IV-E-2 - DMV.....................................................................................................................................................6

IV-E-3 - DMF.....................................................................................................................................................6

V - Cas d'tude............................................................................................................................................................6

V-A - Cration des instances d'objets d'audit SQL Server....................................................................................7

V-B - Cration des objets de spcification d'audit serveur..................................................................................10

V-C - Cration des objets de spcification d'audit de bases de donnes...........................................................10

V-D - Visualisation des enregistrements d'audit.................................................................................................. 11

V-D-1 - Audit de scurit des comptes de connexion....................................................................................11

V-D-2 - Audit des changements d'tat du serveur SQL.................................................................................13V-D-3 - Audit des accs la table dbo.access..............................................................................................14

V-E - Visualisation de la configuration des audits............................................................................................... 15

VI - Conclusion.......................................................................................................................................................... 17

VII - Webographie......................................................................................................................................................17

VIII - Bibliographie..................................................................................................................................................... 17

IX - Remerciements...................................................................................................................................................17
http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://blog.developpez.com/mikedavem/http://mikedavem.developpez.com/http://www.developpez.net/forums/u70376/mikedavem/http://www.developpez.com/

7/30/2019 auditer SQL 2008

3/17




I - Introduction

Dans des environnements complexes o il existe diffrents types d'acteurs qui interagissent tous de diffrentes

manires avec la base de donnes, le besoin d'auditer certains vnements devient primordial pour une entreprise.

Mais que doit auditer exactement un administrateur de bases de donnes ? La rponse n'est pas si vidente prioriet doit faire l'objet d'un vritable travail en amont car ceci dpend bien entendu des besoins et du contexte client.

SQL Server 2008 propose une nouvelle fonctionnalit d'audit unifie qui permet de mettre en place facilement ces

audits ce qui permet un administrateur de bases de donnes de se concentrer uniquement sur le vritable enjeu :

trouver les audits qui seront en adquation avec les besoins de l'entreprise.

II - Quels avantages utiliser ce systme d'audit ?

Les audits ne sont pas une nouveaut sur SQL Server. Depuis la version 2000 il existe diffrents outils qui permettent

d'auditer plus ou moins prcisment les vnements sur le serveur de bases de donnes. Cependant, jusqu' la

version 2005 de SQL Server, il fallait utiliser plusieurs de ces outils pour pouvoir auditer diffrentes catgories

d'vnements. Par exemple l'implmentation des triggers de type DML peuvent servir enregistrer des instructions

DML dans des tables d'historisation. Des triggers de type DDL, quant eux, peuvent enregistrer n'importe quelle

instruction DDL. Enfin le profiler, les notifications d'vnements ou les alertes de type WMI peuvent enregistrer

d'autres vnements comme les changements de configuration de serveur ou les changements de droits sur les

comptes de connexion ou les utilisateurs.

Le nouveau systme d'audit propos avec SQL Server 2008 facilite le travail de l'administrateur de bases de donnes

car il ne propose qu'un seul outil utiliser pour l'ensemble des catgories d'vnements auditer.

Un autre aspect important de cette nouvelle fonctionnalit est la scurit qui n'est pas implment avec les autres

outils proposs par SQL Server. En effet, les changements qui pourraient intervenir sur les audits eux mmes sont

enregistrs, ce qui limite fortement les possibilits de contournements par une personne mal intentionne. Il va de

soi que les fichiers d'audit ne soient accessibles que par un nombre restreint de personnes.

Enfin, l'impact sur les performances du serveur est minimis avec ce nouveau systme pour plusieurs raisons :

Il est possible d'crire de faon asynchrone les vnements dans les cibles d'audit

En adoptant une stratgie de limitation au strict ncessaire des vnements surveiller on minimise l'impact

sur les performances du serveur

Le choix du type de cible est galement dterminant. Les performances sont amliores si l'on choisit les

fichiers plats comme type de cible

III - Quels sont les inconvnients ?

Cependant si cette fonctionnalit possde beaucoup d'avantages il faut galement tenir compte de certains

inconvnients :

Elle n'est disponible qu' partir de la version Entreprise

Elle ne peut tre implmente qu'au niveau d'une instance. On ne peut donc pas utiliser cette fonctionnalit

de manire centralise sur une batterie de serveurs.

Il n'existe pas d'outil de Reporting en natif.
http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://blog.developpez.com/mikedavem/http://mikedavem.developpez.com/http://www.developpez.net/forums/u70376/mikedavem/http://www.developpez.com/

7/30/2019 auditer SQL 2008

4/17




IV - Architecture du systme d'audit

L'architecture de la nouvelle fonctionnalit d'audit utilise les vnements tendus de SQL Server 2008. Ces

vnements tendus sont galement une nouveaut de cette version. Cette infrastructure permet de corrler des

donnes d'vnements provenant de SQL Server mais aussi du systme d'exploitation, voire mme de certaines

applications de bases de donnes. Nous ne rentrerons pas dans le dtail des vnements tendus. Ils feront l'objetd'un autre article.

Revenons-en aux audits. Plusieurs types d'objets composent cette architecture. Chaque type d'objet a un rle bien

spcifique et permet de grer les audits diffrents niveaux. En effet, il est possible d'auditer des vnements au

niveau de l'instance SQL Server, au niveau d'une base de donnes ou encore au niveau d'un schma d'une base de

donnes spcifique. Chaque audit peut tre enregistr dans plusieurs types de cibles qui peuvent tre des fichiers

plats ou des journaux d'vnements.

L'architecture gnrale peut donc tre reprsente de la manire suivante :

Des objets de spcification d'audit de serveur ou de bases de donnes sont l'coute d'vnements ou groupes

d'vnements en se basant sur l'infrastructure d'vnements tendus. Ceux-ci sont ensuite transmis aux instances

d'objets d'audit serveur. Selon le paramtrage dfini, les vnements sont ensuite enregistrs de manire synchrone

ou asynchrone dans leurs cibles respectives.

IV-A - Objet d'audit SQL Server

L'objet d'audit SQL Server dfinit la cible dans laquelle les vnements seront enregistrs. Chaque type de cible

engendre une configuration des paramtres qui lui sont propres. Le mode d'criture dans une cible peut tre

7/30/2019 auditer SQL 2008

5/17




synchrone ou asynchrone par exemple (proprit QUEUE_DELAY). Il est galement possible de contrler le

comportement de l'instance SQL Server lorsque celui-ci ne peut plus crire dans la cible (proprit ON_FAILURE).

Il existe deux scnarios typiques :

L'instance SQL Server est arrte en cas d'chec d'criture dans une cible. Dans ce premier cas, il peut tre

ncessaire de redmarrer le serveur en mode mono utilisateur (option -m) ou avec une configuration minimale (option

-f), de dsactiver l'audit l'origine du problme et enfin permettre un redmarrage normal du serveur. Une analysedu problme pourra se faire postrieri par un administrateur de bases de donnes.

Le serveur SQL n'est pas arrt en cas d'chec d'criture dans une cible. Dans ce deuxime cas la mmoire tampon

ddie l'objet d'audit SQL Server commence se remplir. Par dfaut, chaque objet d'audit SQL Server se voit

allouer 4MB d'espace mmoire). Que se passe t'il si cette mmoire tampon est pleine ? Tant qu'il est impossible

d'crire dans la cible pour une raison quelconque, l'objet d'audit SQL Server bloquera tous les vnements qu'il est

cens enregistrer. Si le problme n'est pas rsolu avant que le systme d'exploitation renvoie lui mme une erreur

(erreur d'criture sur disque ou encore disque plein par exemple) l'objet d'audit SQL Server peut tre dsactiv et

une erreur correspondante sera crite dan le journal d'erreurs de SQL Server. Une fois le problme rsolu, l'objet

d'audit SQL Server devra tre redmarr.

Chaque objet d'audit SQL Server ne peut collecter les donnes que d'un seul type d'objet de spcification d'audit.Les objets de spcification d'audit peuvent tre crs diffrents niveaux en fonction de l'vnement ou du groupe

d'vnement que l'on dsire surveiller. Il peut exister plusieurs objets d'audit SQL Server pour une instance. Enfin

chaque objet d'audit SQL Server possde un tat. Par dfaut celui-ci est dsactiv. Il faut l'activer pour pouvoir

l'utiliser. Il faudra galement dsactiver un objet d'audit SQL Server si l'on veut modifier une de ses proprits.

IV-B - Cibles

Les cibles sont des containers dans lesquels sont enregistres les donnes relatives aux vnements audits. Il existe

3 types de cibles dans l'architecture d'audit SQL Server 2008. Deux concernent directement le journal d'vnement

Windows (APPLICATION_LOG ou SECURITY_LOG). Il est possible d'enregistrer les donnes d'vnement d'audit

soit dans le journal des applications soit dans le journal de scurit. Pour ce dernier, certains paramtragessupplmentaires sont ncessaires au niveau du systme d'exploitation. Nous y reviendrons plus tard dans l'article.

La 3me

cible concerne les fichiers plats (FILE). Il est possible d'utiliser un ou plusieurs de ces fichiers en roulement

(proprit MAX_ROLLOVER_FILES) pour ce type de cible l'instar des traces SQL Profiler ou Perfmon. Il est donc

ncessaire de paramtrer le chemin du ou des fichiers (proprit FILEPATH), le nombre de fichiers qui pourront

tre utiliss pour l'opration de roulement, et optionnellement de rserver directement l'espace total requis pour ces

fichiers (proprit RESERVE_DISK_SPACE). Il est enfin ncessaire de crer le rpertoire qui recevra les fichiers

d'audit (le moteur ne le cre pas, mais vrifie son existence).

IV-C - Objet de spcification d'audit serveur

L'objet de spcification d'audit serveur est cr au niveau de l'instance. Celui-ci permet de transmettre diffrentsvnements ou actions au niveau de l'instance par le biais des vnements tendus. Ces vnements sont

rassembls en groupes d'vnements classs par type. Le groupe d'vnements est donc ici la granularit la plus fine

qu'il est possible d'utiliser au niveau serveur. De la mme manire qu'un objet d'audit serveur, un objet de spcification

d'audit serveur est cre avec un tat dsactiv. Il faudra donc l'activer pour pouvoir l'utiliser. Il faudra galement

dsactiver un objet de spcification d'audit serveur pour pouvoir modifier une de ses proprits ou les vnements

transmettre.

IV-D - Object de spcification d'audit de bases de donnes

Un objet de spcification d'audit de bases de donnes fonctionne de la mme manire qu'un objet de spcification

d'audit de serveur quelques diffrences prs. Un objet de spcification d'audit de bases de donnes coute et

transmet les vnements au niveau d'une base de donnes. De plus, le niveau de granularit des vnements n'estpas le mme. En effet, il est possible de rcuprer des groupes d'vnements ou un seul type vnement.
http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://blog.developpez.com/mikedavem/http://mikedavem.developpez.com/http://www.developpez.net/forums/u70376/mikedavem/http://www.developpez.com/

7/30/2019 auditer SQL 2008

6/17




IV-E - Vues de gestion des audits

Bien entendu comme pour la plupart des autres fonctionnalits, SQL Server fournit des vues systmes, des DMV

(Dynamic Management View) et DMF (Dynamic Management Function) pour les audits. En voici la liste :

IV-E-1 - Vues systmes

sys.server_audits : Chaque ligne de cette vue correspond une instance d'objet d'audit d'audit SQL Server.

sys.server_audit_specifications : Chaque ligne de cette correspond une instance d'objet de spcification d'audit

serveur.

sys.server_audit_specification_details : Chaque ligne de cette vue correspond un groupe d'vnements audit

pour chaque instance d'objet de spcification d'audit serveur. Il peut y avoir plusieurs lignes pour une instance d'objet

de spcification d'audit serveur tant donn que celle-ci peut auditer plusieurs groupes d'vnements la fois.

sys.database_audit_specifications : Chaque ligne de cette vue correspond une instance d'objet de spcificationd'audit de bases de donnes.

sys.database_audit_specification_details : Chaque ligne de cette vue correspond un groupe d'vnements ou

un vnement audit pour chaque instance d'objet de spcification d'audit de bases de donnes. De la mme manire

que pour les instances d'objets de spcification d'audit serveur, il peut exister plusieurs lignes pour une instance

d'objet de spcification d'audit de bases de donnes.

IV-E-2 - DMV

sys.dm_server_audit_statut : Cette vue permet de connatre l'tat de chaque instance d'objet d'audit SQL Server.

sys.dm_audit_actions : Cette vue permet de lister les vnements ou groupes d'vnements pouvant tre

enregistrs dans un fichier de log. Il est galement possible de connatre leur niveau de configuration (bases de

donnes ou serveur) et quel groupe d'vnements ils appartiennent.

sys.dm_audit_class_type_map : Cette vue retourne une table de correspondance entre les valeurs d'une colonne

class_type d'un fichier d'audit et d'une colonne class_desc de la DMV sys.dm_audit_actions.

sys.server_file_audits : Chaque ligne de cette vue correspond un fichier d'audit cr sur une instance SQL Server.

On y retrouve notamment des informations de paramtrage comme le nom et le chemin du fichier, sa taille maximum,

le type d'criture (synchrone ou asynchrone), sa date de cration etc .

IV-E-3 - DMF

fn_get_audit_file() : Cette fonction permet d'extraire les informations d'un fichier d'audit. Celle-ci permet galement

de lire plusieurs fichiers en mme temps. Cette fonction ne peut tre utilise que pour les cibles de type fichier plat.

V - Cas d'tude

Ce cas d'tude rpond trois besoins que vous pouvez rencontrer en entreprise :

Auditer et enregistrer tous les changements intervenant sur les comptes de connexion du serveur de bases

de donnes.

Auditer et enregistrer les changements d'tat du serveur de bases de donnes. Auditer l'accs certains objets d'une base de donnes pour connatre son utilisation relle.

7/30/2019 auditer SQL 2008

7/17




Nous utiliserons 3 types de cibles pour enregistrer les diffrents audits. La politique de scurit de l'entreprise impose

que les audits relatifs aux changements intervenant sur les comptes de connexion se fassent de manire synchrone

et dans le journal de scurit Windows. Si un problme quelconque ne permettait pas d'enregistrer ces changements

alors le serveur de bases de donnes doit tre arrt. Les audits des changements d'tat ou de configuration du

serveur de bases de donnes ainsi que tous les changements relatifs l'accs des objets d'une base de donnes

nomme DB_AUDITse feront dans un fichier plat d'une taille de 100Mo maximum, ce qui permettra de garder un

historique convenable en concordance avec les exigences de l'entreprise. Les critures dans le fichier se ferontde manire asynchrone avec un dlai maximum d'une seconde pour ne pas altrer de manire significative les

performances du serveur.

Nous utiliserons le langage TSQL pour crer nos objets d'audits. Il est cependant possible d'arriver au mme rsultat

avec la console graphique de gestion des bases de donnes SQL Server Management Studio.

CREATEDATABASE DB_AUDIT;GO

USE DB_AUDIT;

GO

CREATETABLE dbo.access

(

id INTPRIMARYKEY,name_emp VARCHAR(50) NOTNULL

);

GO

V-A - Cration des instances d'objets d'audit SQL Server

Commenons donc par crer 3 instances d'objet d'audit SQL Server avec les 3 types de cibles exigs.

USE masterGO

-- Cible --> journal d'application Windows

CREATE SERVER AUDIT application_log_modif_status_server

TOFILE(

FILEPATH ='E:\audit\',

MAXSIZE =100 MB,

RESERVE_DISK_SPACE =ON)

WITH

(

QUEUE_DELAY =1000,

ON_FAILURE = CONTINUE);

-- Activitation de l'instance d'objet audit SQL ServerALTER SERVER AUDIT application_log_modif_status_server

WITH ( STATE =ON );

-- Cible --> journal de scurit WindowsCREATE SERVER AUDIT security_log_modif_security_login_user

TO SECURITY_LOG

WITH

(QUEUE_DELAY =0,

ON_FAILURE = SHUTDOWN

);

-- Activation de l'instance d'objet audit SQL ServerALTER SERVER AUDIT security_log_modif_security_login_user

WITH ( STATE =ON );

7/30/2019 auditer SQL 2008

8/17




-- Cible --> Fichier plat

CREATE SERVER AUDIT file_modif_objects_database

TOFILE

(FILEPATH ='E:\audit\',

MAXSIZE =100 MB,

RESERVE_DISK_SPACE =ON

)WITH

(

QUEUE_DELAY =1000,

ON_FAILURE = CONTINUE

);

-- Activation de l'instance d'objet audit SQL Server

ALTER SERVER AUDIT file_modif_objects_database

WITH ( STATE =ON );

Aprs avoir cr et activ les objets d'audit SQL Server pour les 3 cibles concernes, il faut ensuite effectuer un

paramtrage supplmentaire pour que SQL Server soit autoris crire dans le journal de scurit Windows. Pour

cela nous utiliserons la console de gestion des stratgies locales du serveur mais il est galement possible d'utiliser

les utilitaires en ligne de commande auditpolou secpol. On accde la console de gestion des stratgies locales duserveur partir du Panneau de configuration qui est rfrence dans le menu Outils d'administration

La premire tape consiste autoriser le compte de service SQL Server gnrer des audits de scurit. Pour cela

avec la console de gestion des stratgies locales, il faut se rendre sous le n?ud Attribut de des droits utilisateurs

et choisir le paramtre Gnrer des audits de scurit

Il faut ajouter le compte de service du serveur SQL. Dans notre cas nous ajouterons le compte LOCAL SYSTEM.

7/30/2019 auditer SQL 2008

9/17




Dans un deuxime temps il faut galement activer les audits d'accs sur les objets. Ceci ce fait aussi par la console

de gestion des stratgies locales sous le n?ud Stratgie d'audit et le paramtre Auditer l'accs aux objets .

Il faut activer les audits pour les 2 types d'accs : Russite et Echec

Bien entendu il faudra ventuellement veiller ce qu'aucune stratgie descendue par GPO ne vienne modifier le

comportement des paramtres configurs dans les stratgies locales. Attention bien veiller paramtrer ces accssurtout dans le cas o vous avez dcid de stopper le serveur SQL en cas d'chec d'criture dans le journal de

7/30/2019 auditer SQL 2008

10/17




scurit Windows. En effet lorsque vous activerez cet audit et si les accs ne sont pas corrects, l'audit stoppera

automatiquement le serveur !!!

V-B - Cration des objets de spcification d'audit serveur

Il faut maintenant crer deux objets de spcification d'audit au niveau de l'instance SQL Server. Un objet despcification d'audit sera l'coute des vnements ou actions concernant les changements sur les comptes de

connexion. Un deuxime objet de spcification d'audit sera, quant lui, destin aux changements d'tat intervenant

sur le serveur.

USE[master];GO

-- Audit changement statut serveur

CREATE SERVER AUDIT SPECIFICATION audit_change_status_serverFOR SERVER AUDIT file_modif_objects_database

ADD (SERVER_STATE_CHANGE_GROUP),

ADD (SERVER_OPERATION_GROUP)

WITH ( STATE =ON );GO

-- Audit modification scurit des comptes de connexion

CREATE SERVER AUDIT SPECIFICATION audit_modif_users_logins

FOR SERVER AUDIT security_log_modif_security_login_userADD (FAILED_LOGIN_GROUP), -- Connexion choue

ADD (LOGIN_CHANGE_PASSWORD_GROUP), -- Changement de mot passe

ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), -- Changement rle serveur

ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP) -- Changement rle base de donnesWITH ( STATE =ON );

Nous allons ajouter l'objet de spcification d'audit serveur nomm audit_modif_users_login le groupe

d'vnement SERVER_PRINCIPAL_CHANGE_GROUP qui permet de surveiller, entre autres, les vnements de

cration, de suppression et de modification de compte de connexion. Pour rappel, il faudra, au pralable, le dsactiverpour pouvoir le modifier.

ALTER SERVER AUDIT SPECIFICATION audit_modif_users_logins

WITH ( STATE =OFF );

-- Ajout d'un vnement pour l'instance d'objet de spcification d'audit serveur-- audit_modif_users_logins


ADD (SERVER_PRINCIPAL_CHANGE_GROUP); -- Evnement CREATE, DROP , ALTER;


WITH ( STATE =ON );

V-C - Cration des objets de spcification d'audit de bases de donnes

Aprs avoir cr les objets de spcification d'audit serveur, il nous faut crer maintenant un autre objet de spcification

d'audit de bases de donnes pour surveiller les vnements SELECT, INSERT, UPDATE, DELETE et RERENCES

en provenance de l'utilisateurdbo pour la table dbo.access .

USE DB_AUDIT

GO

-- Audit d'accs pour la table dbo.access de la base de donnes

-- DB_AUDIT

CREATEDATABASEAUDIT SPECIFICATION file_modif_objects_databaseFOR SERVER AUDIT file_modif_objects_database

ADD (SELECT, INSERT, UPDATE, DELETE, REFERENCES ON dbo.accessBYpublic)

7/30/2019 auditer SQL 2008

11/17




WITH (STATE =ON)GO

V-D - Visualisation des enregistrements d'audit

Les outils de visualisation d'vnements diffrent selon le type de cible utilis. On peut par exemple utiliser

directement le journal des vnements pour un type de cible se basant sur le journal de scurit ou d'application.

SQL Server met galement disposition une DMFqui permet de lire les donnes d'audit dans un fichier plat.

V-D-1 - Audit de scurit des comptes de connexion

Commenons par simuler des actions pouvant tre effectues sur des objets de type comptes de connexion :

-- Cration compte de connexion test1

CREATE LOGIN test1

WITH PASSWORD ='test1';

-- Cration compte de connexion test2CREATE LOGIN test2

WITH PASSWORD ='test2';

-- Modification mot de passe compte de connexion test1ALTER LOGIN test1

WITH PASSWORD ='%test1%';

-- Modification mot de passe compte de connexion test2ALTER LOGIN test2

WITH PASSWORD ='%test2%';

-- Ajout du compte de connexion test1 au rle fixe de serveur sysadminEXEC sp_addsrvrolemember 'test1', 'sysadmin';

-- Ajout du compte de connexion test2 au rle fixe de serveur sysadminEXEC sp_addsrvrolemember 'test2', 'bulkadmin';

-- Suppression du compte de connexion test1

DROP LOGIN test1;

-- Suppression du compte de connexion test2DROP LOGIN test2;

Il suffit maintenant de regarder dans le journal de scurit de Windows. L'ID de l'vnement gnr par les audits

SQL Server 2008 dans le journal de scurit est gale 33205. Voici un exemple de ce que l'on trouve dans le journal

aprs avoir excut les scripts TSQL ci-dessus :

7/30/2019 auditer SQL 2008

12/17




Le dtail d'une ligne du journal concernant la cration d'un des comptes de connexion :

Le dtail d'une ligne du journal concernant le changement de mot de passe d'un des comptes de connexion :

Le dtail d'une ligne du journal concernant l'affectation d'un des comptes de connexion au rle de serveur fixe

sysadmin :

7/30/2019 auditer SQL 2008

13/17




Le dtail d'une ligne du journal concernant la suppression d'un des comptes de connexion :

V-D-2 - Audit des changements d'tat du serveur SQL

Initions maintenant un changement dans les options de configuration du serveur. Nous autorisons la procdure

xp_cmdshell.

EXEC sp_configure 'show advanced options', 1;GO

RECONFIGURE;

GO

EXEC sp_configure 'xp_cmdshell' , 1;GO

RECONFIGURE;

... et nous redmarrerons galement notre serveur de bases de donnes :

net stop mssqlserver /y && net start mssqlserver && net start sqlserveragent

Pour pouvoir visualiser les enregistrements d'audits dans un fichierplat nous utiliserons la DMF fn_get_audit_file,

les vues systmes sys.dm_audit_actions et sys.dm_audit_class_type de la manire suivante :

SELECT

f.session_id,a.nameAS action_name,

m.class_type_desc,

a.covering_action_name,f.statement,

f.server_principal_name

7/30/2019 auditer SQL 2008

14/17




FROM fn_get_audit_file('E:\audit\application_log_modif_status_server*' , DEFAULT, DEFAULT)AS fINNERJOIN sys.dm_audit_class_type_map AS m

ON m.class_type = f.class_type

INNERJOIN sys.dm_audit_actions AS a

ON a.action_id = f.action_idORDERBY event_time DESC

Le rsultat est le suivant :

On peut voir les lignes d'audit en relation avec les actions de type ALTER SETTINGS et SERVER_STARTED .

V-D-3 - Audit des accs la table dbo.access

-- opration SELECT

SELECT*FROM dbo.access

-- opration INSERT

INSERT dbo.accessVALUES (1, 'emp1');INSERT dbo.accessVALUES (2, 'emp2');

-- opration UPDATE

UPDATE dbo.accessSET name_emp ='emp1'

WHERE id =1;

-- opration DELETE

DELETEFROM dbo.accessWHERE id =2;

GO

Le rsultat est le suivant :

Chaque ligne d'audit correspond un vnement. On peut remarquer pour les instructions UPDATE et DELETE

il existe 2 lignes d'audit. Quelle en est la raison ? Dans notre cas, la requte effectue des mises jour ou des

suppressions en utilisant un prdicat (clause WHERE). Cette action est possible la condition de possder le droit

SELECT sur l'objet concern (ici la table dbo.access). C'est la raison pour laquelle nous aurons, chaque fois qu'une

instruction UPDATE ou DELETE est effectue en utilisation une clause WHERE, deux lignes correspondant aux

actions SELECT et UPDATE ou DELETE.
http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://blog.developpez.com/mikedavem/http://mikedavem.developpez.com/http://www.developpez.net/forums/u70376/mikedavem/http://www.developpez.com/

7/30/2019 auditer SQL 2008

15/17




V-E - Visualisation de la configuration des audits

Un certain nombre de DMVexiste pour visualiser la configuration des diffrents composants de l'architecture d'audit

SQL Server 2008. Voici quelques exemples d'utilisation de ces vues de gestion :

Visualisation des diffrentes instances d'objets d'audits SQL Server, de leurs instances d'objets despcification d'audit respectives, les vnements ou groupes d'vnements audits et les paramtres de

fichier pour les types de cibles fichier plat :

SELECTsa.nameAS audit_name,

sa.is_state_enabled,

sa.on_failure_desc,

sa.queue_delay,

sa.type_desc,sas.nameAS srv_spe_name,

sas.is_state_enabled,

(SELECT audit_action_name +', '

FROM sys.server_audit_specification_details AS sasd WHERE sasd.server_specification_id = sas.server_specification_id

FOR XML PATH('')) AS list_actions,

fa.log_file_path,

fa.log_file_name,fa.max_file_size,

fa.max_rollover_files

FROM sys.server_audit_specifications AS sas

INNERJOIN sys.server_audits AS saON sa.audit_guid = sas.audit_guid

LEFTJOIN sys.server_file_audits AS fa

ON fa.audit_guid = sa.audit_guid

Visualisation de la configuration des diffrentes instances d'objets d'audit de spcification de bases de

donnes

SELECT

DISTINCTsa.name,

sa.is_state_enabled,

class_desc,

(SELECT sasd.audit_action_name +', '

FROM sys.database_audit_specification_details AS sasd WHERE sasd.database_specification_id = sa.database_specification_id

FOR XML PATH('')) AS list_actions

FROM sys.database_audit_specification_details AS sas

INNERJOIN sys.database_audit_specifications AS saON sas.database_specification_id = sa.database_specification_id

Visualisation du statut des diffrentes cibles d'audits :

SELECT

nameAS audit_name,status_desc,

status_time,

audit_file_path,

audit_file_size /1024AS size_in_Ko

FROM sys.dm_server_audit_status;

7/30/2019 auditer SQL 2008

16/17




Visualisation de la configuration des instances d'audits SQL Server concernes par le type de cible fichier :

SELECT

name,

on_failure,

max_file_size,max_rollover_files,

log_file_path,

log_file_name

FROM sys.server_file_audits

Visualisation du dtail de la configuration d'une spcification d'audit pour une base de donnes :

USE DB_AUDIT;

GO

SELECT

database_specification_id,audit_action_name,

class_desc,

audited_result,

is_groupFROM sys.database_audit_specification_details AS das

WHERE das.database_specification_id = (SELECT database_specification_id

FROM sys.database_audit_specifications

WHEREname='file_modif_objects_database')ORDERBY class_desc;

Visualisation des informations d'audits et de leurs sessions d'vnements tendues respectives : Remarquez

que pour l'objet d'audit "security_log_modif_security_login_user" aucun buffer n'est allou. Ceci est normal

puisque nous avons demand une criture synchrone pour cet audit.

SELECT

a.name,

x.nameAS session_name,t.target_name,

x.total_buffer_size

FROM sys.dm_server_audit_status AS aINNERJOIN sys.dm_xe_sessions AS xON a.event_session_address = x.address

7/30/2019 auditer SQL 2008

17/17


- 17 -Les sources prsentes sur cette page sont libres de droits et vous pouvez les utiliser votre convenance Par contre la page de prsentation

INNERJOIN sys.dm_xe_session_events AS eON x.address = e.event_session_address

INNERJOIN sys.dm_xe_session_targets AS t

ON t.event_session_address = e.event_session_address

VI - Conclusion

SQL Server 2008 possde un outil d'audit extrmement puissant. Cette nouvelle fonctionnalit permet de rpondre

parfaitement aux aspects scurit, performance et gestion. Dommage que cette fonctionnalit ne soit disponible qu'en

version entreprise ... Cependant si votre entreprise possde dj un outil d'audit des bases de donnes SQL Server,

cela vaut la peine de tester cette nouvelle fonctionnalit et d'tablir un benchmarking des fonctionnalits !!

VII - Webographie

Documentation msdn

VIII - Bibliographie

70-432 : Microsoft SQL Server 2008--Implementation and Maintenance : Training Kit

IX - Remerciements

Je remercie tout particulirement Elsuket pour avoir contribu l'laboration de cet article.
http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://mikedavem.developpez.com/tutoriels/sqlserver/2008/audits-avec-sql-server-2008/http://www.developpez.net/forums/u52290/elsuket/http://msdn.microsoft.com/en-us/library/cc280386.aspxhttp://blog.developpez.com/mikedavem/http://mikedavem.developpez.com/http://www.developpez.net/forums/u70376/mikedavem/http://www.developpez.com/

Documents

auditer SQL 2008