Table of Contents
Создание нового пользователя и настройка прав в MySQL
Имя пользователя MySQL
В MySQL имя пользователя состоит из 2-х частей: имени пользователя (обязательно) и хоста (может быть опущена, тогда она означает <html>'%'</html>):
<html>'username'@'hostname'</html>, аналогично, почтовому адресу.
Поняв это правило, посмотрим, как по умолчанию выглядит суперпользователь. На самом деле, полностью учетная запись записывается трижды: <html>'root'@'localhost'</html>, <html>'root'@'127.0.0.1'</html> и <html>'root'@'::1'</html> с одинаковым парольным хешем.
В хостовой части могут использоваться DNS-имена, IP-адреса и символ подстановки <html>%</html>, обозначающий любой (любые) символы.
hostname.domain.com localhost 127.0.0.1 ::1 192.168.123.% 192.168.123.0/255.255.255.0 %
Просмотр всех пользователей
Давайте проверим, какие пользователи есть в нашей БД. Выведем основную информацию о пользователях:
SELECT host, USER, password_expired FROM mysql.user; +-----------+------------------+------------------+ | host | USER | password_expired | +-----------+------------------+------------------+ | % | example_user | N | | localhost | debian-sys-maint | N | | localhost | exampl_usr | N | | localhost | mysql.infoschema | N | | localhost | mysql.session | N | | localhost | mysql.sys | N | | localhost | nevvad | N | | localhost | office_mgr | N | | localhost | root | N | +-----------+------------------+------------------+
Когда список получается большим, мы можем добавить фильтр (в примере – по хостам, начинающимся с msk):
SELECT host, USER, password_expired FROM mysql.user WHERE host LIKE 'msk%';
Или использовать в конце модификатор <html>\G</html>, оптимизирующий вывод для отображения в консоли:
SELECT host, USER, password_expired FROM mysql.user\G; *************************** 1. ROW *************************** host: % USER: example_user password_expired: N *************************** 2. ROW *************************** host: localhost USER: debian-sys-maint password_expired: N ...
Подробная информация:
SELECT * FROM mysql.user\G;
Создание нового пользователя MySQL
Новый пользователь в MySQL добавляется командой:
CREATE USER 'username'@'hostname' IDENTIFIED BY 'P@$$w0rd';
Полезная возможность – добавление комментария:
CREATE USER 'test'@'localhost' COMMENT 'My comment here';
FLUSH PRIVILEGES
Обратим внимание на эту команду: она дает серверу команду перечитать привилегии. Как следует из документации, команда <html>FLUSH PRIVILEGES</html> в MySQL нужна только в случае прямой модификации таблиц привилегий MySQL операторами типа <html>INSERT</html>, <html>UPDATE</html> или <html>DELETE</html>. Но для простоты запоминания будем указывать ее и для других операторов таких как: <html>GRANT</html>, <html>REVOKE</html>, <html>SET PASSWORD</html> и <html>RENAME USER</html>, как в примере выше и остальных, используемых в статье.
Удаление пользователя MySQL
Для удаления пользователя используется команда
DROP USER 'username'@'hostname';
Создание дополнительного суперпользователя
Это не лучшая практика, но бывают ситуации, когда у СУБД несколько хозяев и всем нужно быть суперпользователями. В MySQL добавить пользователя с root-правами можно так:
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;
Теперь пользователь root безопасно хранится у нас, а для административной работы с БД мы можем передать коллегам или партнерам учетную запись admin.
Отзыв полномочий у пользователя
Команда отзыва привилегий функционально обратна <html>GRANT</html>, <html>TO</html> заменяется на <html>FROM</html>:
REVOKE SELECT ON `somedb`.* FROM 'username'@'hostname'; REVOKE ALL PRIVILEGES ON `somedb`.* FROM 'username'@'hostname'; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'hostname';
Смена пароля
Для изменения пароля учетной записи пользователя применяется команда <html>ALTER USER</html>:
ALTER USER 'test_user'@'localhost' IDENTIFIED BY 'new_password';
Предоставление доступа пользователю MySQL
Доступ предоставляется командой:
GRANT SELECT ON `some_db`.* TO 'username'@'hostname'; FLUSH PRIVILEGES;
Допустим, наше ПО использует базу данных <html>test_db</html>. Для его работы мы создали пользователя <html>test_user</html>, а FQDN хоста, где работает ПО – наш локальный хост (<html>localhost</html>). Наше приложение только считывает данные из БД – выполняет <html>SELECT</html>.
Создадим пользователя и БД (часто БД называют схемой, в терминах MySQL):
CREATE SCHEMA test_DB; CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'P@$$w0rd';
Команда для предоставления доступа будет выглядеть так:
GRANT SELECT ON `test_db`.* TO 'test_user'@'localhost'; FLUSH PRIVILEGES;
Наследование привилегий
В предыдущем примере наш пользователь сможет только читать данные из базы <html>test_db</html>, но передать свои права другому пользователю не сможет. Используя GRANT OPTION, мы можем позволить ему сделать это. Тогда пользователь получит возможность передавать другим то, что разрешено ему самому.
GRANT SELECT, INSERT, UPDATE, DELETE ON `some_db`.* TO 'username'@'hostname' WITH GRANT OPTION;
В этом примере пользователю <html>'username'@'hostname'</html> предоставили правами на <html>SELECT</html>, <html>INSERT</html>, <html>UPDATE</html>, <html>DELETE</html> для базы <html>some_db</html>. Из соображений безопасности использовать <html>GRANT OPTION</html> небезопасно! В случае компрометации учетной записи злоумышленник сможет не только получить доступ к данным, но и сделать закладку в виде копии учетной записи.
Доступ к таблице
Примеры выше дают доступ ко всей БД. Часто доступ должен быть ограничен строго определенным набором таблиц. Для начало создадим таблицу:
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 'username'@'hostname';
Пример:
SHOW GRANTS FOR 'nevvad'@'localhost'; +----------------------------------------------------------------------+ | Grants FOR nevvad@localhost | +----------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `nevvad`@`localhost` | | GRANT ALL PRIVILEGES ON `example_database`.* TO `nevvad`@`localhost` | +----------------------------------------------------------------------+ 2 ROWS IN SET (0.02 sec)
Проверка полномочий к данным
Через read-only БД <html>information_schema</html> доступно множество метаданных – системной информации. Информация о доступе на БД (схемы), таблицы и столбцы доступны в таблицах <html>schema_privileges</html>, <html>table_privileges</html> и <html>column_privileges</html>. Работа с ними – обычные 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="'nevvad'@'localhost'";
Просмотр привилегий через системную БД 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;