Как разрешить доступ пользователю только к определённым таблицам в MySQL (MariaDB)

Как настроить права доступа на отдельные таблицы в MySQL (MariaDB)

Можно ли в MySQL (MariaDB) разрешить доступ пользователя только к определённым таблицам в указанных базах данных? Можно ли настроить различные права доступа для таблиц в базах данных? Ответ на все эти вопросы — да, можно. Именно этому посвящена данная статья.

В MySQL (MariaDB) права доступа можно настроить следующим образом:

глобально (применяются ко всем базам данных и их содержимому)

на уровне баз данных (применяются к указанным базам данных и их содержимому)
на уровне таблиц (применяются к указанным таблицам и их содержимому)
на уровне колонок (применяются к указанным колонкам и их содержимому)

Данная стать посвящена настройке доступа на уровне таблиц в MySQL (MariaDB).

Все запросы в данной статье будут иметь следующий вид:

GRANT _PRIVILEGE_ ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

В этом запросе:

_PRIVILEGE_ - привилегия (право доступа), которую вы хотите предоставить пользователю. Одновременно можно указать несколько привилегий, в этом случае их нужно перечислить через запятую
_TestDB_ - база данных, в которой находится таблица на которую вы хотите предоставить привилегии пользователю
_TestTABLE_ - таблица, на которую вы хотите предоставить привилегии пользователю. Если вместо имени таблицы указать «*» (звёздочку), то предоставленные привилегии будут иметь уровень базы данных, то есть распространяться сразу на все таблицы
_USERNAME_ - имя пользователя, которому предоставляются привилегии

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

При предоставлении пользователю любой привилегии на таблицу, содержащая её база данных становится видимой для пользователя при выполнении запроса

SHOW DATABASES;

Также при предоставлении любой привилегии пользователю по доступу или управлению таблицы, он может выполнять запросы с оператором «USE» в отношении базы данных, в которой находится эта таблица.

Также предоставление привилегий на таблицу разрешает использование запроса:

SHOW TABLES;

Но в этом случае будут показаны только таблицы, на которые пользователь имеет какие-либо привилегии.

Смотрите также:

Как разрешить пользователю создавать определённую таблицу в базе данных

CREATE — разрешает пользователю создавать новые таблицы в базе данных.

Поскольку разрешение даётся на определённую таблицу, то эта таблица может не существовать на момент дачи разрешения — такая настройка не запрещена. Если таблица уже существует, то пользователь сможет воспользоваться своей привилегией на создание таблицы с указанным именем, если эта таблица будет удалена в будущем.

Итак, чтобы разрешить пользователю _USERNAME_ создавать таблицу _TestTABLE_ в базе данных _TestDB_ нужно выполнить следующий запрос:

GRANT CREATE ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

Обратите внимание, что привилегия CREATE не исчезает после создания таблицы. Поэтому если таблица была удалена, то указанный пользователь может вновь её создать.

Как разрешить пользователю удалять определённую таблицу в базе данных

DROP — разрешает пользователю удалять таблицы в базе данных.

Итак, чтобы разрешить пользователю _USERNAME_ удалять таблицу _TestTABLE_ в базе данных _TestDB_ нужно выполнить следующий запрос:

GRANT DROP ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

Обратите внимание, что привилегия DROP не исчезает после удаления таблицы. Поэтому если таблица с таким же именем была создана вновь, то указанный пользователь вновь может её удалить.

Как разрешить пользователю удалять записи в таблице

DELETE — разрешает пользователю удалять строки в таблицах.

Итак, чтобы разрешить пользователю _USERNAME_ удалять записи в таблице _TestDB_ которая находится в базе данных _TestDB_ нужно выполнить следующий запрос:

GRANT DELETE ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

Как разрешить пользователю добавлять записи в таблицу

INSERT — разрешает пользователю вставлять новые данные в таблицы.

Итак, чтобы разрешить пользователю _USERNAME_ вставлять новые записи в таблицу _TestTABLE_ которая находится в базе данных _TestDB_ нужно выполнить следующий запрос:

GRANT INSERT ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

Как разрешить пользователю просматривать записи в таблице

SELECT — разрешает пользователю читать записи в таблице.

Чтобы разрешить пользователю _USERNAME_ просматривать записи в таблице _TestTABLE_ базы данных _TestDB_ нужно выполнить следующий запрос:

GRANT SELECT ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

Как разрешить пользователю обновлять записи в таблице

UPDATE — разрешает пользователю обновлять существующие строки указанной таблицы.

Чтобы разрешить пользователю _USERNAME_ обновлять записи в таблице _TestTABLE_ которая находится в базе данных _TestDB_ нужно выполнить следующий запрос:

GRANT UPDATE ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

Как разрешить пользователю изменять структуру таблицы

ALTER — разрешает пользователю использовать «ALTER TABLE» для изменения структуры таблицы.

Чтобы разрешить пользователю _USERNAME_ изменять структуру таблицы _TestTABLE_ в базе данных _TestDB_ (то есть добавлять и изменять столбцы данных, менять тип данных столбцов), нужно выполнить следующий запрос:

GRANT ALTER ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

Какие ещё существуют привилегии уровня таблиц

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

REFERENCES — разрешает пользователю создавать foreign key
INDEX — разрешает пользователю включать или удалять индексы
CREATE VIEW — включает создание или изменение views

Как назначить пользователю сразу несколько привилегий на управление определённой таблицей

Пример команды, которая назначает ряд привилегий пользователю _USERNAME_ над таблицей _TestTABLE_ в базе данных _TestDB_:

GRANT CREATE, DROP, DELETE, INSERT, SELECT, UPDATE, ALTER, REFERENCES ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

Как назначить пользователю сразу все привилегии по управлению таблицей

Если вы хотите предоставить пользователю все возможные привилегии на таблицу, то используйте запрос вида (замените _TestTABLE_ на имя таблицы, также замените _TestDB_ на имя вашей базы данных и замените _USERNAME_ на имя пользователя):

GRANT ALL PRIVILEGES ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost';

Как разрешить пользователю передавать привилегии управления таблицей другому пользователю

Если вы хотите дать пользователю возможность передавать его привилегии другим пользователям, то добавьте к запросу «WITH GRANT OPTION».

Например, следующий запрос даёт привилегию SELECT (просмотр) на таблицу _TestTABLE_ в базе данных _TestDB_ пользователю _USERNAME_ с правом передачи любому другому пользователю:

GRANT SELECT ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost'; WITH GRANT OPTION;

А этот запрос даёт все возможные привилегии на таблицу _TestTABLE_ в базе данных _TestDB_ пользователю _USERNAME_ с правом передачи любому другому пользователю:

GRANT ALL PRIVILEGES ON _TestDB_._TestTABLE_ TO '_USERNAME_'@'localhost'; WITH GRANT OPTION;

Как проверить текущие привилегии пользователя

Чтобы проверить, какими привилегиями на чтение и изменение таблиц обладает пользователь выполните следующий запрос (замените _USERNAME_ на имя пользователя):

SHOW GRANTS FOR '_USERNAME_'@'localhost';