Создание пользователя и наделение его правами в СУБД MySQL

Казалось бы тема проще некуда, но, судя по форумам и специализированным сайтам ответов, у многих людей она вызывает вопросы, надо прояснить. Как же создать пользователя в СУБД и наделить его правами администратора? Разумеется, речь идёт о , т.к. как создавать пользователей через какой-нибудь менеджер БД типа «PhpMyAdmin» или «MySQL Workbench» все конечно знают.

OK, let’s . Создавать будем самый распространённый вид пользователя, который со всеми правами на какую-нибудь базу или на все базы сразу. Обычно именно таких пользователей и создают и не настраивают детально права на таблицы и операции с данными.

На одного пользователя нужно, как правило, всего 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)

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

Links

Поделиться!
Tags: , , , , , , , ,

6.91MB | MySQL:54 | 0,052sec