Упрощение администрирования путем разделения концепций пользователя и схемы

В статьях о безопасности в SQL 2000 я рассказывала об ограничениях, которые имеют место при организации цепочек владения, и дополнительных вопросах безопасности, возникающих в процессе создания цепочек владения при взаимодействии между базами данных. Упоминалось и о неразберихе, связанной с моделью безопасности SQL Server 2000, которая не разделяет концепции пользователя и схемы. На этот раз мы рассмотрим усовершенствования в системе безопасности SQL Server 2005 (или Yukon), которые касаются данных ограничений.

В SQL Server 2005 разработчики Microsoft сделали основной упор на безопасность, были усилены многие элементы системы безопасности, которые были неполными в предыдущих версиях. Наиболее существенное изменение состоит в том, что SQL Server 2005 поставляется с должным образом настроенной системой безопасности, даже если развертывать продукт со всеми установками по умолчанию. Терминология в системе безопасности SQL Server 2000 тоже стала несколько иной. Помимо термина «схема», мы обсудим в этой статье такие понятия безопасности, как аутентификация и авторизация. Эти два новых понятия являются фундаментальными в модели безопасности SQL Server 2005. Мы можем представить себе схему как контейнер, в котором разработчик создает объекты базы данных. Обращаясь к объекту в SQL Server2005, вы ссылаетесь на схему, которая содержит данный объект, а не на владельца объекта. Аутентификация — это процесс, с помощью которого SQL Server подтверждает и устанавливает личность при каждой попытке доступа к ресурсу. Авторизация — это процесс, с помощью которого SQL Server определяет, разрешен ли данному пользователю доступ к ресурсу.

Контекст исполнения программных модулей

Организация цепочек владения является вопросом авторизации, с помощью которого SQL Server автоматически авторизует одного пользователя (например, user1) для доступа к объектам другого пользователя (например, user2), при условии что user1 имеет право на исполнение программных модулей (хранимых процедур или функций), которыми владеет user2. Однако, кроме механизма организации цепочек владения, в SQL Server 2000 нет другого механизма для управления аутентификацией пользователя во время доступа к зависимым объектам. Пользователь всегда выполняет модуль от своего имени и может получить доступ к зависимым объектам только при выполнении одного из следующих условий.

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

SQL Server 2005 позволяет задать контекст выполнения хранимых процедур и пользовательских функций (UDF) — исключая макрофункции внутри таблиц — с помощью выражения EXECUTE AS, помещенного в заголовок определения модуля. Данный механизм предоставляет разработчику приложений возможность более строгого контроля аутентификации, позволяя одному пользователю выполнять действия внутри модуля так, будто он аутентифицирован как другой пользователь.

Оператор EXECUTE AS полезен при использовании динамического SQL внутри модуля. SQL Server 2000 всегда проверяет разрешения для каждого оператора динамического SQL, который выполняется, игнорируя условие № 1. Механизм организации цепочек владения не применяется. Если пользователь, вызывающий процедуру, не имеет разрешения на работу с объектами, к которым обращается динамический SQL, выполнение прекращается с ошибкой.

Однако в SQL Server 2005, если пользователь создает процедуру, содержащую динамический SQL, так, чтобы она выполнялась в контексте пользователя, имеющего разрешения на объекты, упоминаемые в процедуре, процедура выполнится успешно.

Во второй бета-версии SQL Server 2005 параметр EXECUTE AS имел три возможных значения для авторизации: CALLER (по умолчанию), USER= имя пользователя и SELF.

EXECUTE AS CALLER. Выполнение от имени вызывающего пользователя является значением по умолчанию и обратно совместимо с SQL Server 2000. Когда программист указывает EXECUTE AS CALLER, операторы внутри модуля выполняются в контексте пользователя, вызвавшего процедуру. Поэтому пользователь, выполняющий процедуру, должен иметь соответствующие разрешения не только на запуск процедуры, но и на любые объекты базы данных, на которые она ссылается. Пользователю, вызывающему процедуру, разрешения на такие объекты должны быть даны явно или могут быть неявными за счет цепочки владения. В последнем случае SQL Server 2005 вычисляет разрешения на ссылочные объекты, анализируя цепочку владения между вызывающими объектами и ссылочными объектами точно так же, как это делает SQL Server 2000. Однако нужно иметь в виду, что в SQL Server 2005 объекты не имеют владельцев. Схемы же имеют владельцев и содержат объекты.

EXECUTE AS USER = <имя пользователя>. Когда используется значение EXECUTE AS USER = <имя пользователя>, процедура выполняется в контексте того пользователя, чье имя указано в параметре. Когда выполняется процедура, SQL Server сначала проверяет, имеет ли пользователь разрешение EXECUTE на данную процедуру, затем проверяет разрешения на операторы внутри процедуры для пользователя, указанного в параметре EXECUTE AS USER. Произвольно задать имя пользователя во время выполнения модуля невозможно. Для того чтобы иметь возможность указать AS некоторое имя пользователя, необходимо иметь специальные разрешения (например, IMPERSONATE) или быть членом специальной роли (sysadmin, или db_owner).

Предположим, Сюзан создает хранимую процедуру в своей схеме по умолчанию (схема, в которой она является владельцем), из которой происходит обращение к таблице в схеме Джо (JoeSchema), владельцем которой она не является. Сюзан должна иметь разрешение на создание процедуры в схеме Джо, если она не является владельцем этой схемы. А вот Мэри имеет разрешение SELECT на таблицу в схеме Джо. Сюзан указывает EXECUTE AS USER=Mary в операторе CREATE PROCEDURE, как показано ниже:

CREATE PROCEDURE AccessMyTable

WITH EXECUTE AS USER =Mary

AS SELECT * FROM JoeSchema.MyTable

Затем Сюзан предоставляет разрешение EXECUTE на данную процедуру пользователю по имени Скотт. Когда Скотт выполняет процедуру, SQL Server проверяет, имеет ли Скотт разрешение на выполнение процедуры, однако при обращении к таблице SQL Server проверяет разрешения Мэри. При таком сценарии, даже если Скотт не имеет явного разрешения SELECT на эту таблицу, он может получить доступ к данным через процедуру, потому что она будет выполняться в контексте Мэри, а у Мэри есть права на таблицу.

EXECUTE AS SELF. EXECUTE AS SELF обозначает «исполнение от имени текущего пользователя, который создает или изменяет модуль». EXECUTE AS SELF аналогично EXECUTE AS USER = <имя пользователя>, где имя пользователя является именем человека, создающего или изменяющего модуль. Каталог сохраняет идентификатор пользователя (UID) вместо самого значения SELF.

Замечу, что пользователь, указанный в параметре SELF, не обязательно должен быть владельцем объекта. На самом деле объекты в SQL Server 2005 не имеют владельцев, но можно думать о владельцах схем так, как будто они владеют всеми объектами в схемах. Пользователь может иметь привилегии на создание объектов в схемах, владельцем которых он не является. Например, Сюзан имеет разрешение ALTER SCHEMA на схему, владельцем которой является Джо. Если Сюзан создает таблицу в схеме Джо, Джо является владельцем, а Сюзан — создателем; любая процедура, созданная Сюзан с параметром EXECUTE AS SELF, будет выполняться в контексте Сюзан.

Какой параметр выбрать

Изучение новых возможностей займет некоторое время, поэтому ниже приводится несколько рекомендаций по поводу применения параметра EXECUTE AS в различных ситуациях.

Итак, EXECUTE AS CALLER рекомендуется применять в следующих случаях:

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

Используйте EXECUTE AS USER = <имя пользователя> в следующих ситуациях:

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

Иногда может понадобиться выдать разрешения, которые обычно не предоставляются, такие как разрешение TRUNCATE TABLE. Даже в SQL Server 2005 нельзя предоставить такое разрешение. Однако владелец базы может написать хранимую процедуру, которая усекает таблицу. Если владелец базы и владелец таблицы — одно и то же лицо, то это непрерывная цепочка владения. Преимущества использования цепочки владения применимы только к предложениям языка манипуляции данными (SELECT, INSERT, UPDATE, DELETE), поэтому пользователь, имеющий разрешение на выполнение процедуры, столкнется с отсутствием разрешения на выполнение оператора усечения таблицы. Но если при создании процедуры задействовать оператор EXECUTE AS, пользователь, выполняющий процедуру, может во время выполнения выдать себя за другого, и оператор TRUNCATE TABLE будет выполнен успешно.

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

Разделение пользователя и схемы

Следующее существенное изменение в модели безопасности SQL Server 2005 — это упрощение взаимоотношений между пользователями и объектами, позволяющее добавлять и удалять пользователей, не заботясь об объектах, которыми те владеют. Для соответствия ANSI SQL Server 2005 проводит различие между пользователями и схемами.

Стандарт ANSI SQL-92 определяет схему как коллекцию объектов базы данных, имеющих одного владельца и находящихся в одном пространстве имен (набор объектов, которые не могут иметь повторяющиеся имена). Например, две таблицы могут иметь одинаковые имена, только если они находятся в разных схемах; никакие две таблицы в одной схеме не могут иметь одинаковые имена. Напомню, что при работе с инструментами базы данных схема обращается к информационному каталогу, который описывает объекты в схеме или базе данных. А в Analysis Services схема описывает многомерные объекты, такие как кубы и размерности.

В SQL Server 2005 нет привязки пользователей к схемам; как первичные, так и вторичные принципалы могут владеть схемами. Термин «принципал» в данном случае обозначает сущность, которая может получить доступ к защищенным объектам. Первичный принципал представляет одного пользователя (такого, как учетная запись SQL Server или Windows); вторичный принципал представляет множество пользователей (это может быть роль или группа Windows). Другое изменение в SQL Server 2005 заключается в том, что объекты не имеют владельцев. Любой владелец схемы «владеет» объектами этой схемы. Важно помнить, что схема содержит объекты, а не пользователей.

Каждая новая база данных, созданная в SQL Server 2005, включает несколько схем. Так же как в базах SQL Server 2000 были «пользователи» dbo, INFORMATION_SCEMA, guest, в каждой новой базе SQL Server 2005 имеются схемы с такими же именами. Кроме того, в каждой базе SQL Server 2005 существует схема с именем sys, которая предоставляет пользователям доступ ко всем системным таблицам и представлениям. И наконец, каждая предопределенная роль в базе SQL Server 2000 соответствует одноименной схеме в SQL Server 2005.

Можно назначить новому пользователю схему по умолчанию, которая может не существовать во время создания учетной записи данного пользователя. Схема по умолчанию используется для разрешения имен во время создания объектов и при обращении к объектам. Если не указать схему по умолчанию, то для пользователя схемой по умолчанию будет схема dbo. При обращении к объектам SQL Server 2005 будет всегда проверять сначала схему sys, независимо от установленной схемы по умолчанию. Например, если пользователь Сью выполняет запрос Select * from table1, а схемой по умолчанию для нее является SueSchema, то разрешение имен будет происходить в такой последовательности:

  1. Поиск таблицы sys.table1
  2. Поиск таблицы SueSchema.table1
  3. Поиск таблицы dbo.table1

Данный поисковый механизм применим ко всем операторам, даже к ALTER и DROP. Напомню, что, когда системный администратор создает объект и указывает одну часть имени (без схемы), схема всегда будет DBO. Однако системный администратор может явно указать схему, в которой будет создан объект.

Обратная совместимость

SQL Server 2000 не разделяет имена пользователей и схем, он даже не включает в себя концепцию схемы. Поэтому SQL Server 2000 помечает любой объект, созданный пользователем, с помощью имени пользователя вместо схемы. В SQL Server 2005 для обратной совместимости разработчики Microsoft переписали хранимые процедуры sp_grantdbaccess и sp_adduser для использования новых операторов DDL, которые рассматриваются во врезке «Новые операторы DDL». В SQL Server 2005 sp_grantdbaccess создает пользователя и схему с тем же именем и ID, как у пользователя. Эта схема является для пользователя схемой по умолчанию, а пользователь является владельцем данной схемы. Например, если кто-то выполняет процедуру:

EXEC sp_grantdbaccess Susan, sue, db_datareader

SQL Server 2005 выполняет внутреннюю трансляцию такого вызова в новый DDL, как показано в листинге. Кроме того, SQL Server 2005 переписывает вызов

sp_revokedbaccess sue

как

DROP CHEMA sue

DROP USER sue

Замечу, что SQL Server 2005 удаляет схему, если она имеет такие же имя и ID, как у пользователя.

Нужно иметь в виду, что если вместо sp_grantdbaccess используется CREATE USER и схема по умолчанию не указана, то схемой по умолчанию станет DBO. SQL Server 2005 не создаст новую схему с таким же именем, как у пользователя.

Будь готов

Даже если переходить на SQL Server 2005 сразу после его выхода не планируется, рекомендую все же начать думать о пользователях и схемах как об отдельных сущностях. Для SQL Server 2000 схема всегда имеет такое же имя, как пользователь, но, чтобы не запутаться, нужно помнить, что разрешения выдаются пользователям, тогда как схемы содержат (и уточняют) объекты. Разделение схем и пользователей упрощает администрирование. Основное преимущество состоит в том, что осуществлять управление пользователями, особенно удаление пользователей, будет намного легче. Также можно будет устанавливать несколько пользователей в качестве владельцев схемы с помощью вторичных принципалов (т. е. групп и ролей). Кроме того, несколько пользователей могут иметь одну схему по умолчанию для общего разрешения имен.

Возможность задать пользователя как исполнителя для определенного модуля позволяет более полно управлять доступом к объектам и дает много преимуществ по сравнению с механизмом цепочек владения при взаимодействии между базами данных, реализованным в SQL Server 2000 Service Pack 3 (SP3). Механизм цепочек владения между базами данных является решением, работающим по принципу «все или ничего»: цепочка владения либо применяется, либо нет. С помощью дополнительного управления в SQL Server 2005 разработчик модулей может определить, какие модули пользователь имеет право запускать, а какие — нет.

Рассмотренные улучшения — это только верхушка айсберга системы безопасности Yukon. Когда мы вплотную приблизимся к моменту выхода SQL Server 2005, я поведаю все подробности относительно использования ее особенностей.

Новое в DDL

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

С помощью CREATE SCHEMA можно создать новую схему, дополнительно можно использовать выражение AUTHORIZATION для указания владельца схемы. Если не указывать AUTHORIZATION, то владельцем схемы станет пользователь, выполняющий оператор. Владелец также может иметь другие схемы и схему по умолчанию, отличную от той, которой он владеет.

ALTER SCHEMA позволяет изменить владельца схемы. Одновременно у схемы может быть только один владелец, но таким владельцем может быть пользователь SQL Server, пользователь Windows, группа Windows или роль SQL Server. Только пользователь с правом TAKE OWNERSHIP на схему может забрать право владения схемой. Член роли db_owner может поменять владельца схемы.

DROP SCHEMA проходит успешно, только если схема не содержит каких-либо объектов. Если схема содержит объекты, она не будет удалена.

Кроме того, SQL Server 2005 содержит новые команды языка DDL для работы с пользователями. Это соответствует новому стандарту использования операторов CREATE и ALTER вместо применения хранимых процедур. CREATE USER позволяет указать имя учетной записи пользователя и имя для регистрации для ассоциации с учетной записью данного пользователя. Можно также указать схему по умолчанию. ALTER USER позволяет изменить как имя пользователя, так и схему пользователя по умолчанию.

Независимый консультант и инструктор по SQL Server. Имеет сертификаты MCT и MCSE. Автор книги Inside SQL Server 2000 (Microsoft Press) kalen@sqlmag.com