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

  • Статья
  • Чтение занимает 8 мин

Примечание

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

Область применения: База данных SQL Azure

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

Примечание

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

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

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

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

Примечание

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

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

Важно!

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

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

Предопределенных ролей на уровне сервера

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

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

Чтобы запретить разрешение CONNECT для определенной базы данных, пользователи могут создать соответствующую учетную запись пользователя для этого имени входа в базе данных, а затем ЗАПРЕТИТЬ разрешение CONNECT для пользователя базы данных. Это разрешение 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_DatabaseManager## могут создавать и удалять базы данных. Рекомендуется использовать эту роль сервера для роли уровня базы данных loginmanager, которая существует в master.
##MS_SecurityDefinitionReader## Участники предопределенной роли сервера ##MS_SecurityDefinitionReader## могут просматривать все представления каталога, доступ к которым предоставляется разрешением VIEW ANY SECURITY DEFINITION (Просмотр любого определения безопасности) и, соответственно, имеет разрешение VIEW SECURITY DEFINITION (Просмотр определения безопасности) для любой базы данных, в которой у участника этой роли есть учетная запись пользователя. Это небольшое подмножество того, к чему имеет доступ роль сервера ##MS_DefinitionReader##.
##MS_ServerStateReader## Участники предопределенной роли сервера ##MS_ServerStateReader## могут просматривать все динамические административные представления и функции, доступ к которым предоставляется разрешением VIEW SERVER STATE (Просмотр состояния сервера), соответственно VIEW DATABASE STATE (Просмотр состояния базы данных) для любой базы данных, в которой у участника этой роли есть учетная запись пользователя.
##MS_ServerStateManager## У участников предопределенной роли сервера ##MS_ServerStateManager## есть те же разрешения, что и у роли ##MS_ServerStateReader##. Кроме того, в ней есть разрешение ALTER SERVER STATE (Изменение состояния сервера), которое обеспечивает доступ к нескольким операциям управления, таким как DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE ('ALL'), DBCC SQLPERF().

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

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

Предопределенная роль уровня сервера Разрешения уровня серверов Разрешения на уровне базы данных (если существуют пользователь базы данных, совпадающий с именем входа)
##MS_DatabaseConnector## CONNECT ANY DATABASE CONNECT
##MS_DatabaseManager## CREATE ANY DATABASE
ALTER ANY DATABASE		 ALTER
##MS_DefinitionReader## VIEW ANY DATABASE (Просмотр любой базы данных), VIEW ANY DEFINITION (Просмотр любого определения), VIEW ANY SECURITY DEFINITION (Просмотр любого определения безопасности) VIEW DEFINITION (Просмотр определения), VIEW SECURITY DEFINITION (Просмотр определения безопасности)
##MS_LoginManager## CREATE LOGIN
ALTER ANY LOGIN		 Н/Д
##MS_SecurityDefinitionReader## ПРОСМОТР ВСЕХ ОПРЕДЕЛЕНИЙ БЕЗОПАСНОСТИ ПРОСМОТР ОПРЕДЕЛЕНИЯ БЕЗОПАСНОСТИ
##MS_ServerStateReader## VIEW SERVER STATE (Просмотр состояния сервера), VIEW SERVER PERFORMANCE STATE (Просмотр состояния производительности сервера), VIEW SERVER SECURITY STATE (Просмотр состояния безопасности сервера) VIEW DATABASE STATE (Просмотр состояния базы данных), VIEW DATABASE PERFORMANCE STATE (Просмотр состояния производительности базы данных), VIEW DATABASE SECURITY STATE (Просмотр состояния безопасности базы данных)
##MS_ServerStateManager## ALTER SERVER STATE (Изменение состояния сервера), VIEW SERVER STATE (Просмотр состояния сервера), VIEW SERVER PERFORMANCE STATE (Просмотр состояния производительности сервера), VIEW SERVER SECURITY STATE (Просмотр состояния безопасности сервера) VIEW DATABASE STATE (Просмотр состояния базы данных), VIEW DATABASE PERFORMANCE STATE (Просмотр состояния производительности базы данных), VIEW DATABASE SECURITY STATE (Просмотр состояния безопасности базы данных)

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

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

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

Примеры

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

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

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

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

Б. Перечисление всех субъектов (проверка подлинности 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

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

Часть 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##

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

-- Creating 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

Г. Проверка ролей уровня сервера для имен входа AAD

Выполните эту команду в виртуальной базе данных master, чтобы получить все имена входа AAD, которые являются членами ролей на уровне сервера в База данных SQL. Дополнительные сведения об именах входа на сервере AAD см. в статье Субъекты сервера Azure Active Directory.

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 OUTER 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 OUTER JOIN sys.database_principals AS DR1
    ON DbRMem.role_principal_id = DR1.principal_id LEFT OUTER 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 и другими базами данных на том же логическом сервере.

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

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

См. также раздел