Решение проблем с правами доступа в MySQL: вопросы и ответы

2017-05-10 в 8:58, admin, рубрики: access control, databases, devops, mysql, Администрирование баз данных, Блог компании PG Day'17 Russia, хранение данных, хранилища данных

В феврале этого года Света Смирнова (ведущий инженер компании Percona) провела вебинар, посвященный решению проблем с правами доступа в MySQL. Запись и слайды с вебинара доступны здесь. Предлагаем вашему вниманию небольшой обзор самых популярных вопросов на эту тему.

Какие права нужно давать пользователю root@localhost: ALL или Super? Включают ли права All и Super права тоже?

У вас обязательно должен быть пользователь с полными правами. Лучше, если у этого пользователя будет доступ только из localhost. Права ALL включают права SUPER.

У нас есть пользователи, подключающиеся с ноутбуков, которые получают динамические IP адреса, так что предоставление доступа через имя сервера — наиболее простой способ управления этими пользователями. Можно ли предоставить доступ к базе данных MySQL с помощью имени хоста, а не IP адреса? Например, myname@mymachine.mydomain.com вместо “myname@10.10.10.10”? Требуется ли для этого кэш хоста/performance_schema?

Решение проблем с правами доступа в MySQL: вопросы и ответы - 1 Да, можно. Но похоже, я недостаточно разъяснила, что такое кэш хоста. Это внутренняя структура, которая всегда доступна и содержит ответы от DNS сервера. Вы не можете включить его или выключить. До версии 5.6 вы также не могли контролировать его. К примеру, если кэш оказался поврежден, единственное, что вы могли сделать — это перезапустить сервер. В версии 5.6 таблица HOST_CACHE была представлена в Performance Schema. С помощью этой таблицы вы можете проверить содержимое кэша хоста и, при необходимости, урезать его.

Если в таблице пользователей имеется несколько записей, которые соответствуют подключаемому пользователю (например, через шаблоны, имя хоста и IP), по каким правилам MySQL выбирает, какая из них будет использоваться для аутентификации? Будет ли он проверять каждую, пока не получит совпадение пароля?

Нет, mysqld не пытается взломать ваши пароли. Вместо этого он сортирует таблицу пользователей по имени и хосту в порядке убывания, как показано на слайде №37 (стр. 110). Затем он берет первую подходящую строку. То есть, если вы создали пользователей foo@somehost, foo@some% и foo@1.2.3.4, а подключаетесь как foo из somehost, mysqld сначала проверяет имя пользователя, а затем выбирает первую подходящую строку foo@somehost. Если вместо этого вы подключаетесь как foo от someotherhost, mysqld выбирает foo@some%. Хост на базе IP выбирается, либо если mysqld запущен с опцией skip-networking, либо если 1.2.3.4 указывает на хост, чье имя не начинается с «some».

Смешивание хостов на основе IP с хостами на основе имен опасно в ситуациях, когда один и тот же хост может быть принят и как somehost, и как 1.2.3.4. В этом случае, если что-то пойдет не так с кэшем хоста или DNS-сервером, может быть выбрана неправильная запись из таблицы пользователей. Допустим, первоначально у вас было три хоста: uniquehost (который преобразовывается как 1.2.3.4), somehost (который преобразовывается как 4.3.2.1) и someothershost (который преобразовывается как 4.3.2.2). Теперь вы решили переместить uniquehost на машину с IP 1.2.3.5 и использовать IP 1.2.3.4 для хоста с именем someyetanotherhost. В этом случае клиенты с машины с IP 1.2.3.4 будут рассматриваться как foo@some%, а это совсем не то, что вы хотели.

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

mysql> create user sveta@Thinkie;
Query OK, 0 rows affected (0,01 sec)
mysql> create user sveta@'192.168.0.4';
Query OK, 0 rows affected (0,00 sec)
mysql> grant all on *.* to 'sveta'@'Thinkie';
Query OK, 0 rows affected (0,00 sec)
mysql> grant all on db1.* to 'sveta'@'192.168.0.4';
Query OK, 0 rows affected (0,00 sec)

Затем я изменила файл /etc/hosts и указала адрес 192.168.0.4 для имени Thinkie:

127.0.0.1   localhost
# 127.0.1.1   Thinkie
192.168.0.4 Thinkie

Теперь если я подсоединюсь как sveta, и Thinkie, и 192.168.0.4 будут преобразованы в один и тот же хост:

sveta@Thinkie:$ mysql -hThinkie -usveta
...
mysql> select user(), current_user();
+---------------+----------------+
| user()        | current_user() |
+---------------+----------------+
| sveta@Thinkie | sveta@thinkie  |
+---------------+----------------+
1 row in set (0,00 sec)
mysql> show grants;
+--------------------------------------------------+
| Grants for sveta@thinkie                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'sveta'@'thinkie' |
+--------------------------------------------------+
1 row in set (0,00 sec)
mysql> q
Bye
sveta@Thinkie:$ mysql -h192.168.0.4 -usveta
...
mysql> show grants;
+--------------------------------------------------+
| Grants for sveta@thinkie                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'sveta'@'thinkie' |
+--------------------------------------------------+
1 row in set (0,00 sec)
mysql> select user(), current_user();
+---------------+----------------+
| user()        | current_user() |
+---------------+----------------+
| sveta@Thinkie | sveta@thinkie  |
+---------------+----------------+
1 row in set (0,00 sec)
mysql> q
Bye

После этого я изменила файл /etc/hosts и снова привязала Thinkie к 127.0.0.1 (localhost):

127.0.0.1   localhost
127.0.1.1   Thinkie
# 192.168.0.4 Thinkie

Но хост 192.168.0.4 по-прежнему преобразовывается в Thinkie:

sveta@Thinkie:$ mysql -h192.168.0.4 -usveta
...
mysql> select user(), current_user();
+---------------+----------------+
| user()        | current_user() |
+---------------+----------------+
| sveta@Thinkie | sveta@thinkie  |
+---------------+----------------+
1 row in set (0,00 sec)
mysql> show grants;
+--------------------------------------------------+
| Grants for sveta@thinkie                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'sveta'@'thinkie' |
+--------------------------------------------------+
1 row in set (0,00 sec)
mysql> q
Bye

Причиной этого является устаревший кеш хоста, что хорошо видно в Performance Schema:

sveta@Thinkie:$ mysql -uroot
...
mysql> select * from performance_schema.host_cacheG
*************************** 1. row ***************************
                                        IP: 192.168.0.4
                                      HOST: Thinkie
                            HOST_VALIDATED: YES
                        SUM_CONNECT_ERRORS: 0
                 COUNT_HOST_BLOCKED_ERRORS: 0
           COUNT_NAMEINFO_TRANSIENT_ERRORS: 0
           COUNT_NAMEINFO_PERMANENT_ERRORS: 0
                       COUNT_FORMAT_ERRORS: 0
           COUNT_ADDRINFO_TRANSIENT_ERRORS: 0
           COUNT_ADDRINFO_PERMANENT_ERRORS: 0
                       COUNT_FCRDNS_ERRORS: 0
                     COUNT_HOST_ACL_ERRORS: 0
               COUNT_NO_AUTH_PLUGIN_ERRORS: 0
                  COUNT_AUTH_PLUGIN_ERRORS: 0
                    COUNT_HANDSHAKE_ERRORS: 0
                   COUNT_PROXY_USER_ERRORS: 0
               COUNT_PROXY_USER_ACL_ERRORS: 0
               COUNT_AUTHENTICATION_ERRORS: 0
                          COUNT_SSL_ERRORS: 0
         COUNT_MAX_USER_CONNECTIONS_ERRORS: 0
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0
             COUNT_DEFAULT_DATABASE_ERRORS: 0
                 COUNT_INIT_CONNECT_ERRORS: 0
                        COUNT_LOCAL_ERRORS: 0
                      COUNT_UNKNOWN_ERRORS: 0
                                FIRST_SEEN: 2017-03-02 23:19:32
                                 LAST_SEEN: 2017-03-02 23:20:31
                          FIRST_ERROR_SEEN: NULL
                           LAST_ERROR_SEEN: NULL
1 row in set (0,00 sec)
mysql> truncate performance_schema.host_cache;
Query OK, 0 rows affected (0,00 sec)
mysql> q
Bye

После очистки таблицы host_cache числовой хост преобразовывается так, как я ожидаю:

sveta@Thinkie:$ mysql -h192.168.0.4 -usveta
...
mysql> show grants;
+----------------------------------------------------------+
| Grants for sveta@192.168.0.4                             |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'sveta'@'192.168.0.4'              |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'sveta'@'192.168.0.4' |
+----------------------------------------------------------+
2 rows in set (0,00 sec)
mysql> select user(), current_user();
+-------------------+-------------------+
| user()            | current_user()    |
+-------------------+-------------------+
| sveta@192.168.0.4 | sveta@192.168.0.4 |
+-------------------+-------------------+
1 row in set (0,00 sec)
mysql> q
Bye

Какие права требуются не root и не super пользователю, чтобы использовать mysqldump для сброса базы данных и последующего ее восстановления на другом сервере?

Как правило, вы должны иметь права SELECT для всех объектов, которые вы собираетесь сбросить. Если вы сбрасываете представления, у вас также должны быть права SHOW VIEW для запуска SHOW CREATE TABLE. Если вы хотите сбросить хранимые процедуры/события, вам также нужен доступ к ним. Если вы используете опцию --lock-tables или --lock-all-tables, у вас должны быть права LOCK.

Если в MySQL достигнуто значение max_connection, может ли залогиниться root@localhost с правами ALL или пользователь с правами Super?

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

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

Нет, MySQL отклонит такое утверждение:

mysql> show grants for sveta@'192.168.0.4';
+----------------------------------------------------------+
| Grants for sveta@192.168.0.4                             |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'sveta'@'192.168.0.4'              |
| GRANT ALL PRIVILEGES ON `db1`.* TO 'sveta'@'192.168.0.4' |
+----------------------------------------------------------+
2 rows in set (0,00 sec)
mysql> revoke update on db1.t1 from sveta@'192.168.0.4';
ERROR 1147 (42000): There is no such grant defined for user 'sveta' on host '192.168.0.4' on table 't1'

Каким образом можно организовать пользовательские роли… в виде группы грантов для конкретной роли?

У вас есть несколько вариантов:

Использовать MariaDB 10.0.5 или новее. Вы можете почитать о поддержке ролей в MariaDB здесь.
Использовать MySQL 8.0. Почитать о ролях в MySQL 8.0 можно здесь.
С помощью MySQL 5.7: имитировать роли так, как я показала на слайде 19 (стр. 53-60).
С помощью MySQL 5.5 и 5.6: использовать тот же метод, что на слайдах, но применить плагин кастомной аутентификации, поддерживающий прокси-пользователей.
Всегда: создать шаблон с правами, назначить права для каждого пользователя вручную.

Как бы вы мигрировали моделирование ролей с прокси-пользователями на фактические роли в MySQL 8.x?

Я бы сбросила прокси-пользователя и создала роль с теми же правами, а затем назначила прокси-пользователю эту новую роль вместо PROXY.

Существует ли плагин для интеграции Active Directory и MySQL, чтобы использовать группы Active Directory?

Существует коммерческий плагин аутентификации Windows Authentication Plugin, доступный в версиях 5.5 и новее. Вы также можете использовать плагин аутентификации с открытым исходным кодом Percona PAM authentication plugin и подключить его к Active Directory так же, как это делается для LDAP. Есть статья, в которой описывается, как это сделать, но я сама никогда не использовала этот метод.

Можно ли использовать централизованную аутентификацию в MySQL?

Да, с помощью плагина PAM. Есть инструкции для LDAP и Active Directory. Вы можете использовать аналогичные методы для установки других видов аутентификации, таких как Kerberos.

Друзья, если работа с MySQL является для вас ежедневной задачей, обязательно приезжайте к нам на PG Day'17 Russia. Света Смирнова, Петр Зайцев и другие специалисты компании Percona готовят для вас увлекательные доклады и мастер-классы об устройстве и функционировании MySQL в рамках секции, посвященной базам данных с открытым исходным кодом.