Создание баз данных

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

CREATE TRIGGER [dbo].[tr_OrderConstraint] ON [dbo].[Order]

  AFTER INSERT,UPDATE

AS

BEGIN

  SET NOCOUNT ON;

 

  IF EXISTS(SELECT *

            FROM inserted AS i INNER JOIN

                   Customer AS cust ON i.IdCust = cust.IdCust INNER JOIN

                     City AS c ON cust.IdCity = c.IdCity

            WHERE c.CityName = N'Москва')

    BEGIN

      ROLLBACK TRANSACTION

      RAISERROR('Оформление заказов для клиентов из Москвы запрещено',16,1)

    END

END

Поскольку триггер  выполняется в рамках транзакции соответствующей ей операции модификации  данных для отмены самой операции достаточно выполнить команду ROLLBACK TRANSACTION. Кроме того в случае попытки нарушения заданного бизнес-правила желательно с помощью команды RAISEERROR отправить пользователю сообщение об ошибке c ее подробным описание.

Задание для самостоятельной работы: Создайте триггер, запрещающий добавлять в заказ товары, отсутствующие на складе.

 

Лабораторная  работа №11: Система безопасности SQL Server

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

Общая схема системы безопасности SQL Server

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

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

На уровне базы данных пользователю может быть предоставлен определенный набор разрешений с помощью назначения ему фиксированных ролей базы данных. Все пользователи автоматически становятся членами стандартной роли public, у которой по умолчанию нет никаких разрешений. Пользовательские роли — это дополнительные роли, служащие в качестве групп. Роли может быть разрешен доступ к объектам базы данных, а пользователю могут быть назначены роли.

Разрешения  к объектам назначаются с помощью  инструкций GRANT (предоставить), REVOKE (отозвать) и DENY (запретить). Запрет привилегии замещает собой ее предоставление, а предоставление привилегии замещает собой ее отзыв. Пользователю может быть предоставлено  множество разрешений к объекту (индивидуальных, наследованных от роли, обеспеченных принадлежностью  к роли public). Если какая-либо из этих привилегий запрещена, для пользователя блокируется доступ к объекту. В противном случае, если какая-либо из привилегий предоставляет разрешение, пользователь получает доступ к объекту.

Разрешения  объекта достаточно детализированы. Существуют отдельные разрешения для каждого из возможных действий (SELECT, INSERT, UPDATE, RUN и т.д.) над объектом.

Выбор типа логина и настройка режима аутентификации

SQL Server поддерживает два типа логинов (имен входа):

• логин Windows (логин для локальной учетной записи Windows, логин для доменной учетной записи Windows, логин для группы Windows);
• логин SQL Server.

При использовании  логинов Windows в системные таблицы базы данных master записывается информация об идентификаторе учетной записи или группы Windows (но не пароль). Аутентификация (т. е. проверка имени пользователя и пароля) производится обычными средствами Windows при входе пользователя на свой компьютер.

При использовании  логина SQL Server пароль для этого логина (точнее, его хэшированное значение) хранится вместе с идентификатором логина в базе данных master. При подключении пользователя к серверу ему придется указать имя логина и пароль.

Предпочтительный  вариант логина для пользователя — это логин Windows, при этом не для учетной записи, а для группы (лучше всего для локальной доменной группы). Преимуществ у такого решения множество:

• пользователю достаточно помнить один пароль — для входа на свой компьютер;
• повышается уровень защищенности SQL Server. Это происходит, по крайней мере, за счет того, что пароль не будет передаваться по сети открытым текстом, как это происходит по умолчанию при использовании команд CREATE LOGIN и ALTER LOGIN. Кроме того, хэши Windows более защищены, чем хэши логинов SQL Server;
• проверка при входе пользователя производится быстрее.

Эти преимущества справедливы для любых логинов  Windows: как для учетных записей, так и для групп. Но при использовании логинов для групп Windows появляются дополнительные преимущества:

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

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

• на предприятии вполне может не оказаться домена Windows (если, например, сеть построена на основе NetWare или UNIX);
• пользователи SQL Server могут не входить в домен (например, если они подключаются к SQL Server из филиалов или через Web-интерфейс с домашнего компьютера).

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

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

• В режиме аутентификации Windows SQL Server полностью доверяет (делегирует) аутентификацию операционной системе.
• В смешанном режиме аутентификация Windows и самого сервера сосуществуют независимо друг от друга.

Третьего  варианта, в котором использование  логинов Windows было бы запрещено, не предусмотрено: логины этого типа доступны всегда.

Установленный при инсталляции режим аутентификации можно изменить в утилите Management Studio выбрав нужный переключатель в группе «Серверная проверка подлинности» на странице «Безопасность» диалогового окна «Свойства сервера».

Установите  переключатель в положение «Проверка  подлинности SQL Server и Windows». Таким образом, вы включите смешанный режим аутентификации, в котором и будут выполняться остальные упражнения. Для того чтобы изменение режима аутентификации вступило в силу сервер нужно перезапустить.

Создание логина и настройка  его параметров

Логины любого типа создаются одинаково:

• при помощи графического интерфейса — из окна «Создание имени входа». Это окно открывается с помощью команды «Создать имя входа…» контекстного меню узла «Безопасность | Имена входа» дерева обозревателя объектов в SQL Server Management Studio;

• из скрипта — при помощи команды  CREATE LOGIN.

Например, команда  на создание логина SQL Server с именем User1 и паролем P@sswOrd (для всех остальных параметров будут приняты значения по умолчанию) может выглядеть так:

CREATE LOGIN User1 WITH PASSWORD = 'P@sswOrd';

Если вы создаете логин Windows, вам потребуется выбрать соответствующую учетную запись или группу Windows.

Если вы создаете логин SQL Server, вам придется ввести его имя и пароль. Пароль всегда чувствителен к регистру, а логин — только тогда, когда чувствительность к регистру была определена при установке SQL Server. Конечно, кроме имени и пароля для логинов можно определить множество других параметров. Некоторые из них перечислены ниже.

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

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

На вкладке  «Состояние» свойств логина можно  настроить для этого логина дополнительные параметры:

• Разрешение на подключение к ядру СУБД — по умолчанию для всех логинов устанавливается значение «Предоставить», т. е. подключаться к SQL Server разрешено. Значение «Запретить», как правило, используется только в одном случае — когда вы предоставляете доступ на SQL Server логину для группы Windows, а одному или нескольким членам этой группы доступ нужно запретить. Поскольку явный запрет всегда имеет приоритет перед разрешением, то достаточно будет создать свои собственные логины Windows для этих пользователей и установить для них значение «Запретить».
• Имя входа (Включено/Отключено) — конечно, все логины по умолчанию включены. Обычно отключать их приходится только в ситуации, когда какой-то пользователь увольняется или переходит на другую работу. Чтобы сэкономить время, достаточно просто отключить данный логин, а при появлении пользователя со схожими рабочими обязанностями переименовать этот логин, поменять пароль и включить. Заниматься предоставлением разрешений заново в этом случае не придется.
• Имя входа заблокировано — установить этот флажок вы не можете (только снять его). Учетная запись пользователя блокируется автоматически после нескольких попыток неверного ввода пароля для логина SQL Server, если такая блокировка настроена на уровне операционной системы, а для логина установлен флажок «Требовать использование политики паролей».

Разрешения на уровне сервера. Фиксированные  серверные роли

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

На графическом  экране работа с ролями сервера производится или из свойств логина (вкладка  «Роли сервера»), или из свойств  самой серверной роли (узел «Роли  сервера» дерева обозревателя объектов Management Studio).

Отметим несколько  моментов, связанных с серверными ролями:

• набор серверных ролей является фиксированным. Вы не можете создавать свои серверные роли (в отличие от ролей базы данных);
• для предоставления прав на уровне всего сервера необязательно использовать серверные роли. Вы вполне можете предоставить эти права напрямую логину (при помощи вкладки «Разрешения» окна свойств сервера). По умолчанию каждый логин обладает на уровне всего сервера двумя правами: CONNECT SQL (т. е. подключаться к серверу, это право предоставляется логину напрямую) и VIEW ANY DATABASE (т. е. просматривать список баз данных, это право пользователь получает через серверную роль public);
• серверные роли используются только в специальных случаях. Для большинства пользователей настраивать их не нужно.

Серверных ролей  не так много, поэтому приведем их полный список с комментариями:

• public — права этой роли автоматически получают все, кто подключился к SQL Server, и лишить пользователя членства в этой роли нельзя. Обычно эта роль используется для предоставления разрешений всем пользователям данного сервера;
• sysadmin — логин, которому назначена эта роль, получает полные права на весь SQL Server (и возможность передавать эти права другим пользователям);
• serveradmin — эта роль для оператора, который обслуживает сервер. Можно изменять любые настройки работы сервера и отключать сервер, но получать доступ к данным и изменять разрешения нельзя;
• securityadmin — эта роль для того, кто выдает разрешения пользователям, не вмешиваясь в работу сервера. Он может создавать логины для обычных пользователей, изменять их пароли, предоставлять разрешения в базах данных. Однако предоставить кому-либо административные права или изменять учетную запись администратора эта роль не позволяет;
• bulkadmin — роль для сотрудников, которые выполняют массовые загрузки данных в таблицы SQL Server;
• dbcreator — эта роль позволяет создавать базы данных на SQL. Server (а тот, кто создал базу данных, автоматически становится ее владельцем);
• diskadmin — эта роль позволяет выполнять любые операции с файлами баз данных на диске;
• processadmin — эта роль предназначена для выполнения единственной обязанности: закрытия пользовательских подключений к серверу (например, зависших);
• setupadmin — права этой роли позволяют подключать внешние серверы SQL Server.