Управляющие конструкции SQL. Управление доступом в sql

Среда SQL Server Management Studio имеет два основных назначения: администрирование серверов баз данных и управление объектами баз данных. Эти функции рассматриваются далее.

Администрирование серверов баз данных

Задачи администрирования, которые можно выполнять с помощью среды SQL Server Management Studio, включают, среди прочих, следующие:

регистрация серверов;

подключение к серверу;

создание новых групп серверов;

управление множественными серверами;

пуск и остановка серверов.

Эти задачи администрирования описываются в следующих подразделах.

Регистрация серверов

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

Сервер можно зарегистрировать при первом запуске среды SQL Server Management Studio или позже. Чтобы зарегистрировать сервер базы данных, щелкните правой кнопкой требуемый сервер в обозревателе объектов и в контекстном меню выберите пункт Register. Если панель обозревателя объектов скрыта, то откройте ее, выполнив команду меню View --> Object Explorer. Откроется диалоговое окно New Server Registration (Регистрация нового сервера), как это показано на рисунке ниже:

Выберите имя сервера, который нужно зарегистрировать, и тип проверки подлинности для этого сервера (т.е. проверка подлинности Windows или проверка подлинности SQL Server), после чего нажмите кнопку Save.

Подключение к серверу

Среда SQL Server Management Studio также разделяет задачи регистрации сервера и подключения к серверу. Это означает, что при регистрации сервера автоматического подключения этого сервера не происходит. Чтобы подключиться к зарегистрированному серверу, нужно щелкнуть правой кнопкой требуемый сервер в окне инспектора объектов и в появившемся контекстном меню выбрать пункт Connect (Подключиться).

Создание новой группы серверов

Чтобы создать новую группу серверов в панели зарегистрированных серверов, щелкните правой кнопкой узел Local Server Groups (Группы локальных серверов) в окне Registered Server и в контекстном меню выберите пункт New Server Group (Создание группы серверов). В открывшемся диалоговом окне New Server Group Properties (Свойства новой группа серверов) введите однозначное имя группы и, по выбору, ее описание.

Управление множественными серверами

Посредством обозревателя объектов среда SQL Server Management Studio позволяет администрировать множественные серверы баз данных (называемые экземплярами) на одном компьютере. Каждый экземпляр компонента Database Server имеет свой собственный набор объектов баз данных (системные и пользовательские базы данных), который не разделяется между экземплярами.

Для управления сервером и его конфигурацией щелкните правой кнопкой имя сервера в обозревателе объектов и в появившемся контекстном меню выберите пункт Properties (Свойства). Откроется диалоговое окно Server Properties (Свойства сервера), содержащее несколько страниц, таких как General (Общие), Security (Безопасность), Permissions (Разрешения) и т.п.

На странице General отображаются общие свойства сервера:

Страница Security содержит информацию о режиме аутентификации сервера и методе аудита входа. На странице Permissions воспроизводятся все учетные записи и роли, которые имеют доступ к серверу. В нижней части страницы отображаются все разрешения, которые можно предоставлять этим учетным записям и ролям.

Можно изменить имя сервера, присвоив ему новое имя. Для этого щелкните правой кнопкой требуемый сервер в окне обозревателя объектов и в контекстном меню выберите пункт Register. Теперь можно присвоить серверу новое имя и изменить его описание. Серверы не следует переименовывать без особой на это надобности, поскольку это может повлиять на другие серверы, которые ссылаются на них.

Запуск и останов серверов

Сервер Database Engine по умолчанию запускается автоматически при запуске операционной системы Windows. Чтобы запустить сервер с помощью среды SQL Server Management Studio, щелкните правой кнопкой требуемый сервер в инспекторе объектов и в контекстном меню выберите пункт Start (Запустить). Это меню также содержит пункты Stop (Остановить) и Pause (Приостановить) для выполнения соответствующих действий с сервером.

Управление базами данных посредством обозревателя объектов Object Explorer

Задачи администрирования, которые можно выполнять с помощью среды SQL Server Management Studio, включают:

создание баз данных, не прибегая к использованию языка Transact-SQL;

модифицирование баз данных, не прибегая к использованию языка Transact-SQL;

управление таблицами, не прибегая к использованию языка Transact-SQL;

создание и исполнение инструкций SQL (описывается в следующей статье).

Создание баз данных без использования T-SQL

Новую базу данных можно создать посредством обозревателя объектов Object Explorer . Как можно судить по его названию, обозреватель объектов также можно использовать для исследования объектов сервера. С панели этого инструмента можно просматривать все объекты сервера и управлять сервером и базами данных. Дерево иерархии объектов сервера содержит, среди прочих папок, папку Databases (Базы данных). Эта папка, в свою очередь, содержит несколько подпапок, включая папку для системных баз данных, и по папке для каждой базы данных, созданной пользователем.

Чтобы создать базу данных посредством обозревателя объектов, щелкните правой кнопкой узел Databases и выберите пункт меню New Database (Создать базу данных). В открывшемся диалоговом окне New Database в поле Database name введите имя новой базы данных, после чего нажмите кнопку ОК.

Каждая база данных обладает несколькими свойствами, такими как тип файла, начальный размер и т.п. Список страниц свойств базы данных расположен в левой панели диалогового окна New Database. Страница General (Общие) диалогового окна Database Properties содержит, среди прочего, такую информацию, как имя, владелец и параметры сортировки базы данных:

Свойства файлов данных определенной базы данных перечисляются на странице Files (Файлы) и содержат такую информацию, как имя и начальный размер файла, расположение базы данных, а также тип файла (например, primary). База данных может храниться в нескольких файлах. В SQL Server применяется динамическое управление дисковым пространством. Это означает, что можно сконфигурировать размер базы данных для автоматического увеличения и уменьшения по мере надобности.

Чтобы изменить свойство Autogrowth (Автоувеличение) на странице Files, в столбце Autogrowth нажмите значок троеточия (...) и внесите соответствующие изменения в диалоговом окне Change Autogrowth. Чтобы позволить автоматическое увеличение размера базы данных, нужно установить флажок Enable Autogrowth . Каждый раз, когда существующий размер файла недостаточен для хранения добавляемых данных, сервер автоматически запрашивает систему выделить файлу дополнительное дисковое пространство. Объем дополнительного дискового пространства (в процентах или мегабайтах) указывается в поле File Growth (Увеличение размера файла) в том же диалоговом окне. А в разделе Maximum File Size (Максимальный размер файла) можно или ограничить максимальный размер файла, установив переключатель Limited to (MB) (Ограничение (Мбайт)), или снять ограничения на размер, установив переключатель Unlimited (Без ограничений) (это настройка по умолчанию). При ограниченном размере файла нужно указать его допустимый максимальный размер.

На странице Filegroups (Файловые группы) диалогового окна Database Properties отображаются имена файловых групп, к которым принадлежит файл базы данных, раздел файловой группы (по умолчанию или заданный явно), а также операции, разрешенные для выполнения с файловой группой (чтение и запись или только чтение).

На странице Options (Параметры) диалогового окна Database Properties можно просмотреть и модифицировать все параметры уровня базы данных. Существуют следующие группы параметров: Automatic (Автоматически), Containment (Включение), Cursor (Курсор), Miscellaneous (Вспомогательные), Recovery (Восстановление), Service Broker (Компонент Service Broker) и State (Состояние). Группа State содержит, например, следующие четыре параметра:

Позволяет установить доступ к базе данных полный доступ или доступ только для чтения. В последнем случае пользователи не могут модифицировать данные. Значение по умолчанию этого параметра - False.

Устанавливает количество пользователей, которые могут одновременно использовать базу данных. Значение по умолчанию - MULTI_USER.

Описывает состояние базы данных. Значение по умолчанию этого параметра - Normal.

Определяет режим шифрования базы данных. Значение по умолчанию этого параметра - False.

На странице Extended Properties (Расширенные свойства) отображаются дополнительные свойства текущей базы данных. На этой странице можно удалять существующие свойства и добавлять новые.

На странице Permissions (Разрешения) отображаются все пользователи, роли и соответствующие разрешения.

Остальные страницы Change Tracking (Отслеживание изменений), Mirroring (Зеркальное отображение) и Transaction Log Shipping (Доставка журналов транзакций) описывают возможности, связанные с доступностью данных.

Модифицирование баз данных

С помощью обозревателя объектов можно модифицировать существующие базы данных, изменяя файлы и файловые группы базы данных. Чтобы добавить новые файлы в базу данных, щелкните правой кнопкой требуемую базу данных и в контекстном меню выберите пункт Properties. В открывшемся диалоговом окне Database Properties выберите страницу Files и нажмите кнопку Add, расположенную внизу раздела Database files. В раздел будет добавлена новая строка, в поле Logical Name которой следует ввести имя добавляемого файла базы данных, а в других полях задать необходимые свойства этого файла. Также можно добавить и вторичную файловую группу для базы данных, выбрав страницу Filegroups (Файловые группы) и нажав кнопку Add.

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

Чтобы удалить базы данных с помощью обозревателя объектов, щелкните правой кнопкой имя требуемой базы данных и в открывшемся контекстном меню выберите пункт Delete (Удалить).

Управление таблицами

Следующей задачей после создания базы данных является создание всех необходимых таблиц. Подобно созданию базы данных, таблицы в ней также можно создать либо с помощью языка Transact-SQL, либо посредством обозревателя объектов. Как и в случае с созданием базы данных, здесь мы рассмотрим создание таблиц только с помощью обозревателя объектов.

Для практики создания таблиц, в базе данных SampleDb создадим таблицу Department. Чтобы создать таблицу базы данных с помощью обозревателя объектов, разверните в нем узел Databases, а потом узел требуемой базы данных, щелкните правой кнопкой папку Tables и в открывшемся контекстном меню выберите пункт New Table. В верхней части с правой стороны окна средства Management Studio откроется окно для создания столбцов новой таблицы. Введите имена столбцов таблицы, их типы данных и разрешение значений null для каждого столбца, как это показано в правой верхней панели на рисунке ниже:

Чтобы выбрать для столбца один из поддерживаемых системой типов данных, в столбце Data Type (Тип данных) выберите, а затем нажмите направленный вниз треугольник у правого края поля (этот треугольник появляется после того, как будет выбрана ячейка). В результате в открывшемся раскрывающемся списке выберите требуемый тип данных для столбца.

Тип данных существующего столбца можно изменить на вкладке Column Properties (Свойства столбца) (нижняя панель на рисунке). Для одних типов данных, таких как char, требуется указать длину в строке Length, а для других, таких как decimal, на вкладке Column Properties требуется указать масштаб и точность в соответствующих строках Scale (Масштаб) и Precision (Точность). Для некоторых других, таких как int, не требуется указывать ни одно из этих свойств. (Недействительные значения для конкретного типа данных выделены затененным шрифтом в списке всех возможных свойств столбца.)

Чтобы разрешить значения null для данного столбца, следует установить для него соответствующий флажок поля. Также, если для столбца требуется значение по умолчанию, его следует ввести в строку Default Value or Binding (Значение по умолчанию или привязка) панели Column Properties. Значение по умолчанию присваивается ячейке столбца автоматически, если для нее явно не введено значение.

Столбец Number является первичным ключом таблицы Department. Чтобы сделать столбец первичным ключом таблицы, щелкните его правой кнопкой и в контекстном меню выберите пункт Set Primary Key (Задать первичный ключ).

Завершив все работы по созданию таблицы, щелкните крестик вкладки конструктора таблиц. Откроется диалоговое окно с запросом, сохранить ли сделанные изменения. Нажмите кнопку Yes, после чего откроется диалоговое окно Choose Name (Выбор имени) с запросом ввести имя таблицы. Введите требуемое имя таблицы и нажмите кнопку OK. Таблица будет сохранена под указанным именем. Чтобы отобразить новую таблицу в иерархии базы данных, в панели инструментов обозревателя объектов щелкните значок Renew (Обновить).

Для просмотра и изменения свойств существующей таблицы разверните узел базы данных, содержащей требуемую таблицу, разверните узел Tables в этой базе данных и щелкните правой кнопкой требуемую таблицу, а затем в контекстном меню выберите пункт Properties. В результате для данной таблицы откроется диалоговое окно Table Properties. Для примера, на рисунке ниже показано диалоговое окно Table Properties на вкладке General для таблицы Employee базы данных SampleDb.

Чтобы переименовать таблицу, в папке Tables щелкните ее правой кнопкой в списке таблиц и в контекстном меню выберите пункт Rename. А чтобы удалить таблицу, щелкните ее правой кнопкой и выберите пункт Delete.

Создав все четыре таблицы базы данных SampleDb (Employee, Department, Project и Works_on - подробную структуру таблиц вы можете найти в исходниках), можно использовать еще одну возможность среды SQL Server Management Studio, чтобы отобразить диаграмму типа "сущность - отношение" - диаграмму (ER) (entity-relationship) этой базы данных. (Процесс преобразования таблиц базы данных в диаграмму "сущность - отношение" (ER) называется обратным проектированием.)

Чтобы создать диаграмму ER для базы данных SampleDb, щелкните правой кнопкой ее подпапку Database Diagrams (Диаграммы баз данных) и в контекстном меню выберите пункт New Database Diagram (Создать диаграмму базы данных). Если откроется диалоговое окно, в котором спрашивается, создавать ли вспомогательные объекты, выберите ответ Yes.

После этого откроется диалоговое окно Add Table, в котором нужно выбрать таблицы для добавления в диаграмму. Добавив все необходимые таблицы (в данном случае все четыре), нажмите кнопку Close, и мастер создаст диаграмму, подобную показанной на рисунке ниже:

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

Между таблицами базы данных SampleDb существует три отношения. Таблица Department имеет отношение типа 1:N с таблицей Employee, поскольку каждому значению первичного ключа таблицы Department (столбец Number) соответствует одно или более значений столбца DepartmentNumber таблицы Employee (в одном отделе может работать несколько сотрудников).

Аналогично существует отношение между таблицами Employee и Works_on, поскольку только значения, которые присутствуют в столбце первичного ключа таблицы Employee (Id) также имеются в столбце EmpId таблицы Works_on. Третье отношение существует между таблицами Project и Works_on, т.к. только значения, которые присутствуют в первичном ключе таблицы Project (Number) также присутствуют в столбце ProjectNumber таблицы Works_on.

Чтобы создать эти три отношения, диаграмму ER нужно реконструировать, указав для каждой таблицы столбцы, которые соответствуют ключевым столбцам других таблиц. Такой столбец называется внешним ключом (foreign key) . Чтобы увидеть, как это делается, определим столбец DepartmentNumber таблицы Employee, как внешний ключ таблицы Department. Для этого выполним следующие действия:

Подобным образом создаются и другие два отношения. На рисунке ниже показана диаграмма ER, отображающая все три отношения между таблицами базы данных SampleDb:

Первая версия Microsoft Servers SQL была представлена компанией в далеком 1988 году. СУБД сразу позиционировалась как реляционная имеющая, по заявлению производителя, три достоинства:

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

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

Интеграция с Интернетом позволила использовать SQL Servers 2005 как основу для создания систем электронной коммерции с простым и защищенным доступом к данным через популярные браузеры с использованием встроенного Firewall Версия Enterprise поддерживала параллельные вычисления на неограниченном количестве процессоров.

На смену версии 2005 пришел Microsoft SQL Server 2008, который до сих пор является одним из самых популярных серверов баз данных, а немного позже появилась и следующая версия - SQL Servers 2012 года, с поддержкой совместимости с.NET Framework и другими передовыми технологиями обработки информации и среды разработки Visual Studio. Для доступа к был создан специальный модуль SQL Azure.

Transact-SQL

С 1992 года SQL является стандартом доступа к базам данных. Практически все языки программирования для доступа к БД используют именно его, даже если пользователю кажется, что он работает с информацией напрямую. Базовый синтаксис языка остается неизменным для обеспечения совместимости, но каждый производитель систем управления базами данных старался пополнить SQL дополнительными функциями. Компромисса найти не удалось, и после «войны стандартов» остались два лидера: PL/SQL компании Oracle и Transact-SQL в Microsoft Servers SQL.

T-SQL процедурно расширяет SQL для доступа к Microsoft Servers SQL. Но это не исключает разработку приложений на «стандартных» операторах.

Автоматизируйте бизнес с помощью SQL Server 2008 R2

Надежная работа бизнес-приложений чрезвычайно важна для современного бизнеса. Малейший простой базы данных может привести к огромным убыткам. Сервер баз данных Microsoft SQL Server 2008 R2 позволяет надежно и безопасно хранить информацию практически неограниченного объема, используя знакомые всем администраторам средства управления. Поддерживается вертикальное масштабирование до 256 процессоров.

Технология Hyper-V максимально эффективно использует мощность современных многоядерных систем. Поддержка на одном процессоре множества виртуальных систем снижает издержки и улучшает масштабируемость.

Анализируйте данные

Для быстрого анализа в режиме реального времени потоков данных используется компонент SQL Server StreamInsight, оптимизированный под данный тип задач. Возможна разработка собственных приложений на основе.NET.

Бесперебойная работа и безопасность данных

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

Неограниченный размер базы данных

Хранилище данных можно масштабировать быстро и безопасно. Пользователи могут использовать готовые шаблоны Fast Track Date Warehouse для поддержки дисковых массивов до 48 Тб. Базовая конфигурация поддерживает оборудование ведущих фирм, таких как HP, EMC и IBM. Функции сжатия информации по стандарту UCS 2 позволяют более экономно расходовать дисковое пространство.

Повышение эффективности работы разработчиков и администраторов

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

Инструменты для персонального бизнес-анализа

В компаниях никогда не было единого мнения относительно того, кто должен заниматься аналитикой - IT-отделы или непосредственно пользователи. Система создания персональных отчетов решает эту проблему посредством современных инструментов безопасного и эффективного построения, анализа и моделирования бизнес-процессов. Поддерживается прямой доступ к базам данных в Microsoft Office и SharePoint Server. Корпоративная информация может интегрироваться с материалами других типов, таких как карты, графика и видео.

Удобная среда для совместной работы

Предоставьте своим сотрудникам доступ к информации, совместной разработке и анализу данных при помощи приложения PowerPivot для электронной таблицы Excel. Программа позволяет производить анализ информации и моделирование бизнес-процессов и публиковать отчеты для общего доступа в Интернете или системе SharePoint.

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

Работайте с базами данных бесплатно

Компания предоставляет небольшим проектам и начинающим разработчикам специальную бесплатную версию Microsoft SQL Server Express. Сюда включены те же технологии баз данных, что и у «полных» версий SQL Server.

Поддерживаются среды разработки Visual Studio и Web Developer. Создавайте сложные таблицы и запросы, разрабатывайте интернет-приложения с поддержкой баз данных, получайте прямой доступ к информации из PHP.

Пользуйтесь всеми возможностями Transact-SQL и самыми передовыми технологиями доступа к данным ADO.NET и LINQ. Поддерживаются хранимые процедуры, триггеры и функции.

Сконцентрируйтесь на элементах бизнес-логики, а оптимизацию структуры базы данных система сделает самостоятельно.

Создавайте насыщенные отчеты любой степени сложности. Пользуйтесь подсистемой поиска, интегрируйте отчеты с приложениями Microsoft Office и добавляйте в документы информацию о географическом положении.

Разрабатываемые приложения могут работать при отсутствии подключения к серверу БД. Синхронизация производится автоматически с использованием фирменной технологии транзакционной репликации Sync Framework.

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

SQL Server 2008 R2 Express Edition идеально подходит для быстрого развертывания сайтов и интернет-магазинов, программ для персонального использования, малого бизнеса. Это отличный вариант для начала работы и обучения.

Управляйте базами данных, используя SQL Server Management Studio

Microsoft SQL Server Management представляет собой специализированную среду для создания, доступа и управления базами данных и всеми элементами SQL Server, включая службы отчетов.

Система объединяет в одном интерфейсе все возможности программ администрирования из ранних версий, таких как Query Analyzer и Enterprise Manager. Администраторы получают софт с большим набором графических объектов разработки и управления, а также расширенный язык создания сценариев работы с БД.

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

Модуль «Обозреватель объектов» позволяет просматривать и управлять любыми встроенными объектами Microsoft Servers SQL на всех серверах и экземплярах баз данных. Легкий доступ к нужной информации чрезвычайно важен для быстрой разработки приложений и контроля версий.

Система построена на базе системы Visual Studio Isolated Shell, которая поддерживает расширяемые настройки и расширения сторонних разработчиков. В сети Интернет имеется множество сообществ, на которых можно найти всю необходимую информацию и примеры кода для разработки собственных инструментов управления и обработки данных.

По данным исследовательской компании Forrester Research, сервер баз данных Microsoft SQL Server 2012 вошел в тройку лидеров рынка корпоративных информационных хранилищ по итогам 2013 года. Эксперты отмечают, что быстрый рост доли рынка Microsoft обусловлен комплексным подходом корпорации к автоматизации бизнес-процессов. Microsoft SQL Server является современной платформой для управления и хранения данных любых типов, дополненной инструментами аналитики и разработки. Отдельно стоит отметить простоту интеграции с другими продуктами компании, такими как Office и SharePoint.

В стандарте SQL определены два оператора: GRANT и REVOKE пре­доставления и отмены привилегий соответственно.

Оператор предоставления привилегий имеет следующий формат:

GRANT {<список действий> | ALL PRIVILEGES }

ON <имя_объекта>

ТО {<имя_пользователя> | PUBLIC }

Здесь список действий определяет набор действий из обще допустимого перечня действий над объектом данного типа.

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

<имя_объекта> - задает имя конкретного объекта: таблицы, представления, хра­нимой процедуры, триггера.

<имя_пользователя> или PUBLIC определяет, кому предоставляются данные приви­легии.

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

Рассмотрим пример, пусть у нас существуют три пользователя с абсолютно уни­кальными именами user1, user2 и user3. Все они являются пользователями од­ной БД.

Userl создал объект Tab1, он является владельцем этого объекта и может пере­дать права на работу с эти объектом другим пользователям. Допустим, что поль­зователь user2 является оператором, который должен вводить данные в Tab1 (например, таблицу новых заказов), а пользователь user3 является менеджером отдела, который должен регулярно про­сматривать введенные данные.

Для объекта типа таблица полным допустимым перечнем действий является на­бор из четырех операций: SELECT, INSERT, DELETE, UPDATE. При этом операция об­новления может быть ограничена несколькими столбцами.

Общий формат оператора назначения привилегий для объекта типа таблица бу­дет иметь следующий синтаксис:

GRANT {[.INSERT][.DELETE][.UPDATE

(<список столбцов>)]} ON <имя_таблицы>

TO {<имя_пользователя> | PUBLIC }

В рассматриваемом случае необходимо выполнить следующие назначения:

Эти назначения означают, что пользователь user2 имеет право только вводить новые строки в отношение Tab1, а пользователь user3 имеет право просматри­вать все строки в таблице Tab1.

При назначении прав доступа на операцию модификации можно уточнить, зна­чение каких столбцов может изменять пользователь. Допустим, что менеджер отдела имеет право изменять цену на предоставляемые услуги. Предположим, что цена задается в столбце SENA таблицы Tab1. Тогда операция назначения при­вилегии пользователю user3 может измениться и выглядеть следующим образом:

GRANT SELECT, UPDATE (SENA)

Если наш пользователь user1 предполагает, что пользователь user4 может его за­мещать в случае его отсутствия, то он может предоставить этому пользователю все права по работе с созданной таблицей Tab1.

GRANT ALL PRIVILEGES

WITH GRANT OPTION

В этом случае пользователь user4 может сам назначать привилегии по работе с таблицей Tab1 в отсутствие владельца объекта пользователя user1. Поэтому в случае появления нового оператора пользователя user5 он может назначить ему права на ввод новых строк в таблицу командой:

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

GRANT SELECT, UPDATE, DELETE

WITH GRANT OPTION

то пользователь user4 не сможет передать полномочия на ввод данных пользова­телю user5, потому что эта операция не входит в список разрешенных для него самого.

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

Так как представления могут соответствовать итоговым запросам, то для этих представлений недопустимы операции изменения, и, следовательно, для таких представлений набор допустимых действий ограничивается операцией SELECT. Если же представления соответствуют выборке из базовой таблицы, то для та­кого представления допустимыми будут все 4 операции- SELECT, INSERT, UPDATE и DELETE.

Для отмены ранее назначенных привилегий в стандарте SQL определен опера­тор REVOKE. Оператор отмены привилегий имеет следующий синтаксис:

REVOKE {<список операций>| ALL PRIVILEGES}

ON <имя_объекта>

FROM {<список пользователей | PUBLIC }

(CASCADE | RESTRICT }

Параметры CASCADE или RESTRICT определяют, каким образом должна произво­диться отмена привилегий. Параметр CASCADE отменяет привилегии не только пользователя, который непосредственно упоминался в операторе GRANT при пре­доставлении ему привилегий, но и всем пользователям, которым этот пользова­тель присвоил привилегии, воспользовавшись параметром WITH GRANT OPTION.

Например, при использовании операции

REVOKE ALL PRIVILEGES

TO user4 CASCADE

будут отменены привилегии и пользователя user5, которому пользователь user4 успел присвоить привилегии.

Параметр RESTRICT ограничивает отмену привилегий только пользователю, не­посредственно упомянутому в операторе REVOKE. Но при наличии делегирован­ных привилегий этот оператор не будет выполнен.Так, например, операция:

REVOKE ALL PRIVILEGES

TO user4 RESTRICT

не будет выполнена, потому что пользователь user4 передал часть своих полно­мочий пользователю user5.

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

Поэтому корректным будет следующее использование оператора REVOKE

TO user2, user4 CASCADE

При работе с другими объектами изменяется список операций, которые исполь­зуются в операторах GRANT и REVOKE.

По умолчанию действие, соответствующее запуску (исполнению) хранимой про­цедуры, назначается всем членам группы PUBLIC.

Если необходимо изменить это условие, то после создания хранимой процедуры необходимо записать оператор REVOKE

TO PUBLIC CASCADE

И теперь можно назначить новые права пользователю user4

Системный администратор может разрешить некоторому пользователю созда­вать и изменять таблицы в некоторой БД. Тогда он может записать оператор предоставления прав следующим образом:

GRANT CREATE TABLE ALTER TABLE DROP TABLE

В этом случае пользователь user1 может создавать, изменять или удалять табли­цы в БД DB_LIB, однако он не может разрешить создавать или изменять таблицы в этой БД другим пользователям, потому что ему дано разрешение без права де­легирования своих возможностей.

В некоторых СУБД пользователь может получить права создавать БД. Напри­мер, в MS SQL Server системный администратор может предоставить пользова­телю main_user право на создание своей БД на данном сервере. Это может быть сделано следующей командой:

GRANT CREATE DATABASE

По принципу иерархии пользователь main_user, создав свою БД, теперь может предоставить права на создание или изменение любых объектов в этой БД дру­гим пользователям.

В СУБД, которые поддерживают однобазовую архитектуру, такие разрешения недопустимы. Например, в СУБД Oracle на сервере создается только одна БД, но пользователи могут работать на уровне подсхемы (части таблиц БД и свя­занных с ними объектов). Поэтому там вводится понятие системных привиле­гий. Их очень много, 80 различных привилегий.

Для того чтобы разрешить пользователю создавать объекты внутри этой БД, используется понятие системной привилегии, которая может быть назначена од­ному или нескольким пользователям. Они выдаются только на действия и кон­кретный тип объекта. Поэтому если системный администратор предо­ставил пользователю право создания таблиц (CREATE TABLE), то для того чтобы он мог создать триггер для таблицы, ему необходимо предоставить еще одну системную привилегию CREATE TRIGGER. Система защиты в Oracle считается од­ной из самых мощных, но это имеет и обратную сторону - она весьма сложная. Поэтому задача администрирования в Oracle требует хорошего знания как се­мантики принципов поддержки прав доступа, так и физической реализации этих возможностей.

Rick Dobson, Ph.D. Выборочный перевод Леденева С. А.

Эта статья рассказывает о том, как управлять безопасностью SQL сервера, используя Access совместно с библиотекой SQL Distributed Management Objects (SQL-DMO) в Visual Basic for Applications (VBA)

Вступление

Эта статья в основном ориентирована на разработчиков ADP проектов. Возможно, Вы знакомы с 2-мя вещами относительно безопасности SQL сервера. Во-первых, безопасность - это не опция, как это было в Jet. Во-вторых, модель безопасности SQL сервера отличается от модели безопасности Access. Даже если у Вас имеются приблизительные понятия о безопасности SQL сервера, эта статья научит Вас, как настроить ее для защиты ресурсов вашего проекта.

Эта статья также может быть полезна администраторам SQL серверов. Решения, предлагаемые здесь, могут помочь им в управлении безопасностью сервера средствами Access. Из содержания этой статьи администраторы могут узнать об управлении безопасностью сервера без использования графических средств Enterprise Manager или запуска Transact SQL (T-SQL) скриптов из Query Analyzer. Изучив, как управлять безопасностью сервера, используя Access, администраторы получат альтернативу создания более быстрого по исполнению решения.

Cтатью можно условно разделить на две части о безопасности SQL Server 7.0 и SQL Server 2000. В начале рассматривается концепция безопасности SQL сервера, акцентируя внимание на том, как управлять безопасностью, используя Access. Этих сведений достаточно для разработки многопользовательских приложений с разными группами пользователей, имеющих четко определенные права на использование объектов сервера. Вторая часть статьи демонстрирует программные решения, основанные на управлении объектами SQL-DMO (SQL Distributed Management Objects) средствами Microsoft Visual Basic® for Applications (VBA). Поскольку SQL-DMO имеет иерархическую модель построения объектов, очень похожую на модель построения объектов Microsoft Office, то все, что необходимо, это больше узнать об объектах, свойствах, методах, и событиях SQL-DMO относительно безопасности. Ко всему прочему, программистам Access 2002 не обойтись без программирования функций настройки безопасности в VBA, поскольку они недоступны из меню Безопасность базы данных (Database Security).

Концепция безопасности SQL сервера

Этот раздел состоит из 4-х основных частей. Ознакомление начинается с описания аутентификации - процесса проверки пользователя на право доступа к базе данных сервера. Используя Enterprise Manager, SQL сервер предоставляет два вида аутентификации. Первая часть раздела показывает различия и сходства между ними.

Далее говорится о понятии авторизованного пользователя, служащего для осуществления соединения с сервером. Авторизованные пользователи называются логинами (logins). Два типа логинов соответствуют двум моделям аутентификации SQL сервера. Логины отвечают за права доступа к серверу.

Третья часть знакомит с понятием пользователей (users). SQL сервер резервирует понятие users, как понятие, характеризующее пользователей баз данных. Обычно на сервере существует несколько логинов, каждый логин ассоциируется с несколькими пользователями - одним на каждую базу данных, доступ к которой разрешен для данного логина. Логины и пользователи могут принадлежать фиксированным ролям, которые описывают набор разрешений. Вы можете управлять поведением логинов и пользователей, управляя их принадлежностью фиксированным ролям сервера и фиксированным ролям базы данных соответственно.

В четвертой части говорится об определяемых пользователем (user-defined) ролях баз данных.

Аутентификация

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

SQL сервер поддерживает два вида аутентификации: SQL Server и Microsoft Windows® (или Windows NT® с SQL Server 7.0). Эти два вида определяют, кто осуществляет проверку логина: SQL сервер или Windows. При Windows аутентификации пользователи имеют доверительные учетные записи для доступа к SQL серверу. Эти учетные записи проверяет Windows, но SQL сервер "знает" имя учетной записи. Windows аутентификация дает пользователям возможность авторизоваться в Windows и на SQL сервере вводом только одного пароля. При аутентификации SQL Server SQL сервер сам осуществляет авторизацию. При любом типе аутентификации SQL сервер должен знать авторизованные логины.

Есть плюсы и минусы и у первого и у второго типа, однако предпочтительней Windows аутентификация. В Windows осуществляется более сложная процедура авторизации, и она освобождает администратора SQL сервера от необходимости управлять учетными записями. С другой стороны, не все операционные системы, поддерживающие SQL сервер, поддерживают Windows авторизацию. Например, Microsoft Data Engine (MSDE), установленный с Microsoft Office 2000 и Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) установленный с Office XP: и тот и другой вариант могут работать в системе Windows 98, которая не поддерживает Windows аутентификацию. Или SQL Server 7.0 и MSDE, работающие в системе Windows 95. Некоторые организации могут предпочесть возможность контролировать доступ к ресурсам базы данных на уровне администратора БД вместо более глобального уровня администратора Windows.

Любой экземпляр SQL сервера может иметь любой из двух типов аутентификации, которые можно установить в Enterprise Manager (я расскажу о третьем типе далее в этой статье). Документация SQL сервера определяет термин смешанного (mixed) режима подключения к SQL серверу, который поддерживает и Windows, и SQL Server аутентификацию. Если сервер поддерживает только Windows аутентификацию, тогда сервер имеет Windows (или Windows NT) тип аутентификации. По умолчанию MSDE устанавливается со смешанным типом аутентификации. MSDE 2000, напротив, по умолчанию устанавливается с Windows аутентификацией. Разработчики и администраторы, использующие Enterprise Manager, могут изменять тип аутентификации, используя графические средства. Так же любое приложение может использовать библиотеку SQL-DMO для изменения типа аутентификации сервера.

Логины и фиксированные серверные роли

Проект ADP содержит окно базы данных (Database window) похожее на традиционное для Jet решений. Однако проект ADP соединяется с сервером через OLE DB соединение в отличие от базы данных Jet. В проекте ADP тип логина указывается в диалоговом окне Параметры соединения (Data Link Properties). Для вызова этого диалога: из окна базы данных выберите меню Файл (File), в меню выберите Соединение (Connection).

Диалоговое окно Параметры соединения позволяет выбрать сервер баз данных, логин, и базу данных, с которой проект соединится через OLE DB. Подключаться к серверу можно двумя способами. При первом выбирается использование внутренней проверки безопасности Windows NT (use Windows NT integrated security). Это называется Windows аутентификацией. Выбрав эту опцию, не нужно указывать логин и пароль. Это потому, что Windows авторизует пользователя во время запуска Windows. Когда выбрана опция использования внутренней безопасности, проект ADP посылает логин Windows на SQL сервер при попытке создать с ним OLE DB соединение. Выбрав вторую опцию (use a specific user name and password), необходимо указать логин SQL сервера. В поле имени пользователя (user name) должно быть введено стандартное имя логина SQL сервера - логина, поддерживаемого данным SQL сервером. Пароль опционален, однако строго рекомендуется использовать пароль, поскольку совместно с логином, пароль предоставляет дополнительный уровень безопасности.

Хотя ввод логина является ключевым моментом при создании OLE DB соединения в проекте ADP, логин сам по себе не является достаточным для предоставления прав на выполнение каких-либо задач на сервере или с базой данных. Существует понятие членства логина в фиксированных серверных ролях, которое позволяет проекту ADP выполнять некоторые серверные функции, такие как, создание новых баз данных и управление логинами. В зависимости от версии SQL сервера, к которому подключается ADP проект, существует семь или восемь фиксированных серверных ролей. SQL Server 7.0 и MSDE предоставляют семь фиксированных серверных ролей, а SQL Server 2000 и MSDE 2000 - восемь. SQL Server Books Online (BOL) содержит исчерпывающую документацию о фиксированных серверных ролях, включая T-SQL выражения для назначения и удаления логинов из членства в ролях. К примеру, раздел Roles в BOL, рассказывает о фиксированных серверных полях и соответствующем наборе разрешений для доступа к базам данных, таких как просмотр и запись в таблицы.

Следующая таблица содержит краткий обзор имен фиксированных серверных ролей и краткое их описание. Выполнив системную хранимую процедуру sp_helpsrvrole можно получить список имен фиксированных серверных ролей. Исполнение системной хранимой процедуры sp_srvrolepermission выводит подробный перечень функций для каждой фиксированной серверной роли. Есть различия между фиксированными серверными ролями в 7.0 и 2000 версиях SQL сервера. Например, bulkadmin является новой ролью в SQL Server 2000. Дополнительно, выражение DROP DATABASE было доступно только для роли sysadmin в SQL Server 7.0, а SQL Server 2000 дает право на выполнение этой процедуры также и членам роли dbcreator.

Логин sa является специальным логином SQL сервера. Этот логин входит в группу sysadmin и предоставляет право на выполнение любых функций на сервере. SQL сервер создает этот логин на этапе установки и его нельзя удалить. Сразу после окончания установки логин sa не имеет пароля. Необходимо задать пароль для sa для обеспечения безопасности Вашего сервера баз данных, особенно для серверов со смешанным типом аутентификации. Помните: сервера с Windows аутентификацией не принимают и не обрабатывают логины SQL сервера.

При установке SQL сервера на Windows 98 или Windows ME сервер всегда устанавливается со смешанным типом аутентификации, потому он может принимать логины SQL сервера. Типы аутентификации по умолчанию различаются для SQL Server 7.0 и MSDE от SQL Server 2000 и MSDE 2000, устанавливаемых на Windows 2000 и Windows NT. Для SQL Server 7.0 и MSDE процесс установки по умолчанию устанавливает сервер со смешанным типом аутентификации. Напротив, SQL Server 2000 и MSDE 2000 по умолчанию устанавливаются с Windows аутентификацией. Кроме того, процесс установки версии 2000 назначает членов группы администраторов Windows членами фиксированной серверной роли sysadmin. Потому эти логины подобны логину sa, который имеет полный контроль над сервером.

Пользователи и фиксированные роли баз данных

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

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

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

Видимость объектов в Окне базы данных проекта ADP зависит от того, принадлежит ли логин проекта фиксированной серверной роли sysadmin. Если логин проекта ADP является членом роли sysadmin, в Окне базы данных показываются все объекты, владельцем которых является пользователь dbo, без круглых скобок после их имен. Для пользователей dbo все объекты, владельцами которых являются другие пользователи, кроме dbo, показываются с именем пользователя, заключенного в круглые скобки после имени объекта базы данных. В случае если логин проекта ADP не принадлежит роли sysadmin, имена объектов, владельцем которых является этот пользователь, показываются в окне базы данных без круглых скобок. Имена объектов, владелец которых пользователь dbo показываются с суффиксом (dbo). Объекты, владельцы которых не являются пользователями логина проекта ADP или пользователя dbo, не показываются в Окне базы данных.

Большинство логинов имеют пользователей для каждой базы данных, к которой они имеют доступ. Однако логин может получить доступ к базе данных даже без пользователя в этой базе данных, если база данных имеет специального пользователя. Имя этого пользователя - guest, это пользователь для логинов без учетных записей в базе данных. Пользователь guest не ассоциирован ни с каким логином. Вы можете назначать разрешения для пользователя guest так же как и для любого другого пользователя базы данных. Это позволяет настраивать права для логинов не имеющих специфических пользователей в конкретной базе данных.

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

Один из наиболее быстрых и простых способов предоставления пользователю прав на выполнение функций в базе данных это назначение пользователя членом фиксированных ролей баз данных. Существует девять фиксированных ролей баз данных на SQL сервере, они одинаковы для SQL Server 7.0 и SQL Server 2000. Имена и краткое описание фиксированных ролей баз данных приведены в следующей таблице. Выполнив системную хранимую процедуру sp_helpdbfixedrole можно получить список имен фиксированных ролей баз данных. Вызов системной хранимой процедуры sp_dbfixedrolepermission вернет табличный набор со специфическими разрешениями для каждой фиксированной роли базы данных.

Назначение пользователям фиксированных ролей базы данных сказывается на функциях, которые пользователи смогут выполнять. Есть возможность изменять влияние членства пользователя в фиксированной роли баз данных назначением разрешений пользователю на конкретные объекты базы данных. Для простоты, в этом разделе об этом не говорится, однако следующий раздел посвящен назначению разрешений на конкретные объекты и определенным пользователем (user-defined) ролям базы данных. В Окне базы данных не показываются никакие таблицы, пока пользователь для логина проекта ADP не является членом фиксированной роли базы данных db_datareader. Пользователь без членства в роли db_backupoperator не может выполнять команды меню: Архивировать (Backup) или Восстановить (Restore) (меню Сервис (Tools), подменю Служебные программы (Database Utilities)). Соответственно, не могут создавать таблицы или другие объекты в базе данных пользователи без членства в фиксированной роли базы данных db_ddladmin. Средствами Окна базы данных, пользователь без членства в роли db_ddladmin может редактировать объекты сервера, такие как таблицы и представления, или создавать новые, однако, отредактированные и созданные объекты не будут сохранены в базе данных.

Члены фиксированной роли db_owner базы данных могут выполнять все функции предоставляемые членством в любой другой роли базы данных. Так как логины этой роли могут создавать базы данных без членства в фиксированной серверной роли sysadmin, члены группы db_owner не обязаны быть пользователями dbo. В дальнейшем, члены фиксированной серверной роли sysadmin могут выполнять любые функции в базе данных, которые может выполнить член группы db_owner. Дополнительно, таблицы и представления, созданные пользователем dbo, обладают улучшенной видимостью по сравнению с таблицами и представлениями, созданным членами роли db_owner, которые в то же время не являются членами фиксированной серверной роли sysadmin.

Определяемые пользователем (User-defined) роли базы данных и назначение разрешений

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

Шесть имен разрешений на объекты и их описание показано в следующей таблице. Вы можете назначать эти разрешения конкретным объектам базы данных. Если Вы хотите раздать разрешения на все объекты, Вам придется назначать разрешение для каждого объекта базы данных. Однако Вам лучше использовать фиксированные роли базы данных в случае, если необходимо дать разрешение на все объекты базы данных. Например, если Вы хотите дать разрешение на SELECT для всех таблиц и представлений (включая те, что еще не созданы), проще включить пользователя в фиксированную роль базы данных db_datareader. Если Ваша цель - ограничить разрешение на SELECT только для одного или двух существующих представлений, тогда создайте определяемую пользователем роль базы данных, дайте разрешение на SELECT для этих представлений, и включите в эту роль тех пользователей, которым нужно дать специальные разрешения.

Разрешения на конкретные объекты базы данных могут иметь три состояния (статуса): разрешено (granted), запрещено (denied), и отменено (revoked). Если Вы даете доступ к объекту, значение разрешения будет иметь статус granted. Соответственно, если Вы запрещаете доступ, разрешение будет denied. Если вы отменяете разрешение, поменяйте разрешения с granted или denied на revoked. Отмененные разрешения не разрешают и не запрещают доступ к объекту.

Поскольку допускается включать пользователя в несколько фиксированных и определяемых пользователем ролей базы данных, возможен конфликт разрешений между ролями. Вы можете использовать эти конфликты для тонкой настройки Вашей системы безопасности. Например, пользователь включен в фиксированную роль базы данных db_datareader, дающую ему право выполнять запросы SELECT из всех таблиц и представлений базы данных. Однако Вы можете запретить чтение из таблицы продаж пользователю, имеющему разрешение чтения данных из всех таблиц, чтобы скрыть от него конфиденциальную информацию в таблице продаж. Соответственно, члены фиксированной роли базы данных db_denydatareader не могут адресовать запросы SELECT к любой таблицы базы данных, даже если пользователи включены в определяемую пользователем роль базы данных, которая дает разрешение на чтение из некоторых конкретных таблиц. Разрешение denied всегда переопределяет разрешение granted.

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

Программирование безопасности SQL Server

SQL-DMO является контейнерным приложением (Automation application), назначение которого - администрирование SQL Server. Поскольку SQL-DMO является API для SQL Server Enterprise Manager, Вы можете программировать на SQL-DMO все, что могут предоставить Вам графические средства Enterprise Manager, включая управление всеми аспектами безопасности SQL Server. Этот аспект SQL-DMO особенно полезен для проектов, использующих MSDE и MSDE 2000, потому что в их состав не входит Enterprise Manager. Дополнительно, программистам Access 2002 необходимо изучить некоторые программные решения по управлению безопасностью, поскольку Access 2002 не имеет возможности управлять безопасностью с помощью меню (как это было в Access 2000).

В Access Вы можете программировать, используя SQL-DMO так же, как используются любые другие COM объекты. Ваш VBA проект должен при этом использовать библиотеку SQL-DMO. Microsoft SQLDMO Object Library - имя этой объектной библиотеки. Добавьте ссылку на библиотеку с помощью команды References (меню Tools). Файл DLL с библиотекой включен в MSDE и MSDE 2000. Другие версии SQL Server содержат DLL и файл справки, который Вы можете открыть прямо из окна Visual Basic Editor (VBE). Использующие Microsoft Office XP Developer Edition, могут установить версию SQL сервера, которая включает в себя файл справки по SQL-DMO.

На рисунке представлен фрагмент иерархической модели SQL-DMO с объектами для примеров кода, расположенных далее по тексту. Обратите внимание, что объект SQLServer расположен во главе иерархии. Многие приложения могут соединяться с SQL Server посредством объекта SQLServer. Наследники объекта SQLServer являются коллекциями объектов для логинов и баз данных, а также для индивидуальных объектов, таких как, IntegratedSecurity, которые предоставляют возможность управления безопасностью SQL Server. Последующий пример использует свойство SecurityMode объекта IntegratedSecurity для демонстрации того, как установить режим аутентификации SQL Server. Членами коллекции Databases являются индивидуальные базы данных, каждая из которых имеет собственные коллекции и объекты. Одна из этих коллекций - коллекция Users. Другая коллекция используется для фиксированных и определяемых пользователем ролей базы данных (DatabaseRoles). Членство отдельных пользователей в коллекции DatabaseRoles определяет способность пользователей читать и модифицировать объекты коллекций Tables и Views.

Рисунок. Фрагмент объектной модели SQL-DMO, относящийся к безопасности SQL Server

Перед началом рассказа об SQL-DMO важно знать, что DLL для SQL-DMO отличается для SQL Server 7 и SQL Server 2000. Две версии SQL-DMO различаются как минимум в двух аспектах. Во-первых, версия для SQL Server 2000 включает в себя новые объекты, появившиеся в SQL Server 2000, а также улучшенные традиционные. Также версия SQL-DMO для SQL Server 2000 дополнительно содержит традиционные объекты для управления SQL Server 7.0. Имена объектов для 2000 обычно оканчиваются 2 (например, SQLServer2 для новых объектов, вместо SQLServer для традиционных). Во-вторых, программами, использующими SQL-DMO для SQL Server 2000 нельзя управлять SQL Server 7.0 (даже если программы используют традиционные имена объектов SQL-DMO). Однако, используя библиотеку SQL-DMO для SQL Server 7.0 можно управлять SQL Server 2000. Это ограничение является результатом изменения файловых форматов библиотек при переходе к новой версии; синтаксис скрипта SQL-DMO не претерпел изменений.

Различие между версиями SQL-DMO требует внимательного отношения к тому, какую версию библиотеки Вы будете использовать в Вашем проекте. Если необходимо, чтобы приложение SQL-DMO работало с серверами SQL Server 7.0 и SQL Server 2000, необходимо писать программы, используя SQL-DMO для SQL Server 7.0. С другой стороны, если необходимо использовать преимущество SQL Server 2000, тогда Вам необходимо разрабатывать Ваше приложение, используя библиотеку для SQL Server 2000. Поскольку статья демонстрирует технику программирования безопасности, используя SQL-DMO, я использовал версию SQL-DMO для SQL Server 7.0. В любом случае, код выглядит одинаковым для разных версий, кроме случаев, когда Вы используете новые объекты, представленные в SQL Server 2000.

Подключение к SQL серверу

Подобно тому, как вы можете подключиться к SQL Server двумя способами, используя диалог Свойства соединения в проекте ADP, Вы можете подключиться к SQL Server двумя способами через SQL-DMO. Один способ соответствует аутентификации средствами SQL Server. Используя этот способ, Ваш код должен посылать имя сервера, логин, и пароль через SQL-DMO на сервер. Вы можете использовать параметр "имя сервера" для указания различных экземпляров SQL Server на локальной рабочей станции или на другой рабочей станции в сети. SQL-DMO также позволяет Вам соединяться, указав только имя сервера. В этом случае, SQL-DMO посылает идентификатор авторизованного в Windows пользователя на экземпляр SQL сервера. Для того чтобы использовать этот способ, следует установить свойство LoginSecure сервера в значение True.

На следующем листинге показана пара процедур, демонстрирующих синтаксис подключения к экземпляру SQL Server, используя логин SQL Server. Первая процедура определяет три строковых параметра для имени сервера (srvname), логина (suid) и пароля (pwd). Далее она посылает их второй процедуре, которая начинается инициализацией объекта SQLServer. Этот объект представляет экземпляр сервера. Затем вторая процедура вызывает метод Connect объекта SQLServer. Этот метод принимает на вход три параметра. Демонстрируется синтаксис передачи переменных имени сервера, логина и пароля.

Изменение режима аутентификации

Одним из основных преимуществ, которое дает SQL-DMO программистам, работающим с MSDE и MSDE 2000, является реализация этой возможности, которая иначе была бы им недоступна. Вызвано это тем, что Enterprise Manager не включен в установочный пакет MSDE или MSDE 2000. К примеру, клиентский компонент SQL Server Enterprise Manager дает возможность администраторам графическими средствами изменить режим аутентификации сервера:Windows аутентификация или смешанный режим. Проект ADP не предоставляет такой возможности. Однако следующая пара процедур позволит Вам изменить режим аутентификации сервера, даже не имея возможности использовать Enterprise Manager.

Первая процедура определяет значение одного параметра и посылает его второй процедуре. Этот параметр определяет один из двух режимов аутентификации. В комментариях к процедуре показаны имена двух предопределенных констант в соответствии с режимом аутентификации. Смешанный режим аутентификации является режимом по умолчанию. SQL-DMO позволяет установить режим аутентификации, который недоступен даже в Enterprise Manager. Другими словами, Вы можете использовать SQL-DMO для указания серверу принимать только логины SQL Server.

Вторая процедура выполняет подключение к серверу с использованием идентификатора пользователя Windows. Затем она устанавливает свойство SecurityMode объекта IntegratedSecurity для сервера в значение параметра, переданного ей из первой процедуры. Если значение этого свойства меняет режим аутентификации, режим не сменится, пока Вы не остановите и не перезапустите сервер. Однако вызов метода Stop объекта сервера не может моментально остановить сервер. Вы должны подождать, пока сервер не остановится. При помощи свойства Status объекта сервера Ваша программа может следить за сообщением, что сервер остановился. Процедура использует цикл, ожидая смены значения Status на SQLDMOSvc_Stopped. Далее процедура выполняет метод Start объекта SQLServer. Вызов этого метода установит новый режим аутентификации для сервера.

Открытие проекта ADP

Иногда бывает необходимо из Вашего приложения SQL-DMO программно открыть проект ADP. Технология открытия проекта ADP не требует использования SQL-DMO, однако, Ваше приложение SQL-DMO может использовать преимущество такой возможности. В последнем примере кода этой статьи (прямо перед заключительным разделом) демонстрируется процедура создания нового пользователя. Необходимость открытия ADP проекта основана на генерации строки соединения при установке приложения, основанного на ADP проекте.

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

Перед описанием второй процедуры было бы полезным объяснить некоторые особенности кода. Во-первых, процедура, открывающая проект ADP уже работает в сессии Access. Поэтому необходимо создать новый экземпляр Access для проекта, который мы намерены открыть. Для создания сессии Access используется OLE класс.

Во-вторых, любой существующий проект ADP всегда открывается с сохраненными во время предыдущего открытия параметрами соединения с сервером. Поэтому программа должна открыть проект, используя старые параметры подключения, а потом эти параметры сменить. Новые параметры подключения можно назначить только открытому проекту. После закрытия проекта новые параметры соединения сохраняются, пока пользователь не изменит их в диалоге Параметры соединения. (Однако объектная модель офиса позволяет Вам скрыть и сделать недоступной команду меню Соединение в меню Файл).

В-третьих, сессия с открытым проектом ADP будет жить, пока живет переменная, представляющая эту сессию. Используйте описание Public в определении сессии для открываемого проекта. Объявляется переменная в разделе описаний модуля. Это позволит сессии жить, пока живет открывающий ее проект, то есть наш код. Для переменной сессии Access в примере используется имя appAccess, и следующее объявление в разделе описаний стандартного модуля. Поскольку объявление должно быть в верхней части модуля, Вы его не увидите в коде ни для первой, ни для второй процедуры.

Public appAccess as Access.Application

Вторая процедура - OpenADPWindowsOrSQLServer - начинается выводом сообщения, спрашивающим оставить ли сессию открытой после завершения процедуры. Если пользователь ответит "нет", процедура установит параметры соединения для проекта, а затем закроет сессию без шанса посмотреть изменения. Далее проект запускает новую сессию Access при помощи функции CreateObject. Аргументы этой функции различаются в зависимости от версии Access, которую Вы используете (в коде описаны особенности использования версий Access 2000 или Access 2002). После создания сессии для проекта процедура вызывает метод OpenAccessProject для добавления проекта ADP в сессию. Аргументом для этого метода служит путь вместе с именем файла проекта. Расширение.adp опционально. После открытия проекта процедура переопределяет его параметры соединения при помощи метода OpenConnection объекта CurrentProject. Процедура переопределяет соединения, используя две различные строки соединения в зависимости от значения переменной bolWindowsLogin. Эта Логическая переменная устанавливается первой процедурой для указания того, какой тип авторизации требуется установить для проекта. На последнем шаге процедуры сессия закрывается, если пользователь ответил положительно на вопрос в окне сообщения перед открытием сессии.

Добавление и удаление логинов

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

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

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

Вторая процедура после соединения с сервером инициализирует объект login. Этот объект - член коллекции Logins, показанной на рисунке. Далее процедура устанавливает три параметра для логина: его имя (login_name), базу данных по умолчанию (defaulf_db_name) и пароль (password). Операции по простому присвоению работают с двумя первыми аргументами. Для установки пароля для логина SQL сервера вызывается метод SetPassword объекта login. При установке пароля для нового логина используйте пустую строку в качестве первого аргумента метода; укажите пароль для нового логина вторым аргументом. В нашем примере пароль для логина login_name определен как "password" (без кавычек). Добавление логина login_name завершается вызовом метода Add коллекции Logins.

Также Вы можете использовать метод SetPassword для переопределения пароля существующего логина. Первый аргумент метода SetPassword в этом случае - текущий пароль. Однако если Вы не знаете текущего пароля, Вы можете указывать первым аргументом пустую строку-так же, как при создании пароля для нового логина. Создание или изменение логина возможно в том случае, если Ваш текущий логин (под которым Вы осуществили соединение с сервером) входит в одну из фиксированных ролей сервера: sysadmin или securityadmin. Неважно, каким способом Вы меняете пароль: программно или другими средствами. В примере используется логин sa, входящий в фиксированную роль сервера sysadmin.

В нескольких следующих строках второй процедуры создается логин для группы Windows group с именем msdn_OS_users. В моем случае эта группа Windows из трех идентификаторов пользователей Windows, в действительности группа Windows могла быть как группой пользователей локального компьютера, так и домена сервера Windows. Процесс создания логина начинается с инициализации нового объекта login (lgn1). При попытке повторного использования lgn1 без переинициализации (обнуления), возникнет ошибка выполнения, напоминающая о том, что требуется новый объект. Процедура использует название группы Windows, состоящее из двух частей. Первая часть - имя сервера Windows, вторая - имя группы сервера Windows. Обратный слэш служит разделителем. Базой данных по умолчанию для второго логина снова указывается база данных your_db_name. Поскольку второй логин создается для аутентификации Windows, нет необходимости устанавливать пароль. Однако необходимо установить свойство Type в SQLDMOLogin_NTGroup. Это значение соответствует внутренней константе, соответствующей логину группы Windows. При добавлении логина SQL Server не нужно устанавливать значение свойства Type, поскольку оно задано по умолчанию для логинов SQL сервера. Заканчивается создание второго логина вызовом метода Add коллекции Logins.

После добавления двух новых логинов процедура печатает имена и типы всех логинов сервера. Свойство Type соответствует значению внутренних констант. Чтобы вывод типов на экран был читаемым, используется функция, преобразовывающая константы в соответствующие текстовые названия.

После печати членов коллекции Logins процедура удаляет два вновь созданных логина поименно: login_name и YOUR_SERVER_NAME\msdn_OS_users. Этот пример демонстрирует использование коллекции Logins ее метода Remove и свойства Name логина, которого Вы хотите удалить. Для подтверждения удаления двух логинов процедура выводит список логинов повторно из коллекции Logins. Откройте окно Immediate и сравните два списка логинов: после добавления и удаления двух логинов.

Создание логина с пользователем - участником роли db_datareader

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

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

После соединения с сервером и создания нового логина login_name, код создает нового пользователя. Далее назначаются свойства Name и Login для пользователя. Свойство Login означает логин, под которым пользователь соединяется - login_name в нашем примере. После этого пользователь готов к добавлению в коллекцию Users базы данных. Отметим, что синтаксис добавления нового пользователя соответствует синтаксису иерархии объектов. Метод Add для нового пользователя применяется к коллекции Users принадлежащей в свою очередь объекту database. Database - член коллекции Databases принадлежащей объекту сервера (SQLserver). После создания пользователя требуется одна или несколько строчек для внесения пользователя в фиксированную роль базы данных db_datareader. Метод по добавлению пользователя в роль называется AddMember. В нашем случае метод AddMember элемента коллекции DatabaseRoles добавляет пользователя в роль базы данных. Этот метод берет в качестве параметра строковое выражение свойства Name объекта нового пользователя.

Как только настройка безопасности нового логина и пользователя завершены, вызывается процедура OpenADPWindowsOrSQLServer. В предыдущих листингах есть пример использования этой процедуры. В нашем случае открывается файл msdn_test_security.adp, основываясь на логине login_name. Для проверки работоспособности логина откройте таблицы базы данных your_db_name. Дополнительно, работоспособность нового логина можно проверить в диалоговом окне Параметры соединения, указав логин your_login.

Если попытаться повторно запустить процедуру MakeLoginWithDatareaderUser попытка закончится ошибкой. Это потому, что логин your_login уже существует. Ошибка возникнет при попытке повторно его добавить. Попытка запустить процедуру может закончиться неудачей и по другой причине: Вы не закрыли проект, подключенный к БД с этим логином. Вы можете написать стандартную обработку ошибок VBA для обработки ошибок времени выполнения. Подобные ошибки могут сопровождать выполнение предыдущих примеров. Я предпочел не углубляться в обсуждение обнаружения ошибок времени выполнения, акцентируя внимание на вопросах безопасности SQL сервера.

Заключение

Безопасность SQL Server построена на принципах, отличных от тех, что использовали программисты в базах данных Jet. Тем не менее, SQL Server Books Online тщательно документирует правила по безопасности SQL Server. Эта статья переводит основные правила BOL в специфические правила, цель которых использовать проект Access с SQL Server. Примеры кода, приведенные в этой статье, демонстрируют, как управлять безопасностью SQL Server с помощью Access.

С выходом SQL Server 2005 разработчики и администраторы баз данных (DBA) получат целый комплект новых средств управления. Версия SQL Server 2005 отличается не только набором добавленных в нее новых служб; при разработке этого продукта создание новых инструментов управления для DBA тоже было одной из важнейших задач. На смену SQL Server 2000 Enterprise Manager и Query Analyzer пришел новый программный инструмент - SQL Server Management Studio. Еще необходимо упомянуть о двух новых вспомогательных средствах: SQL Server Configuration Manager и Surface Area Configuration tool. Прежде чем приступать к рассмотрению возможностей SQL Server Management Studio, давайте познакомимся с каждым из этих инструментов.

Средства управления конфигурацией

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

SQL Server Configuration Manager. Этот инструмент помогает управлять учетными записями различных служб SQL Server 2005 на сервере, а также самими этими службами. Непосредственно из SQL Server Management Studio можно запускать и устанавливать службы SQL Server 2005, подобно тому как это раньше делалось с помощью Enterprise Manager, так что заново изучать SQL Server Configuration Manager не придется. Данная программа может быть вызвана через меню Start в Windows или через Computer Management в меню Administrative Tools. А чтобы запустить SQL Server Management Studio, который мы рассмотрим в следующем разделе, можно воспользоваться диалоговым окном Management Studio Registered Servers.

SQL Server Configuration Manager используется для управления службами SQL Server, сетевыми библиотеками и SQL Native Client (см. экран 1 ). Здесь можно устанавливать и изменять параметры учетных записей служб, а также включать или отключать эти службы. Данный инструмент частично перекрывает функции двух других средств, которые осуществляют управление этими службами и другими функциями с точки зрения управления защитой от возможных угроз.

Средства настройки. В SQL Server 2005 появилось много новых служб. Теперь, в дополнение к стандартным службам, таким как SQL Server, SQL Agent и Analysis Server, можно еще управлять параметрами службы SQL Server Integration Services (ранее Data Transformation Services, DTS), Reporting Services, Full-Text Search и SQL Browser.

При установке SQL Server 2005 эти службы представляют собой объект для потенциальных атак. Для принятия мер по минимизации поверхности атаки Microsoft предлагает два новых средства настройки, одно из которых предназначено для управления службами, а другое - для настройки параметров соединений. На конечной стадии процесса установки SQL Server 2005 имеются ссылки на эти программы, они также доступны через меню Start. С помощью этих инструментов можно контролировать те службы и функции, которые представляют собой потенциально уязвимые места для атак на SQL Server, любую из этих служб можно включать или отключать непосредственно из этих программ.

С помощью утилиты Surface Area Configuration for Services and Connections можно запускать и отключать различные службы (см. экран 2 ). Здесь же можно управлять статусом службы SQL Browser, которая работает через порт UDP 1434 и преобразует соединения в именованные объекты.

Программа Surface Area Configuration for Features, окно которой показано на экране 3 , позволяет управлять соединениями, в том числе специализированными удаленными запросами, настройками CLR, хранимыми процедурами Database Mail (SMTP), удаленными выделенными административными подключениями (Dedicated Administrator Connections), конечными точками служб Web (SOAP), OLE-автоматизацией расширенных хранимых процедур, конечными точками Service Broker, хранимыми процедурами SQL Mail (MAPI), xp_cmdshell и Web Assistant. Все эти функции по умолчанию отключены и должны активизироваться выборочно, по мере необходимости. Для включения многих из этих функций можно использовать системную хранимую процедуру sp_configure, с ее помощью также можно задействовать наиболее важные команды T-SQL на конечных точках.

Management Studio

В SQL Server 2005 появилось новое консолидированное средство управления - SQL Server Management Studio. Это всеобъемлющий инструмент, в котором объединены функции, актуальные для DBA и для разработчиков. Management Studio представляет собой комплексное программное средство, которое содержит столько возможностей, что для их подробного описания одной статьи явно недостаточно. Что касается наиболее значительных новшеств и изменений, имеющихся в данном продукте, то их можно описать тремя фразами: управление сервером, редактирование запросов и анализ производительности.

Management Studio - это огромный «ящик с инструментами»: разработчики Microsoft тщательно отобрали лучшие функции администрирования из Enterprise Manager и лучшие функции анализа и редактирования запросов из Query Analyzer, а затем соединили их в новом программном средстве. Далее в статье мы бросим беглый взгляд на эти новые возможности.

Запустив оболочку Management Studio в первый раз, вы обнаружите в ней сходство с Visual Studio. Но несмотря на то что Management Studio базируется на оболочке Visual Studio и имеет с ней некоторое внешнее сходство, это обманчивое впечатление: Management Studio представляет собой абсолютно новое программное средство, разработанное с использованием управляемого кода.

Сразу становится очевидно, что это два типа диалогов: центральная, не перемещаемая часть окна Management Studio Window называется областью окна документа, а объекты, размещаемые по краям, называются компонентами. Изначально в окне документа по умолчанию отображается страница сводки (Summary), содержащая некоторые отчетные данные о зарегистрированных серверах SQL Server. При первом запуске Management Studio в левой части будут отображаться диалоги компонентов Registered Servers и Object Explorer, а окно документа, справа, будет содержать страницу Summary (см. экран 4 ). Здесь отображается предлагаемый по умолчанию набор компонентов, но его можно изменить, добавив нужные компоненты в выбранные места. Страница Summary может содержать перечень компонентов узла Object Explorer или формировать отчет по выбранному узлу. Некоторое время поработав с компонентами, вы познакомитесь с третьим типом диалогов, так называемыми немодальными диалоговыми окнами, которые не связаны с основным окном Management Studio.

Как и в Visual Studio, каждое из окон компонентов является фиксируемым и скрываемым (hideable). С помощью значка канцелярской кнопки, имеющегося в панели кнопок каждого из окон, можно включить функцию автоматического скрытия (auto-hide), еще до нее можно добраться, щелкнув правой кнопкой мыши в самом верху окна. Скрываемые автоматически окна удобны в тех случаях, когда требуется дополнительное экранное пространство для расширения Object Browser, авторизующих запросов (authoring queries) и т. д. Характер представления этих окон можно изменить, нажав кнопку Window Position (кнопка с изображением небольшой треугольной стрелки, направленной вниз, которая имеется в правом верхнем углу каждого из окон компонентов).

Через меню View можно задействовать многие другие окна компонентов. Для целей администрирования наиболее актуальны активные по умолчанию окна Registered Servers и Object Explorer. Что касается окон компонентов Solution Explorer, Template Explorer и Properties, то они больше пригодятся для редактирования запросов и анализа. В Management Studio запоминается последнее отображение и параметры конфигурации окна, поэтому все они сохранятся при следующем запуске программы. Если нужно сбросить настройки окна в состояние по умолчанию, то в меню Window следует выбрать пункт Reset Window.

В Management Studio реализована поддержка администрирования и редактирования сценариев для многих элементов: экземпляров базы данных SQL Server, вместе с Analysis Services, Report Services, Integration Services и SQL Server Mobile. Все эти типы служб могут регистрироваться через Management Studio с помощью Register Servers, а затем их можно администрировать с помощью Object Explorer. Здесь можно редактировать сценарии T-SQL, сценарии Analysis Services и сценарии SQL Server Mobile, а также объединять их в проекты с помощью окна Solution Explorer, что будет показано в разделе, посвященном редактированию запросов.

Администрирование

Для того чтобы в полной мере оценить те новые возможности, которые получили администраторы баз данных с появлением Management Studio, сначала сравним Management Studio с Enterprise Manager, стандартным средством администрирования в SQL Server 2000. Программа Enterprise Manager представляет собой встроенную в SQL Server 2000 графическую утилиту, предназначенную для выполнения задач администрирования сервера баз данных. Данной утилите присущ ряд недостатков, в частности с ее помощью можно управлять только реляционным механизмом SQL Server и службой SQL Agent, но не службой Analysis Services. Кроме того, Enterprise Manager не может эффективно отображать большое количество серверов, баз данных или объектов баз данных. Вторая проблема заключается в том, что для решения многих задач здесь используются модальные диалоги, поэтому после запуска какой-либо процедуры, например резервного копирования, часто приходится запускать еще одну копию Enterprise Manager для продолжения наблюдения за сервером. И наконец, Enterprise Manager - это оснастка Microsoft Management Console (MMC), что обусловливает не слишком стабильную работу данного приложения и имеющие место весьма неприятные сбои, а порой оно просто перестает отвечать.

Все упомянутые ограничения в Management Studio устранены. Отсюда можно администрировать не только экземпляры реляционного механизма, но также и Analysis Services. Management Studio использует новый прикладной интерфейс SQL Management Objects (SMO) API, что обеспечивает эффективное управление большим количеством объектов баз данных. И наконец, везде, где это возможно, используются немодальные диалоги, разработанные с использованием стабильного управляемого кода.

Registered servers. В Management Studio, в отличие от Enterprise Manager, окно, предназначенное для регистрации серверов и их организации, отделено от окна просмотра серверных объектов. Информацию о зарегистрированных серверах или группах серверов можно экспортировать в конфигурационный файл формата XML и далее импортировать на тот же самый или какой-либо другой сервер, просто щелкнув правой кнопкой на соответствующем зарегистрированном сервере или группе серверов и выбрав пункт Export.

Кроме того, в SQL Server 2005 также разнесены функции управления службами SQL Server и функции управления сервером. Например, из Registered Servers нельзя запускать или останавливать службы - для этих целей предназначен компонент SQL Server Configuration Manager. Также в Management Studio разделены процедуры регистрации сервера, подключения к серверу и просмотра объектов сервера. Разумеется, в ходе регистрации сервера через окно Registered Servers устанавливается кратковременное соединение с сервером в целях проверки, но данное соединение не поддерживается постоянно. Тем не менее, если щелкнуть правой кнопкой мыши на зарегистрированном сервере, а затем выбрать пункт Object Explorer или New Query, то информация о соединении, сохраненная вместе с информацией о зарегистрированном сервере, будет использоваться SQL Server в дальнейшем для установления соединений с данным сервером.

Object Explorer. Это основной инструмент для организации объектов сервера. Object Explorer предоставляет не только функции администрирования, но и возможности редактирования запросов. Если поглубже заглянуть внутрь процессора баз данных SQL Server 2005, то можно, например, увидеть, что результирующее дерево параметров имеет сходство с Enterprise Manager. Однако здесь еще можно регистрировать экземпляры Analysis Services и управлять ими, что не было предусмотрено в классическом Enterprise Manager.

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

Когда будет развернут узел Object Explorer на уровне SQL Server database engine, обратите внимание, что SQL Agent имеет собственный узел и все объекты кода T-SQL подпадают под категорию узла Programmability, который имеет в своем составе узел для размещения сборок (assembly). Если развернуть дерево на уровне Analysis Server, то здесь вы также увидите новый узел, предназначенный для размещения хранимых процедур, которые тоже являются сборками.

Диалоговые окна Object Explorer базируются на SMO, который пришел на смену SQL-DMO, реализованному в SQL Server 2000, и представляет собой усовершенствованную объектную модель, обладающую большей гибкостью. SMO работает в асинхронном режиме, что позволяет открывать узел сервера и работать со многими объектами или методами, не оказывая влияния на другие функции. Допустим, мы открываем базу данных, содержащую сотни или даже тысячи таблиц, и в то время, пока она открывается, можем открыть еще один узел и выполнить какую-либо другую работу. Также следует упомянуть, что SMO содержит все необходимые механизмы для работы со сценариями, что позволяет автоматизировать с помощью сценариев практически любые действия, выполняемые в Object Explorer.

Выполнение задач администрирования

В Object Explorer системные объекты отделены от объектов пользовательской категории. Например, системные базы данных (master, model, msdb и tempdb) относятся к категории System Databases, в то время как новые учебные базы данных, AdventureWorks и AdventureWorksDW, являются пользовательскими. Здесь следует иметь в виду, что учебные базы данных по умолчанию не устанавливаются, поэтому, если в ходе установки продукта не был выбран соответствующий параметр, по ее окончании у вас не будет ни одной доступной пользовательской базы.

Как и в случае установки свойств сервера, можно настраивать параметры базы данных, вызвав окно Database Properties. В Management Studio также поддерживаются средства проектирования таблиц и представлений (известные в SQL Server 2000 как Visual Data Tools), в том числе инструмент Database Diagrams. В те диалоговые окна, которые предназначены для работы с объектами баз данных, теперь встроен механизм поддержки сценариев, так что любые действия здесь могут быть автоматизированы с помощью сценариев (см. экран 5 ).

Компонент Object Explorer в Management Studio содержит узлы, предназначенные для управления параметрами безопасности. Также можно управлять унаследованными от SQL Server 2000 пакетами DTS, службами Notification Services, триггерами уровня базы данных, Database Mail и триггерами DDL уровня сервера.

Как можно было ожидать, в Management Studio встроены механизмы управления SQL Server Agent, но при этом сюда добавлен новый узел для управления SQL Server Agent Proxy (посредник SQL Server Agent). Через SQL Server Agent Proxy для каждого отдельного шага задания может определяться соответствующий контекст безопасности, это достигается путем предоставления посредников безопасности пользователю Windows компонентом SQL Agent. Обычно в заданиях T-SQL на уровне шагов посредники не используются, и тем не менее с их помощью в заданиях можно пошагово назначать посредников для элементов управления Active X, операций xp_cmdshell, репликаций, а также служб Analysis Services, SQL Server и SQL Server Integration Services.

Редактирование запросов

Помимо большого количества новых административных функций, в Management Studio появилась полнофункциональная среда для работы с запросами, существенно превосходящая по функциональности Query Analyzer из SQL Server 2000.

SQL Server 2000 Query Analyzer больше всего подходит для редактирования кода T-SQL и сценариев. И хотя в Query Analyzer имеется прекрасный редактор T-SQL, тем не менее ему свойствен ряд ограничений. В частности, с его помощью нельзя редактировать другие типы сценариев SQL Server, например такие, как запросы MDX. Если в Query Analyzer выполняется редактирование запроса или сценария, то при этом окно редактирования должно иметь соединение с SQL Server, причем подключиться к другому серверу в данном сеансе редактирования нельзя. Что касается графика плана исполнения, то возможности Query Analyzer прекрасно подходят для анализа хода выполнения запроса, однако графическое представление выходных данных не переносимо. Кроме того, хотя в Query Analyzer можно редактировать сценарии T-SQL, находящиеся на диске в виде файлов, в SQL Server 2000 отсутствует интерфейс прямого доступа к программам контроля версий. И последнее. Хотя с помощью инструмента Object Browser, имеющегося в Query Analyzer, можно выполнять в базе данных поиск объектов кода T-SQL, его нельзя использовать ни для каких задач, связанных с управлением базой данных.

Что касается возможностей редактирования в Query Analyzer, то практически все они сохранились и в Management Studio, но в новом продукте все описанные выше ограничения устранены. С помощью Management Studio можно редактировать все типы сценариев SQL Server, причем не только написанные на T-SQL, но и XML-сценарии. При редактировании запросов необязательно иметь постоянное соединение с сервером, теперь это можно делать и в автономном режиме либо в ходе сеанса переключиться на другой сервер, на котором данный запрос должен быть выполнен. Полученное в Management Studio графическое отображение плана исполнения на выходе может быть передано в другой сеанс Management Studio, соответственно можно работать с полученными результатами без необходимости подключения к исходному серверу. Имеющийся в Management Studio компонент Object Explorer содержит всю функциональность Object Explorer из Query Analyzer, но здесь также в полном объеме реализована поддержка функций администрирования.

Работа в автономном режиме. Для запуска нового запроса в Management Studio существует несколько способов. При выборе из меню File пункта New можно создавать запросы трех типов: запрос T-SQL, запрос Analysis Services MDX, DMS или XMLA либо запрос SQL Server Mobile. Кроме того, созданные запросы можно запускать из Object Explorer или Solution Explorer, о чем мы поговорим далее.

Процесс редактирования запросов в Management Studio является независимым от соединения. Запросы можно запускать, имея подключение к серверу, не имея его сначала и установив в нужный момент либо переключаться на другой сервер для выполнения этого же запроса. Это дает возможность наиболее естественным образом использовать предусмотренную в SQL Server 2000 практику редактирования запросов из сценариев и текстовых файлов. При переходе в режим редактирования появится несколько дополнительных пиктограмм редактирования запросов, предназначенных, в частности, для установки, разрыва и изменения соединения. При редактировании запросов в Management Studio можно задействовать функцию auto-hide для окна Object Browser, тогда освободится дополнительное пространство экрана для редактирования.

Решения и проекты. Management Studio имеет расширенные возможности редактирования, он позволяет объединять созданные сценарии и текстовые файлы в приложения (solution) и проекты (project). Проект представляет собой именованную коллекцию сценариев и текстовых файлов, которые обычно размещаются в стандартном каталоге Windows, имеющем то же имя, что и сам проект. Приложение - это набор из одного или более проектов, при этом все проекты, входящие в состав данного приложения, могут быть включены в каталог с именем, соответствующим имени приложения, но могут быть и приложения, содержащие только один проект и, соответственно, имеющие один вариант размещения. Для создания нового проекта в Management Studio нужно выбрать меню File, затем New, Project, при этом запускается диалоговое окно New Project.

В Management Studio по умолчанию приложению присваивается то же имя, что и проекту, а размещаются приложение и проект в каталоге My Documents. В тех случаях когда необходимо объединить большое количество сценариев в один крупный проект, который желательно разбить на несколько подчиненных проектов (sub-project), можно собрать несколько проектов в одно приложение с осмысленным именем и задать для него необходимое размещение. При этом сначала создается новый проект, а затем указывается приложение, в состав которого он должен быть включен. Имя, присваиваемое решению по умолчанию, может быть изменено. После того как были созданы проект и соответствующее решение, можно осуществлять навигацию по приложению и входящим в него проектам с помощью компонента Solution Explorer, как показано на экране 6 .

Предположим, требуется модифицировать несколько объектов, относящихся к схеме AdventureWorks HumanResources. При этом часть сценариев будет модифицировать таблицы, часть - представления, а какие-то из них будут изменять код хранимых процедур. Сценарии каждого из этих типов могут быть объединены в соответствующие проекты, а проекты затем объединяются в приложение. Один из способов реализации описанного подхода показан на экране 6: здесь приложение с именем soln_HR содержит несколько проектов, каждый из которых соответствует определенному типу вносимых в схему HR изменений.

Используя функциональность приложений и проектов, можно создавать наборы сценариев для SQL Server Analysis Services или для SQL Mobile, группируя их в собственные проекты. Наборы, определяемые как проекты, будут помещаться в соответствующие каталоги на диске и отображаться в Solution Explorer в виде отдельных узлов. Каждому проекту можно назначить имя и указать папку для размещения на диске. По умолчанию каталог проекта размещается внутри каталога приложения, и все файлы данного проекта будут храниться в этом каталоге.

Возможность организации файлов в проекты и приложения - это только одно из преимуществ новой технологии работы со сценариями в SQL Server. Кроме этого, Management Studio имеет тесную взаимосвязь с программами проверки исходного кода, если данная система контроля исходного кода является подключаемым модулем. В пакете Visual SourceSafe (VSS) имеется однозначное соответствие между проектами VSS и проектами Management Studio, поэтому после создания проекта и приложения в Management Studio приложение целиком можно загрузить в VSS для выполнения соответствующей проверки, а затем выполнять проверку на уровне отдельных проектов или файлов. Для доступа к функциям загрузки в систему контроля кода следует щелкнуть правой кнопкой на любом узле в Solution Explorer. Настроить параметры проверки кода можно в диалоговом окне Options, которое вызывается из меню Tools программы Management Studio. В этом же окне (Tools, Options) можно включить некоторые другие функции редактирования, такие как нумерация строк и динамическая справка.

Интерактивная работа со сценариями баз данных. Для редактирования в базе данных объектов T-SQL можно использовать Object Explorer. Когда вы попадаете внутрь программируемого объекта базы данных, такого как хранимая процедура, функция или триггер, вы можете вызвать редактор запросов T-SQL, для чего требуется выбрать New или Modify. Если выбрано New, тогда будет использоваться шаблон, параметры которого можно просматривать и изменять с помощью компонента Template Explorer. При выборе варианта Modify генерируется сценарий ALTER, соответствующий выбранному объекту. Функции New и Modify заменили компоненты Assisted Editor, существовавшие в ранних бета-версиях Management Studio для SQL Server 2005.

Усовершенствования для плана исполнения. Management Studio имеет два существенных расширения, связанных с возможностями просмотра планов. Здесь, как и в Query Analyzer, можно просматривать графический вариант плана исполнения, однако и сами пиктограммы, и цветовая гамма претерпели изменения. Кроме того, теперь большая часть пиктограмм показывает детали той операции, которую SQL Server выполняет в настоящий момент. Если нажать на значок с символом «+» в правом нижнем углу окна запроса на план исполнения, то можно перейти к увеличенному отображению запроса. А если выбрать какой-либо из узлов запроса на план исполнения, то в диалоговом окне Properties будет отображена дополнительная информация об этом действии.

Я думаю, многим понравится новая возможность, появившаяся в Management Studio, позволяющая сделать графическое изображение плана исполнения переносимым. Начать можно с прямого вывода в XML с помощью SET SHOWPLAN_XML ON. Если результаты запроса передаются в контейнер, тогда результирующий набор данных будет включать ссылку на документ XML, содержащий информацию о плане исполнения. Если щелкнуть по этой ссылке, то можно просмотреть содержимое плана исполнения в редакторе XML Management Studio.

В целях графического просмотра данные XML можно экспортировать в переносимый (portable) формат. В полученном наборе данных нужно щелкнуть правой кнопкой на ссылке XML и сохранить данные в виде файла с расширением.sqlplan, как показано на экране 7 . Если открыть сохраненный файл через пункт Open меню File, то Management Studio отобразит XML-документ в виде графического изображения плана исполнения, как показано на экране 8 .

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

Соберем все вместе

Имеющийся в SQL Server 2005 инструментарий существенно расширяет возможности как управления, так и разработки. Например, интерфейс SQL Server Management Studio является настраиваемым, поэтому в каждом случае можно для себя решить, какие функции являются самыми необходимыми, и, соответственно, скрыть те функции, которые не будут использоваться в работе. Разумеется, в процессе освоения новых инструментов работы можно столкнуться с неизбежными в ходе обучения проблемами, но, как сказал поэт, «не пропадет ваш скорбный труд».

Кэлен Дилани - Независимый консультант и инструктор по SQL Server. Имеет сертификаты MCT и MCSE. Автор книги Inside SQL Server 2000 (Microsoft Press). [email protected]

Рон Талмадж - Владелец и руководитель Prospice LLC, консалтинговой компании в области баз данных из Сиэтла. Руководитель группы Pacific Northwest SQL Server Users Group, имеет сертификаты MCSD и MCP в SQL Server, ведет рассылку PASSnews и является SQL Server MVP. Написал Microsoft SQL Server 7.0 Administrator?s Guide (издательство Prima).