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

Сценарий

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

• Оригинальные базы данных: товары, клиенты, заказы, аналитика
• Копировать базы данных: продукты_копия, клиенты_копия, заказы_копия, аналитика_копия

Ваша задача — настроить разрешения для пользователя с именем «аналитик» со следующими требованиями:

1. Аналитик должен иметь возможность просматривать базы данных и таблицы из исходных баз данных.
2. Аналитик не должен иметь возможности редактировать исходные базы данных.
3. Аналитик не должен иметь возможности создавать новые базы данных.
4. Аналитик должен иметь полный доступ (просмотр, редактирование, удаление, создание таблиц) к базам данных '_copy'.

Давайте углубимся в то, как этого можно добиться, используя операторы MySQL GRANT и REVOKE.

Шаг 1: Начальная настройка

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

mysql -h hostname -P port -u admin -p

Замените «имя хоста», «порт» и «admin» фактическими данными вашего сервера и именем администратора.

Шаг 2. Создайте пользователя

Если пользователь еще не существует, нам нужно его создать:

CREATE USER 'analyst'@'%' IDENTIFIED BY 'password';

Замените «пароль» на надежный и безопасный пароль.

Шаг 3. Предоставьте необходимые разрешения

Теперь предоставим необходимые разрешения:

-- Grant SELECT on original databases
GRANT SELECT ON products.* TO 'analyst'@'%';
GRANT SELECT ON customers.* TO 'analyst'@'%';
GRANT SELECT ON orders.* TO 'analyst'@'%';
GRANT SELECT ON analytics.* TO 'analyst'@'%';

-- Grant all privileges on copy databases
GRANT ALL PRIVILEGES ON products_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON customers_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON orders_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON analytics_copy.* TO 'analyst'@'%';

-- Grant global privileges
GRANT PROCESS, SHOW DATABASES ON *.* TO 'analyst'@'%';

-- Apply the changes
FLUSH PRIVILEGES;

Шаг 4. Проверьте разрешения

После настройки разрешений очень важно их проверить:

SHOW GRANTS FOR 'analyst'@'%';

Отладка и устранение неполадок

Проблема 1: слишком много привилегий

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

mysql> SHOW GRANTS FOR 'analyst'@'%';
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| Grants for analyst@%                                                                                                                                                                                                                                                                                                              |
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO "analyst"@"%" WITH GRANT OPTION |
| GRANT REPLICATION_APPLIER,ROLE_ADMIN ON *.* TO "analyst"@"%" WITH GRANT OPTION                                                                                                                                                                                                                                                    |
...
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

Решение:

Чтобы это исправить, мы отозвали все привилегии, а затем предоставили только необходимые:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'analyst'@'%';
GRANT PROCESS, SHOW DATABASES ON *.* TO 'analyst'@'%';
-- Then re-grant the specific permissions as shown in Step 3

Проблема 2. Отсутствуют разрешения на копирование баз данных.

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

mysql> SHOW GRANTS FOR 'analyst'@'%';
 ----------------------------------------------------- 
| Grants for analyst@%                                |
 ----------------------------------------------------- 
| GRANT PROCESS, SHOW DATABASES ON *.* TO "analyst"@"%"|
| GRANT SELECT ON "products".* TO "analyst"@"%"       |
| GRANT SELECT ON "customers".* TO "analyst"@"%"      |
| GRANT SELECT ON "orders".* TO "analyst"@"%"         |
| GRANT SELECT ON "analytics".* TO "analyst"@"%"      |
 ----------------------------------------------------- 

Решение:

Мы добавили недостающие разрешения для копий баз данных:

GRANT ALL PRIVILEGES ON products_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON customers_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON orders_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON analytics_copy.* TO 'analyst'@'%';
FLUSH PRIVILEGES;

Окончательный результат

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

mysql> SHOW GRANTS FOR 'analyst'@'%';
 ----------------------------------------------------- 
| Grants for analyst@%                                |
 ----------------------------------------------------- 
| GRANT PROCESS, SHOW DATABASES ON *.* TO "analyst"@"%"|
| GRANT SELECT ON "products".* TO "analyst"@"%"       |
| GRANT SELECT ON "customers".* TO "analyst"@"%"      |
| GRANT SELECT ON "orders".* TO "analyst"@"%"         |
| GRANT SELECT ON "analytics".* TO "analyst"@"%"      |
| GRANT ALL PRIVILEGES ON "products_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "customers_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "orders_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "analytics_copy".* TO "analyst"@"%"|
 ----------------------------------------------------- 

Заключение

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

Запомните следующие ключевые моменты:

1. Всегда начинайте с принципа наименьших привилегий.
2. Используйте SHOW GRANTS для проверки разрешений после внесения изменений.
3. Будьте осторожны с глобальными привилегиями (ON .).
4. Не забудьте УДАЛИТЬ ПРИВИЛЕГИИ после внесения изменений.

Следуя этим рекомендациям и шагам, описанным в этом посте, вы будете готовы эффективно управлять разрешениями пользователей MySQL.