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

Относится к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureAzure Synapse AnalyticsСистема платформы аналитики (PDW)База данных SQL в Microsoft Fabric

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

База данных SQL и база данных SQL в Microsoft Fabric предоставляют одинаковые параметры в каждой базе данных, но разрешения на уровне сервера недоступны.

• В базе данных SQL см. руководство. Защита базы данных в базе данных SQL Azure. Рекомендуется использовать проверку подлинности идентификатора Microsoft Entra. Дополнительные сведения см. в руководстве по созданию пользователей Microsoft Entra с помощью приложений Microsoft Entra.

• В базе данных SQL в Microsoft Fabric единственным поддерживаемым методом проверки подлинности для пользователей базы данных является идентификатор Microsoft Entra. Роли и разрешения на уровне сервера недоступны. Дополнительные сведения см. в разделе "Авторизация в базе данных SQL" в Microsoft Fabric.

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

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

Logins

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

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

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

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

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

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

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

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

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

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

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

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

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

Другие основные участники

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

Рисунок, показывающий связи между пользователями Windows, группами Windows, именами входа и пользователями базы данных, см. в разделе "Создание пользователя базы данных".

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

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

В Windows Active Directory или Microsoft Entra ID

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 Azure. Сведения о разрешениях см. в разделе "Разрешения" (ядро СУБД) и см. на диаграмме далее в этой статье.

• ON <securable>::<name> представляет тип защищаемого объекта (сервер, объект сервера, база данных или объект базы данных) и его имя. Некоторые разрешения не требуют <securable>::<name>, так как это было бы однозначным или недопустимым в контексте. Например, разрешение CREATE TABLE не требует условия <securable>::<name> (GRANT CREATE TABLE TO Mary; позволяет 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 . Разрешения накапливаются, то есть пользователь получает все разрешения, предоставленные пользователю, имени входа и любой группе, членом которой он является. При этом отклонение разрешения отменяет все предоставленные ранее разрешения.

Группа продаж получает SELECT разрешения на таблицу OrderStatus с помощью инструкции GRANT SELECT ON OBJECT::OrderStatus TO Sales;. Пользователь Jae является членом 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;, то Джей, как участник роли Sales, лишен разрешения на SELECT, так как DENY переопределяет Sales, которые имеют преимущество над их отдельным 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 недостаточно. Эти две системы работают параллельно, но редко взаимодействуют друг с другом. Корпорация Майкрософт рекомендует использовать более новую, детализированную систему разрешений вместо фиксированных ролей по возможности.

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

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

¹ Это представление недоступно в базе данных SQL.

Examples

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

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

Чтобы вернуть явные разрешения, предоставленные или запрещенные в базе данных (SQL Server и базе данных SQL), выполните следующую инструкцию Transact-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
     INNER JOIN sys.database_principals AS dp
         ON perms.grantee_principal_id = dp.principal_id
     INNER 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 OUTER 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
     INNER JOIN sys.database_principals AS dp
         ON dRo.member_principal_id = dp.principal_id
     INNER 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)
• Определение эффективных разрешений ядра СУБД
• Руководство: Начало работы с ядро СУБД
• Занятие 1. Создание и запрос объектов базы данных
• Руководство: SQL Server Management Studio
• Руководство. Написание инструкций Transact-SQL