13.7.1.3 CREATE USER Statement

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

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

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

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

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

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

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

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

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

• ALL PRIVILEGES — предоставляет полные права на использование данных.
• *.* — права предоставляются на все базы и все таблицы.
• dbuser — имя учетной записи.
• localhost — доступ для учетной записи будет предоставлен только с локального компьютера.

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

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

• ALL PRIVILEGES — предоставляет полные права на использование данных.
• *.* — права предоставляются на все базы и все таблицы.
• dbuser — имя учетной записи.
• localhost — доступ для учетной записи будет предоставлен только с локального компьютера.
• password — пароль, который будет задан пользователю.
• WITH GRANT OPTION — будут предоставлены дополнительные права на изменение структуры баз и таблиц.

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

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

Смена пароля

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

MySQL

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

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

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

MariaDB

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

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

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

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

MySQL < 8 или MariaDB

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

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

MySQL 8 и выше

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

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

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

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

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

и меняем его.

Сброс пароля для 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 — система не потребует пароль:

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

> FLUSH PRIVILEGES;

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

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

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

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

systemctl start mysql

systemctl start mariadb

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Откроем его и задачим значение для опции 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:

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

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

Проверка

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

> SHOW VARIABLES LIKE ‘validate_password%’;

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

• 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 — без загрузки таблиц разрешений.

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

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

systemctl restart mysql

• Как создать базу данных и нового пользователя в MySQL: инструкция

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

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

Приведенные команды подойдут для управления MySQL, развернутой на сервере. Если же вы пользуетесь облачной базой данных, пользователь и сама база будут созданы автоматически при создании БД в панели управления.

Создание базы данных

Подключитесь к MySQL под учётной записью root:

mysql -u root -p

Посмотрите, какие базы уже хранятся на сервере:

Даже если вы ещё ничего не делали, таблица не будет пустой. В ней отобразятся служебные БД. Удалять их не нужно.

создать базу данных MySQL

CREATE DATABASE my_timeweb;

Вместо my_timeweb укажите имя, которое необходимо задать новой БД.

Чтобы убедиться, что новая база появилась на сервере, ещё раз выполните:

В таблице отобразится название БД, которую вы только что создали.

Нельзя, чтобы у двух и более БД были одинаковые имена. Если ещё раз создать базу my_timeweb, то появится сообщение об ошибке с кодом 1007. 

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

CREATE DATABASE IF NOT EXISTS my_timeweb;

В этом случае СУБД проверит условие. Если БД с таким именем не существует, то MySQL создаст её. Если же такая база была создана ранее, то ничего не изменится.

Удаление базы данных

Для удаления выполните:

DROP DATABASE my_timeweb;

Вместо my_timeweb укажите имя БД, которую следует удалить.

У DROP DATABASE также есть расширенный синтаксис, который помогает избежать ошибок:

DROP DATABASE IF EXISTS my_timeweb;

Запрос будет выполнен только при наличии БД с таким именем.

DROP DATABASE удалит БД и связанные с ней данные. Интерфейс командной строки MySQL не предложит вам подтвердить действие. Поэтому применяйте DROP DATABASE с осторожностью.

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

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

MySQL создать пользователя, авторизуйтесь как root и выполните в консоли:

Если нужно указать не конкретный хост, а целый пул, используйте знак %. Например:

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

Удаление учётной записи

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

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

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

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

Для выдачи прав нужно выполнить команду GRANT. Например:

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

Отзыв привилегий

Чтобы отозвать привилегии, используйте команду REVOKE. У неё такой же синтаксис, как у GRANT. Но результат будет противоположным.

Если учётная запись не имела никаких прав в этой БД, то ничего не изменится.

Ключевые команды, которые нужно знать

Как создать БД в MySQL:

Как удалить БД:

создать нового пользователя MySQL:

Как удалить пользователя:

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

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

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

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

• [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. Одновременно выдать и то, и другое нельзя. Чтобы выдавать привилегии, необходимо самому иметь эти привилегии.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

• A Linux server with MySQL or MariaDB installed and running
• Access to the MySQL root user credentials
• Access to a terminal window/command-line (Ctrl-Alt-T / Ctrl-Alt-F2)

sudo mysql –u root –p

2. Type in the root password for this account and press Enter.

The prompt should change to show that you are in the mysql> shell.

CREATE USER 'username' IDENTIFIED BY 'password';

• If you are working on the machine with MySQL, use username@localhost to define the user.
• If you are connecting remotely, use username@ip_address, and replace ip_address with the actual address of the remote system hosting MySQL.

Therefore, the command will be:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

CREATE USER 'username'@'ip_address' IDENTIFIED BY 'password';

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

How to Grant Permissions in MySQL

• All Privileges: The user account has full access to the database
• Insert: The user can insert rows into tables
• Delete: The user can remove rows from tables
• Create: The user can create entirely new tables and databases
• Drop: The user can drop (remove) entire tables and databases
• Select: The user gets access to the select command, to read the information in the databases
• Update: The user can update table rows
• Grant Option: The user can modify other user account privileges

GRANT permission_type ON database.table TO 'username'@'localhost';

GRANT INSERT ON *.* TO 'username'@'localhost';

You can replace the privilege level according to your needs. Run the command for each privilege you wish to grant.

GRANT INSERT *database_name.table_name* TO 'username'@'localhost';

MySQL User Management

How to List MySQL User Account-Privileges

SHOW GRANTS FOR username;

How to Grant All Privileges on a Database in MySQL

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

GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';

GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';

Revoke Privileges MySQL User Account

REVOKE permission_type ON database.table TO 'username'@'localhost';

Remove an Entire User Account

DROP USER 'username'@'localhost';

INSTALL PLUGIN auth_socket  
You should be signed in automatically through the Unix socket authentication you configured.

6 rows in set (0.00 sec)
It’s usually best to end the command with the \G statement terminator instead of the usual colon (;) so that you can view the results a bit more clearly.
*************************** 1. row ***************************
1 row in set (0.00 sec)

 How do you change the password for a MySQL user?
 How do you change authentication plugins for a MySQL user?
You can also change the mechanism or plugin used to authenticate an account.
First, identify the default authentication plugin for your server. If it’s a password-based authentication method, it’s probably best to reuse the default selection:
 
In this case, the default authentication plugin is caching_sha2_password, so we’ll use that when we switch to password authentication.

Specifying a database on the command line is optional. If none is specified, you’ll connect to the server but not to a specific database.

Query OK, 0 rows affected, 1 warning (0.00 sec)

You can also set a temporary password by using: