Démarrage avec les autorisations du moteur de base de données

Article
03/16/2024

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Les autorisations dans le moteur de base de données sont gérées au niveau du serveur, par le biais des connexions et des rôles serveur, et au niveau de la base de données, par le biais des utilisateurs de base de données et des rôles de base de données. Le modèle pour SQL Database expose le même système dans chaque base de données, mais les autorisations de niveau serveur ne sont pas disponibles. Cet article passe en revue les concepts de sécurité de base, puis décrit une implémentation classique des autorisations.

Remarque

Microsoft Entra ID était anciennement appelé Azure Active Directory (Azure AD).

Principaux de sécurité

Le principal de sécurité est le nom officiel des identités qui utilisent SQL Server et qui peuvent être autorisées à prendre des mesures. Ce sont généralement des personnes ou des groupes de personnes, mais il peut également s’agir d’entités qui se font passer pour des personnes. Les principaux de sécurité peuvent être créés et gérés à l'aide des instructions Transact-SQL répertoriées ou de SQL Server Management Studio.

Connexions

Les connexions sont des comptes d'utilisateur qui permettent d'ouvrir une session sur le moteur de base de données SQL Server. SQL Server et SQL Database prennent en charge les connexions basées sur des Authentification Windows et des connexions basées sur l'authentification SQL Server. Pour plus d’informations sur les deux types de connexions, consultez Choose an Authentication Mode.

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 de niveau 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 (Transact-SQL). SQL Database ne prend pas en charge les rôles serveur fixes, mais a deux rôles dans la base de données master (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 de niveau 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 (Transact-SQL). SQL Database ne prend pas en charge les rôles serveur définis par l'utilisateur.

Utilisateurs de base de données

Pour qu'une connexion puisse accéder à une base de données, un utilisateur de base de données doit être créé dans une base de données, puis mappé à la connexion. En général, le nom d'utilisateur de base de données est le même que le nom de connexion, mais ce n'est pas obligatoire. Chaque utilisateur de base de données est mappé à une seule connexion. Une connexion ne peut être mappée qu’à un seul utilisateur dans une base de données, mais peut être mappée comme utilisateur de base de données dans plusieurs bases de données.

En outre, les utilisateurs de base de données peuvent être créés sans avoir de connexion correspondante. Ceux-ci sont appelés utilisateurs de base de données autonome. Microsoft encourage l'utilisation de ces utilisateurs de base de données autonome, car cela 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, consultez Utilisateurs de base de données autonome - Rendre votre base de données portable.

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 voir une liste d'utilisateurs, consultez CREATE USER (Transact-SQL).

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 de niveau 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 (Transact-SQL).

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

Les utilisateurs ayant 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 (Transact-SQL).

Autres principaux

Des principaux de sécurité supplémentaires non présentés ici incluent les rôles d’application, ainsi que 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 Create a Database User.

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

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

Si la personne qui se connecte doit se connecter à plusieurs bases de données

Créez une connexion pour les groupes Windows. (Si vous utilisez l'authentification SQL Server, ignorez les étapes d'Active Directory et créez ici des connexions d'authentification SQL Server.)
Dans la base de données utilisateur, créez un utilisateur de base de données pour la connexion représentant les groupes Windows.
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, analyste financier et analyste des ventes.
Ajoutez les utilisateurs de base de données à un ou plusieurs rôles de base de données définis par l’utilisateur.
Accordez des autorisations aux rôles de base de données définis par l’utilisateur.

Si la personne qui se connecte doit se connecter à une seule base de données

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 ici une authentification SQL Server d'utilisateur de base de données autonome.)
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, analyste financier et analyste des ventes.
Ajoutez les utilisateurs de base de données à un ou plusieurs rôles de base de données définis par l’utilisateur.
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, REVOKE ou DENY.
PERMISSION établit l’action autorisée ou interdite. Le nombre exact d'autorisations diffère entre SQL Server et SQL Database. Les autorisations sont répertoriées dans l'article Autorisations (moteur de base de données) et dans le graphique ci-dessous.
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 n'exigent pas ON SECURABLE::NAME, car le contexte ne le justifie pas. Par exemple, l'autorisation CREATE TABLE ne nécessite pas la clause ON SECURABLE::NAME (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.

Dans l'exemple d'instruction grant suivante, l'autorisation UPDATE est accordée sur la table ou 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 grant suivant accorde l'autorisation UPDATE sur le schéma Production, et par extension sur n'importe quelle table ou vue contenue dans ce schéma au rôle nommé ProductionTeam, ce qui est une approche plus efficace et justifiable pour l'attribution d'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.

Conseil

Une erreur courante consiste à tenter de supprimer un GRANT à l’aide de DENY au lieu de REVOKE. Cela peut engendrer des problèmes quand un utilisateur reçoit des autorisations de plusieurs sources, ce qui est assez courant. L’exemple suivant illustre le principal.

Le groupe Sales reçoit des autorisations SELECT sur la table OrderStatus par le biais de l’instruction GRANT SELECT ON OBJECT::OrderStatus TO Sales;. L'utilisateur Jae est membre du rôle Sales. Jae se voit également accorder l'autorisation SELECT sur la table OrderStatus sous son propre nom d'utilisateur par le biais de l'instruction GRANT SELECT ON OBJECT::OrderStatus TO Jae;. Supposons que l’administrateur souhaite supprimer le GRANT associé au 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 par le biais de son instruction GRANT individuelle.
Par contre, si l'administrateur exécute DENY SELECT ON OBJECT::OrderStatus TO Sales; incorrectement, Jae, en tant que membre du rôle Sales, se voit refuser l'autorisation SELECT, car l'instruction DENY associée à Sales remplace son instruction GRANT individuelle.

Remarque

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 sélectionnez 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 d'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 (enfant) 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 sur la table Region inclut toutes les autres autorisations sur la table Region, y compris ALTER, SELECT, INSERT, UPDATE, DELETE, et certaines autres autorisations.
SELECT sur le schéma Customers qui possède la table Region inclut l’autorisation SELECT sur la table Region.

Ainsi, l’autorisation SELECT sur la table Region peut être obtenue par le biais de ces six instructions :

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;

Octroyer l'autorisation minimale

La première autorisation répertoriée ci-dessus (GRANT SELECT ON OBJECT::Region TO Jae;) est la plus granulaire ; autrement dit, cette instruction est l’autorisation minimale la plus stricte pour accorder l’autorisation SELECT. Aucune autorisation sur des objets subordonnés ne l’accompagne. C'est un bon principe de toujours accorder le moins d'autorisations possible (vous pouvez en apprendre plus sur le principe du moindre privilège), tout en essayant (ce qui peut sembler contradictoire) de les accorder à des niveaux plus élevés afin de simplifier le système d'octroi. Ainsi, si Jae a besoin d'autorisations pour l'ensemble du schéma, accordez SELECT une fois au niveau du schéma, au lieu d'accorder SELECT plusieurs fois au niveau de la table ou de la vue. La conception de la base de données peut affecter considérablement la réussite de cette stratégie. Cette dernière fonctionne de façon optimale si votre base de données permet d’inclure dans un seul schéma les objets nécessitant des autorisations identiques.

Conseil

Lors de la conception d'une base de données et de ses objets, dès le début, planifiez qui ou quelles applications accèderont à quels objets et en fonction de cela, placez les objets, à savoir des tables mais aussi des vues, des fonctions et des procédures stockées dans des schémas en fonction des compartiments de type d'accès autant que possible.

Diagramme 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 du moteur de base de données au format PDF.

A screenshot from the Database Engine permissions PDF.

Pour obtenir un graphique montrant les relations entre les principaux 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 vs. rôles serveur et de base de données fixes

Les autorisations des rôles serveur fixes et des rôles de base de données fixes sont similaires, mais ne sont pas exactement les mêmes que les autorisations granulaires. Par exemple, les membres du rôle serveur fixe sysadmin disposent de toutes les autorisations sur l'instance de SQL Server, de même que les connexions avec l'autorisation CONTROL SERVER. Toutefois, l'octroi de l'autorisation CONTROL SERVER ne fait pas d'une connexion un membre du rôle serveur fixe sysadmin et l'ajout d'une connexion au rôle serveur fixe sysadmin n'octroie pas explicitement à celle-ci l'autorisation CONTROL SERVER. Parfois, une procédure stockée vérifie les autorisations en examinant le rôle fixe et pas l’autorisation granulaire. Par exemple, le détachement d'une base de données requiert 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 granulaire plus récent au lieu des rôles fixes chaque fois que possible.

Surveillance des autorisations

Les vues suivantes retournent des informations de sécurité.

Vous pouvez examiner les connexions et les rôles serveur définis par l’utilisateur sur un serveur à l’aide de la vue sys.server_principals . Cette option n'est pas disponible dans SQL Database.
Vous pouvez examiner les utilisateurs et les rôles définis par l’utilisateur dans une base de données à l’aide de la vue sys.database_principals .
Vous pouvez examiner les autorisations accordées aux connexions et aux rôles serveur fixes définis par l’utilisateur à l’aide de la vue sys.server_permissions . Cette option n'est pas disponible dans SQL Database.
Vous pouvez examiner les autorisations accordées aux utilisateurs et aux rôles de base de données fixes définis par l’utilisateur à l’aide de la vue sys.database_permissions .
L’appartenance au rôle de base de données peut être examinée à l’aide de la vue sys.database_role_members .
L’appartenance au rôle serveur peut être examinée à l’aide de la vue sys.server_role_members . Cette option n'est pas disponible dans SQL Database.
Pour obtenir des vues supplémentaires associées à la sécurité, consultez Affichages catalogue de sécurité (Transact-SQL).

Exemples

Les instructions suivantes retournent des informations utiles sur les autorisations.

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

Pour retourner les autorisations explicites accordées ou refusées dans une base de données (SQL Server et SQL Database), exécutez l'instruction 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
JOIN sys.database_principals AS dp
    ON perms.grantee_principal_id = dp.principal_id
JOIN sys.objects AS obj
    ON perms.major_id = obj.object_id;

B. Liste des membres du rôle serveur

Pour retourner 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 JOIN sys.server_principals AS members
    ON server_role_members.member_principal_id = members.principal_id;

C. Liste tous les principaux de base de données membres d'un rôle au niveau de la base de données

Pour retourner 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
JOIN sys.database_principals AS dp
    ON dRo.member_principal_id = dp.principal_id
JOIN sys.database_principals AS dRole
    ON dRo.role_principal_id = dRole.principal_id;