Создание баз данных. Основы Transact SQL. Обработка ошибок. Управление транзакциями. Триггеры

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

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

CREATE TRIGGER имя_триггера ON имя_таблицы AFTER { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { инструкции T-SQL }

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

Определим в таблице City триггер, отслеживающий все изменения в данной таблице. Для хранения информации обо всех операциях модификации, выполняемых над данными в этой таблице, создадим специальную таблицу sysCityAudit со следующими столбцами: IdOperation (уникальный идентификатор операции), TypeOp (тип операции: вставка, обновление или удаление), IdCity, CityName, DateAndTime (дата и время выполнения операции), UserName (имя пользователя, изменившего данные). Запрос на создание данной таблицы приведен ниже:

CREATE TABLE [dbo].[sysCityAudit](

[IdOperation] [int] IDENTITY(1,1) NOT NULL,

[TypeOp] [varchar](50) NOT NULL,

[IdCity] [int] NOT NULL,

[CityName] [nvarchar](20) NULL,

[DateAndTime] [datetime] NOT NULL CONSTRAINT [DF_sysCityAudit_DateAndTime] DEFAULT (getdate()),

[UserName] [nvarchar](256) NOT NULL CONSTRAINT [DF_sysCityAudit_UserName] DEFAULT (user_name()),

CONSTRAINT [PK_sysCityAudit] PRIMARY KEY CLUSTERED

(

[IdOperation] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Запрос на создание триггера, отслеживающего все изменения в таблице City:

CREATE TRIGGER [dbo].[tr_CityAudit] ON [dbo].[City]

AFTER INSERT,DELETE,UPDATE

AS

BEGIN

SET NOCOUNT ON;

IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)

INSERT sysCityAudit(TypeOp,IdCity,CityName)

SELECT 'Обновление', IdCity, CityName

FROM inserted

ELSE IF EXISTS(SELECT * FROM inserted) --

INSERT sysCityAudit(TypeOp,IdCity,CityName)

SELECT 'Вставка', IdCity, CityName

FROM inserted

ELSE

INSERT sysCityAudit(TypeOp,IdCity,CityName)

SELECT 'Удаление', IdCity, CityName

FROM deleted

END

Проверьте работоспособность вновь созданного триггера. Для этого произведите в таблице City различные изменения и убедитесь, что подробная информация о них была записана в таблицу sysCityAudit.

Для демонстрации применения триггера, в качестве инструмента ограничивающего возможные операции с данными в таблице в соответствии с определенными бизнес-правилами, создадим триггер, запрещающий оформлять заказы от клиентов из Москвы (к базе данных 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

Рис. 11.1

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 выбрав нужный переключатель в группе «Серверная проверка подлинности» на странице «Безопасность» диалогового окна «Свойства сервера».

Рис. 11.2

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

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

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

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



Рис. 11.3

из скрипта - при помощи команды 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, если такая блокировка настроена на уровне операционной системы, а для логина установлен флажок «Требовать использование политики паролей».

Рис. 11.4

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

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

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

Рис. 11.5

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

набор серверных ролей является фиксированным. Вы не можете создавать свои серверные роли (в отличие от ролей базы данных);

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

серверные роли используются только в специальных случаях. Для большинства пользователей настраивать их не нужно.



Рис. 11.6

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

public - права этой роли автоматически получают все, кто подключился к SQL Server, и лишить пользователя членства в этой роли нельзя. Обычно эта роль используется для предоставления разрешений всем пользователям данного сервера;

sysadmin - логин, которому назначена эта роль, получает полные права на весь SQL Server (и возможность передавать эти права другим пользователям);

serveradmin - эта роль для оператора, который обслуживает сервер. Можно изменять любые настройки работы сервера и отключать сервер, но получать доступ к данным и изменять разрешения нельзя;

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

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

dbcreator - эта роль позволяет создавать базы данных на SQL. Server (а тот, кто создал базу данных, автоматически становится ее владельцем);

diskadmin - эта роль позволяет выполнять любые операции с файлами баз данных на диске;

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

setupadmin - права этой роли позволяют подключать внешние серверы SQL Server.

Пользователи баз данных. Схемы

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

Логины и пользователи баз данных - это совершенно разные объекты. Разделение логинов и пользователей баз данных обеспечивает большую гибкость. Например, пользователь, который входит от имени одного и того же логина, сможет работать в разных базах данных от имени разных пользователей.

Создать пользователя базы данных можно:

В среде Management Studio вызвав команду «Создать пользователя…» в контекстном меню подузла «Безопасность | Пользователи» узла конкретной базы данных дерева обозревателя объектов. В открывшемся окне «Пользователь базы данных - Создать», снимок экрана с которым приведен ниже, необходимо указать два обязательных параметра: имя нового пользователя и выбрать соответствующий ему логин (Windows или SQL Server).

При помощи команды CREATE USER.

Рис. 11.7

Изменение свойств пользователя и его удаление производится из того же контейнера в Management Studio, что и создание пользователя, а также при помощи команд ALTER USER/DROP USER.

В SQL Server 2000 и в более старых версиях объект пользователя базы данных нес на себе двойную нагрузку: во-первых, он использовался для предоставления разрешений в базе данных, а во-вторых, имя пользователя базы данных использовалось для идентификации объектов. Например, обращение к таблице по полному ее имени могло выглядеть так: SELECT * FROM MyServer.MyDatabase.User1.Table1;

Если разработчик использовал в коде приложения просто имя объекта (например, SELECT * FROM Table1), то при подключении к серверу из этого приложения от имени другого пользователя могли возникнуть проблемы, поскольку вместо User1 подставлялось текущее имя пользователя (а если объект с таким полным именем не был обнаружен, то имя специального пользователя dbo).

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

SELECT * FROM MyServer.MyDatabase.Schema1.Table1;

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

Пользователю можно назначить схему по умолчанию. В эту схему SQL Server будет по умолчанию помещать объекты, которые создает этот пользователь. Кроме того, искать объекты, к которым обращается пользователь (например, в случае запроса вида SELECT * FROM Table1), SQL Server также будет в первую очередь в его схеме по умолчанию.

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

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

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

при удалении пользователя из базы данных не придется переименовывать его объекты;

упрощается предоставление разрешений для наборов объектов в базе данных.

Список схем можно увидеть в подузле «Безопасность | Схемы» узла конкретной базы данных дерева обозревателя объектов Management Studio.

При создании любой базы данных в ней автоматически создаются четыре специальных пользователя:

dbo (от database owner) - пользователь-владелец базы данных. Он автоматически создается для того логина, от имени которого была создана эта база данных. Конечно же, как владелец, он получает полные права на свою базу данных (при помощи встроенной роли базы данных db_owner);

guest (гость) - этот специальный пользователь предназначен для предоставления разрешений всем логинам, которым не соответствует ни один пользователь в базе данных. По умолчанию у этого пользователя нет права login для базы данных, и, следовательно, работать он не будет. Этот пользователь используется чаще всего для предоставления разрешений логинам на какие-то учебные/тестовые базы данных или на базы данных-справочники, доступные только на чтение;

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

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

Роли базы данных

Обычно после создания логина и пользователя базы данных следующее, что нужно сделать, - предоставить пользователю разрешения в базе данных. Один из способов сделать это - воспользоваться ролями базы данных.

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

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

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

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

db_accessadmin - роль для сотрудника, ответственного за пользователей базы данных. Этот сотрудник получит возможность создавать, изменять и удалять объекты пользователей баз данных, а также создавать схемы. Других прав в базе данных у него нет;

db_securityadmin- эта роль дополняет роль db_accessadmin. Сотрудник с правами этой роли получает возможность назначать разрешения на объекты базы данных и изменять членство во встроенных и пользовательских ролях. Прав на создание и изменение объектов пользователей у этой роли нет;

db_backupoperator - эта роль дает право выполнять резервное копирование базы данных;

db_ddladmin - эта роль применяется в редких ситуациях, когда пользователю необходимо дать право создавать, изменять и удалять любые объекты в базе данных, не предоставляя прав на информацию, которая содержится в существующих объектах;

db_datareader и db_datawriter - эти встроенные роли дают право просматривать и изменять соответственно (в том числе добавлять и удалять) любую информацию в базе данных. Очень часто пользователю необходимо дать права на чтение и запись информации во всех таблицах базы данных, не предоставляя ему лишних административных разрешений (на создание и удаление объектов, изменение прав и т. п.). Самый простой вариант в этой ситуации - воспользоваться этими двумя ролями.

db_denydatareader и db_denydatawriter- эти роли противоположны ролям db_datareader и db_datawriter. Роль db_ denydatareader явно запрещает просматривать какие-либо данные, а db_denydatawriter запрещает внесение изменений. Явный запрет всегда имеет приоритет перед явно предоставленными разрешениями. Обычно эти роли используются в ситуации, когда "разрешаем всем, а потом некоторым запрещаем".

Как уже говорилось ранее, в отличие от серверных ролей, роли баз данных вы можете создавать самостоятельно. Это можно сделать из контекстного меню узла «Безопасность | Роли | Роли базы данных» обозревателя объектов в Management Studio или при помощи команды CREATE ROLE.



Рис. 11.8

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

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

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

Работа с разрешениями производится одинаково для всех объектов базы данных:

на вкладке «Разрешения» свойств этого объекта (эта вкладка предусмотрена не для всех объектов, для которых можно предоставить разрешения);

на странице «Защищаемые объекты» окна свойств пользователя или роли;

при помощи команд GRANT (предоставить разрешение), DENY (явно запретить что-то делать) и REVOKE (отменить явно предоставленное разрешение или запрет).

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

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

ALTER - возможность вносить любые изменения в свойства объекта (за исключением смены владельца).

CONTROL - тот, кому предоставлено такое разрешение, получает полные права как на сам объект, так и на информацию в нем.

DELETE - возможность удалять существующую информацию в таблицах. Применяется к таблицам, представлениям и столбцам.

EXECUTE - право запускать на выполнение. Применяется к хранимым процедурам и функциям.

INSERT - право на вставку новых данных в таблицы. Применяется к таблицам, представлениям и столбцам.

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

SELECT - право на чтение информации. Предоставляется для таблиц, представлений, столбцов и табличных функций.

TAKE OWNERSHIP - право на принятие на себя владения данным объектом. Владелец автоматически обладает полными правами на свой объект. Такое право можно назначить для любых объектов.

UPDATE - возможность вносить изменения в существующие записи в таблице. Предоставляется на таблицы, представления и столбцы.

VIEW DEFINITION - право на просмотр определения для данного объекта. Предусмотрено для таблиц, представлений, процедур и функций.

Для каждого разрешения мы можем установить три значения:

GRANT - разрешение предоставлено явно;

WITH GRANT - разрешение не только предоставлено данному пользователю, но он также получил право предоставлять это разрешение другим пользователям. Можно предоставлять, конечно, только вместе с GRANT;

DENY - явный запрет на выполнение действия, определенного данным разрешением. Как в любых системах безопасности, явный запрет имеет приоритет перед явно предоставленными разрешениями.

Из кода Transact-SQL разрешения можно предоставлять командами GRANT, DENY и REVOKE. Например, чтобы предоставить пользователю User1 возможность просматривать данные в таблице Table1 в схеме dbo, можно воспользоваться командой: GRANT SELECT ON dbo.Table1 TO User1;

Лишить его ранее предоставленного права можно при помощи команды: REVOKE SELECT ON dbo.Table1 TO User1;

Некоторые рекомендации, связанные с предоставлением разрешений:

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

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

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

Задание для самостоятельной работы:

Создайте логин SQL Server «Admin» и назначьте ей роль sysadmin;

Создайте в базе данных роль «Saler» и назначьте ей разрешения на выборку данных из всех таблиц, изменение данных в таблицах Order, OrdItem и запуск хранимой процедуры spr_getOrders;

Создайте логин SQL Server «Ivanov» и сопоставьте его с одноименным пользователем в базе данных Sales. Назначьте созданному пользователю роль Saler.

Размещено на Allbest.ru