Rôles serveur Azure SQL Database pour la gestion des autorisations

  • Article

S’applique à Azure SQL Database

Cet article décrit les rôles fixes au niveau du serveur dans Azure SQL Database.

Remarque

Les rôles fixes au niveau du serveur dans cet article sont en préversion publique pour Azure SQL Database. Ces rôles au niveau du serveur font également partie de la version de SQL Server 2022.

Vue d’ensemble

Dans Azure SQL Database, le serveur est un concept logique, et les autorisations ne peuvent pas être accordées au niveau du serveur. Pour vous aider à gérer les autorisations sur un serveur logique, Azure SQL Database fournit un ensemble de rôles de niveau serveur fixes. Les rôles sont des principaux de sécurité qui regroupent des connexions.

Notes

Dans cet article, le concept de rôle s’apparente aux groupes du système d’exploitation Windows.

Ces rôles de niveau serveur fixes spéciaux utilisent le préfixe ##MS_ et le suffixe ## afin de pouvoir être distingués des principaux standard créés par l’utilisateur.

Comme SQL Server local, les autorisations de serveur sont organisées de façon hiérarchique. Les autorisations détenues par ces rôles au niveau du serveur peuvent se propager aux autorisations de base de données. Pour que les autorisations soient effectives au niveau de la base de données, un compte de connexion doit être membre du rôle au niveau du serveur ##MS_DatabaseConnector##, qui accorde l’autorisation CONNECT à toutes les bases de données, ou avoir un compte d’utilisateur dans les bases de données individuelles. Ceci s’applique aussi aux bases de données master virtuelles.

Par exemple, le rôle ##MS_ServerStateReader## au niveau du serveur contient l’autorisation VIEW SERVER STATE. Si une connexion membre de ce rôle a un compte d’utilisateur dans les bases de données master et WideWorldImporters, cet utilisateur a l’autorisation VIEW DATABASE STATE dans ces deux bases de données.

Remarque

Toute autorisation peut être refusée dans les bases de données utilisateur, ce qui a pour effet de remplacer l’accord à l’échelle du serveur via l’appartenance à un rôle. Toutefois, dans la base de données système master, les autorisations ne peuvent pas être accordées ou refusées.

Azure SQL Database fournit actuellement sept rôles serveur fixes. Les autorisations accordées aux rôles serveur fixes ne peuvent pas être modifiées, et ces rôles ne peuvent pas avoir d’autres rôles fixes en tant que membres. Vous pouvez ajouter des connexions au niveau du serveur en tant que membres à des rôles au niveau du serveur.

Important

Chaque membre d'un rôle serveur fixe peut ajouter des connexions à ce rôle.

Pour plus d’informations sur les connexions et les utilisateurs Azure SQL Database, consultez Configurer SQL Database, SQL Managed Instance et Azure Synapse Analytics pour autoriser l’accès aux bases de données.

Rôles serveur fixes

Le tableau ci-dessous répertorie les rôles serveur fixes et leurs fonctionnalités.

Rôles serveur fixes Description
##MS_DatabaseConnector## Les membres du rôle serveur fixe ##MS_DatabaseConnector## peuvent se connecter à n’importe quelle base de données sans nécessiter de compte d’utilisateur dans la base de données à laquelle se connecter.

Pour refuser l’autorisation CONNECT pour une base de données spécifique, les utilisateurs peuvent créer un compte d’utilisateur correspondant pour cette connexion dans la base de données, puis DENY l’autorisation CONNECT à l’utilisateur de la base de données. Cette autorisation DENY va remplacer l’autorisation GRANT CONNECT provenant de ce rôle.
##MS_DatabaseManager## Les membres du rôle serveur fixe ##MS_DatabaseManager## peuvent créer et supprimer des bases de données. Un membre du rôle ##MS_DatabaseManager## qui crée une base de données en devient le propriétaire, ce qui lui permet de se connecter à cette base de données en tant qu’utilisateur dbo. L’utilisateur dbo a toutes les autorisations de base de données dans la base de données. Les membres du rôle ##MS_DatabaseManager## n’ont pas nécessairement l’autorisation d’accéder aux bases de données qui ne leur appartiennent pas. Il est recommandé d’utiliser ce rôle serveur sur le rôle de niveau base de données dbmanager qui existe dans master.
##MS_DefinitionReader## Les membres du rôle serveur fixe ##MS_DefinitionReader## peuvent lire toutes les vues catalogue qui sont couvertes par VIEW ANY DEFINITION, respectivement VIEW DEFINITION sur toute base de données sur laquelle le membre de ce rôle détient un compte d’utilisateur.
##MS_LoginManager## Les membres du rôle serveur fixe ##MS_LoginManager## peuvent créer et supprimer des connexions. Il est recommandé d’utiliser ce rôle de serveur sur le rôle de niveau base de données loginmanager qui existe dans master.
##MS_SecurityDefinitionReader## Les membres du rôle serveur fixe ##MS_SecurityDefinitionReader## peuvent lire toutes les vues catalogue qui sont couvertes par VIEW ANY SECURITY DEFINITION, et ont respectivement l’autorisation VIEW SECURITY DEFINITION sur les bases de données où le membre de ce rôle a un compte d’utilisateur. C’est un petit sous-ensemble de ce à quoi le rôle serveur ##MS_DefinitionReader## a accès.
##MS_ServerStateManager## Les membres du rôle serveur fixe ##MS_ServerStateManager## ont les mêmes autorisations que celles accordées au rôle ##MS_ServerStateReader##. En outre, ce rôle contient l’autorisation ALTER SERVER STATE, qui permet d’accéder à plusieurs opérations de gestion, telles que DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE ('ALL'), DBCC SQLPERF();
##MS_ServerStateReader## Les membres du rôle serveur fixe ##MS_ServerStateReader## peuvent lire toutes les vues de gestion dynamiques et les fonctions qui sont couvertes par VIEW SERVER STATE, respectivement VIEW DATABASE STATE sur toute base de données sur laquelle le membre de ce rôle détient un compte d’utilisateur.

Autorisations des rôles serveur fixes

Certaines autorisations sont assignées à chaque rôle serveur fixe. Le tableau suivant présente les autorisations attribuées aux rôles de niveau serveur. Il montre aussi les autorisations de niveau base de données, qui sont héritées dès lors que l’utilisateur peut se connecter à des bases de données individuelles.

Rôles serveur fixes Autorisations au niveau du serveur Autorisations au niveau de la base de données (s’il existe un utilisateur de base de données correspondant à la connexion)
##MS_DatabaseConnector## CONNECT ANY DATABASE CONNECT
##MS_DatabaseManager## CREATE ANY DATABASE, ALTER ANY DATABASE ALTER
##MS_DefinitionReader## VIEW ANY DATABASE, , VIEW ANY DEFINITIONVIEW ANY SECURITY DEFINITION VIEW DEFINITION, VIEW SECURITY DEFINITION
##MS_LoginManager## CREATE LOGIN, ALTER ANY LOGIN S/O
##MS_SecurityDefinitionReader## VIEW ANY SECURITY DEFINITION VIEW SECURITY DEFINITION
##MS_ServerStateManager## ALTER SERVER STATE, , VIEW SERVER STATEVIEW SERVER PERFORMANCE STATE, ,VIEW SERVER SECURITY STATE VIEW DATABASE STATE, , VIEW DATABASE PERFORMANCE STATEVIEW DATABASE SECURITY STATE
##MS_ServerStateReader## VIEW SERVER STATE, , VIEW SERVER PERFORMANCE STATEVIEW SERVER SECURITY STATE VIEW DATABASE STATE, , VIEW DATABASE PERFORMANCE STATEVIEW DATABASE SECURITY STATE

Autorisations

Seul le compte administrateur du serveur ou le compte administrateur Microsoft Entra (qui peut être un groupe Microsoft Entra) peut ajouter ou supprimer d’autres connexions vers ou à partir de rôles serveur. Cette remarque est spécifique à Azure SQL Database.

Remarque

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

Utilisation des rôles de niveau serveur

Le tableau ci-dessous explique les vues système et les fonctions permettant d’utiliser les rôles serveur dans Azure SQL Database.

Fonctionnalité Type Description
IS_SRVROLEMEMBER Métadonnées Indique si une connexion SQL est un membre du rôle de niveau serveur spécifié.
sys.server_role_members Métadonnées Retourne une ligne pour chaque membre de chaque rôle serveur.
sys.sql_logins Métadonnées Retourne une ligne pour chaque connexion SQL.
ALTER SERVER ROLE Commande Change l’appartenance d’un rôle serveur.

Exemples

Les exemples de cette section montrent comment utiliser des rôles de niveau serveur dans Azure SQL Database.

A. Ajout d’une connexion SQL à un rôle de niveau serveur

L’exemple suivant ajoute le compte de connexion SQL Jiao au rôle de niveau serveur ##MS_ServerStateReader##. Cette instruction doit être exécutée dans la base de données master virtuelle.

ALTER SERVER ROLE ##MS_ServerStateReader##
    ADD MEMBER Jiao;
GO

B. Répertorier tous les principaux (authentification SQL) qui sont membres d’un rôle au niveau du serveur

L’instruction suivante retourne tous les membres d’un rôle serveur fixe à l’aide des vues catalogue sys.server_role_members et sys.sql_logins. Cette instruction doit être exécutée dans la base de données master virtuelle.

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
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
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

C. Exemple complet : ajout d’une connexion à un rôle au niveau du serveur, récupération des métadonnées pour l’appartenance au rôle et les autorisations, et exécution d’une requête de test

Partie 1 : Préparation de l’appartenance au rôle et du compte d’utilisateur

Exécutez cette commande à partir de la base de données master virtuelle.

ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER Jiao;

-- check membership in metadata:
SELECT IS_SRVROLEMEMBER('##MS_ServerStateReader##', 'Jiao');
--> 1 = Yes

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
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
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

Voici le jeu de résultats obtenu.

MemberPrincipalID MemberPrincipalName RolePrincipalID RolePrincipalName
------------- ------------- ------------------ -----------
6         Jiao      11            ##MS_ServerStateReader##

Exécutez cette commande à partir d’une base de données utilisateur.

-- Create a database-User for 'Jiao'
CREATE USER Jiao
FROM LOGIN Jiao;
GO

Partie 2 : Test de l’appartenance au rôle

Connectez-vous en tant que Jiao et connectez-vous à la base de données utilisateur utilisée dans l’exemple.

-- retrieve server-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'Server');

-- check server-role membership for `##MS_ServerStateReader##` of currently logged on User
SELECT USER_NAME(), IS_SRVROLEMEMBER('##MS_ServerStateReader##');
--> 1 = Yes

-- Does the currently logged in User have the `VIEW DATABASE STATE`-permission?
SELECT HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'VIEW DATABASE STATE');
--> 1 = Yes

-- retrieve database-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');
GO

-- example query:
SELECT * FROM sys.dm_exec_query_stats;
--> will return data since this user has the necessary permission

D. Vérifier les rôles au niveau du serveur pour les connexions Microsoft Entra

Exécutez cette commande dans la base de données master virtuelle pour voir toutes les connexions Microsoft Entra qui font partie des rôles de niveau serveur dans SQL Database. Pour plus d’informations sur les connexions du serveur Microsoft Entra, consultez Principaux du serveur Microsoft Entra.

SELECT member.principal_id AS MemberPrincipalID,
    member.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
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
INNER JOIN sys.server_principals AS member
    ON server_role_members.member_principal_id = member.principal_id
LEFT JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id
WHERE member.principal_id NOT IN (
    -- prevent SQL Logins from interfering with resultset
    SELECT principal_id
    FROM sys.sql_logins AS sql_logins
    WHERE member.principal_id = sql_logins.principal_id
);

E. Vérifier les rôles de la base de données master virtuelle pour des connexions spécifiques

Exécutez cette commande dans la base de données master virtuelle pour vérifier quels rôles a bob, ou modifiez la valeur pour qu’elle corresponde à celle de votre principal.

SELECT DR1.name AS DbRoleName,
    ISNULL(DR2.name, 'No members') AS DbUserName
FROM sys.database_role_members AS DbRMem
RIGHT JOIN sys.database_principals AS DR1
    ON DbRMem.role_principal_id = DR1.principal_id
LEFT JOIN sys.database_principals AS DR2
    ON DbRMem.member_principal_id = DR2.principal_id
WHERE DR1.type = 'R'
    AND DR2.name LIKE 'bob%';

Limitations des rôles au niveau du serveur

  • La prise d’effet des attributions de rôles peut prendre jusqu’à cinq minutes. De plus, pour les sessions existantes, les modifications apportées aux attributions de rôles serveur ne prennent pas effet tant que la connexion n’a pas été fermée puis rouverte. Cela est dû à l’architecture distribuée entre la base de données master et les autres bases de données sur le même serveur logique.

    • Solution de contournement partielle : pour réduire le délai d’attente et être sûr que les attributions de rôle serveur sont à jour dans une base de données, un administrateur de serveur ou un administrateur Microsoft Entra peut exécuter DBCC FLUSHAUTHCACHE dans les bases de données utilisateur auxquelles la connexion a accès. Les utilisateurs actuellement connectés doivent toujours se reconnecter après avoir exécuté DBCC FLUSHAUTHCACHE pour que les modifications d’appartenance prennent effet.

  • IS_SRVROLEMEMBER() n’est pas pris en charge dans la base de données master.

Contenu connexe