Как создать нового пользователя MySQL (MariaDB) и настроить его статус и права

В этой заметке будет показано как создать нового пользователя MySQL (MariaDB), а также настроить его привилегии (права) на уровне СУБД в целом, на уровне определённой базы данных и даже на уровне отдельных таблиц и столбцов.

Зачем создавать нового пользователя MySQL или MariaDB

Использование root для выполнения операций языка управления данными (DML) в базе данных – это плохая идея.

Имея выделенных, отдельных пользователей для каждой базы данных предотвратит вред для всех баз данных от компрометации одного аккаунта. Это означает что в случае, например, взлома одного из веб-сайтов (например, при обнаружении в нём уязвимости SQL-инъекция), то при использовании одного аккаунта на всех веб-сайтах, злоумышленнику будут доступны для чтения все базы данных.

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

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

Чтобы избежать это, мы можем создать новый пользовательский аккаунт MySQL (MariaDB).

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

Все последующие запросы делаются в консоли MySQL (MariaDB). Для подключения к серверу MySQL (MariaDB) используйте соответствующие клиенты:

mysql -u root -p

Или:

mariadb -u root -p

Как создать нового пользователя MySQL или MariaDB

Для создания нового пользователя используйте команду вида:

CREATE USER USERNAME@localhost IDENTIFIED BY 'USERS-PASSWORD';

Например, следующая команда создаст пользователя с именем «mial» и паролем «HackWare.ru».

CREATE USER mial@localhost IDENTIFIED BY 'HackWare.ru';

Примечание: MySQL и MariaDB поддерживают несколько алгоритмов хеширования пароля, а также двух и трёх факторные аутентификации. Установить алгоритм хеширования пароля и многофакторные аутентификации вы можете при создании пользователя. Подробности смотрите по ссылке: https://dev.mysql.com/doc/refman/8.1/en/create-user.html

Почему пользователь MySQL (MariaDB) не может создавать и просматривать базы данных

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

Примечание: исключением являются базы данных «information_schema» и «test», к которым разрешён доступ любому пользователю.

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

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

Смотрите также: Ошибка «ERROR 1044 (42000): Access denied for user ‘mial’@’localhost’ to database ‘TestDB’». Не удаётся создать базу данных MySQL (РЕШЕНО)

Важная информация о привилегиях пользователей в MySQL и MariaDB.

В этом разделе кратко перечислено то, что нужно знать о привилегия (правах доступа) пользователей в MySQL (MariaDB).

1. Права доступа в MySQL (MariaDB) называются «привилегиями».

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

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

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

5. При удалении базы данных (или другой единицы, на которую предоставлена привилегия: таблицы или колонки), выданные пользователю привилегии не исчезают! Это не только контринтуитивно, но и различается от некоторых других SQL СУБД. Итак, выданные пользователю привилегии никак не привязаны с существованием баз данных и таблиц (а также колонок) на которые они выданы. Можно выдавать привилегии на несуществующие базы данных и таблицы — в этом случае пользователь сможет воспользоваться своими привилегиями после созданиях этих единиц.

Смотрите также: Как вывести список пользователей MySQL / MariaDB и их привилегии (с помощью клиента СУБД и PHP)

Как разрешить пользователю создавать базы данных

Запрос для разрешения пользователю _USERNAME_ создавать любые базы данных выглядит следующим образом:

GRANT CREATE ON *.* TO '_USERNAME_'@'localhost';

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

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

GRANT ALL PRIVILEGES ON *.* TO '_USERNAME_'@'localhost';

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

Вы можете предоставить все привилегии на одну конкретную базу данных. Если вы хотите предоставить пользователю все возможные привилегии на базу данных, то используйте запрос вида (замените _TestDB_ на имя вашей базы данных и замените _USERNAME_ на имя пользователя):

GRANT ALL PRIVILEGES ON _TestDB_.* TO '_USERNAME_'@'localhost';

Для филигранной настройки привилегий пользователей смотрите следующие статьи:

• Как разрешить пользователю доступ только к определённым базам данных в MySQL (MariaDB)
• Как разрешить доступ пользователю только к определённым таблицам в MySQL (MariaDB)
• Как разрешить доступ пользователю только к определённым столбцам в MySQL (MariaDB)