SQL-Ex blog

Понимание необходимости GRANT и REVOKE

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

Операторы SQL GRANT и REVOKE предназначены для управления правами и разрешениями, предоставляемым пользователям. GRANT позволяет дать конкретные разрешения пользователю, а REVOKE позволяет отменить разрешения. Давайте рассмотрим синтаксис и функциональность этих операторов.

Синтаксис GRANT

Синтаксис оператора GRANT использует конкретный шаблон. Вы можете использовать его для предоставления разрешений конкретных типов, таких как полный или ограниченный доступ к ресурсам, к указанной таблице в конкретной схеме. Эти разрешения присваиваются пользователю с зарегистрированным именем на локальном сервере, для обращения к которому часто используется имя "localhost", которое является доменным именем для локального IP-адреса (127.0.0.1) на вашей машине.

Например, в среде MySQL вы можете использовать следующий код для создания пользователя с именем 'frank' и паролем 'pass':

CREATE USER 'frank'@'localhost' IDENTIFIED BY 'pass';

Пусть теперь вы хотите разрешить Франку доступ к конкретным ресурсам вашей базы данных, например, к таблице 'Customers'. Вы можете предоставить ему разрешение только для выполнения оператора SELECT к таблице 'Customers' при помощи следующего кода:

GRANT SELECT ON sales.Customers TO 'frank'@'localhost';

Имея это разрешение, Франк может выполнять только оператор SELECT к таблице 'Customers'. Он не сможет использовать другие команды SQL типа DROP, TRUNCATE, ALTER, INSERT, DELETE или любые другие команды манипуляции данными.

Сила GRANT: пример из реальной жизни

Для демонстрации воздействия оператора GRANT давайте обеспечим визуализацию с использованием MySQL Workbench. При входе Франк может только просмотреть таблицу 'Customers' в базе данных 'sales'. У него нет доступа к другим таблицам, в частности, 'sales' и 'items'. Если он попытается выполнить оператор ALTER или любую другую запрещенную команду, возникнет ошибка. Однако при выполнении оператора SELECT, будет немедленно получен ответ, подтверждая успешное выполнение команды.

Но что если вы хотите дать Франку полный доступ ко всем таблицам в базе данных 'sales'? Для этого вы можете использовать следующий код:

GRANT ALL PRIVILEGES ON sales.sales TO 'frank'@'localhost';

С помощью этого оператора Франк получает полный доступ к таблице sales в базе данных 'sales', включая возможность выполнять DROP, TRUNCATE, SELECT, INSERT, ALTER и другие команды SQL. После выполнения этого кода вы можете убедиться при проверке профиля пользователя Frank, что он может видеть в базе данных 'sales' не только таблицу 'Customers', но и sales.

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

Пользователей с полными правами на базу данных часто называют администраторами базы данных; они имеют право представлять или отменять доступ другим пользователям. Это означает, что они могут использовать оба оператора GRANT и REVOKE для управления разрешениями и привилегиями. GRANT используется для предоставления разрешений, а REVOKE выполняет обратную функцию, отменяя разрешения и привилегии пользователей. Синтаксис REVOKE подобен синтаксису GRANT.

Например, мы изначально предоставили Франку привилегию на выполнение оператора SELECT к таблице 'Customers' в базе данных 'sales'. Чтобы отменить это разрешение мы можем использовать следующий оператор REVOKE:

REVOKE SELECT ON sales.Customers FROM 'frank'@'localhost';

При выполнении этого оператора Франк больше не будет иметь разрешения на использование команды SELECT к таблице 'Customers'. Его доступ ограничен.

Сценарий: база данных библиотеки

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

1. Books: В этой таблице хранится информация о коллекции книг в библиотеке, включая название, авторов и год издания.


2. Members: В этой таблице содержатся данные о посетителях библиотеки, такие как имя, ID члена, контактная информация.


3. Staff: Эта таблица хранит информацию о штате библиотеки, включая имя сотрудника, его ID, и должность.

-- Создаем базу данных LibraryDB

CREATE DATABASE LibraryDB;

-- Используем базу данных LibraryDB

USE LibraryDB;

-- Создаем таблицу Books

CREATE TABLE Books (

    BookID INT PRIMARY KEY AUTO_INCREMENT,

    Title VARCHAR(255) NOT NULL,

    Author VARCHAR(255),

    PublicationYear INT,

    Genre VARCHAR(50),

    AvailableCopies INT

);

-- Создаем таблицу Members

CREATE TABLE Members (

    MemberID INT PRIMARY KEY AUTO_INCREMENT,

    FirstName VARCHAR(50) NOT NULL,

    LastName VARCHAR(50) NOT NULL,

    ContactInfo VARCHAR(255)

);

-- Создаем таблицу Staff

CREATE TABLE Staff (

    StaffID INT PRIMARY KEY AUTO_INCREMENT,

    FirstName VARCHAR(50) NOT NULL,

    LastName VARCHAR(50) NOT NULL,

    Role VARCHAR(50)

);

-- Создаем таблицу BorrowedBooks

CREATE TABLE BorrowedBooks (

    BorrowID INT PRIMARY KEY AUTO_INCREMENT,

    BookID INT,

    MemberID INT,

    BorrowDate DATE,

    ReturnDate DATE,

    FOREIGN KEY (BookID) REFERENCES Books(BookID),

    FOREIGN KEY (MemberID) REFERENCES Members(MemberID)

);

-- Вставляем данные в таблицу Books

INSERT INTO Books (Title, Author, PublicationYear, Genre, AvailableCopies) VALUES

('To Kill a Mockingbird', 'Harper Lee', 1960, 'Fiction', 5),

('1984', 'George Orwell', 1949, 'Dystopian', 7),

('The Great Gatsby', 'F. Scott Fitzgerald', 1925, 'Classics', 4),

('Pride and Prejudice', 'Jane Austen', 1813, 'Romance', 3),

('The Catcher in the Rye', 'J.D. Salinger', 1951, 'Fiction', 6),

('Harry Potter and the Sorcerer''s Stone', 'J.K. Rowling', 1997, 'Fantasy', 8),

('The Hobbit', 'J.R.R. Tolkien', 1937, 'Fantasy', 5),

('Brave New World', 'Aldous Huxley', 1932, 'Dystopian', 4),

('The Lord of the Rings', 'J.R.R. Tolkien', 1954, 'Fantasy', 7),

('War and Peace', 'Leo Tolstoy', 1869, 'Historical Fiction', 2);

-- Вставляем данные в таблицу Members

INSERT INTO Members (FirstName, LastName, ContactInfo) VALUES

('Alice', 'Smith', 'alice@example.com'),

('Bob', 'Johnson', 'bob@example.com'),

('Charlie', 'Davis', 'charlie@example.com'),

('David', 'Wilson', 'david@example.com'),

('Eve', 'Brown', 'eve@example.com'),

('Frank', 'Jones', 'frank@example.com'),

('Grace', 'Clark', 'grace@example.com'),

('Henry', 'Lee', 'henry@example.com'),

('Ivy', 'Young', 'ivy@example.com'),

('Jack', 'Moore', 'jack@example.com');

-- Вставляем данные в таблицу Staff

INSERT INTO Staff (FirstName, LastName, Role) VALUES

('Linda', 'White', 'Librarian'),

('Michael', 'Johnson', 'Clerk'),

('Olivia', 'Miller', 'Librarian'),

('William', 'Smith', 'Clerk'),

('Sophia', 'Davis', 'Librarian'),

('James', 'Wilson', 'Clerk'),

('Emily', 'Moore', 'Librarian'),

('Benjamin', 'Brown', 'Clerk'),

('Mia', 'Taylor', 'Librarian'),

('Oliver', 'Harris', 'Clerk');

-- Вставляем данные в таблицу BorrowedBooks

INSERT INTO BorrowedBooks (BookID, MemberID, BorrowDate, ReturnDate) VALUES

(1, 1, '2023-09-15', '2023-09-30'),

(3, 4, '2023-09-20', '2023-10-05'),

(5, 2, '2023-09-25', '2023-10-10'),

(6, 3, '2023-09-12', '2023-09-27'),

(8, 7, '2023-09-30', '2023-10-15'),

(2, 8, '2023-09-10', '2023-09-25'),

(7, 9, '2023-09-05', '2023-09-20'),

(10, 6, '2023-09-17', '2023-10-02'),

(4, 5, '2023-09-22', '2023-10-07'),

(9, 10, '2023-09-08', '2023-09-23');

Теперь давайте применять команды GRANT и REVOKE в контексте этой библиотечной базы данных.

Шаг 1: Создание пользователя

Сначала давайте создадим нового пользователя с именем ‘librarian’ (библиотекарь), кто будет отвечать за управление библиотечным каталогом. Этот пользователь будет нуждаться в разрешениях на выполнение конкретных действий в базе данных.

CREATE USER 'librarian'@'localhost' IDENTIFIED BY 'password';

Шаг 2: Предоставление разрешений с помощью GRANT

Теперь мы хотим предоставить пользователю ‘librarian’ следующие разрешения:

1. Разрешение SELECT на таблицу Books.


2. Разрешение INSERT на таблицу Books (для добавления новых книг).


3. Разрешение UPDATE на таблицу Books (для обновления информации о книгах).


4. Разрешение DELETE на таблицу Books (для удаления книг).

Мы можем использовать оператор GRANT, чтобы это реализовать:

GRANT SELECT, INSERT, UPDATE, DELETE ON libraryDB.Books TO 'librarian'@'localhost';

Эта команда дает ‘librarian’ необходимые разрешения для эффективного управления библиотечным каталогом.

Шаг 3: Использование разрешений

Теперь, когда пользователь ‘librarian’ авторизуется в системе, он сможет:

1. Выполнять операторы SELECT для поиска книг в таблице 'Books'.


2. Добавлять новые книги, используя операторы INSERT.


3. Обновлять информацию о книгах, используя операторы UPDATE.


4. Удалять книги из каталога, используя операторы DELETE.

Например, библиотекарь может выполнить следующие команды SQL:

-- Выбрать книги конкретного автора

SELECT * FROM libraryDB.Books WHERE author = 'J.K. Rowling';

-- Добавить новую книгу в каталог

INSERT INTO libraryDB.Books (title, author, publicationyear) VALUES ('New Book', 'Author Name', 2023);

-- обновить информацию о книге

UPDATE libraryDB.Books SET publicationyear = 2024 WHERE title = 'New Book';

-- Удалить книгу из каталога

DELETE FROM libraryDB.Books WHERE title = 'New Book';

Шаг 4: Отмена привилегий с помощью REVOKE

Предположим, что роль пользователя ‘librarian’ изменилась, и возможность удалять книги ей теперь не нужна. Вы можете отменить разрешение, используя оператор REVOKE:

REVOKE DELETE ON libraryDB.Books FROM 'librarian'@'localhost';

Теперь пользователь ‘librarian’ больше не сможет выполнять операторы DELETE для таблицы 'Books'.

1. Вопрос: Каково назначение оператора GRANT в SQL?

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

2. Вопрос: В нашей библиотечной базе данных какой оператор должен использоваться для предоставлению пользователю разрешения на выборку названий и авторов книг из таблицы Books?

Ответ: Для предоставления пользователю разрешений на выборку названий и авторов книг из таблицы Books можно использовать следующий оператор GRANT:

GRANT SELECT ON LibraryDB.Books TO 'username'@'localhost';

3. Вопрос: Как отменить пользователю разрешение на выполнение команды DELETE для таблицы Books в баблиотечной базе данных?

Ответ: Чтобы отменить разрешение пользователя на выполнение команды DELETE для таблицы Books, вы можете использовать оператор REVOKE:

REVOKE DELETE ON LibraryDB.Books FROM 'username'@'localhost';

4. Вопрос: В контексте базы данных библиотеки, кто обычно использует операторы GRANT и REVOKE?

Ответ: Администраторы базы данных, которыми могут быть библиотекари или сотрудники библиотеки с соответствующими привилегиями, могут использовать операторы GRANT и REVOKE для управления доступом и разрешениями в базе данных библиотеки.

5. Вопрос: Можно ли предоставить разрешения пользователю одновременно на несколько таблиц с помощью одного оператора GRANT?

Ответ: Да, вы можете предоставить разрешения на несколько таблиц одновременно, перечислив таблицы в том же операторе GRANT. Например, чтобы предоставить разрешения на обе таблицы Books и Members, вы можете использовать:

GRANT SELECT ON LibraryDB.Books, LibraryDB.Members TO 'username'@'localhost';

6. Вопрос: Что делает запрос SELECT * FROM information_schema.user_privileges в контексте библиотечной базы данных?

Ответ: Запрос SELECT * FROM information_schema.user_privileges может использоваться для получения информации о привилегиях и разрешениях, предоставленных различным пользователям в базе данных библиотеки.

7. Вопрос: Кто в базе данных библиотеки имеет права на отмену разрешений, используя оператор REVOKE?

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

8. Вопрос: Мржет ли пользователь с ограниченными разрешениями в нашей базе данных библиотеки предоставлять эти ограниченные разрешения другим пользователям?

Ответ: В типичной модели безопасности SQL пользователь с ограниченными разрешениями не может предоставить те же ограниченные разрешения другим пользователям. Только пользователи с достаточными привилегиями могут использовать операторы GRANT и REVOKE.

9. Вопрос: Как проверить разрешения и привилегии, которые пользователь имеет в базе данных библиотеки?

Ответ: Вы можете выполнить запрос к представлению information_schema.user_privileges или проверить назначение привилегий пользователю, используя оператор SHOW GRANTS, чтобы увидеть текущие разрешения пользователя.

10. Вопрос: В чем состоит основное преимущество команд GRANT и REVOKE в нашей библиотечной базе данных?

Ответ: Главным преимуществом команд GRANT и REVOKE является возможность точечного управления доступом и разрешениями пользователя, гарантирующего, что пользователи могут выполнять только те действия и иметь доступ только к тем данным, которые соответствуют их роли и ответственности в базе данных библиотеки.

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

Ответ: Для оптимизации таблицы BorrowedBooks с целью повышения производительности, можно рассмотреть несколько стратегий, таких как использование подходящего индексирования, серционирования таблицы на на основе времени или категории книг, а также архивации исторических данных. Например, можно создать индекс на столбцах MemberID и BorrowDate, чтобы ускорить запросы, связанные с историей проката книг клиентом библиотеки. Кроме того, секционирование может помочь более эффективно управлять данными, а архивация устаревших данных будет поддерживать размер таблицы в разумных для обслуживания пределах.

12. Вопрос: Объясните концепцию управления доступом на основе ролей (RBAC) в контексте базы данных библиотеки. Как она может быть применена для усиления безопасности и эффективного управления привилегиями пользователя?

Ответ: Управление доступом на основе ролей (RBAC) является методом контроля доступа посредством предоставления разрешений ролям, а не отдельным пользователям. В базе данных библиотеки RBAC может быть реализован посредством определения таких ролей, как ‘Librarian’ и ‘Clerk’, и присвоением конкретных разрешений каждой роли. Затем пользователям назначаются роли. Этот подход упрощает управление доступом и гарантирует, что пользователи с одинаковой ответственностю имеют согласованные разрешения. Например ‘Librarian’ может иметь разрешения на модификацию записей книг, а ‘Clerk’ может только получать информацию о книгах.

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

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

14. Вопрос: Что представляет потенциальные риски безопасности, связанные с операторами GRANT и REVOKE в SQL, и как уменьшить эти риски в производственной базе данных библиотеки?

Ответ: Операторы GRANT и REVOKE могут спровоцировать риски безопасности, если применены непродуманно. Например, предоставление чрезмерных разрешений пользователю может привести к утечке данных или несанкционированным действиям. Для уменьшения этих рисков важно следовать принципу минимальных привилегий, предоставляя пользователю только те минимальные разрешения, которые необходимы им для выполнения своей работы. Регулярно пересматривайте и выполняйте аудит разрешений для гарантии, что они согласуются с ролями пользователей. Помимо этого, рассмотрите возможность использования инструментов аудита и мониторинга базы данных для обнаружения и ответа на необычную активность.

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

Ответ: Высоая надежность и доступность данных может быть достигнута за счет такой стратегии как репликация и переключение базы данных. В сценарии библиотечной базы данных вы можете применить репликацию MySQL, установив конфигурацию главный-подчиненный. Главная база данных обрабатывает операции записи, тогда как подчиненная база данных служит репликой только на чтение. Автоматической переключение может быть достигнуто при использовании таких инструментов, как Keepalived и HAProxy. Если главная база данных становится недоступной, система может автоматически переключиться на подчиненную, обеспечивая непрерывность обслуживания. Кроме того, вы можете периодически выполнять создание резервных копий и восстановление их в безопасном вне сайта месте для защиты от потери данных во время сбоев.

Заключение

В мире SQL управление доступом и разрешениями пользователей является критичной задачей, а операторы GRANT и REVOKE являются незаменимыми инструментами для ее решения. GRANT позволяет назначать пользователям конкретные разрешения, а REVOKE позволяет отменять эти разрешения. Эти операторы дают вам детальный контроль над тем, кто что может делать в базе данных.

Эта статья представила введение в мир DCL в SQL, фокусируясь на операторах GRANT и REVOKE. Мы рассмотрели их синтаксис и продемонстрировали применение на реальных примерах. В следующей статье мы более детально изучим синтаксис и дополнительные аспекты этих операторов.

Ссылки по теме
1. Секреты индексов и внешних ключей
2. Нюансы индексов в MySQL
3. Как секционировать таблицы MySQL
4. Резервирование в MySQL: физические и логические резервные копии
5. Работа с хранимыми процедурами в MySQL