Роли сервера Базы данных SQL Azure для управления разрешениями

  • Статья

Применимо к:База данных SQL Azure

В этой статье описываются фиксированные роли уровня сервера в База данных SQL Azure.

Примечание.

Фиксированные роли уровня сервера в этой статье доступны в общедоступной предварительной версии для База данных SQL Azure. Эти роли уровня сервера также входят в выпуск SQL Server 2022.

Обзор

В База данных SQL Azure сервер является логическим понятием и разрешениями не могут быть предоставлены на уровне сервера. Чтобы упростить управление разрешениями, База данных SQL Azure предоставляет набор предопределенных ролей на уровне сервера, которые помогут вам управлять разрешениями на логическом сервере. Роли — это субъекты безопасности, группирующие имена входа.

Примечание.

Роли в этой статье похожи на группы в операционной системе Windows.

Эти специальные предопределенные роли уровня сервера используют префикс ##MS_ и суффикс ## для отличия от других обычных субъектов, созданных пользователем.

Как и в случае локального SQL Server, разрешения сервера организованы в иерархической структуре. Разрешения, которые имеют эти роли на уровне сервера, могут распространяться на разрешения для базы данных. Чтобы разрешения были эффективно полезны на уровне базы данных, имя входа должно быть членом роли ##MS_DatabaseConnector##уровня сервера, которая предоставляет CONNECT всем базам данных или учетную запись пользователя в отдельных базах данных. Это также относится к виртуальной базе данных master.

Например, роль ##MS_ServerStateReader## уровня сервера содержит разрешение VIEW SERVER STATE. Если имя входа, являющегося членом этой роли, имеет учетную запись пользователя в базах данных master , а WideWorldImportersэтот пользователь имеет разрешение VIEW DATABASE STATE в этих двух базах данных.

Примечание.

В пользовательских базах данных может быть отменено любое разрешение путем переопределения разрешения, предоставленного сервером через членство в роли. Однако в системном главного серверах базы данных разрешения не могут быть предоставлены или отклонены.

База данных SQL Azure в настоящее время предоставляет семь фиксированных ролей сервера. Разрешения, предоставленные для предопределенных ролей сервера, нельзя изменить, и у этих ролей не может быть других предопределенных ролей в качестве участников. Вы можете добавлять имена входа на уровне сервера в качестве участников ролей на уровне сервера.

Внимание

Каждый член предопределенной роли сервера может добавлять другие имена входа к той же роли.

Дополнительные сведения об именах входа и пользователях Базы данных SQL Azure см. в статье Авторизация доступа к Базе данных SQL, Управляемому экземпляру SQL и Azure Synapse Analytics.

Фиксированные роли уровня сервера

В следующей таблице представлены предопределенные роли уровня сервера и их возможности.

Предопределенная роль уровня сервера Description
##MS_DatabaseConnector## ##MS_DatabaseConnector## Члены предопределенных ролей сервера могут подключаться к любой базе данных без необходимости подключения к учетной записи пользователя в базе данных.

Чтобы запретить CONNECT разрешение определенной базе данных, пользователи могут создать соответствующую учетную запись пользователя для этого имени входа в базе данных, а затем DENYCONNECT разрешение для пользователя базы данных. Это DENY разрешение переопределяет разрешение, полученное GRANT CONNECT из этой роли.
##MS_DatabaseManager## ##MS_DatabaseManager## Члены предопределенных ролей сервера могут создавать и удалять базы данных. Участник ##MS_DatabaseManager## роли, создающей базу данных, становится владельцем этой базы данных, что позволяет пользователю подключаться к этой базе данных как dbo пользователь. Пользователь dbo имеет все разрешения в этой базе данных. ##MS_DatabaseManager## Члены роли не обязательно имеют разрешения на доступ к базам данных, которым они не принадлежат. Эту роль сервера следует использовать над ролью уровня базы данных dbmanager , которая существует в master.
##MS_DefinitionReader## ##MS_DefinitionReader## Члены предопределенных ролей сервера могут считывать все представления каталога, охватываемые VIEW ANY DEFINITIONсоответственно VIEW DEFINITION любой базой данных, в которой член этой роли имеет учетную запись пользователя.
##MS_LoginManager## ##MS_LoginManager## Члены предопределенных ролей сервера могут создавать и удалять имена входа. Эту роль сервера следует использовать над ролью уровня базы данных loginmanager , которая существует в master.
##MS_SecurityDefinitionReader## ##MS_SecurityDefinitionReader## Члены предопределенных ролей сервера могут считывать все представления каталога, охватываемые VIEW ANY SECURITY DEFINITION, и соответственно имеет разрешение на любую базу данных, в которой член этой роли имеет VIEW SECURITY DEFINITION учетную запись пользователя. Это небольшое подмножество того, к чему ##MS_DefinitionReader## имеет доступ роль сервера.
##MS_ServerStateManager## ##MS_ServerStateManager## Члены предопределенных ролей сервера имеют те же разрешения, что ##MS_ServerStateReader## и роль. Кроме того, он содержит разрешение, позволяющее ALTER SERVER STATE получить доступ к нескольким операциям управления, таким как: DBCC FREEPROCCACHE, ; DBCC FREESYSTEMCACHE ('ALL')DBCC SQLPERF()
##MS_ServerStateReader## ##MS_ServerStateReader## Члены предопределенных ролей сервера могут считывать все динамические административные представления и функции, охватываемые VIEW SERVER STATEсоответственноVIEW DATABASE STATE, в любой базе данных, в которой член этой роли имеет учетную запись пользователя.

Разрешения предопределенных ролей сервера

Каждая предопределенная роль уровня сервера имеет определенные разрешения, назначенные ему. В следующей таблице показаны разрешения, назначенные ролям на уровне сервера. В нем также показаны разрешения на уровне базы данных, которые наследуются до тех пор, пока пользователь может подключиться к отдельным базам данных.

Предопределенная роль уровня сервера Разрешения на уровне сервера Разрешения на уровне базы данных (если существуют пользователь базы данных, совпадающий с именем входа)
##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 Н/П
##MS_SecurityDefinitionReader## VIEW ANY SECURITY DEFINITION VIEW SECURITY DEFINITION
##MS_ServerStateManager## ALTER SERVER STATE, , VIEW SERVER STATEVIEW SERVER PERFORMANCE STATEVIEW 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

Разрешения

Только учетная запись администратора сервера или учетная запись администратора Microsoft Entra (которая может быть группой Microsoft Entra) может добавлять или удалять другие имена входа в роли сервера или из них. Это зависит от База данных SQL Azure.

Примечание.

Идентификатор Microsoft Entra ранее был известен как Azure Active Directory (Azure AD).

Работа с ролями уровня сервера

В следующей таблице описаны представления системы и функции, предназначенные для работы с ролями на уровне сервера в Базе данных SQL Azure.

Функция Тип Описание
IS_SRVROLEMEMBER Метаданные Указывает, является ли имя входа SQL членом указанной роли уровня сервера.
sys.server_role_members Метаданные Возвращает одну строку для каждого члена каждой роли уровня сервера.
sys.sql_logins Метаданные Возвращает по одной строке для каждого имени входа SQL.
ALTER SERVER ROLE Команда Изменяет членство роли сервера.

Примеры

Примеры в этом разделе показывают, как работать с ролями на уровне сервера в Базе данных SQL Azure.

А. Добавление имени входа SQL в роль уровня сервера

В следующем примере имя входа Jiao SQL добавляется в роль ##MS_ServerStateReader##уровня сервера. Эту инструкцию необходимо выполнить в виртуальной базе данных master.

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

B. Перечислить все субъекты (проверка подлинности SQL), которые являются членами роли уровня сервера

Следующая инструкция возвращает всех участников любой предопределенной роли на уровне сервера с помощью представлений каталога sys.server_role_members и sys.sql_logins. Эту инструкцию необходимо выполнить в виртуальной базе данных master.

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. Полный пример: добавление имени входа в роль уровня сервера, получение метаданных для членства в роли и разрешений и выполнение тестового запроса

Часть 1. Подготовка членства в роли и учетной записи пользователя

Запустите эту команду из виртуальной базы данных master.

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

Результирующий набор:

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

Запустите эту команду из пользовательской базы данных.

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

Часть 2. Тестирование членства в роли

Войдите в систему с именем входа Jiao и подключитесь к пользовательской базе данных, используемой в примере.

-- 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. Проверка ролей на уровне сервера для имен входа Microsoft Entra

Выполните эту команду в виртуальной master базе данных, чтобы просмотреть все имена входа Microsoft Entra, которые являются частью ролей уровня сервера в База данных SQL. Дополнительные сведения об именах входа сервера Microsoft Entra см. в разделе "Субъекты сервера 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
);

Е. Проверка ролей виртуальной master базы данных для определенных имен входа

Выполните эту команду в виртуальной базе данных master, чтобы проверить назначенные для роли bob, или укажите другое значение для конкретного субъекта.

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%';

Ограничения ролей на уровне сервера

  • Назначения ролей могут занять до 5 минут, чтобы стать эффективными. Кроме того, для существующих сеансов изменения назначений ролей на уровне сервера не вступают в силу, пока подключение не будет закрыто и снова открыто. Это связано с распределенной архитектурой между master базой данных и другими базами данных на том же логическом сервере.

    • Частичное обходное решение. Чтобы сократить период ожидания и убедиться, что назначения ролей сервера находятся в базе данных, администраторе сервера или администраторе Microsoft Entra, могут выполняться DBCC FLUSHAUTHCACHE в пользовательских базах данных, на которых у входа есть доступ. Пользователи, выполнившие вход, все равно должны повторно подключиться после выполнения DBCC FLUSHAUTHCACHE, чтобы изменения членства вступили в силу.

  • IS_SRVROLEMEMBER() не поддерживается в master базе данных.

Связанный контент