DCL – Data Control Language. Операторы определения доступа к данным языка T-SQL

Приветствую всех посетителей сайта Info-Comp.ru! Сегодня мы с Вами поближе познакомимся с операторами определения доступа к данным DCL в языке T-SQL, узнаем для чего и как их можно использовать, рассмотрим несколько примеров.

Что такое DCL

Язык SQL, с точки зрения реализации, представляет собой набор операторов, которые делятся на определенные группы, и у каждой группы есть свое назначение. Так вот DCL – это одна из таких групп. Подробней о том, какие еще группы есть, можете почитать в отдельной статье – Что такое DDL, DML, DCL и TCL в языке SQL.

Data Control Language (DCL) – это группа операторов определения доступа к данным. Иными словами, это операторы для управления разрешениями, с помощью них мы можем разрешать или запрещать выполнение определенных операций над объектами базы данных.

К операторам DCL относятся:

• GRANT
• REVOKE
• DENY

Давайте подробнее рассмотрим каждый из этих операторов.

Операторы определения доступа к данным в T-SQL

Исходные данные для примеров

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

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

В базе данных у нас есть таблица Goods, она содержит данные о товарах, и процедура TestProcedure. С этими объектами мы и будем работать.

Как Вы понимаете, все данные у нас тестовые, созданные только для демонстрации примеров.

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

   
   USE TestDB;

   GO

   --Создание имени входа
   CREATE LOGIN TestLogin
     WITH PASSWORD='Pa$$w0rd',
     DEFAULT_DATABASE=TestDB;

   GO

   --Создание пользователя базы данных и сопоставление с именем входа
   CREATE USER TestUser FOR LOGIN TestLogin;

   GO

   --Создание таблицы Goods
   CREATE TABLE Goods (
     ProductId       INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
     Category        INT NOT NULL,
     ProductName     VARCHAR(100) NOT NULL,
     Price           MONEY NULL,
   );

   GO

   --Добавление строк в таблицу Goods
   INSERT INTO Goods(Category, ProductName, Price)
     VALUES (1, 'Системный блок', 300),
            (1, 'Монитор', 200),
            (2, 'Смартфон', 100);

   GO

   --Создание процедуры
   CREATE PROCEDURE TestProcedure (@ProductId INT)
   AS
   BEGIN
     SELECT * FROM Goods
     WHERE ProductId = @ProductId;
   END

   GO

   SELECT * FROM Goods;

Заметка! Новичкам рекомендую посмотреть мой видеокурс по T-SQL для начинающих, в нем подробно рассмотрены все базовые конструкции языка T-SQL.

Оператор GRANT

GRANT – предоставляет пользователю или группе разрешения на определённые операции с объектом.

В качестве объекта может выступать: таблица, представление, функция, хранимая процедура и т.д.

Операции над объектами могут быть разными, например, у таблицы это извлечение данных (SELECT), добавление данных (INSERT), изменение данных (UPDATE), удаление данных (DELETE), а также изменение самой таблицы (ALTER).

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

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

Для этого мы можем выполнить вот такую инструкцию.

   
   GRANT SELECT, INSERT, UPDATE, DELETE ON Goods TO TestUser;

Где после команды GRANT мы перечисляем операции, которые хотим разрешить выполнять пользователю. Затем пишем ключевое слово ON и указываем объект, в данном случае таблицу, на который представляем эти права. Далее пишем ключевое слово TO и указываем группу или, как в нашем случае, конкретного пользователя, которому мы представляем права.

И если мы сейчас запустим тот же самый запрос SELECT от имени тестового пользователя, то он у нас выполнится вполне успешно.

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

Однако если он сейчас попытается это сделать, у него выйдет ошибка.

Поэтому давайте дадим ему разрешение на запуск этой процедуры. Это делается следующим образом.

   
   GRANT EXECUTE ON TestProcedure TO TestUser;

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

Оператор REVOKE

REVOKE – отзывает выданные разрешения.

Иным словами, с помощью этого оператора мы можем отменить выданное или запрещенное ранее разрешение.

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

Мы можем это сделать с помощью следующих инструкций.

   
   --Отменяем разрешение на удаление данных
   REVOKE DELETE ON Goods TO TestUser;

   GO

   --Отменяем разрешение на запуск процедуры
   REVOKE EXECUTE ON TestProcedure TO TestUser;

После оператора REVOKE мы пишем разрешения, которые хотим отменить, с помощью ключевого слова ON указываем объект, а с помощью ключевого слова TO указываем субъект, т.е. пользователя, у которого мы хотим отобрать разрешения.

Оператор DENY

DENY– задаёт запрет, имеющий приоритет над разрешением.

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

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

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

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

   
   DENY EXECUTE ON TestProcedure TO TestUser;

В этом случае мы делаем практически все то же самое, только в начале пишем оператор DENY.

Вот мы с Вами и рассмотрели операторы определения доступа к данным языка T-SQL, мы научились предоставлять и отменять разрешения, а также явно запрещать выполнение определённых операций.

Заметка! Еще больше статей, посвященных языку T-SQL, Вы можете найти в сборнике статей для изучения Microsoft SQL Server.

Видео-инструкция

И на сегодня это все, надеюсь, материал был Вам полезен, удачи Вам!