Denali – Schéma par défaut

Il y a quelques temps, lors d’une mission de consulting, j’ai été confronté à un problème de sécurité. Non pas que la sécurité d’une serveur ou  des données ait été compromise, mais simplement, les personnes qui se connectaient au travers de leur login Windows à une application ne pouvaient pas travailler…

Petit retour sur le modèle de sécurité …

L’instruction CREATE LOGIN crée une connexion au serveur, sans droits particuliers. On retrouve les informations associées à ce login dans la table master.sys.server_principals.

Ensuite, on donne des droits d’accès à ce login dans une ou plusieurs bases de données. Cela se fait au travers de l’instruction T-SQL CREATE USER xxx FROM LOGIN. On retrouve les utilisateurs dans la table système sys.database_principals. On voit apparaitre dans cette table la notion de schéma par défaut.

Le schéma par défaut est l’espace de nommage dans lequel vous évoluez par défaut, sans avoir besoin de le mentionner. Si vous voulez accéder à un autre schéma, il faut alors le spécifier explicitement dans le nom de l’objet ( <Schema_Name>.<Object_Name>). Il est courant de voir tous les objets dans le même schéma (dbo pour ne pas le citer) mais rien n’empêche les développeurs de créer un ou plusieurs schémas afin d’y stocker leurs objets de manière à les regrouper par application (Ventes, Marketing, Ressources Humaines, Production, …).

Tout cela est bien beau, sauf que cela ne fonctionne que pour des logins “simples”, entendez par là qu’il n’est pas possible d’affecter un schéma par défaut à un groupe (Windows …). Et c’est bien dommage, car tout le travail qui a été fait par les administrateurs Active Directory pour créer des groupes en fonction du travail, des responsabilités, des accès à des applications ou des données est réduit à néant. Il n’est pas possible jusqu’à SQL Server 2008 R2 inclus d’affecter un schéma par défaut à un groupe :

image

Cela s’explique. Quel schéma par défaut affecter à une personne qui appartient à deux groupes qui ont accès à la même base de donnée ? Quel schéma par défaut affecter à une personne qui accès à la fois à travers son login et grâce à l’appartenance à un groupe à la même base de donnée, pour peu que les schémas par défaut soient différents ?

 

Denali, SQL11, apporte enfin une solution. Il est maintenant possible d’affecter un schéma par défaut à un login de type Groupe. Voici la règle suivie :

  • Si le user a accès à la base au travers de son login propre et au travers d’un groupe, chacun ayant un schéma par défaut, alors le schéma par défaut de son login sera choisi comme schéma par défaut pour cette connexion.
  • Si le user a accès au travers de son login propre, sans schéma par défaut, et au travers d’un groupe, alors le schéma par défaut du groupe sera choisi comme schéma par défaut pour cette connexion.
  • Si le user appartient à plusieurs groupes ayant un schéma par défaut, mais pas de login propre, alors le schéma par défaut du groupe ayant la plus faible valeur pour la colonne principle_id sera utilisé.
  • Si aucun schéma par défaut ne peut être obtenu, alors dbo est le schéma par défaut affecté à la connexion.
  • Pour les membres du rôle fixe serveur SYSADMIN, la valeur du schéma par défaut est ignoré et est systématiquement fixée à dbo.

Cela devrait simplifier la sécurité et limiter le nombre de logins sur bon nombres de serveurs SQL…

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Cet article, publié dans SQL Server, est tagué , . Ajoutez ce permalien à vos favoris.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s