Как создать пользователя в mysql windows

Введение

В статье речь пойдет о работе с пользователями открытой реляционной системы управления базами данных (СУБД) MySQL, появившейся в 1994 году. В 2008 году Sun Microsystems купил MySQL AB, а в 2010 уже Sun была поглощена Oracle. Эти продажи побудили авторов исходной СУБД создать форк — MariaDB, свободный от лицензионных ограничений текущего владельца и совместимый с Oracle MySQL. Помимо «Марии» известен другой форк, Percona, — от Петра Зайцева и Вадима Ткаченко. Оба форка совместимы с MySQL.

БД от Percona обладает дополнительными функциями, направленными на повышение производительности. Многие дистрибутивы (например, Red Hat) перешли на MariaDB из-за предсказуемой лицензионной политики. В своих проектах автор использует MariaDB.

Есть несколько способов работы с БД MySQL: через графические phpMyAdmin, MySQL WorkBench и т.д.

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

Для этого понадобится минимум — консольный клиент mysql. Запускать его можно на своей рабочей станции (mysql —host=<адрес сервера> [—user=<name>] [—password=<pass>] [database]) или через ssh на самом сервере (в случае ОС Linux).

Зачем нужны пользователи

После установки MySQL технически мы можем подключаться из нашего ПО от имени root’а, но это не безопасно. Работая с информационными системами, мы всегда должны помнить и соблюдать принцип наименьших привилегий. Для более безопасной работы и создаются пользователи БД. Привилегии должны быть предоставлены пользователю строго только те, что действительно необходимы.

Администратору MariaDB в работе требуется создавать учетные записи «обычных» пользователей с ограниченным доступом к данным, определять права доступа, при необходимости — создавать дополнительных (привилегированных) суперпользователей. Также важно проводить аудит — просматривать выданные полномочия и корректировать их по мере необходимости.

Пользователи MySQL

Имя пользователя MySQL

В MySQL имя пользователя состоит из 2-х частей: имени пользователя (обязательно) и хоста (может быть опущена, тогда она означает ‘%’):

‘someuser’@’somehost’, аналогично, почтовому адресу.

Поняв это правило, посмотрим, как по умолчанию выглядит суперпользователь. На самом деле полностью учетка записывается трижды: ‘root’@’localhost’, ‘root’@’127.0.0.1’ и ‘root’@’::1’ с одинаковым парольным хешем.

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

Примеры записи хоста:

somehost.example.com
localhost
127.0.0.1
::1
192.168.123.%
192.168.123.0/255.255.255.0
%

Примечание: имена и адреса следует указывать в том формате, в каком возвращает системный DNS resolver сервера.

Просмотр всех пользователей

Давайте проверим, какие пользователи есть в нашей БД. Выведем основную информацию о пользователях:

SELECT host, user, password, password_expired FROM mysql.user;

Когда список получается большим, мы можем добавить фильтр (в примере — по хостам, начинающимся с msk):

SELECT host, user, password FROM mysql.user WHERE host LIKE 'msk%';

Или использовать в конце модификатор \G, оптимизирующий вывод для отображения в консоли:

SELECT host, user, password FROM mysql.user\G;

Подробная информация:

SELECT * FROM mysql.user[\G];

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

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

CREATE USER 'some_user'@'somehost.somedomain' IDENTIFIED BY 'some_password';

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

CREATE USER 'test'@'localhost' IDENTIFIED BY 'secret';
FLUSH PRIVILEGES;

Полезная возможность — добавление комментария:

CREATE USER 'test'@'localhost' COMMENT 'My 1st user for app';

FLUSH PRIVILEGES

Обратите внимание на эту команду: она дает серверу команду перечитать привилегии. Как следует из документации, команда FLUSH PRIVILEGES в MySQL нужна только в случае прямой модификации таблиц привилегий MySQL операторами типа INSERT, UPDATE или DELETE. Но для простоты запоминания будем указывать ее и для «правильных» операторов таких как GRANT, REVOKE, SET PASSWORD и RENAME USER, как в примере выше и остальных, используемых в статье.

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

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

DROP USER 'some_user'@'somehost.somedomain';

На нашем предыдущем примере:

DROP USER 'test'@'localhost';
FLUSH PRIVILEGES;

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

Это не лучшая практика, но бывают ситуации, когда у СУБД несколько хозяев и всем нужно быть суперпользователями. В MySQL добавить пользователя с root-правами можно так:

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

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

Отзыв полномочий у пользователя

Команда отзыва привилегий функционально обратна GRANT, “TO” заменяется на “FROM”:

REVOKE SELECT ON `somedb`.* FROM 'someuser'@'somehost';
REVOKE ALL PRIVILEGES ON `somedb`.* FROM 'someuser'@'somehost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'someuser'@'somehost';

Смена пароля

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

ALTER USER 'test_user'@'localhost' IDENTIFIED BY 'new_password';

Предоставление доступа пользователю MySQL

Доступ предоставляется командой:

GRANT SELECT ON `some_db`.* TO 'some_user'@'somehost.somedomain';
FLUSH PRIVILEGES;

Допустим, наше ПО использует базу данных test_db. Для его работы мы создали пользователя test_user, а FQDN хоста, где работает ПО — наш локальный хост (localhost).  Наше приложение только считывает данные из БД — выполняет SELECT.

Создадим пользователя и БД (часто БД называют схемой, в терминах MySQL):

CREATE SCHEMA test_DB;
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'secret';

Команда для предоставления доступа будет выглядеть так:

GRANT SELECT ON `test_db`.* TO 'test_user'@'localhost';
FLUSH PRIVILEGES;

Наследование привилегий

В предыдущем примере наш пользователь сможет только читать данные из базы test_db, но передать свои права другому пользователю не сможет. Используя GRANT OPTION, мы можем позволить ему сделать это. Тогда пользователь получит возможность передавать другим то, что разрешено ему самому.

GRANT SELECT, INSERT, UPDATE, DELETE ON `some_db`.* TO 'some_user'@'somehost' WITH GRANT OPTION;

В этом примере some_user может поделиться правами на SELECT, INSERT, UPDATE, DELETE для базы some_db.

Из соображений безопасности использовать GRANT OPTION небезопасно! В случае компрометации учетной записи злоумышленник сможет не только получить доступ к данным, но и сделать закладку в виде копии учетной записи. 

Доступ к таблице

Примеры выше дают доступ ко всей БД. Часто доступ должен быть ограничен строго определенным набором таблиц:

GRANT SELECT ON `test_db`.`table_users` TO 'test_user'@'localhost';

Выполнение команды приведет к ошибке, т.к. этой таблицы еще нет.

Создадим ее

CREATE TABLE `test_db`.`table_users` (id INT AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(16) NOT NULL, password VARCHAR(32));

и повторим предоставление доступа:

GRANT SELECT ON `test_db`.`table_users` TO 'test_user'@'localhost';

Доступ к столбцу

Предоставляется перечислением столбцов:

GRANT SELECT (id, user_name), UPDATE (user_name) ON `test_db`.`table_users` TO 'test_user'@'localhost';

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

Просмотр привилегий пользователей MySQL

Часто возникает задача выяснить полномочия учетной записи или определить, кому дан доступ к базе или таблице. Остановимся на этом подробнее.

Проверка текущих полномочий пользователя

Нам пригодится команда:

SHOW GRANTS FOR 'someuser'@'somehost.somedomain';

Пример:

SHOW GRANTS FOR 'appuser'@'srv14.example.com';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for appuser@srv14.example.com                                                                               	|
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'appuser'@'srv14.example.com' IDENTIFIED BY PASSWORD '*F4E0A7F0B10264F70558CF07A4ABD4E041182D6E' |
| GRANT SELECT ON `net_database`.* TO 'appuser'@'srv14.example.com'                                                     	|
+--------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Проверка полномочий к данным

Через read-only БД information_schema доступно множество метаданных — системной информации. Информация о доступе на БД (схемы), таблицы и столбцы доступны в таблицах schema_privileges, table_privileges и column_privileges. Работа с ними — обычные SQL-запросы:

SELECT * FROM information_schema.schema_privileges;
SELECT * FROM information_schema.table_privileges;
SELECT * FROM information_schema.column_privileges;
SELECT * FROM information_schema.column_privileges WHERE GRANTEE="'test_user'@'localhost'";

Пример:

MariaDB [information_schema]> select * from information_schema.column_privileges WHERE GRANTEE="'test_user'@'localhost'";
+-------------------------+---------------+--------------+------------+-------------+----------------+--------------+
| GRANTEE             	| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+-------------------------+---------------+--------------+------------+-------------+----------------+--------------+
| 'test_user'@'localhost' | def       	| test_db  	| table_usr  | id      	| INSERT     	| NO       	|
| 'test_user'@'localhost' | def       	| test_db  	| table_usr  | user_name   | UPDATE     	| NO       	|
+-------------------------+---------------+--------------+------------+-------------+----------------+--------------+
2 rows in set (0.001 sec)

Просмотр привилегий через системную БД mysql

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

Информация о пользователях:

SELECT * FROM mysql.user;

Привилегии на базы данных:

SELECT * FROM mysql.db;

Права, назначенные на таблицы:

SELECT * FROM mysql.tables_priv;

И на столбцы:

 SELECT * FROM mysql.columns_priv;

Просмотр глобальных привилегий

Глобальные полномочия смотрим здесь:

SELECT * FROM information_schema.user_privileges;

Заключение

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

При выдаче прав избегайте избыточности. Права не нужно выдавать с запасом, часто выполнение GRANT ALL PRIVILEGES ON *.* TO ‘myUser’@’%’ — не лучший выход. Другой важный момент, часто упускаемый из виду новичками, — наличие в имени хостовой части. Игнорирование хоста может привести к ошибкам.

Всем высоких скоростей, безаварийной работы и долгого аптайма!

Обновлено: 31.01.2023
Опубликовано: 01.08.2016

Тематические термины: MySQL, MariaDB.

Учетные записи в СУБД MySQL/MariaDB представлены в виде связки <имя пользователя>@<удаленный хост, с которого можно подключаться>. Это может вызвать путаницу, поэтому необходимо быть внимательнее, например, учетные записи root@localhost и root@192.168.0.15 — разные.

В инструкции мы рассмотрим базовые аспекты работы с пользователями и рассмотрим некоторые примеры.

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

Рассмотрим два варианта создания учетных записей в СУБД MySQL/MariaDB.

1. С помощью команды CREATE USER.

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

Пример создания учетной записи:

> CREATE USER ‘dbuser’@’localhost’ IDENTIFIED BY ‘password’;

* в данном примере будет создана учетная запись dbuser@localhost (доступ разрешен только с локального компьютера) и паролем password.

После можно задать права командой:

> GRANT <тип привилегий> ON <объект> TO <пользователь> <дополнительные опции>;

Например:

> GRANT ALL PRIVILEGES ON *.* TO ‘dbuser’@’localhost’;

2. С помощью команды GRANT.

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

Синтаксис:

> GRANT <тип привилегий> ON <объект> TO <пользователь> [IDENTIFIED BY <пароль>] <дополнительные опции>;

Например, эта команда предоставляет права доступа пользователю и, если его не существует, создает его:

> GRANT ALL PRIVILEGES ON *.* TO ‘dbuser’@’localhost’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;

Описание команды:

Посмотреть существующих пользователей и их привилегии

Список пользователей:

> SELECT user, host FROM mysql.user;

Список привилегий (для кажого пользователя смотряться отдельно):

> SHOW GRANTS FOR ‘root’@’localhost’;

* где ‘root’@’localhost’ — учетная запись, для которой смотрим привилегии; если упустить FOR, команда выдаст результат для пользователя, под которым выполнено подключение к СУБД.

Смена пароля

Команды отличаются в зависимости от версии СУБД.

MySQL

Версия 5.7.6 и более современная:

> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘New_Password’;

Версия 5.7.5 и древнее:

> SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘New_Password’);

* посмотреть версию СУБД можно командой mysql -V.

MariaDB

В MariaDB команда для смены пароля одна, независимо от версии:

> SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘New_Password’);

* в данном примере будет задан пароль New_Password для пользователя root.

Смена пароля пользователю root после установки

Для старых версий СУБД и новых процедуры различаются.

MySQL < 8 или MariaDB

Первый раз пароль задается из командной строки операционной системы (для MySQL/MariaDB):

> mysqladmin -u root password

Для смены пароля root необходимо выполнить команду в оболочке mysql по инструкции, описанной выше.

MySQL 8 и выше

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

grep ‘password’ /var/log/mysqld.log

Мы увидим что-то на подобие:

2021-08-16T20:14:13.173577Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: oi25?wznro%W

* где oi25?wznro%W — пароль для пользователя root.

Заходим в оболочку sql с помощью данного пароля:

mysql -p

и меняем его.

Сброс пароля для root

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

Для начала необходимо остановить сервис:

systemctl stop mysql

… или:

systemctl stop mariadb

Создаем каталог /var/run/mysqld и зададим для него нужные права:

mkdir -p /var/run/mysqld

chown mysql:mysql /var/run/mysqld

* в некоторых системах данного каталога может не быть, однако, для безопасного режима mysql он необходим.

Запускаем в безопасном режиме СУБД:

mysqld_safe —skip-grant-tables &

Запускаем оболочку sql — система не потребует пароль:

mysql -uroot

Выполняем запрос FLUSH PRIVILEGES:

> FLUSH PRIVILEGES;

Меняем пароль вышеописанным способом.

Выходим из оболочки:

> quit

Уничтожаем процесс, который запустили ранее:

kill %1

После, запускаем mysql в нормальном режиме:

systemctl start mysql

… или:

systemctl start mariadb

Доступ по сети

Для успешного подключения к СУБД по сети нам нужно выполнить 3 условия:

1. Создать правильную учетную запись.
2. Сервер баз данных должен слушать сетевые запросы.
3. Правила брандмауэра не должны блокировать доступ по порту mysql (по умолчанию 3306).

Рассмотрим выполнение данных условий по-очереди.

Создание учетной записи

Как было сказано в начале, имя учетной записи состоит из частей <имя пользователя> + @ + <удаленный хост, с которого можно подключаться>.

Таким образом, если нам нужно разрешить подключение пользователю dbuser с компьютера 192.168.0.15, создаем:

> CREATE USER ‘dbuser’@’192.168.0.15’ IDENTIFIED BY ‘password’;

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

Для этого используем вместо IP знак %:

> CREATE USER ‘dbuser’@’%’ IDENTIFIED BY ‘password’;

Также мы можем разрешить подключение для подсети, заменив октет знаком %, например:

> CREATE USER ‘dbuser’@’192.168.0.%’ IDENTIFIED BY ‘password’;

* в этом случае мы получим доступ с любого компьютера в сети 192.168.0.0/24.

Не забываем данному пользователю предоставить привилении:

> GRANT ALL PRIVILEGES ON *.* TO ‘dbuser’@’192.168.0.15’;

Настройка сервера для работы по сети

Проверить, на каком сетевом интерфейсе слушает сервер можно командой:

ss -tunlp | grep 3306

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

tcp   LISTEN   0   50   127.0.0.1:3306   …

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

… нам нужно настроить сервер.

Открываем конфигурационный файл. Раньше он находился по пути /etc/my.cnf, но теперь данный файл не содержит настроек, а просто подгружает конфигурационные файлы из каталога /etc/my.cnf.d. В зависимости от СУБД и ее версии, файлы настройки могут находиться в разных местах последнего, например:

vi /etc/my.cnf.d/server.cnf

vi /etc/my.cnf.d/mariadb-server.cnf.

Откроем его и задачим значение для опции bind-address:

bind-address            = 0.0.0.0

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

Перезапускаем сервис

systemctl restart mysql

Настройка брандмауэра

В зависимости от утилиты управления правилами фаервола, наши действия будут отличаться.

а) Для iptables (как правило, в системах на основе deb):

iptables -I INPUT -p tcp —dport 3306 -j ACCEPT

Чтобы сохранить правила, можно использовать iptables-persistent:

apt install iptables-persistent

netfilter-persistent save

б) Для firewalld (как правило, в системах на основе rpm):

firewall-cmd —permanent —add-port=3306/tcp

firewall-cmd —reload

Проверка

Можно пробовать подключаться. Пример команды для подключения клиентом mysql к удаленному серверу:

mysql -udbuser -p -h192.168.0.10

* в данном примере мы попробуем подключиться к серверу 192.168.0.10 пользователем dbuser. Система выведет запрос на ввод пароля.
** стоит напомнить, что в нашем примере подключиться можно будет пользователем dbuser с компьютера 192.168.0.15.

Примеры работы с пользователями

Рассмотрим часто встречаемые операции по работе с пользователями сервера баз данных.

1. Особые права

Предоставление особых прав пользователю:

> GRANT SELECT, UPDATE ON base1.* TO ‘dbuser’@’localhost’ IDENTIFIED BY ‘password’;

* права на выборку и обновление данных во всех таблицах базы base1 для пользователя dbuser
** список всех возможных прав: all privileges, alter, create, create temporary tables, delete, drop, execute, file, index, insert, lock tables, process, references, reload, replication client, replication slave, select, show databases, shutdown, super, update, usage

2. Удаленное подключение

Разрешение на удаленное подключение и использование базы MySQL:

> GRANT ALL PRIVILEGES ON *.* TO ‘dbuser’@’192.168.0.55’ IDENTIFIED BY ‘password’;

* предоставит права пользователю dbuser, который будет подключаться с компьютера с IP-адресом 192.168.0.55.

Мы можем сделать менее жесткое ограничение, разрешив доступ из подсети (особенно удобно при использовании динамических адресов):

> GRANT ALL PRIVILEGES ON *.* TO ‘dbuser’@’192.168.0.%’ IDENTIFIED BY ‘password’;

* в данном примере мы разрешим подключение для любого узла из сети 192.168.0.0/24.

Или мы можем, вовсе, разрешить водключение с любого хоста (не безопасно):

> GRANT ALL PRIVILEGES ON *.* TO ‘dbuser’@’%’ IDENTIFIED BY ‘password’;

3. Права на резервное копирование

Создание учетной записи MySQL с правами создания резервных копий:

> GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES ON *.* TO ‘backup’@’localhost’ IDENTIFIED BY ‘backup’;

4. Права доступа к определенной таблице

По сути, это такое же предоставление прав, но с указанием конкретной таблицы после базы:

> GRANT ALL PRIVILEGES ON base1.table1 TO ‘dbuser’@’localhost’ IDENTIFIED BY ‘password’;

* в данном примере предоставлены все права на таблицу table1 в базе base1.

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

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

> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘dbuser’@’localhost’;

> DROP USER ‘dbuser’@’localhost’;

* в данном примере мы первой командой отняли все права у пользователя dbuser (localhost) и второй — удалили его.

6. Права на создание других пользователей

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

GRANT CREATE USER ON *.* TO ‘creator’@’localhost’;

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

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

GRANT CREATE USER ON *.* TO ‘creator’@’localhost’ WITH GRANT OPTION;

* обратите внимание, что мы просто добавили WITH GRANT OPTION.

Возможные ошибки

1. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

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

> SHOW VARIABLES LIKE ‘validate_password%’;

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

+—————————————+———+
| Variable_name | Value |
+—————————————+———+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+—————————————+———+

* где:

• validate_password_check_user_name — пароль не должен совпадать с именем пользователя.
• validate_password_dictionary_file — использовать специальный файл со словарем запрещенных паролей.
• validate_password_length — минимальная длина пароля.
• validate_password_mixed_case_count — сколько, как минимум, должно быть символов в малой и большой раскладках.
• validate_password_number_count — какое минимальное количество цифр использовать в пароле.
• validate_password_policy — позволяет задать определенный набор правил. Доступны значения LOW (или 0), MEDIUM (1), STRONG (2).
• validate_password_special_char_count — минимальное количество специальных символов (например, # или !).

Решение:

1. Привести пароль в соответствие требованиям политик.
2. Отключить политику, которая не позволяет использовать желаемый пароль. Например, чтобы отключить требование использовать цифры вводим:

> SET GLOBAL validate_password_number_count = 0;

2. ERROR 1728 (HY000): Cannot load from mysql.tables_priv. The table is probably corrupted

Причина: система считает, что таблица tables_priv в базе mysql неисправна.

Решение: чаще всего, ошибка возникает при переносе баз с одного сервера в другой. Если версии СУБД разные, таблица tables_priv может работать со сбоями. Для исправления необходимо выполнить команду mysql_upgrade — она проверяет все таблицы на совместимость с текущей версией MySQL/MariaDB и вносит исправления. Применение команды:

mysql_upgrade -u root -p

3. ERROR 1290 (HY000): The MySQL server is running with the —skip-grant-tables option so it cannot execute this statement

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

Причина: СУБД загружена в режиме skip-grant-tables — без загрузки таблиц разрешений.

Решение: открываем конфигурационный файл СУБД. Это может быть:

vi /etc/my.cnf

vi /etc/mysql/mysql.conf.d/mysqld.cnf

… и так далее. Ориентироваться можно по содержимому /etc/my.cnf — в нем будет либо конфигурация, либо include.

Находим строку:

skip-grant-tables

И комментируем ее:

#skip-grant-tables

Перезапускаем сервис:

systemctl restart mysql

Готово.

• Главная

• Инструкции

• MySQL

• Создание нового пользователя и раздача прав в MySQL

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

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

CREATE USER [IF NOT EXISTS]
user [auth_option] [, user [auth_option]] ...
DEFAULT ROLE role [, role ] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH resource_option [resource_option] ...]
[password_option | lock_option] ...
[COMMENT 'comment_string' | ATTRIBUTE 'json_object']

Описание полей:

• [IF NOT EXISTS] – если пользователь существует, то команда остановит выполнение. Необязательный параметр.
• User – имя учетной записи. Состоит из имени пользователя и имени хоста. (‘user_name‘@’host_name‘). Часть ‘host_name‘ является не обязательной (задает хост, с которого можно будет подключиться с данным именем, % — означает все возможные хосты). Если мы хотим в MySQL создать пользователя для удалённого подключения, то можно указать доменное имя, IP-адрес или адрес подсети. Имя учетной записи, состоящей только из имени пользователя, можно записать так: ‘user_name‘@’%‘ – такая учетная запись будет доступна с любого хоста. Кроме того, можно создать анонимного пользователя: »@’localhost’.
• [auth_option] – необязательное значение, указывает, как учетная запись проходит аутентификацию (пароль, свойства блокировки и т.д.).
• DEFAULT ROLE – задает роль пользователя.
• REQUIRE {NONE | tls_option [[AND] tls_option] …} – если указано, то проверяет сертификат X.509 в дополнении к обычной аутентификации.
• WITH – если указано, позволяет установить ограничение на использование ресурсов сервера. Например, ограничить кол-во запросов, обновлений или подключений к серверу.
• password_option – параметры управления паролями (необязательный параметр). Например, срок действия, ограничение повторного использования, отслеживание неудачных попыток входа.
• COMMENT…| ATTRIBUTE – добавление пользовательских комментариев или атрибутов в формате json (нельзя использовать одновременно). Необязательный параметр.

Выдача привилегий

Создать пользователя и дать права на базу в MySQL – первое, что вы делаете, начиная работу. Чтобы выдать привилегии или назначить роль (роль – это именованный набор привилегий) используется оператор GRANT. Одновременно выдать и то, и другое нельзя. Чтобы выдавать привилегии, необходимо самому иметь эти привилегии.

Синтаксис для определения роли учетной записи:

GRANT role [, role] ...
TO user_or_role [, user_or_role] ...
[WITH ADMIN OPTION]

• role – роль, которую предоставляем.
• user_or_role – имя учетной записи или роль.
• WITH ADMIN OPTION – если указано, то с учетной записи, которой предоставили роль, можно будет также выдать эту роль кому-то ещё. Пример:

GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';

Синтаксис для добавления привилегий:

GRANT
priv_type
ON [object_type] priv_level
TO user_or_role

• priv_type – тип привилегий. Их много. Например, привилегии для создания таблиц, их обновления, заполнения или удаления; для создания ролей, пользователей, индексов; привилегии администрирования резервного копирования и учетных записей без паролей и много-много всего еще.
• object_type – если указано, задает тип следующего объекта (может быть TABLE, FUNCTION или PROCEDURE). 
• priv_level – задает уровень привилегий.

Привилегии могут быть:

• глобальные – например, на создание пользователей. Применяются ко всем базам на сервере. Синтаксис: ON *.*

 GRANT SELECT, INSERT ON *.* TO 'user_name'@'localhost';

• баз данных – применяются ко всем объектам в указанной базе данных. Применяются для создания или удаления таблиц. Пример:

 GRANT SELECT, INSERT ON mydb.* TO 'user_name'@'localhost';

• таблиц – применяются ко всем столбцам в таблице. Применяется вставка или обновление записей. Пример:

GRANT SELECT, INSERT ON mydb.mytable TO 'user_name'@'localhost';

• столбцов – применяется к определенным столбцам (вставка, просмотр, обновление данных). В примере за привилегией следует название столбца в круглых скобках:

 GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl TO 'user_name'@'localhost';

Также есть привилегии подпрограмм и прокси-пользователей.

Если предоставить учетной записи все привилегии, то можно создать «суперпользователя»:

 GRANT ALL ON *.* TO 'user_name'@'localhost';

Для того чтобы отозвать роли или привилегии учетной записи, используется REVOKE.

REVOKE INSERT ON *.* FROM 'user_name'@'localhost';

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

Чтобы удалить пользователя, используется DROP:

DROP USER 'user_name'@'localhost';

Заключение

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

When you install the MySQL database service, it creates a root account automatically. You can use it to perform any action and access any data. This can be a security nightmare!

To keep your site safe, it’s wise to set up additional accounts that don’t have these elevated privileges. Fortunately, there are multiple ways to create new users in MySQL.

In this post, we’ll explore all of the reasons you may want to add more users to your MySQL database. We’ll then show you how to create these accounts and manage their permissions for maximum security. Let’s get started!

An Introduction to MySQL (and Why You May Want To Add New Users)

The official WordPress distribution supports both the MySQL and MariaDB database engines. If you’ve weighed up the pros and cons, you may have opted for the latest version of the MySQL database management software. At some point, you might want to create additional users.

In MySQL, each account consists of a username and password. It also specifies the client host or hosts where the user can connect to your MySQL server.

You’ll typically create additional accounts when more people require access to your database. While multiple people could interact with MySQL from the same account, sharing login credentials is a security risk. If you detect any errors or suspicious activity within your database, then a shared account will also make it more difficult to identify the culprit.

MySQL is a permission-based system. It enables you to control exactly what information each user has access to and the actions they can perform. Creating multiple accounts with different permissions is far more secure than relying on a single account with global access.

When you install MySQL, it automatically creates a single ‘root’@’localhost’ superuser account. Even if you’re managing the database solo, it’s still wise to make at least one additional account. Doing so ensures that you won’t lose access to your entire database just because you encounter an issue with ‘root’@’localhost.’

This root account also has permission to access all your data and perform any action. Even experienced database users can cause serious damage with this level of control. For this reason, you may want to create a regular user account that you can use for day-to-day MySQL admin. You can then reserve ‘root’@’localhost’ for tasks that require elevated privileges.

Installing the MySQL database service can be a security nightmare 😱… but with this guide, you can keep your site safe. ⬇Click to Tweet

How To Manage Your MySQL Database

When it comes to managing your MySQL database, you have two choices. You can use MySQL Shell, which is a Python, Javascript, or SQL interface and a component of the MySQL Server. This tool is available on Microsoft Windows, Linux, and macOS for 64-bit platforms.

Alternatively, you can use the free phpMyAdmin tool. Let’s take a closer look at both of these options.

1. Manage Your Database From MySQL Shell

To enter the MySQL Shell prompt, open a Terminal or Command Prompt. You can then run the following command:

> mysqlsh

This opens MySQL Shell without connecting to a server. By default, MySQL Shell launches in JavaScript mode, but you can switch modes using the sql, py, or js command:

In MySQL Shell, connections to MySQL Server instances are handled by a session object. If X Protocol is available, then you can use a Session object. Note that X Protocol requires the X Plugin.

The X Plugin listens to the port specified by mysqlx_port, which defaults to 33060. You can create a Session object using the following:

–mysqlx (–mx)

For example, you can establish an X Protocol connection to a local MySQL Server instance listening at port 33060, using the following:

shell> mysqlsh –mysqlx -u user -h localhost -P 33060

If the X Protocol isn’t available, then you can use a ClassicSession object. This object is intended for running SQL against servers using the classic MySQL protocol. To create a ClassicSession object, type in:

–mysql (–mc)

You can verify the results of your connection attempt using MySQL Shell’s status command or the shell.status() method. For example:

mysql-js []> shell.status()

To assign privileges via MySQL Shell, you’ll need to execute your commands as a root user. To achieve this, enter the following and then provide your MySQL root user password when prompted:

mysql -u root -p

Here, the -u indicates that the following parameter is a username, which in this instance is root. Instead of displaying the password in plain text, we’re using -p. This specifies that the password is not provided and that you’ll need to enter it when prompted. Using these parameters can help keep your root account secure.

2. Manage Your Database From phpMyAdmin

If you prefer to use a Graphical User Interface (GUI), you may be able to manage the MySQL database via phpMyAdmin. With proper permissions, this interface can allow you to create new accounts, view and edit permissions, and delete accounts you no longer require. You’ll need to be logged in as root to perform these actions.

You can download this tool for free from the phpMyAdmin website.

How To Create and Manage a MySQL User (4 Key Processes)

Once you’re connected to your database, you can perform lots of different actions. Here are four key ways you can manage your MySQL user accounts.

1. Create a New User in the Database Server

In phpMyAdmin, you can create an account using the Add a new user link. You can then use the text fields and dropdowns to configure this account:

At this point, you have the option to assign global privileges. This grants the user unlimited access to all of your databases and tables. It can be a security risk, so you should only give these elevated privileges when they’re required. You can assign database-specific privileges after creating this account.

To spawn a new account via MySQL shell, enter the CREATE USER command. You’ll also need to provide a username and the hostname. The latter is the name of the host from which the user connects to the MySQL Server:

CREATE USER ‘exampleuser’@’localhost’ IDENTIFIED BY ‘password’;

When typing this command, make sure to replace exampleuser and password with the values you want to assign to this account. The user can always change their MySQL password at a later date.

In the above example, we’re setting the hostname to “localhost”. Therefore, the user will only be able to connect to the MySQL server from the system where MySQL Server is running.

You may want to replace the word localhost with your server’s address. For example, if this user needs to access MySQL from a machine with the Internet Protocol (IP) address 11.0.0.0, then you’d run the following:

CREATE USER ‘newuser’@’11.0.0.0’ IDENTIFIED BY ‘user_password’;

If you want to create a user who can connect from any host, you can include the % wildcard in your command. For example:

CREATE USER ‘newuser’@’%’ IDENTIFIED BY ‘user_password’;

Alternatively, you can omit the hostname value entirely. This will have the same result.

2. Assign MySQL Permissions via phpMyAdmin

By default, a new MySQL user doesn’t have permission to perform any actions. Even if they log into their account, they’ll be unable to create any tables or select any data.

After creating a new user, you’ll typically want to start assigning them some permissions. To keep your database secure, it’s a good idea to give each user the minimum privileges required to complete their work. It’s also wise to regularly review their permissions and then revoke any unnecessary ones.

You can edit a user’s privileges from phpMyAdmin’s Users dashboard.

Granting unrestricted access may seem like the easy option, but it can put your database at risk. Unless you have a specific reason, you’ll typically want to avoid giving anyone free reign over your entire database.

3. Modify User Permissions From MySQL Shell

If you’re using MySQL Shell, you can view all the privileges granted to a specific account with the SHOW GRANTS command. You’ll need to replace “username” and “localhost” with information for the account that you want to review:

SHOW GRANTS FOR ‘username’@’localhost’;

When you create an account, this list will be empty. You can start assigning access to different parts of your database using the GRANT command:

GRANT type_of_permission ON database_name.table_name TO ‘username’@’localhost’;

In this command, we’re specifying the privileges that we’re assigning, followed by the database and tables that these changes should apply to. Finally, we’re providing the username and server that will receive the named privileges. You’ll find a full list of all the available privileges in the official MySQL documentation.

If you want to grant access to any database or table, you can use an asterisk (*) in place of the database or table name. For example:

GRANT ALL PRIVILEGES ON *. * TO ‘exampleuser’@’localhost’;

Here, we’re granting exampleuser permission to read, edit, execute, and perform all tasks across all databases and tables. You’ll typically want to reserve these elevated privileges for admins.

Sometimes, you may need to take away a permission. For example, maybe you granted a privilege by mistake, or the user no longer needs it. To revoke access, run the following:

REVOKE type_of_permission ON database_name.table_name FROM ‘username’@’localhost’;

If you assign privileges using the GRANT command, MySQL should notice these changes and apply them automatically. However, it’s still smart to refresh a user’s privileges by running the FLUSH PRIVILEGES; command.

4. Remove a User From MySQL

Sometimes, you may need to remove a user from your MySQL database. For example, perhaps this person is leaving your organization, or they’re relocating to another department. You may also be actively trying to limit the number of people who can access your database, in order to boost your security.

If you’re working with MySQL Shell, you can delete an account using the DROP USER statement. This command will also delete all of their privileges:

DROP USER ‘username’@’localhost’;

Alternatively, with phpMyAdmin, find the account you want to remove on the Users page. You can then select the accompanying checkbox and choose whether you also want to remove any database with the same name.

When you’re happy to proceed, click on Go. This person will now be deleted from MySQL.

Ready to add a new user to your MySQL database? 💪 Learn how to create accounts & manage them for maximum security right here 🔒Click to Tweet

Summary

MySQL automatically creates a ‘root’@’localhost’ superuser account, but it’s never a good idea to use it for everything. By creating multiple MySQL users, you can assign different privileges to keep your data safe.

Additionally, you can ensure that you don’t lose access to your database because you’re having issues with ‘root’@’localhost’.

As a website owner, your database is often your most valuable asset. At Kinsta, our team of experienced WordPress experts can help keep your database in tip-top shape.

All of our hosting plans include round-the-clock support! By partnering with Kinsta, you’ll always have WordPress veterans on hand to help resolve any issues with your MySQL database.

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

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

Как получить доступ к MySQL серверу

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

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

Установить соединение с локальным сервером, вы можете, используя следующую команду в командной строке операционной системы:

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

Разберемся с этой командой. Данная команда запускает консоль MySQL сервера, оболочку, которая позволяет работать с базами данных, операции, которые можно производить с данными, могут быть самыми различными, все зависит от прав доступа пользователя MySQL, в данном случае вы можете производить любые операции со всеми базами данных, поскольку вход осуществлен от имени root пользователя MySQL. На заметку начинающим пользователям UNIX-подобных операционных систем: root пользователь MySQL сервера и root пользователь операционной системы – это два разных пользователя. Имя пользователя MySQL сервера пишется после конструкции «-u».

Пароль пользователя MySQL сервера пишется после конструкции «-p». Его можно сразу не вводить, тогда его придется ввести после того, как нажмете Enter. После конструкции «-h» пишется имя хоста, на котором находится MySQL сервер, в данном случае мы явно указываем, что соединение нужно устанавливать с локальным сервером баз данных, этого можно и не делать.

После того, как будет выполнена данная команда, вам потребуется ввести пароль пользователя MySQL. Если пользователь MySQL не имеет пароля, то после выполнения команды следует нажать клавишу Return. Надеюсь, что мы разобрались с получением доступа к MySQL серверу и каких-то вопросов тут не возникнет.

Как посмотреть информацию о существующих базах данных MySQL сервера

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

Обратите внимание: все SQL запросы оканчиваются точкой с запятой, так СУБД, с которой вы работаете, понимает, что команда завершена и начинает ее выполнять, в том числе и приведенную выше, введя эту команду вы получите список всех баз данных, находящихся под управлением MySQL сервера.

Чтобы изменять базу данных, нужно начать ее использовать, для этого существует команда use.

Обратите внимание: команда use – это не SQL запрос, поэтому точку с запятой в конце этой команды можно не ставить, а можно и поставить, результат будет один: вы начнете работать с указанной базой данных MySQL сервера. Database_name – это имя базы данных, с которой будет работать пользователь MySQL, оно может быть любым из полученного списка, который выдала команда show databases. Думаю, это все, что нужно знать, чтобы в дальнейшем разобраться с тем, как создать пользователя MySQL.

Как создать пользователя MySQL. Команда GRANT.

Итак, перейдем к вопросу: как создать пользователя MySQL. Новая учетная запись пользователя MySQL создается командой GRANT. При помощи команды GRANT создаются не только пользователи MySQL сервера, но и назначаются права доступа и привилегии MySQL пользователей. Все пользователи MySQL делятся на две категории, одна из них маленькая, другая – большая:

1. Root пользователь MySQL. Способен делать все, это главный администратор MySQL сервера, зачастую права root излишни, с этими правами вы можете навредить сами себе.
2. Пользователи MySQL, которых создал root. Эти пользователи MySQL сервера обладают различными правами, права назначаются пользователем root при помощи SQL запроса GRANT.

Итак, давайте разберемся с тем, как создать пользователя MySQL, если вы еще не забыли: MySQL пользователи создаются запросом GRANT:

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

Мы создадим нового пользователя MySQL. На моем сервере есть база данных с именем mydb, вы можете создать базу данных свою собственную с нужным вам именем. Стоит обратить внимание: даже если пользователь MySQL уже существует, ему все равно придется дать определенные привилегии командой GRANT. Итак, создадим нового пользователя, который может совершать любые действия с базой данных mydb сервера MySQL. Приведу вводные данные. Имя пользователя MySQL: «newusermysql», пароль пользователя MySQL: «userpassword». Хост, на котором установлен сервер: localhost. Действия, котороые сможет выполнять новый пользователь: любые действия с базой данных mydb. Запрос GRANT на создание пользователя MySQL с указанными правами:

Точка с запятой в конце команды обязательна, так как GRANT – это SQL запрос. Разберем команду для создания пользователя MySQL по частям. IDENTIFIED BY ‘userpassword’: установить пароль для пользователя MySQL, как «userpassword». ‘newusermysql’@’localhost’: указываем хост, с которым будет соединен пользователь с именем «newusermysql».  Инструкция GRANT может использоваться только root пользователем MySQL. Инструкция ALL ON предоставляет все полномочия пользователю newusermysql на базу данных mydb.

На заметку: база данных WordPress находится под управлением MySQL сервера, поэтому пользователей WordPress можно создавать вручную, заполняя соответствующие поля и давая им привилегии, с другой стороны: WordPress – это CMS, которая облегчает жизнь вебмастера и предоставляет графический интерфейс для создания пользователей.

Думаю, что я подробно рассмотрел вопрос о том, как создать пользователя MySQL, теперь вы сможете создавать новых пользователей, о привилегиях пользователей MySQL мы поговорим в одной из следующих публикаций,  если все-таки возникают вопросы по данной теме, то прошу задавать их в комментариях, если вы считаете, что статья вам помогла и вы хотите помочь ресурсу – воспользуйтесь блоком социальных кнопок на этой странице, буду очень вам признателен и благодарен! Спасибо за внимание!