Определение эффективных разрешений ядра СУБД

Применимо к:SQL ServerAzure SQL DatabaseAzure Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)

В этой статье описывается, как определить, кто имеет разрешения на различные объекты в ядре СУБД SQL Server. SQL Server реализует две системы разрешений для ядра СУБД. Более старая система предопределенных ролей имеет предварительно настроенные разрешения. Начиная с SQL Server 2005 (9.x) доступна более гибкая и точную систему.

Заметка

Сведения в этой статье относятся к SQL Server 2005 (9.x) и более поздним версиям. Некоторые типы разрешений недоступны в некоторых версиях SQL Server.

Следует всегда учитывать следующие моменты:

• Действующие разрешения — это совокупность обеих систем разрешений.
• Отклонение разрешений переопределяет их предоставление.
• Если пользователь является членом предопределенной роли сервера sysadmin, разрешения не проверяются дальше, поэтому отказы не будут применяться.
• Старая и новая системы имеют сходства. Например, участие в предопределенной роли сервера sysadmin похоже на наличие разрешений CONTROL SERVER. Но системы не идентичны. Например, если имя входа имеет только разрешение CONTROL SERVER и хранимые процедуры выполняют проверку членства в предопределенной роли сервера sysadmin, то произойдет сбой проверки разрешения. То же самое будет наблюдаться и в обратной ситуации.

Сводка

• Разрешения уровня сервера могут исходить из членства в предопределенной роли сервера или пользовательских серверных ролях. Все пользователи принадлежат к предопределенной роли сервера public и получают назначенные ей разрешения.
• Разрешения уровня сервера могут исходить из разрешений, предоставленных имени входа или серверным ролям, определяемым пользователем.
• Разрешения уровня базы данных могут исходить из членства в предопределенных ролях базы данных или в определяемых пользователем ролях базы данных в каждой базе данных. Все пользователи принадлежат к предопределенной роли базы данных public и получают назначенные ей разрешения.
• Разрешения уровня базы данных могут исходить из разрешений, предоставленных пользователям или пользовательским ролям базы данных в каждой базе данных.
• Разрешения могут быть получены от имени входа guest или пользователя базы данных guest, если он включен. Имя входа guest и пользователи по умолчанию отключены.
• Пользователи Windows могут быть участниками групп Windows, которые могут иметь имена входа. SQL Server узнает о членстве в группе Windows, когда пользователь Windows подключается и предоставляет токен Windows с идентификатором безопасности группы Windows. Так как SQL Server не управляет автоматическими обновлениями о членстве в группах Windows, SQL Server не может надежно сообщать о разрешениях пользователей Windows, полученных от членства в группах Windows.
• Разрешения можно получить, переключившись на роль приложения и предоставив пароль.
• Разрешения можно получить, выполнив хранимую процедуру, которая включает в себя предложение EXECUTE AS.
• Разрешения можно получить от имен входа и пользователей с помощью разрешения IMPERSONATE.
• Участники группы администраторов локальных компьютеров всегда могут повышать свои права доступа до sysadmin. (Не применяется к базе данных SQL.)
• Участники предопределенной роли сервера securityadmin могут повысить многие права и в некоторых случаях могут повысить уровень привилегий до sysadmin. (Не применяется к базе данных SQL.)
• Администраторы SQL Server могут просматривать сведения о всех именах входа и пользователях. Пользователи с меньшими полномочиями обычно видят сведения только о собственных идентификаторах.

Более старая система разрешений предопределенных ролей

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

Заметка

Не применимо к Базе данных SQL или Azure Synapse Analytics, где разрешение уровня сервера недоступно. Столбец is_fixed_rolesys.server_principals добавлен в SQL Server 2012 (11.x). Он не требуется для более старых версий SQL Server.

SELECT SP1.name AS ServerRoleName,
    ISNULL(SP2.name, 'No members') AS LoginName
FROM sys.server_role_members AS SRM
RIGHT JOIN sys.server_principals AS SP1
    ON SRM.role_principal_id = SP1.principal_id
LEFT JOIN sys.server_principals AS SP2
    ON SRM.member_principal_id = SP2.principal_id
WHERE SP1.is_fixed_role = 1 -- Remove for SQL Server 2008
ORDER BY SP1.name;

Заметка

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

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

SELECT DP1.name AS DatabaseRoleName,
    ISNULL(DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT JOIN sys.database_principals AS DP1
    ON DRM.role_principal_id = DP1.principal_id
LEFT JOIN sys.database_principals AS DP2
    ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.is_fixed_role = 1
ORDER BY DP1.name;

Чтобы понять разрешения, предоставляемые каждой роли, см. описание ролей на иллюстрациях в электронной документации (роли уровня сервера и роли уровня базы данных).

Более новая детальная система разрешений

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

На следующем рисунке показаны разрешения и их связи друг с другом. Некоторые из разрешений более высокого уровня (например, CONTROL SERVER) указаны несколько раз. Рисунок в этой статье слишком мал для чтения. Вы можете скачать полноразмерный плакат разрешений ядра СУБД в формате PDF.

Классы безопасности

Разрешения можно предоставлять на уровне сервера, на уровне базы данных, на уровне схемы или на уровне объекта и т. д. Существует 26 уровней (называемых классами). Полный список классов в алфавитном порядке выглядит следующим образом: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, AVAILABILITY GROUP, CERTIFICATE, CONTRACT, DATABASE, DATABASESCOPED CREDENTIAL, ENDPOINT, FULLTEXT CATALOG, FULLTEXT STOPLIST, LOGIN, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SEARCH PROPERTY LIST, SERVER, SERVER ROLE, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION. (Некоторые классы недоступны для некоторых типов SQL Server.) Для предоставления полных сведений о каждом классе требуется другой запрос.

Субъекты

Разрешения предоставляются субъектам. Субъектами могут быть серверные роли, имена входа, роли базы данных или пользователи. Имена входа могут представлять группы Windows, которые содержат множество пользователей Windows. Так как группы Windows не поддерживаются SQL Server, SQL Server не всегда знает, кто является членом группы Windows. Когда пользователь Windows подключается к SQL Server, пакет входа содержит токены членства в группе Windows для пользователя.

При подключении пользователя Windows с помощью имени входа на основе группы Windows для некоторых действий серверу SQL Server потребуется создать имя входа или пользователя для олицетворения отдельного пользователя Windows. Например, группа Windows ("Инженеры") содержит пользователей (Мария, Тимофей, Григорий), и у этой группы есть учетная запись пользователя базы данных. Если Мария имеет разрешение и создает таблицу, можно создать пользователя (Mariya) в качестве владельца таблицы. Если для Тимофея отклонено разрешение, которое имеют остальные участники группы "Инженеры", необходимо создать соответствующего пользователя, чтобы отследить отклонение разрешения.

Помните, что пользователь Windows может быть членом нескольких групп Windows (например, инженеров и менеджеров). Разрешения, предоставленные или отклоненные для имени входа "Инженеры" или "Менеджеры", индивидуально для каждого пользователя или для ролей, участником которых является пользователь, будут объединены и оценены для определения действующих разрешений. Функция HAS_PERMS_BY_NAME выявляет наличие определенного разрешения у пользователя или имени входа. Тем не менее невозможно определить источник предоставления или отклонения разрешения. Изучите список разрешений и попробуйте их на практике.

Полезные запросы

Разрешения сервера

Следующий запрос возвращает список разрешений, которые были предоставлены или запрещены на уровне сервера. Этот запрос должен выполняться в master базе данных.

Заметка

Разрешения уровня сервера нельзя предоставлять и запрашивать в Базе данных SQL или Azure Synapse Analytics.

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.server_principals AS pr
LEFT JOIN sys.server_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE is_fixed_role = 0 -- Remove for SQL Server 2008
ORDER BY pr.name,
    type_desc;

Разрешения базы данных

Следующий запрос возвращает список разрешений, которые были предоставлены или отклонены на уровне базы данных. Этот запрос должен быть выполнен в каждой базе данных.

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.database_principals AS pr
LEFT JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE pr.is_fixed_role = 0
ORDER BY pr.name,
    type_desc;

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

SELECT pr.type_desc,
    pr.name,
    pe.state_desc,
    pe.permission_name,
    s.name + '.' + oj.name AS OBJECT,
    major_id
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
INNER JOIN sys.objects AS oj
    ON oj.object_id = pe.major_id
INNER JOIN sys.schemas AS s
    ON oj.schema_id = s.schema_id
WHERE class_desc = 'OBJECT_OR_COLUMN';

Используйте функцию HAS_PERMS_BY_NAME, чтобы определить, имеет ли разрешение конкретный пользователь (в данном случае TestUser). Например:

EXECUTE AS USER = 'TestUser';
SELECT HAS_PERMS_BY_NAME ('dbo.T1', 'OBJECT', 'SELECT');
REVERT;

Сведения о синтаксисе см. в статье HAS_PERMS_BY_NAME.

Далее

• Приступая к работе с разрешениями Database Engine
• Руководство по началу работы с ядром СУБД