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

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

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

Обзор

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

Эти специальные предопределенные роли уровня сервера используют префикс ##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.

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

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

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

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

Разрешения

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

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

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

Примеры

Примеры в этом разделе показывают, как работать с ролями на уровне сервера в Базе данных 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 базе данных.

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

• Роли уровня базы данных
• Представления каталога безопасности (Transact-SQL)
• Функция безопасности (Transact-SQL)
• Разрешения (ядро СУБД)
• DBCC FLUSHAUTHCACHE (Transact-SQL)