Déterminer les autorisations effectives du moteur de base de données
S’applique à :
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)
Cet article explique comment déterminer les détenteurs d’autorisations sur différents objets dans le moteur de base de données SQL Server. SQL Server met en œuvre deux systèmes d’autorisations pour ce moteur de base de données. Un ancien système de rôles fixes dispose d’autorisations pré-configurées. À compter de SQL Server 2005 (9.x), un système plus flexible et plus précis est disponible.
Note
Les informations contenues dans cet article s’appliquent à SQL Server 2005 (9.x) et versions ultérieures. Certains types d’autorisations ne sont pas disponibles dans certaines versions de SQL Server.
Vous devez toujours garder à l’esprit les points suivants :
- Les autorisations effectives représentent l’agrégat des deux systèmes d’autorisations.
- Les refus d’autorisation se substituent aux octrois d’autorisation.
- Si un utilisateur est membre du rôle serveur fixe sysadmin, les autorisations ne sont pas vérifiées plus loin, de sorte que les dénis ne seront pas appliqués.
- L’ancien et le nouveau système présentent des similitudes. Par exemple, l’appartenance au rôle serveur fixe
sysadminrevient à posséder l’autorisationCONTROL SERVER. Mais les systèmes ne sont pas identiques. Par exemple, si une connexion ne dispose que de l’autorisationCONTROL SERVERet qu’une procédure stockée vérifie l’appartenance au rôle serveur fixesysadmin, la vérification des autorisations échoue. L’inverse est également vrai.
Résumé
- L’autorisation de niveau serveur peut provenir de l’appartenance aux rôles serveur fixes ou aux rôles serveur définis par l’utilisateur. Tout le monde appartient au rôle serveur fixe
publicet reçoit les autorisations qui y sont attribuées. - Les autorisations de niveau serveur peuvent provenir d’une autorisation attribuée à des connexions ou à des rôles serveur définis par l’utilisateur.
- L’autorisation de niveau base de données peut émaner de l’appartenance aux rôles base de données fixes ou définis par l’utilisateur dans chaque base de données. Tout le monde appartient au rôle base de données fixe
publicet reçoit les autorisations qui y sont attribuées. - Les autorisations de niveau base de données peuvent provenir d’une autorisation attribuée à des utilisateurs ou à des rôles base de données définis par l’utilisateur dans chaque base de données.
- Les autorisations peuvent être reçues à partir de la connexion
guestou de l’utilisateur de base de donnéesguests’ils sont activés. La connexion et les utilisateursguestsont désactivés par défaut. - Les utilisateurs Windows peuvent être des membres de groupes Windows disposant de connexions. SQL Server détecte l’appartenance au groupe Windows lorsqu’un utilisateur Windows se connecte et présente un jeton Windows avec l’identificateur de sécurité d’un groupe Windows. Étant donné que SQL Server ne gère pas ou ne reçoit pas de mises à jour automatiques sur les appartenances aux groupes Windows, SQL Server ne peut pas signaler de manière fiable les autorisations des utilisateurs Windows reçus de l’appartenance à un groupe Windows.
- Les autorisations peuvent être acquises en basculant sur un rôle d’application et en fournissant le mot de passe associé.
- Les autorisations peuvent être acquises en exécutant une procédure stockée qui comprend la clause
EXECUTE AS. - Les autorisations peuvent être acquises par le biais de connexions ou d’utilisateurs dotés de l’autorisation
IMPERSONATE. - Les membres du groupe des administrateurs locaux peuvent toujours élever leurs privilèges à
sysadmin. (Ne s’applique pas à SQL Database.) - Les membres du rôle serveur fixe
securityadminpeuvent élever la plupart de leurs privilèges et dans certains cas les élever jusqu’àsysadmin. (Ne s’applique pas à SQL Database.) - Les administrateurs SQL Server peuvent voir les informations se rapportant à toutes les connexions et à tous les utilisateurs. Les utilisateurs avec moins de privilèges ne voient généralement que les informations relatives à leur propre identité.
Système d’autorisation de rôle fixe plus ancien
Les rôles serveur fixes et les rôles de base de données fixes disposent d’autorisations préconfigurées qui ne peuvent pas être modifiées. Pour déterminer qui est membre d’un rôle serveur fixe, exécutez la requête suivante :
Note
Ne s’applique ni à SQL Database ni à Azure Synapse Analytics, où les autorisations au niveau du serveur ne sont pas disponibles. La is_fixed_role colonne d’a sys.server_principals été ajoutée dans SQL Server 2012 (11.x). Elle n’est pas nécessaire pour les versions antérieures de SQL Server.
SELECT SP1.name AS ServerRoleName,
ISNULL(SP2.name, 'No members') AS LoginName
FROM sys.server_role_members AS SRM
RIGHT JOIN sys.server_principals AS SP1
ON SRM.role_principal_id = SP1.principal_id
LEFT JOIN sys.server_principals AS SP2
ON SRM.member_principal_id = SP2.principal_id
WHERE SP1.is_fixed_role = 1 -- Remove for SQL Server 2008
ORDER BY SP1.name;
Note
Toutes les connexions sont membres du rôle public et il est impossible de les supprimer. La requête vérifie les tables de la master base de données, mais elle peut être exécutée dans n’importe quelle base de données pour le produit local.
Pour déterminer qui est membre d’un rôle base de données fixe, exécutez la requête suivante dans chaque base de données.
SELECT DP1.name AS DatabaseRoleName,
ISNULL(DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.is_fixed_role = 1
ORDER BY DP1.name;
Pour comprendre les autorisations accordées à chaque rôle, consultez les descriptions des rôles dans la documentation en ligne (rôles au niveau du serveur et rôles au niveau de la base de données).
Système d’autorisation granulaire plus récent
Ce système est souple, ce qui signifie qu’il peut être plus complexe si la configuration voulue doit être précise. Pour simplifier les choses, il permet de créer des rôles, d’attribuer des autorisations aux rôles, puis d’ajouter des groupes d’utilisateurs à ces rôles. Et c’est encore plus simple si l’équipe de développement de la bases de données sépare l’activité par schémas, pour accorder ensuite des autorisations de rôle à un schéma entier plutôt qu’à des procédures ou des tables individuelles. Les scénarios réels sont complexes et les besoins de l’entreprise peuvent aboutir à des exigences de sécurité inattendues.
L’image suivante montre les autorisations et leurs relations entre elles. Certaines des autorisations de niveau supérieur (telles que CONTROL SERVER) figurent plusieurs fois. Dans cet article, l’affiche est trop petite pour être lue correctement. Vous pouvez télécharger l’affiche des autorisations du moteur de base de données de taille complète au format PDF.
Classes de sécurité
Les autorisations peuvent être accordées au niveau du serveur, au niveau de la base de données, au niveau du schéma ou au niveau de l’objet, etc. Il existe 26 niveaux (appelés classes). La liste complète des classes dans l’ordre alphabétique est la suivante : APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, AVAILABILITY GROUP, CERTIFICATE, CONTRACT, DATABASE, DATABASESCOPED CREDENTIAL, ENDPOINT, FULLTEXT CATALOG, FULLTEXT STOPLIST, LOGIN, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SEARCH PROPERTY LIST, SERVER, SERVER ROLE, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION. (Certaines classes ne sont pas disponibles sur certains types de SQL Server.) Pour fournir des informations complètes sur chaque classe, vous devez disposer d’une requête différente.
Principaux
Les autorisations sont accordées aux principaux. Ces principaux peuvent être des rôles de serveur, des connexions, des rôles de base de données ou des utilisateurs. Les connexions peuvent représenter des groupes Windows incluant de nombreux utilisateurs Windows. Étant donné que les groupes Windows ne sont pas gérés par SQL Server, SQL Server ne sait pas toujours qui est membre d’un groupe Windows. Lorsqu’un utilisateur Windows se connecte à SQL Server, le paquet de connexion associé contient les jetons de l’appartenance de l’utilisateur au groupe Windows.
Lorsqu’un utilisateur Windows se connecte au moyen d’une connexion de groupe Windows, certaines activités peuvent obliger SQL Server à créer une connexion ou un utilisateur pour représenter l’utilisateur Windows individuel. Par exemple, un groupe Windows (Techniciens) contient les utilisateurs (Mary, Todd, Pat) et ce groupe de techniciens dispose d’un compte d’utilisateur de base de données. Si Mary a l’autorisation et crée une table, vous pouvez créer un utilisateur (Mary) pour qu’il soit propriétaire de la table. Par ailleurs, si une autorisation est refusée à Todd alors qu’elle est détenue par le reste du groupe de techniciens, vous devez créer l’utilisateur Todd afin de suivre le refus d’autorisation.
N’oubliez pas qu’un utilisateur Windows peut être membre de plusieurs groupes Windows (par exemple, ingénieurs et gestionnaires). Qu’elles soient accordées ou refusées à la connexion des techniciens, à celle des responsables, à l’utilisateur individuellement, aux rôles dont l’utilisateur est membre, les autorisations seront toutes agrégées et évaluées pour les autorisations effectives. La fonction HAS_PERMS_BY_NAME permet de savoir si un utilisateur ou une connexion dispose d’une autorisation particulière. Toutefois, il n’existe aucun moyen avéré de déterminer la source de l’octroi ou du refus d’une autorisation. Examinez la liste des autorisations et procédez éventuellement par tâtonnements.
Requêtes utiles
Autorisations du serveur
La requête suivante retourne la liste des autorisations qui ont été accordées ou refusées au niveau serveur. Cette requête doit être exécutée dans la master base de données.
Note
Les autorisations au niveau du serveur ne peuvent être ni accordées ni interrogées sur SQL Database comme sur Azure Synapse Analytics.
SELECT pr.type_desc,
pr.name,
ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.server_principals AS pr
LEFT JOIN sys.server_permissions AS pe
ON pr.principal_id = pe.grantee_principal_id
WHERE is_fixed_role = 0 -- Remove for SQL Server 2008
ORDER BY pr.name,
type_desc;
Autorisations de base de données
La requête suivante retourne la liste des autorisations qui ont été accordées ou refusées au niveau base de données. Vous devez exécuter cette requête dans chaque base de données.
SELECT pr.type_desc,
pr.name,
ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.database_principals AS pr
LEFT JOIN sys.database_permissions AS pe
ON pr.principal_id = pe.grantee_principal_id
WHERE pr.is_fixed_role = 0
ORDER BY pr.name,
type_desc;
Chaque classe d’autorisations de la table d’autorisations peut être jointe à d’autres vues système qui fournissent des informations relatives à cette classe d’éléments sécurisables. Par exemple, la requête suivante fournit le nom de l’objet de base de données qui est concerné par l’autorisation.
SELECT pr.type_desc,
pr.name,
pe.state_desc,
pe.permission_name,
s.name + '.' + oj.name AS OBJECT,
major_id
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe
ON pr.principal_id = pe.grantee_principal_id
INNER JOIN sys.objects AS oj
ON oj.object_id = pe.major_id
INNER JOIN sys.schemas AS s
ON oj.schema_id = s.schema_id
WHERE class_desc = 'OBJECT_OR_COLUMN';
Utilisez la fonction HAS_PERMS_BY_NAME pour déterminer si un utilisateur particulier (dans ce cas TestUser) dispose d’une autorisation. Par exemple :
EXECUTE AS USER = 'TestUser';
SELECT HAS_PERMS_BY_NAME ('dbo.T1', 'OBJECT', 'SELECT');
REVERT;
Pour obtenir des détails sur la syntaxe, consultez HAS_PERMS_BY_NAME.
Étapes suivantes
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour