Раздел 34 · MySQL с нуля

Пользователи, привилегии и резервные копии

junior~25 мин

без входаВойди, чтобы отмечать прогресс, вести заметки и решать задачи в редакторе. войти

В прошлом уроке мы положили в базу процедуры и триггеры. Всё это время мы работали под root, которому можно всё, включая DROP DATABASE kino. Приложению кассы столько власти не нужно, а ошибка в его коде с такими правами стоит всей базы. Сегодня две темы эксплуатации: как раздать права так, чтобы каждый мог только своё, и как сделать копию базы, из которой её можно поднять заново.

Ключ только от своей двери

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

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

Что такое учётная запись 'имя'@'хост' и как выглядит первый GRANT, мы разобрали в уроке про базу и пользователя. Здесь пойдём дальше: права на отдельные таблицы, отзыв прав и типичные ошибки.

Права на таблицу и на базу

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

CREATE USER 'cashier'@'%' IDENTIFIED BY 'demo-only-pass';
CREATE USER 'analyst'@'%' IDENTIFIED BY 'demo-only-pass';

Кассе нужно читать, продавать и менять билеты, а ещё смотреть расписание сеансов. Выдаём привилегии на две таблицы, а не на всю базу:

GRANT SELECT, INSERT, UPDATE ON kino.tickets TO 'cashier'@'%';
GRANT SELECT ON kino.screenings TO 'cashier'@'%';

Аналитику нужно читать всё, но ничего не менять. kino.* значит «все таблицы базы kino», включая те, что появятся потом:

GRANT SELECT ON kino.* TO 'analyst'@'%';

Проверим, что получилось:

SHOW GRANTS FOR 'cashier'@'%';
SHOW GRANTS FOR 'analyst'@'%';
+-------------------------------------------------------------------+
| Grants for cashier@%                                              |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `cashier`@`%`                               |
| GRANT SELECT ON `kino`.`screenings` TO `cashier`@`%`              |
| GRANT SELECT, INSERT, UPDATE ON `kino`.`tickets` TO `cashier`@`%` |
+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

+-------------------------------------------+
| Grants for analyst@%                      |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `analyst`@`%`       |
| GRANT SELECT ON `kino`.* TO `analyst`@`%` |
+-------------------------------------------+
2 rows in set (0.00 sec)

USAGE ON *.* значит «может подключиться, больше ничего». Остальные строки и есть ключи. Если касса попробует DELETE FROM tickets или SELECT из viewers, сервер ответит ошибкой доступа: таких прав в списке нет. В песочнице одна сессия под root, поэтому войти под кассой здесь не получится, у себя на сервере попробуй обязательно.

REVOKE: забрать право

Решили, что исправлять старые билеты будет только администратор. Забираем у кассы UPDATE:

REVOKE UPDATE ON kino.tickets FROM 'cashier'@'%';
SHOW GRANTS FOR 'cashier'@'%';
+-----------------------------------------------------------+
| Grants for cashier@%                                      |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `cashier`@`%`                       |
| GRANT SELECT ON `kino`.`screenings` TO `cashier`@`%`      |
| GRANT SELECT, INSERT ON `kino`.`tickets` TO `cashier`@`%` |
+-----------------------------------------------------------+
3 rows in set (0.00 sec)

REVOKE зеркален GRANT: те же права, тот же объект, только FROM вместо TO. Забрать то, чего не выдавали, нельзя:

REVOKE DELETE ON kino.tickets FROM 'cashier'@'%';
ERROR 1147 (42000): There is no such grant defined for user 'cashier' on host '%' on table 'tickets'

Ещё одна тонкость: права на таблицу и на базу живут отдельно. Если выдать SELECT ON kino.*, а потом попытаться отозвать SELECT ON kino.tickets, получишь ту же ошибку 1147. Отзывать нужно на том уровне, на котором выдавали.

Типичные ошибки

Опечатка в имени таблицы:

GRANT SELECT ON kino.nosuch TO 'analyst'@'%';
ERROR 1146 (42S02): Table 'kino.nosuch' doesn't exist

Опечатка в имени пользователя. В старых версиях MySQL такой GRANT молча создавал нового пользователя без пароля. С версии 8.0 сервер отказывает:

GRANT SELECT ON kino.* TO 'ghost'@'%';
ERROR 1410 (42000): You are not allowed to create a user with GRANT

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

Аналитик уволился. DROP USER удаляет учётную запись вместе со всеми её правами:

DROP USER 'analyst'@'%';
SHOW GRANTS FOR 'analyst'@'%';
Query OK, 0 rows affected (0.00 sec)

ERROR 1141 (42000): There is no such grant defined for user 'analyst' on host '%'

Повторный DROP USER упадёт с ошибкой 1396. В скриптах пиши IF EXISTS, тогда вместо ошибки будет заметка:

DROP USER IF EXISTS 'analyst'@'%';
SHOW WARNINGS;
Query OK, 0 rows affected, 1 warning (0.00 sec)

+-------+------+------------------------------------------------+
| Level | Code | Message                                        |
+-------+------+------------------------------------------------+
| Note  | 3162 | Authorization ID 'analyst'@'%' does not exist. |
+-------+------+------------------------------------------------+
1 row in set (0.00 sec)

Сами мы всё это время работали так:

SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

root нужен администратору для создания пользователей и баз. Приложению никогда не отдавай пароль root: заведи ему своего пользователя с правами под его задачу.

Резервная копия: mysqldump

Права защищают от чужих ошибок, но не от всех. Диск сгорел, администратор перепутал окно терминала и выполнил DROP TABLE tickets на рабочем сервере. Спасёт только резервная копия.

Самый простой способ снять её в MySQL это программа mysqldump. Она пишет файл с SQL-командами, которые создают таблицы и вставляют строки. Вот как выглядит выгрузка одной таблицы halls:

docker exec mysql mysqldump -uroot -pchange-me kino halls
mysqldump: [Warning] Using a password on the command line interface can be insecure.
-- MySQL dump 10.13  Distrib 8.4.13, for Linux (aarch64)
--
-- Host: localhost    Database: kino
-- ------------------------------------------------------
-- Server version	8.4.13

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `halls`
--

DROP TABLE IF EXISTS `halls`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `halls` (
  `id` int NOT NULL,
  `name` varchar(30) NOT NULL,
  `seats` smallint unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `halls`
--

LOCK TABLES `halls` WRITE;
/*!40000 ALTER TABLE `halls` DISABLE KEYS */;
INSERT INTO `halls` VALUES (1,'Большой',120),(2,'Малый',40),(3,'VIP',12);
/*!40000 ALTER TABLE `halls` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2026-10-24 11:44:49

Пугаться не нужно, читается это сверху вниз:

  1. Комментарии /*!40101 ... */ выполнит только MySQL версии 4.1.1 и новее, остальные базы их пропустят. Они сохраняют настройки сессии и временно отключают проверку внешних ключей, чтобы таблицы можно было загружать в любом порядке.
  2. DROP TABLE IF EXISTS и CREATE TABLE пересоздают таблицу. Восстановление из дампа затрёт текущую таблицу с тем же именем.
  3. INSERT одной командой вставляет все строки.
  4. В конце настройки сессии возвращаются на место.

Первая строка с предупреждением про пароль идёт не в файл, а в терминал. Пароль в командной строке остаётся в истории команд и может мелькнуть в списке процессов, поэтому на настоящем сервере его кладут в файл настроек клиента.

Снять и восстановить

Целую базу снимают в файл так:

docker exec mysql mysqldump -uroot -pchange-me --single-transaction --routines kino > kino.sql

Два флага здесь важны:

  • --single-transaction открывает транзакцию и читает все таблицы из одного снимка, как в REPEATABLE READ из урока про транзакции. Касса продолжает продавать билеты, а в дамп попадает база на один момент времени, без билета, который успели продать на середине выгрузки. Работает это только для InnoDB.
  • --routines добавляет процедуры и функции. Триггеры mysqldump выгружает и без флага, а процедуры нет, и без --routines sell_ticket в копию не попадёт.

Восстанавливают обычным клиентом mysql, подав файл ему на вход. Флаг -i у docker exec пропускает файл внутрь контейнера:

docker exec -i mysql mysql -uroot -pchange-me kino < kino.sql

Дамп одной базы не содержит CREATE DATABASE, поэтому база kino должна существовать до восстановления. Если нужна команда создания базы в самом файле, снимай дамп с флагом --databases kino.

Проверь, что копия живая

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

docker exec mysql mysql -uroot -pchange-me -e "CREATE DATABASE kino_check"
docker exec -i mysql mysql -uroot -pchange-me kino_check < kino.sql
docker exec mysql mysql -uroot -pchange-me -e "SELECT COUNT(*) FROM kino_check.tickets"

Число билетов должно совпасть с рабочей базой на момент снятия. И храни копии не на том же сервере: сгорит диск, сгорят и они.

Для большой базы на сотни гигабайт mysqldump медленный, там используют физическое копирование файлов и бинарный журнал, о котором пойдёт речь в следующем уроке. Как то же устроено в PostgreSQL, с ролями и pg_dump, смотри в уроке про роли и резервные копии.

Попробуй сам

В песочнице ты работаешь под root. Запрос создаёт кассу, выдаёт ей права на две таблицы, забирает одно право и пробует забрать право, которого не было.

Заведи аналитика с правом читать всю базу и попробуй отозвать у него SELECT только на kino.tickets. Выдай GRANT несуществующему пользователю и прочитай ошибку. Удали кассу дважды, второй раз с IF EXISTS.

Проверь себя

Задание

Дальше

Резервная копия спасает от катастрофы, но восстановление занимает время, и всё, что продали после снятия копии, пропадёт. В последнем уроке раздела разбираем репликацию: второй сервер, который в реальном времени повторяет всё за первым.

домашка

Домашка