МДК.11.01 - 24 - Создание пользователей и назначение привилегий

Примеры данной темы используют учебную БД:

SET foreign_key_checks = 0;

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS accounts;
DROP TABLE IF EXISTS transactions;

CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `age` INT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `accounts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) DEFAULT NULL,
  `user_id` INT NOT NULL,
  `balance` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `user_id_idx` (`user_id`),
  CONSTRAINT `user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

CREATE TABLE `transactions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `date_time` DATETIME NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
  `transaction_type` ENUM('deposit', 'withdrawal', 'transfer') NOT NULL,
  `user_id` INT NOT NULL,
  `account_id` INT NOT NULL,
  `transfer_to_account_id` INT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id_idx` (`user_id`),
  KEY `account_id_idx` (`account_id`),
  CONSTRAINT `transaction_user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `account_id`
    FOREIGN KEY (`account_id`)
    REFERENCES `accounts` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES
(1,'Иванов Иван Иванович', 31),
(2,'Петров Петр Петрович', 27),
(3,'Сидоров Сидор Сидорович', 35);
UNLOCK TABLES;

LOCK TABLES `accounts` WRITE;
INSERT INTO `accounts` VALUES (1,'Основной',1,10500.00),(2,'Резервный',1,3412.57),(3,'Основной',2,20750.00),(4,'Основной',3,25000.00),(5,'Накопительный',2,5401.75);
UNLOCK TABLES;

LOCK TABLES `transactions` WRITE;
INSERT INTO `transactions` (`date_time`, `amount`, `transaction_type`, `user_id`, `account_id`, `transfer_to_account_id`)
VALUES
('2022-01-01 12:00:00', 100.00, 'deposit', 1, 1, NULL),
('2022-01-02 15:30:00', 50.00, 'withdrawal', 1, 1, NULL),
('2022-01-03 09:45:00', 200.00, 'deposit', 2, 3, NULL),
('2022-01-04 14:20:00', 75.00, 'withdrawal', 2, 3, NULL),
('2022-01-05 11:10:00', 150.00, 'transfer', 3, 4, 5);
UNLOCK TABLES;

SET foreign_key_checks = 1;

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

Создание пользователя

Новый пользователь в MySQL добавляется командой:

CREATE USER 'имя_пользователя'@'имя_хоста' IDENTIFIED BY 'уникальный_пароль';

Теперь давайте создадим нашего первого пользователя:

CREATE USER 'test'@'localhost' IDENTIFIED BY 'PaSsWoRd';

Эта команда выполняет следующие действия:

• Создание пользователя test с доступом только с локального хоста localhost.
• Пользователь будет иметь пароль PaSsWoRd.

Убедиться в отсутствии каких-либо привилегий мы можем даже не подключаясь к серверу от его имени, достаточно вспомнить материалы прошлой темы и воспользоваться запросом (не забываем про модификатор \G если работаем напрямую через терминал):

SELECT * FROM mysql.user WHERE User = 'test' AND Host = 'localhost';

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

Удаление пользователя

Давайте для начала удалим нашего "бесполезного" пользователя. Сделать это можно при помощи команды DROP:

DROP USER 'test'@'localhost';

Создание дополнительного суперпользователя

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

CREATE USER 'admin'@'%' IDENTIFIED BY 'PaSsWoRd';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Данная команда используется для предоставления полных привилегий пользователю admin на все базы данных и все таблицы в MySQL.

Давайте разберемся, что означает каждая часть этой команды:

• GRANT ALL PRIVILEGES: Эта часть команды указывает, что мы хотим предоставить все привилегии пользователю.
• ON *.*: Здесь указывается, на какие базы данных и таблицы будут распространяться предоставленные привилегии. Знак * перед точкой означает "все базы данных", а знак * после точки означает "все таблицы".
• TO 'admin'@'%': В этой части указывается имя пользователя и его хост. В данном случае, пользователь с именем admin будет иметь эти привилегии. Символ % означает, что пользователь может подключаться к серверу с любого хоста.
• WITH GRANT OPTION: Эта часть указывает, что пользователь admin сможет предоставлять эти же привилегии другим пользователям.

Таким образом, после выполнения этой команды пользователь admin будет иметь полный доступ ко всем базам данных и таблицам в MySQL, и сможет также предоставлять эти же привилегии другим пользователям.

Отзыв всех привилегий у суперпользователя

Команда отзыва привилегий функционально обратна GRANT, с той лишь разницей что TO заменяется на FROM. Давайте попробуем лишить нашего созданного ранее суперпользователя всех привилегий и возможности предоставления другим:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'admin'@'%';
FLUSH PRIVILEGES;

После чего, если данный пользователь больше не нужен, его вполне можно удалить:

DROP USER 'admin'@'%';

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

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

1. У пользователя полный доступ только к этой БД:

CREATE USER 'bank_db_admin'@'localhost' IDENTIFIED BY 'PaSsWoRd_1';
GRANT ALL PRIVILEGES ON bank_db.* TO 'bank_db_admin'@'localhost';
FLUSH PRIVILEGES;

Типовые запросы для проверки:

SELECT * FROM accounts;
INSERT INTO accounts (title, user_id, balance) VALUES ('Тестовый', 1, 100);
UPDATE accounts SET title = 'Тестовый счёт' WHERE id = 6;
DELETE FROM accounts WHERE id = 6;

2. У пользователя доступ только к одной таблице на выбор, добавление, обновление и удаление значений:

CREATE USER 'bank_db_manager'@'localhost' IDENTIFIED BY 'PaSsWoRd_2';
GRANT SELECT, INSERT, UPDATE, DELETE ON bank_db.transactions TO 'bank_db_manager'@'localhost';
FLUSH PRIVILEGES;

Типовые запросы для проверки:

SELECT * FROM transactions;

3. У пользователя доступ только к двум столбцам только на чтение:

CREATE USER 'bank_db_inspector'@'localhost' IDENTIFIED BY 'PaSsWoRd_3';
GRANT SELECT(user_id, balance) ON bank_db.accounts TO 'bank_db_inspector'@'localhost';
FLUSH PRIVILEGES;

Типовые запросы для проверки:

SELECT user_id, balance FROM accounts;

Отзыв конкретного набора привилегий у пользователя

Допустим, нам необходимо отозвать у пользователя 'bank_db_manager'@'localhost' возможность добавлять и удалять данные, оставим только возможность их запрашивать и обновлять:

REVOKE INSERT, DELETE ON bank_db.transactions FROM 'bank_db_manager'@'localhost';
FLUSH PRIVILEGES;

Аналогичным образом будет работать отзыв всех привилегий, на примере утратившего доверие пользователя 'bank_db_admin'@'localhost':

REVOKE ALL PRIVILEGES ON bank_db.* FROM 'bank_db_admin'@'localhost';
FLUSH PRIVILEGES;