Пользователь с ограниченным доступом sql как исправить

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

В феврале этого года Света Смирнова (ведущий инженер компании 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?

Да, можно. Но похоже, я недостаточно разъяснила, что такое кэш хоста. Это внутренняя структура, которая всегда доступна и содержит ответы от 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%, а это совсем не то, что вы хотели.

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

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

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

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

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

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

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

Какие права требуются не 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 отклонит такой запрос:

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

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

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

Я бы удалила прокси-пользователя и создала роль с теми же правами, а затем назначила прокси-пользователю эту новую роль вместо 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 в рамках секции, посвященной базам данных с открытым исходным кодом.

SQL: Перевод БД Microsoft в Single-user Mode

В некоторых случаях требуется перевод БД SQL сервер в монопольный режим доступа (однопользовательский режим базы данных, Single-user Mode) это требуется в случаях выполнения операций, внесения изменений в БД или операций восстановления из резервной копии.
Так, например, при попытке восстановить рабочую БД, из резервной копии появится сообщение:
Exclusive access could not be obtained because the database is in use.

Чтобы исправить данное сообщение об ошибке, рекомендуется закрыть все приложения работающие с данной БД, а также вкладки SQL Management Studio, после этого выполнить команду:
где, AdventureWork — это имя базы данных.
Это откатит все текущие транзакции и переведет базу данных в режим работы Single-user Mode. После этого, если в этом же окне запустить операцию восстановления из резервной копии, то ошибка: «Exclusive access. » не повторится.

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

Выход из однопользовательского режима

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

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

изменения состояния или параметров базы данных ‘my_db’ не могут быть сделаны по адресу
эта пора. База данных находится в однопользовательском режиме, и пользователю
в настоящее время подключен к нему.

Как выхожу ли я из однопользовательского режима? У меня нет ни одного пользователя, использующего эту базу данных.

когда я пытаюсь просмотреть мой сайт с IIS, ошибка, которую я получаю:

необработанное исключение во время выполнения
текущего веб-запроса. Информация о происхождении и местонахождении
исключение можно определить с помощью трассировки стека исключений ниже.

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

15 ответов:

SSMS обычно использует несколько соединений с базой данных за кулисами.

вам нужно будет убить эти соединения перед изменением режима доступа.

во-первых, убедитесь, что обозреватель объектов указывает на системную базу данных, такую как master.

во-вторых, выполните процедуру sp_who2 и найдите все соединения с базой данных ‘my_db’. Убейте все соединения, сделав KILL < session id >где идентификатор сеанса-это SPID перечислены sp_who2 .

в-третьих, открыть новые окно запроса.

выполните следующий код.

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

во-первых, найти и KILL все процессы, которые были запущены в данный момент.

затем выполните следующие T-SQL чтобы установить базу данных в MULTI_USER режим.

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

ALTER DATABASE [my_db] SET MULTI_USER

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

ALTER DATABASE [my_db] SET SINGLE_USER