Déterminer des autorisations de moteur de base de données effectives

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Base de données SQL dans Microsoft Fabric

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. Depuis SQL Server 2005 (9.x), un système plus flexible et plus précis est disponible.

Gardez toujours à 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, et les refus d’accès ne sont donc pas appliqués.
• L’ancien et le nouveau système présentent des similitudes. Par exemple, l’appartenance au rôle serveur fixe sysadmin revient à posséder l’autorisation CONTROL SERVER. Les systèmes, cependant, ne sont pas identiques. Par exemple, si une connexion ne dispose que de l’autorisation CONTROL SERVER et qu’une procédure stockée vérifie l’appartenance au rôle serveur fixe sysadmin, la vérification des autorisations échoue. L’inverse est également vrai.
• Dans la base de données SQL Fabric, l’ID Microsoft Entra pour les utilisateurs de base de données est la seule méthode d’authentification prise en charge. Les rôles et autorisations au niveau du serveur ne sont pas disponibles, mais uniquement au niveau de la base de données. Pour plus d’informations, consultez Autorisation dans la base de données SQL dans Microsoft Fabric.

Summary

• 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 public et 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 public et 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 accordées par le compte de connexion guest ou l’utilisateur de base de données guest, s’il est activé. La guest connexion et les comptes utilisateur sont 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. Du fait que SQL Server ne gère pas et ne reçoit pas les mises à jour automatiques relatives aux appartenances de groupes Windows, il ne peut pas signaler de manière fiable les autorisations des utilisateurs Windows qui ont été reçues de l’appartenance au 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 securityadmin peuvent é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é.

Ancien système d’autorisations de rôle fixe

Il est impossible de modifier les rôles serveur et base de données fixes qui disposent d’autorisations pré-configurées. Pour déterminer qui est membre d’un rôle serveur fixe, exécutez la requête suivante :

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;

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 au niveau du serveur et les rôles au niveau de la base de données.

Système d’autorisations 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 métier peuvent créer des exigences de sécurité inattendues.

L'image suivante illustre les autorisations et leurs relations. 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 en taille réelle au format PDF.

Classes de sécurité

Vous pouvez accorder des autorisations au niveau serveur, base de données, schéma, 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.) Une requête différente est nécessaire pour fournir des informations complètes sur chaque classe.

Principals

Des 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. Comme la plateforme SQL Server ne tient pas à jour les groupes Windows, elle 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 à l’aide d’une connexion basée sur un groupe Windows, certaines activités peuvent nécessiter des SQL Server pour 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. Ou bien, si une autorisation est refusée à Todd alors que le reste du groupe des ingénieurs en dispose, vous devez créer l’utilisateur Todd pour suivre ce refus d’autorisation.

N’oubliez pas qu’un utilisateur Windows peut être membre de plusieurs groupes Windows (par exemple, le groupe des techniciens et celui des responsables). Les autorisations accordées ou refusées à la connexion Engineers, à la connexion Managers, accordées ou refusées individuellement à l’utilisateur, ainsi qu’accordées ou refusées aux rôles auxquels l’utilisateur appartient, seront toutes agrégées et évaluées afin de déterminer 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. Exécutez cette requête dans la master 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.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. Exécutez 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’autorisation du tableau des autorisations peut être associée à d’autres vues système qui fournissent des informations associées sur cette classe de sécurisable. 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.

Contenu connexe

• Prise en main des autorisations du moteur de base de données
• Didacticiel : Mise en route du moteur de base de données