Начало работы с разрешениями ядро СУБД

Применимо: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure azure Synapse Analytics Analytics Platform System (PDW)

Разрешения в ядро СУБД управляются на уровне сервера с помощью имен входа и ролей сервера, а также на уровне базы данных с помощью пользователей базы данных и ролей базы данных. Модель для База данных SQL предоставляет одну и ту же систему в каждой базе данных, но разрешения на уровне сервера недоступны. В этой статье рассматриваются некоторые основные понятия безопасности, а затем описывается типичная реализация разрешений.

Субъекты безопасности

Субъект безопасности — это официальное имя удостоверений, использующих SQL Server и которым можно назначить разрешение на выполнение действий. Обычно это пользователи или группы пользователей, однако субъектами безопасности могут быть и другие сущности, олицетворяющие пользователей. Субъекты безопасности можно создавать и управлять ими с помощью списка Transact-SQL или с помощью SQL Server Management Studio.

Имена входа

Имена входа — это отдельные учетные записи пользователей для входа в ядро СУБД SQL Server. SQL Server и База данных SQL поддерживают имена входа на основе проверка подлинности Windows и имен входа на основе проверки подлинности SQL Server. Дополнительные сведения об этих двух типах имен входа см. в разделе Choose an Authentication Mode.

Предопределенные роли сервера

В SQL Server фиксированные роли сервера — это набор предварительно настроенных ролей, которые обеспечивают удобную группу разрешений на уровне сервера. Имена входа можно добавить в роли, используя инструкцию ALTER SERVER ROLE ... ADD MEMBER . Дополнительные сведения см. в разделе ALTER SERVER ROLE (Transact-SQL). База данных SQL не поддерживает фиксированные роли сервера, но имеет две роли в master базе данных (dbmanagerиloginmanager), которые действуют как роли сервера.

Определяемые пользователем роли сервера

В SQL Server можно создать собственные роли сервера и назначить им разрешения на уровне сервера. Имена входа можно добавить в роли сервера, используя инструкцию ALTER SERVER ROLE ... ADD MEMBER . Дополнительные сведения см. в разделе ALTER SERVER ROLE (Transact-SQL). База данных SQL не поддерживает определяемые пользователем роли сервера.

Пользователи базы данных

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

Пользователи базы данных также могут быть созданы, у которых нет соответствующего имени входа. Эти пользователи называются пользователями автономной базы данных. Корпорация Майкрософт поощряет использование пользователей автономной базы данных, так как это упрощает перемещение базы данных на другой сервер. Как и для входа, пользователь автономной базы данных может использовать проверка подлинности Windows или проверку подлинности SQL Server. Дополнительные сведения см. в статье Пользователи автономной базы данных — создание переносимой базы данных.

Существует 12 типов пользователей с незначительными различиями в способах проверки подлинности и представляемых сущностях. Сведения о списке пользователей см. в статье CREATE USER (Transact-SQL).

Предопределенные роли базы данных

Предопределенные роли базы данных — это набор предварительно настроенных ролей, который представляет собой удобную группу разрешений на уровне базы данных. Пользователи базы данных и определяемые пользователем роли базы данных можно добавить в фиксированные роли базы данных с помощью инструкции ALTER ROLE ... ADD MEMBER . Дополнительные сведения см. в разделе ALTER ROLE (Transact-SQL).

Определяемые пользователем роли базы данных

Пользователи с разрешением CREATE ROLE могут создавать определяемые пользователем роли базы данных для представления групп пользователей с общими разрешениями. Обычно разрешения предоставляются или отклоняются для всей роли, что упрощает управление разрешениями и мониторинг. Пользователей базы данных можно добавлять в роли базы данных с помощью инструкции ALTER ROLE ... ADD MEMBER . Дополнительные сведения см. в разделе ALTER ROLE (Transact-SQL).

Другие субъекты

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

График, отображающий связи между пользователями Windows, группами Windows, именами входа и пользователями базы данных, см. в разделе Create a Database User.

Типичный сценарий

В следующем примере представлен типичный и рекомендуемый способ настройки разрешений.

Идентификатор Windows Active Directory или Microsoft Entra

1. Создайте пользователя для каждого пользователя.

2. Создайте группы Windows, представляющие рабочие единицы и рабочие функции.

3. Добавьте пользователей Windows в группы Windows.

Если пользователь будет подключаться к нескольким базам данных

1. Создайте имя входа для групп Windows. (Если используется проверка подлинности SQL Server, пропустите шаги Active Directory и создайте имена входа проверки подлинности SQL Server здесь.)

2. В базе данных пользователей создайте пользователя базы данных для имени входа, представляющего группы Windows.

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

4. Добавьте пользователей базы данных в одну или несколько определяемых пользователем ролей базы данных.

5. Предоставьте разрешения для определяемых пользователем ролей базы данных.

Если пользователь будет подключаться к только к одной базе данных

1. В базе данных пользователей создайте пользователя автономной базы данных для группы Windows. (Если используется проверка подлинности SQL Server, пропустите шаги Active Directory и создайте проверку подлинности пользователя базы данных SQL Server.

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

3. Добавьте пользователей базы данных в одну или несколько определяемых пользователем ролей базы данных.

4. Предоставьте разрешения для определяемых пользователем ролей базы данных.

Типичным результатом на этом этапе является то, что пользователь Windows является членом группы Windows. Группа Windows имеет имя входа в SQL Server или База данных SQL. Имя входа сопоставляется с удостоверением пользователя в базе данных пользователей. Пользователь является членом роли базы данных. Теперь необходимо добавить разрешения для роли.

Назначение разрешений

Большинство инструкций назначения разрешений имеет следующий формат:

AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL;

• AUTHORIZATION должен быть равен GRANT, REVOKE или DENY.

• Предложение PERMISSION определяет, какое действие разрешено или запрещено. Точное количество разрешений будет разным для SQL Server и Базы данных SQL. Разрешения перечислены в статье "Разрешения" (ядро СУБД) и на диаграмме ниже.

• ON SECURABLE::NAME представляет тип защищаемого объекта (сервер, объект сервера, база данных или объект базы данных) и его имя. Некоторые разрешения не требуются ON SECURABLE::NAME , так как это однозначно или неуместно в контексте. Например, CREATE TABLE разрешение не требует ON SECURABLE::NAME предложения (GRANT CREATE TABLE TO Mary; позволяет Мэри создавать таблицы).

• PRINCIPAL представляет субъект безопасности (имя входа, пользователя или роль), который получает или утрачивает разрешение. Рекомендуется по возможности предоставлять разрешения для ролей.

В следующем примере инструкции предоставления предоставляется UPDATE разрешение на Parts таблицу или представление, содержащееся в схеме Production , роли с именем PartsTeam:

GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam;

В следующем примере инструкция предоставления предоставляет UPDATE разрешение на Production схему, а расширение для любой таблицы или представления, содержащейся в этой схеме, роли с именем ProductionTeam, что является более эффективным и приемлемым подходом к назначению разрешений, чем на уровне отдельных объектов:

GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;

Разрешения предоставляются субъектам безопасности (именам входа, пользователям и ролям) с помощью инструкции GRANT . Разрешения явно запрещены с помощью DENY команды. Для удаления ранее предоставленного или отклоненного разрешения используется инструкция REVOKE . Разрешения накапливаются, то есть пользователь получает все разрешения, предоставленные пользователю, имени входа и любой группе, членом которой он является. При этом отклонение разрешения отменяет все предоставленные ранее разрешения.

Группа Sales получает разрешения SELECT для доступа к таблице OrderStatus посредством инструкции GRANT SELECT ON OBJECT::OrderStatus TO Sales;. Пользователь Джей является членом роли "Продажи". Jae также был предоставлен SELECT разрешение на таблицу OrderStatus под собственным именем пользователя с помощью инструкции GRANT SELECT ON OBJECT::OrderStatus TO Jae;. Предположим, администратор хочет удалить разрешение GRANT для роли Sales.

• Если администратор правильно выполняется REVOKE SELECT ON OBJECT::OrderStatus TO Sales;, Джей сохранит SELECT доступ к таблице OrderStatus с помощью отдельной GRANT инструкции.

• Если администратор неправильно выполняет DENY SELECT ON OBJECT::OrderStatus TO Sales; , Джей, в качестве члена роли "Продажи", будет отказано в SELECT разрешении, так как DENY на продажу переопределяется их отдельный пользователь GRANT.

Иерархия разрешений

К разрешениям применяется иерархия "родители — потомки". То есть при предоставлении разрешения SELECT для базы данных оно включает разрешение SELECT для всех (дочерних) схем в базе данных. При предоставлении разрешения SELECT для схемы оно включает разрешение SELECT для всех (дочерних) таблиц и представлений в схеме. Разрешения являются транзитивными; то есть при предоставлении разрешения SELECT для базы данных оно включает разрешение SELECT для всех (дочерних) схем и всех (внучатых) таблиц и представлений.

Кроме того, предусмотрены покрывающие разрешения. Разрешение CONTROL для объекта, как правило, предоставляет все прочие разрешения для этого объекта.

Поскольку иерархия "родители — потомки" и иерархия покрытия могут применяться к одному разрешению, с течением времени система разрешений может усложняться. Например, рассмотрим таблицу (Region) в схеме (Customers) в базе данных (SalesDB).

• CONTROL для таблицы Region включает все остальные разрешения для таблицы Region, в том числе ALTER, SELECT, INSERT, UPDATE, DELETEи некоторые другие разрешения.

• SELECT для схемы Customers, к которой принадлежит таблица Region, включает разрешение SELECT для таблицы Region.

Таким образом, разрешение SELECT для таблицы Region можно предоставить с помощью любой из этих шести инструкций:

GRANT SELECT ON OBJECT::Region TO Jae;

GRANT CONTROL ON OBJECT::Region TO Jae;

GRANT SELECT ON SCHEMA::Customers TO Jae;

GRANT CONTROL ON SCHEMA::Customers TO Jae;

GRANT SELECT ON DATABASE::SalesDB TO Jae;

GRANT CONTROL ON DATABASE::SalesDB TO Jae;

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

Первое указанное выше разрешение (GRANT SELECT ON OBJECT::Region TO Jae;) — наиболее гранулярное, то есть эта инструкция предоставляет минимально возможное разрешение SELECT. Вместе с ним не предоставляются разрешения для каких-либо вложенных объектов. Это хороший принцип, чтобы всегда предоставлять наименьшее разрешение (вы можете узнать больше о принципе наименьшей привилегии), но в то же время (противоречит этому) попытаться предоставить на более высоких уровнях, чтобы упростить систему предоставления. Таким образом, если Джей нуждается в разрешениях на всю схему, предоставьте SELECT один раз на уровне схемы вместо предоставления SELECT на уровне таблицы или представления много раз. Проектирование базы данных может значительно повлиять на то, как успешная эта стратегия может быть. Стратегия наиболее эффективна, когда объекты в базе данных, которым требуются одинаковые разрешения, включаются в одну схему.

Схема разрешений

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

Рисунок, показывающий связи между субъектами ядро СУБД и объектами сервера и базы данных, см. в разделе "Иерархия разрешений" (ядро СУБД).

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

Разрешения предопределенных ролей сервера и базы данных схожи с гранулярными разрешениями, но все же отличаются от них. Например, члены sysadmin предопределенных ролей сервера имеют все разрешения на экземпляр SQL Server, как и имена входа с разрешением CONTROL SERVER . Но предоставление CONTROL SERVER разрешения не делает вход членом предопределенных ролей сервера sysadmin, а добавление имени входа в sysadmin предопределяемую роль сервера не предоставляет явное CONTROL SERVER разрешение для входа. Иногда хранимая процедура выполняет проверку разрешений путем проверки предопределенной роли, а не гранулярного разрешения. Например, для отключения базы данных требуется членство в предопределенных ролях db_owner базы данных. Эквивалентные CONTROL DATABASE разрешения недостаточно. Эти две системы работают параллельно, но редко взаимодействуют друг с другом. Специалисты Майкрософт рекомендуют по возможности использовать более новую систему гранулярных разрешений вместо предопределенных ролей.

Мониторинг разрешений

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

• Имена входа и определяемые пользователем роли сервера на сервере можно просмотреть в представлении sys.server_principals . Это представление недоступно в База данных SQL.

• Пользователей и определяемые пользователями роли в базе данных можно просмотреть в представлении sys.database_principals .

• Разрешения, предоставленные именам входа и определяемым пользователями предопределенным ролям сервера, можно просмотреть в представлении sys.server_permissions . Это представление недоступно в База данных SQL.

• Разрешения, предоставленные пользователям и определяемым пользователем предопределенным ролям базы данных, можно просмотреть в представлении sys.database_permissions .

• Членство в роли базы данных можно проверить с помощью представления sys.database_role_members .

• Членство в роли сервера можно проверить с помощью представления sys.server_role_members . Это представление недоступно в База данных SQL.

• Дополнительные представления, связанные с безопасностью, см. в статьях "Представления каталога безопасности" (Transact-SQL).

Примеры

Следующие инструкции возвращают полезные сведения о разрешениях.

А. Список разрешений базы данных для каждого пользователя

Чтобы вернуть явные разрешения, предоставленные или запрещенные в базе данных (SQL Server и База данных SQL), выполните следующую инструкцию в базе данных.

SELECT
    perms.state_desc AS State,
    permission_name AS [Permission],
    obj.name AS [on Object],
    dp.name AS [to User Name]
FROM sys.database_permissions AS perms
JOIN sys.database_principals AS dp
    ON perms.grantee_principal_id = dp.principal_id
JOIN sys.objects AS obj
    ON perms.major_id = obj.object_id;

B. Список членов ролей сервера

Чтобы вернуть члены ролей сервера (только SQL Server), выполните следующую инструкцию.

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

C. Вывод списка всех субъектов базы данных, являющихся членами роли уровня базы данных

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

SELECT dRole.name AS [Database Role Name], dp.name AS [Members]
FROM sys.database_role_members AS dRo
JOIN sys.database_principals AS dp
    ON dRo.member_principal_id = dp.principal_id
JOIN sys.database_principals AS dRole
    ON dRo.role_principal_id = dRole.principal_id;

См. также

• Центр безопасности для ядра СУБД SQL Server и Базы данных Azure SQL
• Функция безопасности (Transact-SQL)
• Динамические административные представления и функции, связанные с безопасностью (Transact-SQL)
• Представления каталога безопасности (Transact-SQL)
• sys.fn_builtin_permissions (Transact-SQL)
• Определение действующих разрешений для ядра СУБД

Следующие шаги

• Учебник. Приступая к работе с компонентом Database Engine
• Создание базы данных (учебник)
• Руководство: SQL Server Management Studio
• Учебник. Составление инструкций Transact-SQL