45798 (Новые возможности MS SQL Server 2004 Yukon)

Новые возможности MS SQL Server 2004 "Yukon"

Иван Бодягин

Введение

Описать более-менее подробно все возможности новой версии Microsoft SQL Server задача не тривиальная, поэтому в данной статье предложен лишь небольшой обзор некоторых нововведений. А именно представления метаданных, схем, немного о безопасности, новые возможности при работе с индексами и новые встроенные типы данных. Я не ставил перед собой цели раскопать все в подробностях, поскольку на данный момент доступна лишь первая предварительная версия сервера и многое может измениться, но основная функциональность, очевидно, останется, поэтому ее и имеет смысл рассмотреть.

Метаданные и безопасность

Одно из достаточно серьезных изменений в сервере касается метаданных. Ранее эта информация хранилась в нескольких системных табличках, и была довольно незатейливо структурирована, теперь же все стало несколько сложнее, но в то же время строже и логичнее. Вообще сейчас об этой части сервера сложно говорить что-либо наверняка, так как, судя по всему, часть заявленной функциональности еще не реализована, а часть ожидают довольно серьезные изменения. Главы в Books On-Line, относящиеся к безопасности, на данный момент попросту отсутствуют, но уже можно разглядеть направление дальнейшего развития и даже кое-что потрогать руками.

В предыдущих версиях сервера для обслуживания и тонкой настройки, как правило, использовались специальные системные хранимые процедуры. Теперь же вся эта функциональность вносятся в T-SQL, посредством создания новых DDL операторов или небольшого изменения старых. От системных же процедур в будущих версиях, судя по всему, откажутся. Функции диагностики, сбора статистики и просмотра прочей административной информации, также переходят от системных процедур и DBCC команд к специальным системным функциям.

Безопасность

Как уже говорилось, на данный момент что-то конкретное в этой части сервера раскопать сложновато, поскольку документация практически отсутствует. Но, тем не менее, уже ясно, что ожидаются серьезные изменения.

Row level security

В свое время Microsoft была заявлена поддержка безопасности на уровне отдельных строк в таблице, но как это будет выглядеть, пока непонятно. В BOL по этому поводу – только намеки, и нет ни строчки примера или хотя бы приблизительного описания, а все попытки сделать что-то наугад к успеху не привели.

Работа с логинами и пользователями

Хранимые процедуры для создания пользователя и логина объявлены устаревшими и оставлены исключительно для обратной совместимости, на смену им пришли новые DDL операторы – CREATE USER, CREATE LOGIN, ALTER USER и ALTER LOGIN, которые предоставляют больше возможностей.

В Yukon можно применить политику логинов операционной системы к логинам SQL-сервера. При этом для контроля согласованности политик безопасности ОС и SQL-сервера используется специальное API, появившееся в Windows 2003 Server. При создании или изменении логина может быть выставлено два флага, CHECK_EXPIRATION и CHECK_POLICY, которые и определяют вмешательство ОС в политику логинов сервера.

Значение флага CHECK_EXPIRATION (по умолчанию ON) определяет, будет ли происходить проверка устаревания пароля. Установка этого флага в “OFF” означает, что проверка не производится, и пароль не устаревает.

Значение флага CHECK_POLICY (по умолчанию ON) определяет, будет ли производиться проверка стойкости пароля с использованием локальной политики ОС. Установка этого флага в “OFF” означает, что локальная политика ОС не используется, и действует внутренняя политика СУБД.

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

Если флаг CHECK_POLICY установлен в «OFF», то и CHECK_EXPIRATION так же должен быть установлен в «OFF». Если указан параметр MUST_CHANGE, то флаг CHECK_EXPIRATION должен быть установлен в «ON», а, следовательно, и CHECK_POLICY также должен быть «ON».

Естественно, все эти настройки применимы только к локальным пользователям SQL-сервера.

Например, если попытаться создать логин Vasya с простым и незатейливым паролем:

CREATE LOGIN Vasya WITH PASSWORD = 'password'

то ничего не получится, поскольку по умолчанию флаг CHECK_POLICY установлен в «ON», ОС проверяет пароль на стойкость и можно наблюдать ошибку 15118, примерно следующего содержания:

Password validation failed. The password does not meet policy requirements because it is not complex enough.

Однако если проверку политики безопасности ОС отключить, то создание логина Vasya с простым и незатейливым паролем пройдет вполне успешно:

CREATE LOGIN Vasya WITH PASSWORD = 'password', CHECK_POLICY = OFF

Схемы

По стандарту ANSI SQL под понятием схема (schema) понимается набор объектов БД, принадлежащий одному владельцу (principal) и образующий одно пространство имен (namespace). Иными словами схема – это набор объектов БД, которые не могут иметь одинаковые имена.

В предыдущих версиях SQL-сервера схема была непосредственно связана с владельцем объекта. Фактически между этими двумя понятиями для пользователя не было разницы. Каждый пользователь был владельцем схемы, и имя этой схемы совпадало с именем пользователя. Специальная команда создания схемы – CREATE SCHEMA, строго говоря, схему не создавала, а позволяла создать объект и раздать на него права одним оператором, облегчая тем самым жизнь администраторам.

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

avalon.employee.vasya.account

Таким образом, если в какой-то трагичный момент пользователя Vasya уволят, то для его удаления из базы надо либо удалить все объекты, принадлежащие ему, либо передать их во владение другому пользователю. Если передать эти объекты во владение кому-то другому, например пользователю Masha, то изменится и полное имя объекта:

avalon.employee.masha.account

Это потребует внесения изменений в клиентское приложение и дальнейшего тестирования – приятного в этом мало.

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

Более того, для этой же цели введено новое понятие синонима. Синоним создается с помощью нового оператора CREATE SYNONYM и является альтернативным именем объекта БД. Объект, на который ссылается синоним, называется «базовым объектом» (base object), и с этим базовым объектом синоним связан только по имени. Таким образом, клиентское приложение, использующее синонимы, защищено от изменения имен объектов. Кроме того, синоним, состоящий из одного слова, удобнее использовать, чем полное имя объекта, состоящего из двух, трех или четырех частей. Например, создание синонима для Employee в базе AdventureWorks для использования из Northwind выглядит примерно так:

USE Northwind GO CREATE SYNONYM MyEmployee FOR AdventureWorks.dbo.Employee

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

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

Введено также понятие «схемы по умолчанию» (default schema). Эта схема указывается при создании пользователя или логина, и если пользователь ищет объект без указания определенной схемы, то в первую очередь объект ищется в схеме по умолчанию. Если же при создании пользователя схема по умолчанию не была указана, то используется схема DBO. При создании пользователя можно также указать несуществующую схему и создать ее позднее.

Метаданные

Способ доступа к метаданным изменился кардинально. Теперь до них можно добраться через специальные представления каталога (Catalog Views), которые, по сути, являются реляционным интерфейсом метаданных. Эти представления позволяют просматривать метаданные, которые содержатся в каждой базе сервера, и практически целиком заменили собой системные таблицы и системные представления, которые использовались для работы с метаданными в предыдущих версиях.

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

Например, все объекты ранее были доступны через системную таблицу sysobjects, а теперь эта информация переехала в представление sys.objects. Sysobject теперь – тоже представление, которое делает выборку из sys.objects. Но поскольку часть информации в формате sysobjects отобразить невозможно, то даже выборка всех данных из sys.object и sysobjects вернет разное количество записей.

Системные процедуры также переписаны с использованием новых системных представлений.

Вообще с доступностью метаданных все стало гораздо строже. Теперь даже просмотреть можно только те метаданные, на которые есть соответствующие права.

Например, если создать простенькую процедуру в тестовой базе:

CREATE PROCEDURE tst_sel AS SELECT * FROM employee GO

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

SELECT * FROM sys.procedures WHERE name='tst_sel'

который в принципе должен был вернуть запись с информацией о только что созданной процедуре, не вернет ни одной записи. Если же опять вернуться к предыдущему подключению и дать пользователю Vasya права на исполнение процедуры tst_sel:

GRANT EXECUTE ON tst_sel TO vasya

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

SELECT definition FROM sys.sql_modules WHERE name = 'tst_sel'

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

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

Чтобы избежать этих проблем, а также для большей гибкости при настройке прав просмотра метаданных, в Yukon добавлено новое право – VIEW DEFINITION. Это право перекрывает правила, описанные выше. Если предоставить пользователю Vasya право VIEW DEFINITION на объект, то ему будут доступны для просмотра все метаданные этого объекта, не взирая на остальные права, если же это право явно запретить, то никакие метаданные посмотреть уже будет нельзя, опять-таки не взирая на остальные права.

Права VIEW DEFINITION могут быть применены к объектам, расположенным на разных уровнях иерархии сервера.

-- На уровне базы данных GRANT VIEW DEFINITION TO [WITH GRANT OPTION]; -- На уровне схемы GRANT VIEW DEFINITION ON SCHEMA :: TO [WITH GRANT OPTION]; -- На уровне определенного объекта схемы GRANT VIEW DEFINITION ON TO [WITH GRANT OPTION]; -- Здесь ::= | | PUBLIC ::= | | | | ...

Таким образом, если сейчас разрешить пользователю Vasya VIEW DEFINITION на уровне базы:

GRANT VIEW DEFINITION TO Vasya

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

DENY VIEW DEFINITION TO Vasya

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

SELECT * FROM sysobjects -- или SELECT * FROM sys.objects

Ни один из этих запросов не вернет ни одной записи.

Вернуть первоначальное положение вещей можно, удалив это правило.

REVOKE VIEW DEFINITION TO Vasya

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

Индексы

Индексы – это внутренний механизм сервера, позволяющий кардинально повысить скорость выполнения запросов, и без них производительность реляционных БД была бы удручающе низка. В новой версии Mcrosoft SQL Server разработчики не обошли вниманием столь ответственный участок, и в механику индексирования были внесены, некоторые усовершенствования. Естественно, изменился немного и синтаксис команды создания индекса, теперь он выглядит так:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON [ { database_name . [ schema_name ] . | schema_name . } ] {table_or_view_name} ( column [ ASC | DESC ] [ ,...n ] ) [ INCLUDE (column_name [ ,...n ] ) ] [ WITH ( [ ,...n ] ) ] [ ON {partition_scheme_name ( column_name [,...n]) | filegroup_name |default } ]

А дополнительные настройки таковы:

::= { PAD_INDEX = {ON | OFF} | FILLFACTOR = fillfactor | SORT_IN_TEMPDB = {ON | OFF} | IGNORE_DUP_KEY = {ON | OFF} | STATISTICS_NORECOMPUTE = {ON | OFF} | DROP_EXISTING = {ON | OFF} | ONLINE = {ON | OFF} | ALLOW_ROW_LOCKS = {ON | OFF} | ALLOW_PAGE_LOCKS = {ON | OFF} | MAXDOP = number_of_processors}

Прежде всего стоит обратить внимание на то, что изменился синтаксис указания дополнительных настроек. Теперь рекомендуется параметры ON или OFF указывать в обязательном порядке, а старый синтаксис, без ON/OFF, поддерживается лишь из соображений обратной совместимости. В будущих версиях от его поддержки обещают отказаться. При этом новые команды поддерживают только синтаксис с ON/OFF. Так же недопустимо смешивать два различных синтаксиса в одном операторе, например попытка создания индекса с опциями WITH (DROP_EXISTING, ONLINE = ON ) – вызовет ошибку.

Ряд настроек индекса, которые ранее задавались с помощью специальной хранимой процедуры, теперь можно указать напрямую, при создании или изменении индекса. ALLOW_ROW_LOCKS разрешает или запрещает блокировку индекса на уровне строк, а ALLOW_PAGE_LOCKS – на уровне страниц данных.