Sécurité des données dans l’environnement SQL Server

Lors d’un des derniers projets auquel j’ai participé, nous avons eu à mettre en place une sécurité à la ligne. Rien d’extraordinaire en soi, mais c’est un bon cas d’étude pour comprendre comment fonctionne la sécurité entre les différentes briques de l’environnement SQL Server.

J’ai essayé de schématiser ça en 3 dessins Visio que je laisse en téléchargement par ici (C’est la première fois que j’utilise Google Document comme ça, mailez moi si ça ne marche pas).

Pour faire court :

  • Un groupe d’administration accède à une interface ASP.NET qui lui permet de définir des couples Utilisateur / Périmètre. Le périmètre s’applique sur une hiérarchie organisation, il se compose d’un niveau et du code de l’entité à ce niveau.
  • Le groupe d’utilisateur accède à un reporting qui utilise un cube et des données de la base. On doit lui appliquer la sécurité définit par les administrateurs.

Pour implémenter une sécurité à la donnée, on ne peut pas donner des droits DataReader/DataWriter aux utilisateurs sur la base. En effet la granularité minimale de la sécurité sur SQL Server c’est la table, ni la ligne ni la colonne.
Il va donc falloir utiliser des procédures stockées (droits GRANT EXECUTE) et joindre les résultats sur la table qui stocke les couples Utilisateur / Périmètre.
Pour le cube, il va falloir définir un rôle qui utilise cette même table.

Le point important dans tout ça, c’est que si votre configuration n’est pas mono-serveur, c’est-à-dire si votre service SSRS n’est pas installé sur le serveur qui héberge la base et/ou le cube, il va falloir mettre en place la délégation d’autorité en utilisant Kerberos. C’est en effet la seule manière de faire propager le UserId depuis le SSRS frontal jusqu’aux cubes et aux procédures stockées. Dans le cas contraire, ce sera le compte de service de SSRS qui se propagera jusqu’aux données.

On peut contourner ce pré-requis si on n’utilise pas de cube dans la solution. Pour ce faire, il suffit de transmettre la constante User !UserId de SSRS en paramètre à la procédure stockée appelée. Ce n’est pas la best practice, mais ça marche. Cette manipulation n’est pas possible pour SSAS puisque la gestion des droits est transparente dans le cube.

Voici le premier schéma, il présente une vue d’ensemble de la configuration:

Vue d'ensemble de la sécurité

Concernant la sécurité fonctionnelle, la sécurité à la ligne, on peut utiliser un jeu de tables comme cela pour l’implémenter:

Définition de la sécurité

Et enfin, pour utiliser tout ça, il faut employer les scripts suivants:

Utilisation de la sécurité

Rappel : si vous voulez le VSD de ces schémas, il suffit de remonter en haut de l’article.

Ne disposant pas de la science infuse, j’apprécierai sincèrement les éventuelles corrections / améliorations que vous auriez à proposer 🙂

SQL Server 2008 SP2 et autres sucreries

La nouvelle de la journée : le SQL Server 2008 SP2 sort aujourd’hui! Il est détaillé sur le Team Blog, et se télécharge par là.

J’en profite pour faire le relai d’une sélection d’articles techniques que j’ai collectionné ces 2 derniers mois:

SSRS

  • Importer des cartes autres que celle des USA dans les rapports, via Kasper de Jonge.

SSIS

  • Minimiser les dégâts lorsqu’on effectue un produit cartésien (cross join) dans un flux de données, via Todd McDermid.
  • Une belle remise à plat sur l’utilisation des variables dans les Script Tasks et Script Components, via Todd McDermid encore.
  • Un composant à tester: un nouveau Data Conversion, via Todd McDermid toujours.
  • Une petite piqure de rappel sur les Events et Event Handlers, via le Database Journal.
  • Importer les valeurs d’un result set ADO dans un chaîne, séparées par des virgules. C’est un peu tortueux, mais c’est parfait pour générer des requêtes SQL à la volée. Via SqlServerCentral (enregistrement gratuit)

SSAS

  • MDX : Existing, Count et Filter via Thomas Ivarsson. Il n’explique pas assez à mon gout, mais en jouant avec ses requêtes on comprend bien.

T-SQL

  • Implémenter des tests unitaires automatisés dans Visual Studio 2010 pour le développement des procédures stockées. Ça c’est un sujet à creuser, via Jamie Thomson.
  • Utiliser le MERGE dans SQL Server 2008, via Robert Sheldon de Simple Talk. J’ai honte mais je n’ai pas encore eu le temps de vraiment regarder… Bouh!
  • Dédoublonner un data set grâce à la clause PARTITION BY. Pas sur que ça nous soit très utile en BI cependant, via SqlServerCentral (enregistrement gratuit)

ASP.NET

SharePoint

  • Un livre blanc sur l’authentification Kerberos dans SharePoint 2010, via Kasper de Jonge.