Partager via

SQL Server : Rôles définis par l'utilisateur

  • Article

L'arrivée de SQL Server 2012 s'accompagne de la capacité de créer des rôles de serveur définis par l'utilisateur, un pas de géant en avant pour la gestion SQL.

Denny Cherry

Bien que nous soyons capables de créer des rôles de base de données définis par l'utilisateur depuis des dizaines d'années afin de faciliter le processus d'octroi d'autorisations au niveau des bases de données, neuf rôles de serveur fixes ont toujours été définis au niveau des instances (huit si vous utilisez un logiciel plus ancien que SQL Server 2000 car le rôle bulkadmin a été introduit avec SQL Server 2005). Désormais, avec SQL Server 2012, nous pouvons enfin créer des rôles de serveur définis par l'utilisateur.

Pendant des années, l'impossibilité de créer ces rôles a rendu la gestion SQL très compliquée. Que faites-vous lorsque vous avez besoin d'accorder des droits à plusieurs utilisateurs ou groupes au niveau de l'instance et que vous souhaitez synchroniser ces droits ? Supposons que vous souhaitiez accorder le droit de consultation de l'état du système, View System State, à un grand nombre d'utilisateurs afin qu'ils puissent accéder aux informations de blocage au sein des serveurs de développement. Vous devez alors accorder ce droit individuellement à chaque utilisateur ou groupe de domaines qui en a besoin.

Si toutes les connexions reposent sur des connexions au domaine, l'une des rares solutions à votre disposition consiste à créer un groupe de domaines pour tous les utilisateurs ayant besoin de ce droit (dans certains cas, il peut être nécessaire de disposer d'un groupe de domaines pour chaque serveur si le droit d'accès à tous les serveurs n'est pas accordé aux utilisateurs). Vous pouvez intégrer les utilisateurs à ce groupe, créer une connexion mappée au groupe de domaines, puis accorder à ce groupe le droit View Server State sur le serveur. Vous devez toutefois procéder avec précaution, faute de quoi vous risquez d'accorder des droits de connexion à des utilisateurs qui n'en bénéficiaient pas auparavant. Vous risquez même d'accorder aux utilisateurs des droits dont vous ne souhaitez pas les voir bénéficier.

Rôle avec T/SQL

Vous pouvez créer un rôle de serveur défini par l'utilisateur de plusieurs façons, notamment avec T/SQL, l'interface utilisateur de SQL Server Management Studio, et Windows PowerShell. Si je devais créer un rôle de serveur défini par l'utilisateur à l'aide de T/SQL, j'utiliserais trois commandes différentes. Tout d'abord, l'instruction de création d'un rôle de serveur, Create Server Role, me permettrait de créer le rôle de serveur défini par l'utilisateur ; ensuite, l'instruction de modification d'un rôle de serveur, Alter Server Role, ajouterait un utilisateur au rôle de serveur ; et enfin, l'instruction d'octroi, Grant, accorderait les droits nécessaires au rôle.

Ces trois instructions sont illustrées dans le code suivant qui présente la création d'un rôle de serveur défini par l'utilisateur nommé « ViewServerState ». Cela indique qu'un utilisateur est ajouté au rôle nommé « SomeFakeLogin », puis que le droit View Server State est accordé au rôle. Pour accorder ce droit à des utilisateurs supplémentaires, il vous suffit d'ajouter ceux-ci au rôle de serveur fixe à l'aide de la ligne Alter Server Role :

USE [master] GO CREATE SERVER ROLE [ViewServerState] AUTHORIZATION [sa] GO ALTER SERVER ROLE [ViewServerState] ADD MEMBER [SomeFakeLogin] GO GRANT VIEW SERVER STATE TO [ViewServerState] GO

Pour supprimer un utilisateur d'un rôle de serveur défini par l'utilisateur, utilisez également Alter Server Role. Toutefois, au lieu d'utiliser la syntaxe d'ajout d'un membre, Add Member, vous utiliserez celle d'annulation d'un membre, Drop Member.

ALTER SERVER ROLE [ViewServerState] DROP MEMBER [SomeFakeLogin] GO

En ce qui concerne le déploiement d'un ou de plusieurs rôles de serveur définis par l'utilisateur sur plusieurs instances de SQL Server, diverses options sont à votre disposition. Vous souhaitez probablement éviter de vous connecter à chaque serveur individuellement afin de créer les rôles de serveur définis par l'utilisateur. Une option consiste à utiliser la fonctionnalité de SQL Server Management Studio. Cela vous permet d'exécuter des scripts T/SQL sur plusieurs instances.

Vous pouvez également utiliser les composants Windows PowerShell de SQL Server pour déployer de nouveaux rôles de serveur définis par l'utilisateur sur toutes les instances de SQL Server au sein de votre organisation. Étant donné qu'il existe un grand nombre de façons d'utiliser Windows PowerShell pour déployer ces scripts, nous ne couvrirons pas ce processus dans le cadre de cet article.

SQL Server Management Studio

Vous pouvez aisément effectuer la même action avec l'interface utilisateur de SQL Server Management Studio. Pour créer un rôle de serveur défini par l'utilisateur, connectez-vous à l'instance appropriée dans l'Explorateur d'objets. Accédez à InstanceName | Sécurité | Rôles de serveur dans l'Explorateur d'objets. Cliquez avec le bouton droit de la souris sur Rôles de serveur et sélectionnez Nouveau rôle de serveur dans le menu contextuel. Une fois la fenêtre Nouveau rôle de serveur ouverte, spécifiez le nom du rôle de serveur et le propriétaire, puis sélectionnez les éléments sécurisables et les autorisations que vous souhaitez accorder aux membres du rôle (voir la figure 1).

Figure 1 Spécifiez les droits que vous souhaitez accorder à un utilisateur donné.

Une fois que vous avez rempli les champs de la page Général, sélectionnez la page Membres (voir la figure 2) et spécifiez les comptes de connexion SQL Server qui deviendront membres de ce rôle de serveur défini par l'utilisateur.

Figure 2 Sélection des membres qui spécifieront les utilisateurs ayant accès à ce rôle de serveur.

Après avoir sélectionné les membres du rôle, sélectionnez la page Appartenances. Elle permet d'indiquer les rôles de serveur dont celui défini par l'utilisateur deviendra membre. Si vous spécifiez un rôle de serveur sur cet écran, les utilisateurs membres du rôle de serveur défini par l'utilisateur bénéficieront également des droits accordés par ce rôle de serveur.

Si vous deviez créer un rôle défini par l'utilisateur et le rendre membre du rôle de serveur fixe serveradmin (voir la figure 3), tous les membres du rôle de serveur défini par l'utilisateur ne seraient pas réellement membres du rôle de serveur fixe serveradmin. Comme dans le cas de l'imbrication de groupes de domaines ou de rôles de base de données, vous devez faire preuve d'une grande prudence lors de l'imbrication des rôles afin d'éviter d'accorder aux utilisateurs des droits dont ils ne devraient pas disposer puisque ces droits appartiennent à des groupes de niveau supérieur.

Figure 3 Vous pouvez appliquer votre rôle défini par l'utilisateur à d'autres rôles de serveur.

Pour imbriquer les rôles de serveur à l'aide de T/SQL, vous devez toujours utiliser l'instruction Alter Server Role avec la syntaxe Add Member. Par exemple, pour qu'un rôle de serveur défini par l'utilisateur ViewServerState devienne membre du rôle de serveur fixe setupadmin, vous devez modifier le rôle de serveur fixe setupadmin. Vous ajoutez ensuite le rôle de serveur défini par l'utilisateur ViewServerState comme membre :

ALTER SERVER ROLE [setupadmin] ADD MEMBER [ViewServerState] GO

Les rôles définis par l'utilisateur ont de nombreuses utilisations. Il existe des dizaines de droits au niveau de l'instance que vous pouvez accorder à un rôle de serveur défini par l'utilisateur afin de simplifier la gestion de ces droits. Vous pouvez également créer un rôle DBA junior qui accorde quelques droits, mais pas tous les droits d'administrateur système. Vous pouvez créer un groupe AlwaysOnAdmin, ce qui permet aux droits de basculer sur la base de données AlwaysOn (cette opération doit être effectuée depuis SQL Server) sans disposer des droits d'administrateur système complets.

Il existe divers scénarios d'utilisation des rôles de serveur définis par l'utilisateur. Ils allègeront largement les charges de travail administratives. Ils permettent également d'accroître la sécurité des instances de SQL Server dans la mesure où ces nouveaux rôles de serveur réduisent les risques d'attribution accidentelle de droits inappropriés.

DennyCherry

Denny Cherry bénéficie de plus de dix ans d'expérience en matière de gestion de SQL Server. Parmi ses domaines de compétence figurent l'architecture système, l'optimisation des performances, la réplication et la résolution des problèmes. Il détient plusieurs certifications liées à SQL Server, dont le Microsoft Certified Master pour SQL Server 2008. Il est MVP Microsoft SQL Server depuis plusieurs années et a rédigé de nombreux articles techniques et ouvrages sur la gestion SQL Server et l'intégration de ce dernier à diverses autres technologies.

Contenu associé