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

Безопасность SQL Server и доверенная проверка подлинности

Существует два вида схем безопасности в Microsoft SQL Server: безопасность SQL Server и доверенная проверка подлинности (также известная как проверка подлинности Windows). Безопасность SQL Server — стандартная комбинация имени пользователя для регистрации и пароля, а доверенная проверка подлинности предполагает, что устройство, которое пытается подключиться к экземпляру SQL Server, одобрено процедурой проверки подлинности домена, и результаты этой проверки переданы экземпляру SQL Server: считается, что домен, в котором размещен экземпляр SQL Server, доверяет учетной записи пользователя — проверка выполнена ранее.

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

Имена и пользователи

Существует два уровня доступа к экземпляру SQL Server: учетные записи пользователя сервера (или экземпляра) и пользователи базы данных. С помощью учетных записей серверы позволяют внешнему пользователю (далее в статье термин «пользователь» применяется для любого приложения, службы, API и т. д., пытающихся подключиться к SQL Server) выполнить начальное соединение с экземпляром SQL Server. В случае безопасности на основе SQL для этого требуются имя пользователя и пароль. В случае доверенной проверки подлинности это учетная запись домена.

Есть два способа создать эти учетные записи пользователя: с помощью Transact-SQL (https://msdn.microsoft.com/en-us/library/ms189751.aspx? f=255&MSPPError=-2147217396) или через графический интерфейс. Процедура использования T-SQL для создания учетных записей хорошо документирована, и лучше всего воспользоваться ссылкой на официальную документацию по Microsoft SQL Server. А пока рассмотрим способ создания учетной записи в графическом интерфейсе. Чтобы запустить диалоговое окно для создания учетных записей пользователей, подключитесь к экземпляру SQL Server в среде SQL Server Management Studio (SSMS) в обозревателе объектов, а затем разверните узел Security\Logins («Безопасность\Имена пользователя»). Щелкните правой кнопкой мыши на пункте Logins и выберите в контекстном меню пункт New Login («Создать учетную запись») (см. экран 1).

 

Создание учетной записи пользователя SQL Server
Экран 1. Создание учетной записи пользователя SQL Server

Вы увидите диалоговое окно для настройки параметров учетной записи, показанное на экране 2. Изменить имя пользователя можно в том же окне.

 

Настройка учетной записи пользователя SQL Server
Экран 2. Настройка учетной записи пользователя SQL Server

Это вкладка General («Общие») для создания (и изменения) параметров учетной записи. Она отличается от двух ранее описанных схем безопасности. На вкладке General можно задать:

  • Login name («Имя пользователя»). Используется при проверке подлинности. В случае Windows, или доверенной проверки подлинности, необходимо задать имя в формате DOMAIN\LOGIN, где LOGIN — имя пользователя внутри домена, из которого пользователь выполняет проверку подлинности. Если экземпляр SQL Server расположен в другом домене, то необходимы отношения доверия между этим доменом и доменом SQL Server.
  • Password («Пароль»). При проверке подлинности SQL Server текстовое поле пароля включено, и вы вводите как имя пользователя, так и связанный с ним пароль.
  • Password Policy («Настройки политики паролей») и Expiration («Срок действия»). Флажки для политики пароля и срока действия также установлены в режиме проверки подлинности SQL Server, и применяются те политики, которые действуют в Active Directory в домене, где размещается SQL Server. Назначая имя пользователя SQL Server, вы можете разрешить пользователям менять свои пароли после регистрации. В результате администратор базы данных лишается доступа к имени учетной записи конечного пользователя.
  • Certificates («Сертификаты»), Keys («Ключи»), Credentials («Учетные данные»). В этой статье, предназначенной для начинающих, мы не будем рассматривать сертификаты, ключи и учетные данные.
  • Default Database («База данных по умолчанию»). Когда подключение к SQL Server установлено, выполняются два шага: проверка подлинности (должно существовать имя пользователя для учетных данных домена пользователя, если используется Windows или доверенная проверка подлинности либо необходимо передать комбинацию имени пользователя и пароля в экземпляр SQL Server). Это первый барьер. Второй заключается в том, что у проверенного имени пользователя имеется связанный объект пользователя в базе данных по умолчанию — базе данных, первоначально настроенной как контекст имени пользователя после проверки удостоверения. Даже если первое препятствие преодолено, при отсутствии соответствующего пользователя базы данных в базе данных по умолчанию подключение не будет установлено, и соответствующая запись будет внесена в журнал ошибок SQL. Но есть исключения: если серверная роль пользователя настолько важна, что нужно установить для него по умолчанию неявные права в каждой базе данных, то необязательно наличие соответствующего пользователя в базе данных по умолчанию. Однако я забегаю вперед, так как мы еще не рассматривали пользователей базы данных или роли сервера. Достаточно отметить, что, когда вы выбираете базу данных по умолчанию в графическом интерфейсе, связанный пользователь базы данных не создается. Вы просто указываете, какой должна быть база данных по умолчанию. При этом вы используете вкладку User Mapping («Сопоставление пользователей») диалогового окна Create Login («Создание учетной записи»), чтобы создать связанного пользователя базы данных.

Перейдем к следующей вкладке Server Roles («Роли сервера»), показанной на экране 3. На этой странице можно выбрать любые роли на уровне SQL Server (экземпляра) для нового пользователя. Роли сервера представляют собой коллекции прав, также известные как защищаемые объекты, которые упаковываются в коллекцию, чтобы вам не приходилось назначать права каждому защищаемому объекту отдельно. По умолчанию каждая учетная запись является членом общедоступной роли, что позволяет установить основное подключение к экземпляру SQL Server. Далее в статье будет рассмотрена каждая роль сервера в составе Microsoft SQL Server.

 

Вкладка Server Roles
Экран 3. Вкладка Server Roles

Следующая страница диалогового окна Create Login в среде SQL Server Management Studio предназначена для сопоставления учетных записей пользователей. Каждая учетная запись может иметь пользователя в одной или нескольких базах данных. На этой странице можно создать пользователей базы данных, связанных с новой учетной записью. Для этого нужно предоставить следующую информацию.

  • Database («База данных»). Установите флажок рядом с базой данных, в которой нужно создать связанного пользователя для учетной записи.
  • User Name («Имя пользователя»). Имя объекта пользователя не обязательно соответствует имени учетной записи, и далее будет показано, как это можно изменить.
  • Default Schema («Схема по умолчанию»). Каждый пользователь базы данных должен быть назначен схеме по умолчанию. Схема представляет собой коллекцию объектов базы данных, отделенных логически (но не обязательно физически) от других объектов в базе данных. Можно предоставить пользователю или группе пользователей права для всех объектов данной схемы, например предоставить всем пользователям из бухгалтерии (или учетной записи службы бухгалтерского приложения) определенные права для всех объектов в схеме Billing, но не давать доступ к этим объектам другим пользователям. При назначении схемы по умолчанию для пользователя базы данных нет необходимости включать имя схемы в вызовы T-SQL к базе данных при адресации объектов в этой схеме. Это также означает, что если пользователю предоставлены права на создание объектов, то по умолчанию они будут созданы в этой схеме, если только не указать имя схемы при создании объектов. Далее в статье мы еще коснемся концепции схем.
  • Database Role Membership («Членство в роли базы данных»). Точно так же, как на уровне экземпляра или сервера, каждая база данных располагает заранее определенной коллекцией прав, упакованных в ролях. Чуть позже мы рассмотрим роли базы данных, поставляемые с Microsoft SQL Server.

Обратимся к примеру диалогового окна для учетной записи пользователя SQLCRUISE\skipper (см. экран 4).

 

Пример настроек учетной записи пользователя
Экран 4. Пример настроек учетной записи пользователя

В этом примере пользователю SQLCRUISE\skipper предоставляются права для базы данных по умолчанию (lifeboat), где связанное имя пользователя — просто skipper. Схема по умолчанию — skipper_only. В двух других базах данных, в которых будут созданы пользователи для этой учетной записи, применяется то же имя пользователя, что и в имени пользователя (обычно ради упрощения идентификации), а схема по умолчанию — dbo, которая применяется по умолчанию в Microsoft SQL Server для всех определяемых пользователем объектов. Дополнительные сведения об этом будут приведены в следующем разделе. В случае с базой данных lifeboat мы предоставляем только членство в общедоступной роли базы данных, что предусматривает подключение к базе данных без дополнительных разрешений.

На следующей странице, Securables, представлены защищаемые объекты на уровне сервера или экземпляра. Как отмечалось выше, защищаемые объекты — это разрешения, предоставленные объектам. Защищаемые объекты обычно предоставляются в следующих случаях:

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

В нашем примере я предоставил SQLCRUISE\skipper членство в общедоступной роли сервера и разрешил просматривать любые определения объектов, существующие на уровне сервера (см. экран 5).

 

Назначение дополнительных прав
Экран 5. Назначение дополнительных прав

Наконец, переходим к странице Status («Состояние»). На этой странице можно разрешить или отменить доступ для пользователя (по умолчанию выбирается Grant — разрешить). Поэтому можно создать учетную запись, предоставить права, создать связанных пользователей, а затем отменить доступ. Вы можете вернуться в это окно для существующего пользователя и отменить доступ к экземпляру SQL Server. Аналогично происходит включение и отключение учетной записи (см. экран 6). Наконец, мы можем просмотреть состояние учетной записи пользователя и узнать, была ли учетная запись заблокирована из-за слишком большого числа неудачных попыток регистрации с неверным паролем.

 

Вкладка Status
Экран 6. Вкладка Status

Вы можете изучить код T-SQL в листинге 1, который формируется и выполняется при выполнении этих настроек в графическом интерфейсе.

На данном этапе важно отметить, как организована связь пользователей базы данных с учетной записью пользователя сервера. Как я уже указывал, соответствие имен между двумя объектами необязательно. Это объясняется тем, что объекты объединены в системных таблицах не по имени, а по идентификатору, именуемому sid (идентификатор безопасности). Это позволяет избавиться от привязки к учетной записи, соответствующей имени пользователя, или избежать возникновения ситуации, в которой вы восстанавливаете базу данных с именем пользователя, например trevor на экземпляре SQL Server, где уже имеется учетная запись trevor, но это совершенно другое лицо, которое не должно иметь прав в вашей базе данных. Благодаря sid такая опасность исключается. Если посмотреть на два системных представления, отображающих данные об учетных записях и пользователях, то можно увидеть, как эти объекты выглядят внутри SQL Server. Я подготовил учетную запись и пользователя professor и назначил lifeboat базой данных по умолчанию, создав при этом соответствующего пользователя в lifeboat. Системное представление, отображающее информацию об именах пользователей, — sys.server_principals (sys — схема). Информация о пользователе базы данных выводится через представление sys.database_principals в каждой базе данных. Эти представления могут быть соединены на основе sid (см. листинг 2 и экран 7).

 

Объединение информации о пользователе
Экран 7. Объединение информации о пользователе

Существуют проблемы, возникающие при несоответствии идентификаторов sid. Это более сложная тема, поэтому в одной из следующих статей я расскажу, как определить такую ситуацию, смягчить ее и устранить в случае возникновения так называемых «потерянных пользователей» (orphaned users).

Схемы и роли

Теперь мы переходим к более подробному знакомству со схемами и ролями. И схемы, и роли представляют собой коллекции в терминологии SQL Server. Схемы — это коллекции объектов (таблиц, представлений, хранимых процедур и т. д.). Роли — коллекции прав: роли серверов для прав на уровне сервера/экземпляра и роли базы данных для прав в конкретной базе данных. Однако на этом сходство заканчивается.

Особого внимания заслуживают две схемы по умолчанию: sys и dbo. Схема sys — фактически владелец всех системных объектов в Microsoft SQL Server. Во многих системных представлениях и динамических объектах управления они именуются объектами ms_shipped, которые вы увидите отмеченными в столбцах bit-type в соответствующих представлениях как is_ms_shipped со значением 1 для системных объектов и 0 для пользовательских объектов. Вы можете сами создавать схемы, соответствующие вашим потребностям. Ранее я упоминал в качестве примера схему выставления счетов для бухгалтерских объектов. Если пользователь создает объект без указания схемы, объект будет создан в схеме по умолчанию для этого пользователя. Если схема по умолчанию не определена для пользователя, то в качестве схемы по умолчанию назначается dbo.

При направлении запросов к пользователям рекомендуется применять полные доменные имена, то есть указывать имя базы данных, имя схемы и имя объекта, а не только имя объекта. Как это выглядит на практике? Если имеется таблица с именем tblFoo в схеме dbo базы данных SQLCruise, то можно создать запрос, который будет выбирать все столбцы и строки из этой таблицы несколькими способами (см. листинг 3).

Каждый вариант работает успешно, если имеется только одна таблица с именем tblFoo в базе данных SQL_Cruise и текущим контекстом базы данных была база данных SQL_Cruise. Однако только первый вариант будет работать корректно, независимо от того, к какой базе данных в настоящее время вы подключены на экземпляре SQL Server, содержащем базу данных SQL_Cruise. Второй вариант будет выполнен, если вы подключены к базе данных SQL_Cruise, независимо от числа схем, имеющих tblFoo, так как вы указали схему dbo. Третий вариант выдаст сообщение об ошибке (см. экран 8), если в базе данных SQL_Cruise имеется несколько схем с tblFoo, как показано в листинге 4, где я создал как таблицу dbo.tblFoo, так и таблицу user.tblFoo.

 

Сообщение об ошибке, если в базе данных SQL_Cruise имеется несколько схем с tblFoo
Экран 8. Сообщение об ошибке, если в базе данных SQL_Cruise имеется несколько схем с tblFoo

Да, все верно — объект существует, но вы получаете сообщение об ошибке Invalid object name («Недопустимое имя объекта»). Никогда не будьте уверены заранее, что объекта с таким именем не существует. Сообщение может свидетельствовать о проблеме с синтаксисом.

Предопределенные роли входят в Microsoft SQL Server на уровне как сервера, так и базы данных. Однако вы можете создавать собственные роли, если возникают ситуации, в которых нужно назначить одинаковые разрешения многим пользователям. Создание особых ролей позволяет определить эти права лишь однажды: когда вы создаете роль, а не для каждого пользователя или учетной записи регистрации пользователя (в зависимости от ролей базы данных или сервера). Помимо экономии времени исключается несогласованность при назначении прав многочисленным пользователям или учетным записям.

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

Безопасность Microsoft SQL Server — очень важная тема. Она отличается глубиной, а также своеобразием терминологии. Надеюсь, мне удалось достичь своей цели — объяснить различные термины и дать общий обзор объектов, применяемых на практике. В статьях начального уровня мы рассмотрим еще несколько тем, но уже в скором времени я обращусь к более сложным вопросам, вытекающим из этой публикации. Как всегда, благодарю читателей за внимание и с нетерпением жду комментариев. Надеюсь, статья поможет начинающим администраторам баз данных овладеть тайнами SQL.

Листинг 1. Код, соответствующий настройкам, сделанным в графическом интерфейсе
USE [master]
GO
CREATE LOGIN [SQLCRUISE\skipper] FROM WINDOWS WITH DEFAULT_DATABASE=[lifeboat]
GO

USE [lifeboat]
GO
CREATE USER [skipper] FOR LOGIN [SQLCRUISE\skipper]
ALTER USER [skipper] WITH DEFAULT_SCHEMA=[skipper_only]
GO

CREATE SCHEMA [skipper_only] AUTHORIZATION [skipper]
GO

USE [lifeboat_blank]
GO
CREATE USER [SQLCRUISE\skipper] FOR LOGIN [SQLCRUISE\skipper]
ALTER USER [SQLCRUISE\skipper] WITH DEFAULT_SCHEMA=[dbo]
GO

USE [Lifeboat_Messy]
GO
CREATE USER [SQLCRUISE\skipper] FOR LOGIN [SQLCRUISE\skipper]
ALTER USER [SQLCRUISE\skipper] WITH DEFAULT_SCHEMA=[dbo]
GO

use [master]
GO
GRANT VIEW ANY DEFINITION TO [SQLCRUISE\skipper]
GO
Листинг 2. Информация о пользователях системы и базы данных
SELECT name
        , sid
        , principal_id
        , type_desc
        , default_database_name
FROM sys.server_principals
WHERE name = 'professor';


SELECT name
        , sid
        , principal_id
        , type_desc
        , default_schema_name
FROM lifeboat.sys.database_principals
WHERE name = 'professor';
Листинг 3. Пример запроса для выбора столбцов и строк таблицы
—=========================================================================
— ВАРИАНТ 1: полное доменное имя
—=========================================================================
SELECT *
FROM SQL_Cruise.dbo.tblFoo;
—=========================================================================
— ВАРИАНТ 2: имя, определенное через схему
—=========================================================================
SELECT *
FROM dbo.tblFoo;
—=========================================================================
— ВАРИАНТ 3: только имя объекта
—=========================================================================
SELECT *
FROM tblFoo;
Листинг 4. Создание таблиц с несколькими схемами
USE [SQL_Cruise]
GO
CREATE SCHEMA [user] AUTHORIZATION [dbo]
GO
CREATE TABLE dbo.tblFoo (id INT);
CREATE TABLE [user].tblFoo (id INT);
SELECT *
FROM tblFoo;