Prise en main des autorisations de moteur de base de données

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

Cet article passe en revue certains concepts de sécurité de base, puis décrit une implémentation classique des autorisations. Les autorisations dans le moteur de base de données sont gérées au niveau du serveur par le biais de connexions et de rôles serveur, et au niveau de la base de données via les utilisateurs de base de données et les rôles de base de données.

Sql Database et SQL Database dans Microsoft Fabric fournissent les mêmes options au sein de chaque base de données, mais les autorisations au niveau du serveur ne sont pas disponibles.

• Dans SQL Database, reportez-vous au tutoriel : Sécuriser une base de données dans Azure SQL Database. L’authentification Microsoft Entra ID est recommandée. Pour en savoir plus, consultez Tutoriel : créer des utilisateurs Microsoft Entra à l’aide d’applications Microsoft Entra.

• Dans la base de données SQL dans Microsoft Fabric, la seule méthode d’authentification prise en charge pour les utilisateurs de base de données est Microsoft Entra ID. Les rôles et autorisations au niveau du serveur ne sont pas disponibles. Pour en savoir plus, consultez Autorisation dans la base de données SQL de Microsoft Fabric.

Note

Microsoft Entra ID était précédemment connu sous le nom d’Azure Active Directory (Azure AD).

Principes de sécurité

Un principal de sécurité est l’identité utilisée par SQL Server, à laquelle des permissions peuvent être attribuées pour effectuer des actions. Les principaux de sécurité sont généralement des personnes, ou des groupes de personnes, mais peuvent être d’autres entités qui agissent comme des personnes. Les entités de sécurité peuvent être créées et gérées en utilisant les exemples Transact-SQL présentés dans cet article ou via SQL Server Management Studio.

Logins

Les identifiants sont des comptes d’utilisateur individuels pour accéder au moteur de base de données SQL Server. SQL Server et SQL Database prennent en charge les connexions basées sur l’authentification Windows et les connexions basées sur l’authentification SQL Server. Pour plus d’informations sur les deux types de connexions, consultez Choisir un mode d’authentification.

Rôles serveur fixes

Dans SQL Server, les rôles serveur fixes sont un ensemble de rôles préconfigurés qui fournissent un groupe pratique d’autorisations au niveau du serveur. Les connexions peuvent être ajoutées aux rôles à l’aide de l’instruction ALTER SERVER ROLE ... ADD MEMBER . Pour plus d’informations, consultez ALTER SERVER ROLE. SQL Database ne prend pas en charge les rôles serveur fixes, mais possède deux rôles dans la master base de données (dbmanager et loginmanager) qui agissent comme des rôles serveur.

Rôles de serveur définis par l’utilisateur

Dans SQL Server, vous pouvez créer vos propres rôles serveur et leur attribuer des autorisations au niveau du serveur. Les connexions peuvent être ajoutées aux rôles de serveur à l’aide de l’instruction ALTER SERVER ROLE ... ADD MEMBER . Pour plus d’informations, consultez ALTER SERVER ROLE. SQL Database ne prend pas en charge les rôles serveur définis par l’utilisateur.

Utilisateurs de base de données

Pour accorder l’accès pour une connexion à une base de données, vous créez un utilisateur de base de données dans cette base de données et mappez l’utilisateur de la base de données à une connexion. Le nom d’utilisateur de la base de données est généralement identique au nom de connexion par convention, même s’il n’est pas obligé d’être identique. Chaque utilisateur de base de données est mappé à une seule connexion. Une connexion peut être mappée à un seul utilisateur d’une base de données, mais elle peut être mappée en tant qu’utilisateur de base de données dans plusieurs bases de données différentes.

En outre, les utilisateurs de base de données peuvent être créés sans avoir de connexion correspondante. Ces utilisateurs sont appelés utilisateurs de base de données autonome. Microsoft encourage l’utilisation d’utilisateurs de base de données autonome, car il facilite le déplacement de votre base de données vers un autre serveur. Comme une connexion, un utilisateur de base de données autonome peut utiliser l’authentification Windows ou l’authentification SQL Server. Pour plus d’informations, voir Rendre votre base de données portable en utilisation des bases de données autonomes.

Il existe 12 types d’utilisateurs, qui varient légèrement selon la façon dont ils s’authentifient et qui ils représentent. Pour afficher la liste des utilisateurs, consultez CREATE USER.

Rôles de base de données fixes

Les rôles de base de données fixes sont un ensemble de rôles préconfigurés qui fournissent un groupe pratique d’autorisations au niveau de la base de données. Les utilisateurs de base de données et les rôles de base de données définis par l’utilisateur peuvent être ajoutés aux rôles de base de données fixes à l’aide de l’instruction ALTER ROLE ... ADD MEMBER. Pour plus d’informations, consultez ALTER ROLE.

Rôles de base de données définis par l’utilisateur

Les utilisateurs disposant de l’autorisation CREATE ROLE peuvent créer des rôles de base de données définis par l’utilisateur pour représenter des groupes d’utilisateurs disposant d’autorisations courantes. En général, les autorisations sont accordées ou refusées à l’ensemble du rôle, ce qui simplifie la gestion et la surveillance des autorisations. Les utilisateurs de base de données peuvent être ajoutés aux rôles de base de données à l’aide de l’instruction ALTER ROLE ... ADD MEMBER . Pour plus d’informations, consultez ALTER ROLE.

Autres intervenants principaux

Les autres principaux de sécurité non abordés ici incluent les rôles d’application, les connexions et les utilisateurs basés sur des certificats ou des clés asymétriques.

Pour obtenir un graphique montrant les relations entre les utilisateurs Windows, les groupes Windows, les connexions et les utilisateurs de base de données, consultez Créer un utilisateur de base de données.

Scénario typique

L’exemple suivant illustre une méthode courante et recommandée pour configurer des autorisations.

Dans Windows Active Directory ou Microsoft Entra ID

1. Créez un utilisateur pour chaque personne.
2. Créez des groupes Windows qui représentent les unités de travail et les fonctions de travail.
3. Ajoutez les utilisateurs Windows aux groupes Windows.

Si l’utilisateur se connecte à de nombreuses bases de données

1. Créez une connexion pour les groupes Windows. (Si vous utilisez l’authentification SQL Server, ignorez les étapes d’Active Directory et créez des connexions d’authentification SQL Server ici.)

2. Dans la base de données utilisateur, créez un utilisateur de base de données pour la connexion représentant les groupes Windows.

3. Dans la base de données utilisateur, créez un ou plusieurs rôles de base de données définis par l’utilisateur, chacun représentant une fonction similaire. Par exemple, vous pouvez avoir un rôle d’analyste financier et un rôle d’analyste commercial.

4. Ajoutez les utilisateurs de base de données à un ou plusieurs rôles de base de données définis par l’utilisateur.

5. Accordez des autorisations aux rôles de base de données définis par l’utilisateur.

Si l’utilisateur se connecte à une seule base de données

1. Dans la base de données utilisateur, créez un utilisateur de base de données autonome pour le groupe Windows. (Si vous utilisez l’authentification SQL Server, ignorez les étapes d’Active Directory et créez un utilisateur de base de données autonome basé sur l’authentification SQL Server ici.)

2. Dans la base de données utilisateur, créez un ou plusieurs rôles de base de données définis par l’utilisateur, chacun représentant une fonction similaire. Par exemple, vous pouvez avoir un rôle d’analyste financier et un rôle d’analyste commercial.

3. Ajoutez les utilisateurs de base de données à un ou plusieurs rôles de base de données définis par l’utilisateur.

4. Accordez des autorisations aux rôles de base de données définis par l’utilisateur.

En général, à ce stade, un utilisateur Windows est devenu membre d’un groupe Windows. Le groupe Windows dispose d’une connexion dans SQL Server ou SQL Database. La connexion est mappée à une identité d’utilisateur dans la base de données utilisateur. L’utilisateur est membre d’un rôle de base de données. Vous devez maintenant ajouter des autorisations au rôle.

Attribuer des autorisations

La plupart des instructions d’autorisation ont le format suivant :

<authorization> <permission> ON <securable>::<name> TO <principal>;

• <authorization> doit être GRANT, REVOKEou DENY.

• L'action que vous autorisez ou interdisez est déterminée par <permission>. Le nombre exact d’autorisations diffère entre SQL Server et Azure SQL Database. Pour plus d’informations sur les autorisations, consultez Autorisations (moteur de base de données) et reportez-vous au graphique plus loin dans cet article.

• ON <securable>::<name> est le type d’élément sécurisable (serveur, objet serveur, base de données ou objet de base de données) et son nom. Certaines autorisations ne nécessitent <securable>::<name> pas, car elles ne sont pas ambiguës ou inappropriées dans le contexte. Par exemple, l’autorisation CREATE TABLE ne nécessite pas la <securable>::<name> clause. (GRANT CREATE TABLE TO Mary; autorise Mary à créer des tables.)

• <principal> est le principal de sécurité (connexion, utilisateur ou rôle) qui reçoit ou perd l’autorisation. Accordez des autorisations aux rôles chaque fois que possible.

L’exemple d’instruction suivant accorde l’autorisation UPDATE sur la table ou la vue Parts contenue dans le schéma Production au rôle nommé PartsTeam.

GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam;

L’exemple d’instruction suivant accorde l’autorisation UPDATE , sur le Production schéma et par extension sur n’importe quelle table ou vue contenue dans ce schéma, au rôle nommé ProductionTeam, qui est une approche plus efficace et salable pour attribuer des autorisations qu’au niveau de l’objet individuel :

GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;

Les autorisations sont accordées aux principaux de sécurité (connexions, utilisateurs et rôles) à l’aide de l’instruction GRANT . Les autorisations sont refusées explicitement à l’aide de la commande DENY. Une autorisation précédemment accordée ou refusée est supprimée à l’aide de l’instruction REVOKE . Les autorisations sont cumulatives : l’utilisateur bénéficie de toutes les autorisations accordées à lui-même, à la connexion et à toute appartenance à un groupe. Toutefois, tout refus d’autorisation remplace toutes les attributions.

Caution

Une erreur courante consiste à tenter de supprimer un GRANT à l’aide de DENY au lieu de REVOKE. Cela peut entraîner des problèmes lorsqu’un utilisateur reçoit des autorisations de plusieurs sources, ce qui peut être un scénario courant. L’exemple suivant illustre le principe.

Le groupe des Ventes reçoit des autorisations SELECT sur la table OrderStatus via l’instruction GRANT SELECT ON OBJECT::OrderStatus TO Sales;. L’utilisateur Jae est membre du Sales rôle. Jae a également reçu SELECT l’autorisation pour la table OrderStatus sous son propre nom d’utilisateur au moyen de l’instruction GRANT SELECT ON OBJECT::OrderStatus TO Jae;. Prétendez que l’administrateur souhaite supprimer le rôle GRANT dans le rôle Sales.

• Si l’administrateur exécute correctement REVOKE SELECT ON OBJECT::OrderStatus TO Sales;, Jae conserve l’accès SELECT à la table OrderStatus au moyen de son instruction GRANT individuelle.

• Si l’administrateur exécute incorrectement DENY SELECT ON OBJECT::OrderStatus TO Sales;, Jae, en tant que membre du rôle Sales, se voit refuser l’autorisation SELECT, car le DENY vers le Sales remplace son GRANT individuel.

Note

Les autorisations peuvent être configurées à l’aide de Management Studio. Recherchez l’élément sécurisable dans l’Explorateur d’objets, cliquez dessus avec le bouton droit, puis cliquez sur Propriétés. Sélectionnez la page Autorisations . Pour plus d’aide sur l’utilisation de la page des autorisations, consultez Permissions or Securables Page.

Hiérarchie des autorisations

Les autorisations ont une hiérarchie parent/enfant. Autrement dit, si vous accordez l’autorisation SELECT sur une base de données, cette autorisation inclut l’autorisation SELECT sur tous les schémas (enfants) dans la base de données. Si vous accordez l’autorisation SELECT sur un schéma, elle inclut l’autorisation SELECT sur toutes les tables et vues (enfants) dans le schéma. Les autorisations sont transitives : si vous accordez l’autorisation SELECT sur une base de données, elle inclut l’autorisation SELECT sur tous les schémas (enfants) et toutes les tables et vues (petits-enfants).

En outre, les autorisations ont des autorisations couvrantes. L’autorisation CONTROL sur un objet vous donne normalement toutes les autres autorisations sur l’objet.

Étant donné que la hiérarchie parent/enfant et la hiérarchie de couverture peuvent agir sur la même autorisation, le système d’autorisation peut se compliquer. Par exemple, prenons une table (Region), dans un schéma (Customers), dans une base de données (SalesDB).

• CONTROL l’autorisation sur la table Region inclut toutes les autres autorisations sur la table Region, notamment ALTER, SELECT, INSERT, UPDATE, DELETE, et d'autres autorisations.

• SELECT sur le schéma Customers qui possède la table Region inclut l’autorisation SELECT sur la table Region.

L’autorisation SELECT sur la Region table peut donc être obtenue via l’une des six instructions suivantes :

GRANT SELECT ON OBJECT::Region TO Jae;

GRANT CONTROL ON OBJECT::Region TO Jae;

GRANT SELECT ON SCHEMA::Customers TO Jae;

GRANT CONTROL ON SCHEMA::Customers TO Jae;

GRANT SELECT ON DATABASE::SalesDB TO Jae;

GRANT CONTROL ON DATABASE::SalesDB TO Jae;

Accorder l’autorisation minimale

La première autorisation répertoriée précédemment (GRANT SELECT ON OBJECT::Region TO Jae;) est la plus granulaire. Cette instruction est l’autorisation minimale possible qui accorde le SELECT. Aucune autorisation sur des objets subordonnés ne l’accompagne. C’est un bon principe d’accorder toujours le moins d’autorisations possible, mais vous devez envisager d’accorder à des niveaux supérieurs, afin de simplifier le système d’octroi.

Par conséquent, si Jae a besoin d’une autorisation pour l’ensemble du schéma, accordez SELECT une seule fois au niveau du schéma, au lieu d’accorder SELECT au niveau de la table ou de la vue plusieurs fois. La conception de la base de données peut affecter considérablement la réussite de cette stratégie. Cette stratégie fonctionne mieux lorsque votre base de données est conçue afin que les objets nécessitant des autorisations identiques soient inclus dans un schéma unique.

Tip

Lorsque vous concevez une base de données et ses objets, planifiez à partir du début comment les applications et les utilisateurs accèdent à ces objets. Utilisez ces informations pour contrôler l’accès aux tables, vues, fonctions et procédures stockées à l’aide de schémas. Les schémas vous permettent de regrouper plus facilement les types d’accès.

Schéma des autorisations

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 le poster des autorisations de moteur de base de données en taille réelle au format PDF.

Pour obtenir un graphique montrant les relations entre les principaux de moteur de base de données et les objets serveur et de base de données, consultez Hiérarchie des autorisations (moteur de base de données).

Autorisations associées aux rôles serveur fixes et rôles de base de données fixes

Les autorisations des rôles serveur fixes et des rôles de base de données fixe sont similaires, mais pas exactement identiques aux autorisations granulaires. Par exemple, les membres du rôle serveur fixe sysadmin disposent de toutes les autorisations sur l’instance de SQL Server, comme les connexions avec l’autorisation CONTROL SERVER .

Toutefois, l’octroi de l’autorisation CONTROL SERVER ne fait pas de connexion un membre du rôle serveur fixe sysadmin et l’ajout d’une connexion au rôle serveur fixe sysadmin n’accorde pas explicitement l’autorisation CONTROL SERVER de connexion. Parfois, une procédure stockée vérifie les autorisations en vérifiant le rôle fixe et en ne vérifiant pas l’autorisation granulaire.

Par exemple, le détachement d’une base de données nécessite l’appartenance au rôle de base de données fixe db_owner . L’autorisation CONTROL DATABASE équivalente n’est pas suffisante. Ces deux systèmes fonctionnent en parallèle, mais interagissent rarement. Microsoft recommande d’utiliser le système d’autorisation plus récent et granulaire au lieu de rôles fixes dans la mesure du possible.

Surveiller les autorisations

Les vues suivantes retournent des informations de sécurité. Pour toutes les vues liées à la sécurité, consultez les vues du catalogue de sécurité (Transact-SQL).

View	Description
sys.server_principals 1	Connexions et rôles serveur définis par l’utilisateur sur un serveur
sys.database_principals	Utilisateurs et rôles définis par l’utilisateur dans une base de données
sys.server_permissions 1	Autorisations accordées aux connexions et aux rôles serveur fixes définis par l’utilisateur
sys.database_permissions	Autorisations accordées aux utilisateurs et aux rôles de base de données fixes définis par l’utilisateur
sys.database_role_members	Appartenance au rôle de base de données
sys.server_role_members 1	L'appartenance au rôle de serveur

¹ Cette vue n’est pas disponible dans SQL Database.

Examples

Les instructions suivantes retournent des informations utiles sur les autorisations.

A. Liste des autorisations de base de données pour chaque utilisateur

Pour renvoyer les autorisations explicites accordées ou refusées dans une base de données (SQL Server et SQL Database), exécutez l’instruction Transact-SQL suivante dans la base de données.

SELECT perms.state_desc AS State,
       permission_name AS [Permission],
       obj.name AS [on Object],
       dp.name AS [to User Name]
FROM sys.database_permissions AS perms
     INNER JOIN sys.database_principals AS dp
         ON perms.grantee_principal_id = dp.principal_id
     INNER JOIN sys.objects AS obj
         ON perms.major_id = obj.object_id;

B. Liste des membres du rôle serveur

Pour renvoyer les membres des rôles serveur (SQL Server uniquement), exécutez l’instruction suivante.

SELECT roles.principal_id AS RolePrincipalID,
       roles.name AS RolePrincipalName,
       server_role_members.member_principal_id AS MemberPrincipalID,
       members.name AS MemberPrincipalName
FROM sys.server_role_members AS server_role_members
     INNER JOIN sys.server_principals AS roles
         ON server_role_members.role_principal_id = roles.principal_id
     LEFT OUTER JOIN sys.server_principals AS members
         ON server_role_members.member_principal_id = members.principal_id;

C. Répertorier tous les principaux de base de données membres d’un rôle au niveau de la base de données

Pour renvoyer les membres des rôles de base de données (SQL Server et SQL Database), exécutez l’instruction suivante dans la base de données.

SELECT dRole.name AS [Database Role Name],
       dp.name AS [Members]
FROM sys.database_role_members AS dRo
     INNER JOIN sys.database_principals AS dp
         ON dRo.member_principal_id = dp.principal_id
     INNER JOIN sys.database_principals AS dRole
         ON dRo.role_principal_id = dRole.principal_id;

Contenu connexe

• Sécurité pour le moteur de base de données SQL Server et la base de données Azure SQL
• Fonctions de sécurité (Transact-SQL)
• Fonctions et vues de gestion dynamique relatives à la sécurité (Transact-SQL)
• Vues de catalogue de sécurité (Transact-SQL)
• sys.fn_builtin_permissions (Transact-SQL)
• Déterminer les autorisations effectives du moteur de base de données
• Tutoriel : prise en main du moteur de base de données
• Leçon 1 : créer et interroger des objets de base de données
• Tutoriel : SQL Server Management Studio
• Tutoriel : Écrire des instructions Transact-SQL