Казалось бы тема проще некуда, но, судя по форумам и специализированным сайтам ответов, у многих людей она вызывает вопросы, надо прояснить. Как же создать пользователя в СУБД MySQL и наделить его правами администратора? Разумеется, речь идёт о SQL, т.к. как создавать пользователей через какой-нибудь менеджер БД типа «PhpMyAdmin» или «MySQL Workbench» все конечно знают.
OK, let’s create users. Создавать будем самый распространённый вид пользователя, который со всеми правами на какую-нибудь базу или на все базы сразу. Обычно именно таких пользователей и создают и не настраивают детально права на таблицы и операции с данными.
На одного пользователя нужно, как правило, всего 2 sql-запроса: создание пользователя и выдача ему необходимых прав на сервере. Однако многие забывают о 3-ем шаге — обновление данных о правах пользователей. Поэтому получается, что создание пользователя и наделения его правами в СУБД MySQL состоит как минимум из 3-ёх отдельных SQL запросов (команд): CREATE USER, GRANT, FLUSH PRIVILEGES.
Достаточно вводной теоретической части, давайте приступать к работе. Поскольку мне так удобнее, писать буду отчасти на английском и давать переводы в скобках, хотя там и так всё предельно понятно.
Create user (создание пользователя):
CREATE USER 'some_user'@'%' IDENTIFIED BY 'some_password';
where ‘some_user’ — user name, ‘%’ — host (% — any host), ‘some_password’ — password as plain text
or
CREATE USER 'some_user'@'%' IDENTIFIED WITH mysql_native_password AS 'some_password';
Empowerment, give rights (наделение правами):
GRANT ALL ON *.* TO 'some_user'@'%';
where ‘*.*’ — database («*» — any database, all dbs)
or
GRANT ALL PRIVILEGES ON *.* TO 'some_user'@'%' IDENTIFIED BY 'some_password';
or
GRANT ALL PRIVILEGES ON *.* TO 'some_user'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
or
GRANT ALL PRIVILEGES ON *.* TO 'some_user'@'%' WITH GRANT OPTION;
or
GRANT ALL PRIVILEGES ON *.* TO 'some_user'@'%' REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
Update, entry into force changes. This necessarily (Обновление, вступление в силу изменений. Это обязательно):
FLUSH PRIVILEGES;
Именно последний шаг многие часто забывают и поэтому случаются проблемы.
Это всё, что требуется для добавления (создания) нового пользователя в СУБД MySQL.
Work examples
Теперь давайте посмотрим на примеры созданий пользователей.
Example 1
CREATE USER 'some_user'@'%' IDENTIFIED BY 'password'; GRANT ALL ON *.* TO 'some_user'@'%'; FLUSH PRIVILEGES;
Example 2
CREATE USER 'some_user2'@'%' IDENTIFIED BY 'password2'; GRANT ALL ON *.* TO 'some_user2'@'%' IDENTIFIED BY 'password2'; FLUSH PRIVILEGES;
Таблица с пользователями
Вообще пользователи находятся в таблице «user» технической базы «mysql», т.е. теоретически пользователей можно переносить как и обычные данные. Однако физический перенос пользователей в sql запросах, как обычные данные, считается крайне нежелательным и даже неправильным. Всё дело в том, что структура технической таблицы «mysql.user» постоянно меняется от версии к версии и никто не гарантирует, что методом обычного переноса данные импортируются и подойдут под структуру таблицы на новом сервере. Поэтому пользователей надо не переносить как обычные данные, а именно вновь создавать на новом сервере по стандартному sql синтаксису создания пользователей в СУБД MySQL.
Системная таблица с пользователями имеет примерно такую структуру:
CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
`plugin` char(64) COLLATE utf8_bin DEFAULT '',
`authentication_string` text COLLATE utf8_bin,
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';
Структура этой системной таблицы пользователей постоянно меняется в разных версиях СУБД MySQL.
Примеры переноса пользователей
Перенос только самого необходимого (хост, имя):
INSERT INTO user VALUES ('%','some_user');
Перенос необходимого (хост, имя, пароль):
INSERT INTO mysql.user (Host, User, authentication_string) VALUES ('localhost', 'user', 'password')
Большого смысла в таком переносе нет. Даже если всё будет в порядке, то права всё равно придётся задавать отдельно т.к. у этого пользователя не будет никаких прав ибо они не перенесены.
Попытка полностью перенести всю информацию о пользователе:
INSERT INTO mysql.user VALUES ('%','some_user','*24C62363EE50A2A9110686F1451B862BD197ABD1','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'',NULL)
INSERT INTO user VALUES ('%','bar_user','*24C62363EE50A2A9110686F1451B862BD197ABD1','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'',NULL)
Все подобные попытки переносов пользователей напрямую считаются дурным тоном и вообще это крайне не рекомендуется делать, поэтому делать так не надо.