Рассмотрим такой сценарий:

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

Для создания ролей используйте оператор CREATE ROLE:

CREATE ROLE 'app_developer', 'app_read', 'app_write';

Имена ролей похожи на имена учетных записей пользователей и состоят из части пользователя и части хоста в формате 'user_name'@'host_name'. Часть хоста, если она опущена, по умолчанию равна '%'. Части пользователя и хоста могут быть без кавычек, если они не содержат специальных символов, таких как - или %. В отличие от имен учетных записей, часть пользователя в именах ролей не может быть пустой. Дополнительную информацию см. в Разделе 8.2.5, «Указание имён ролей».

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

GRANT ALL ON app_db.* TO 'app_developer';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

Теперь предположим, что изначально вам требуется одна учетная запись разработчика, две учетные записи пользователей, которые нуждаются в чтении, и одна учетная запись пользователя, которая нуждается в чтении и записи. Используйте CREATE USER для создания учетных записей:

CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';

Чтобы назначить каждой учетной записи пользователя необходимые привилегии, вы могли бы использовать операторы GRANT той же формы, что и показано выше, но это требует перечисления отдельных привилегий для каждого пользователя. Вместо этого используйте альтернативный синтаксис GRANT, который позволяет предоставлять роли вместо привилегий:

GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';

Оператор GRANT для учетной записи rw_user1 предоставляет роли чтения и записи, которые объединяются, чтобы предоставить требуемые привилегии чтения и записи.

Синтаксис оператора GRANT для предоставления ролей учетной записи отличается от синтаксиса для предоставления привилегий: есть фраза ON для назначения привилегий, в то время как нет фразы ON для назначения ролей. Поскольку синтаксисы различаются, вы не можете смешивать назначение привилегий и ролей в одном операторе. (Разрешено назначать как привилегии, так и роли учетной записи, но вы должны использовать отдельные операторы GRANT, каждый с синтаксисом, соответствующим тому, что предоставляется.) Роли не могут быть предоставлены анонимным пользователям.

При создании роль заблокирована, у неё нет пароля и ей назначен плагин аутентификации по умолчанию. (Эти атрибуты роли могут быть изменены позже с помощью оператора ALTER USER пользователями, обладающими глобальной привилегией CREATE USER).

Пока роль заблокирована, она не может использоваться для аутентификации на сервере. Если роль разблокирована, она может использоваться для аутентификации. Это связано с тем, что роли и пользователи являются идентификаторами авторизации, имеющими много общего и мало отличий. См. также Взаимозаменяемость пользователей и ролей.

Можно указать роли как обязательные, указав их в значении системной переменной mandatory_roles. Сервер рассматривает обязательную роль как предоставленную всем пользователям, поэтому её не нужно явно предоставлять какой-либо учётной записи.

Чтобы указать обязательные роли при запуске сервера, определите mandatory_roles в вашем файле сервера my.cnf:

[mysqld]
mandatory_roles='role1,role2@localhost,r3@%.example.com'

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

SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com';

SET PERSIST устанавливает значение для работающего экземпляра MySQL. Он также сохраняет значение, что позволяет ему сохраняться при последующих перезапусках сервера. Чтобы изменить значение для работающего экземпляра MySQL, не сохраняя его при последующих перезапусках, используйте ключевое слово GLOBAL вместо PERSIST. См. Раздел 15.7.6.1, «SET Синтаксис для присваивания переменной».

Установка mandatory_roles требует привилегии ROLE_ADMIN помимо привилегии SYSTEM_VARIABLES_ADMIN (или устаревшей привилегии SUPER), обычно необходимой для установки глобальной системной переменной.

Обязательные роли, как и явно предоставленные роли, не вступают в силу до активации (см. Активация ролей). При входе в систему активация роли происходит для всех предоставленных ролей, если системная переменная activate_all_roles_on_login включена, или для ролей, которые установлены как роли по умолчанию в противном случае. Во время выполнения SET ROLE активирует роли.

Роли, указанные в значении mandatory_roles, не могут быть отозваны с помощью REVOKE или удалены с помощью DROP ROLE или DROP USER.

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

Даже с этой защитой лучше избегать предоставления привилегии SYSTEM_USER через роль, чтобы предотвратить возможность повышения привилегий.

Если роль, указанная в mandatory_roles, отсутствует в таблице mysql.user систем, роль не предоставляется пользователям. Когда сервер пытается активировать роль для пользователя, он не рассматривает несуществующую роль как обязательную и записывает предупреждение в журнал ошибок. Если роль создаётся позднее и становится допустимой, возможно, потребуется FLUSH PRIVILEGES, чтобы сервер рассмотрел её как обязательную.

SHOW GRANTS отображает обязательные роли в соответствии с правилами, описанными в Разделе 15.7.7.22, «Оператор SHOW GRANTS».

Чтобы проверить привилегии, присвоенные учётной записи, используйте SHOW GRANTS. Например:

mysql> SHOW GRANTS FOR 'dev1'@'localhost';
+-------------------------------------------------+
| Grants for dev1@localhost                       |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`        |
| GRANT `app_developer`@`%` TO `dev1`@`localhost` |
+-------------------------------------------------+

Однако это отображает каждую предоставленную роль без “расширения” её до привилегий, которые она представляет. Чтобы также отобразить привилегии роли, добавьте клаузу USING, указывая предоставленные роли, для которых следует отобразить привилегии:

mysql> SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';
+----------------------------------------------------------+
| Grants for dev1@localhost                                |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `app_db`.* TO `dev1`@`localhost` |
| GRANT `app_developer`@`%` TO `dev1`@`localhost`          |
+----------------------------------------------------------+

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

mysql> SHOW GRANTS FOR 'read_user1'@'localhost' USING 'app_read';
+--------------------------------------------------------+
| Grants for read_user1@localhost                        |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `read_user1`@`localhost`         |
| GRANT SELECT ON `app_db`.* TO `read_user1`@`localhost` |
| GRANT `app_read`@`%` TO `read_user1`@`localhost`       |
+--------------------------------------------------------+
mysql> SHOW GRANTS FOR 'rw_user1'@'localhost' USING 'app_read', 'app_write';
+------------------------------------------------------------------------------+
| Grants for rw_user1@localhost                                                |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`localhost`                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO `rw_user1`@`localhost` |
| GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost`               |
+------------------------------------------------------------------------------+

SHOW GRANTS отображает обязательные роли в соответствии с правилами, описанными в Разделе 15.7.7.22, «Оператор SHOW GRANTS».

Роли, предоставленные учётной записи пользователя, могут быть активными или неактивными в рамках сеансов учётной записи. Если предоставленная роль активна в рамках сеанса, её привилегии применяются; в противном случае они не применяются. Чтобы определить, какие роли активны в текущем сеансе, используйте функцию CURRENT_ROLE().

По умолчанию предоставление роли учётной записи или указание её в значении системной переменной mandatory_roles не приводит к автоматическому активации роли в сеансах учётной записи. Например, поскольку до сих пор никакие rw_user1 роли не активированы, если вы подключитесь к серверу как rw_user1 и вызовите функцию CURRENT_ROLE(), результат будет NONE (нет активных ролей):

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+

Чтобы указать, какие роли должны становиться активными каждый раз, когда пользователь подключается к серверу и проходит аутентификацию, используйте SET DEFAULT ROLE. Чтобы установить значение по умолчанию на все назначенные роли для каждой созданной ранее учётной записи, используйте этот оператор:

SET DEFAULT ROLE ALL TO
  'dev1'@'localhost',
  'read_user1'@'localhost',
  'read_user2'@'localhost',
  'rw_user1'@'localhost';

Теперь, если вы подключитесь как rw_user1, начальное значение CURRENT_ROLE() отразит новые настройки ролей по умолчанию:

mysql> SELECT CURRENT_ROLE();
+--------------------------------+
| CURRENT_ROLE()                 |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+

Чтобы все явно предоставленные и обязательные роли автоматически активировались при подключении пользователей к серверу, включите системную переменную activate_all_roles_on_login. По умолчанию автоматическая активация ролей отключена.

В рамках сеанса пользователь может выполнить SET ROLE для изменения набора активных ролей. Например, для rw_user1:

mysql> SET ROLE NONE; SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
mysql> SET ROLE ALL EXCEPT 'app_write'; SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `app_read`@`%` |
+----------------+
mysql> SET ROLE DEFAULT; SELECT CURRENT_ROLE();
+--------------------------------+
| CURRENT_ROLE()                 |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+

Первый оператор SET ROLE деактивирует все роли. Второй делает rw_user1 фактически только для чтения. Третий восстанавливает роли по умолчанию.

Эффективный пользователь для объектов хранимых программ и представлений подчиняется атрибутам DEFINER и SQL SECURITY, которые определяют, происходит ли выполнение в контексте вызывающей стороны или определяющей стороны (см. Раздел 27.6, «Управление доступом к объектам хранимых процедур»):

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

REVOKE role FROM user;

Роли, указанные в значении системной переменной mandatory_roles, не могут быть отозваны.

Также к роли можно применить REVOKE, чтобы изменить предоставленные ей привилегии. Это влияет не только на саму роль, но и на любую учетную запись, которой предоставлена эта роль. Предположим, что вы хотите временно сделать всех пользователей приложения только читателями. Для этого используйте REVOKE, чтобы отозвать привилегии изменения у роли app_write:

REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM 'app_write';

Как видите, это оставляет роль без каких-либо привилегий, что можно увидеть с помощью SHOW GRANTS (что демонстрирует, что данная команда может использоваться с ролями, а не только с пользователями):

mysql> SHOW GRANTS FOR 'app_write';
+---------------------------------------+
| Grants for app_write@%                |
+---------------------------------------+
| GRANT USAGE ON *.* TO `app_write`@`%` |
+---------------------------------------+

Поскольку отзыв привилегий у роли влияет на привилегии любого пользователя, которому назначена изменённая роль, у rw_user1 теперь нет привилегий изменения таблиц (INSERT, UPDATE и DELETE больше отсутствуют):

mysql> SHOW GRANTS FOR 'rw_user1'@'localhost'
       USING 'app_read', 'app_write';
+----------------------------------------------------------------+
| Grants for rw_user1@localhost                                  |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `rw_user1`@`localhost`                   |
| GRANT SELECT ON `app_db`.* TO `rw_user1`@`localhost`           |
| GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost` |
+----------------------------------------------------------------+

По сути, пользователь rw_user1 с правами чтения/записи стал пользователем только для чтения. Это также происходит для любых других учетных записей, которым предоставлена роль app_write, что иллюстрирует, как использование ролей делает ненужным изменение привилегий для отдельных учетных записей.

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

GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

Теперь у rw_user1 снова есть привилегии изменения, как и у любых других учетных записей, которым предоставлена роль app_write.

Для удаления ролей используйте DROP ROLE:

DROP ROLE 'app_read', 'app_write';

Удаление роли отзывает её у каждой учетной записи, которой она была предоставлена.

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

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

Одно из отличий между ролями и пользователями заключается в том, что CREATE ROLE создаёт идентификатор авторизации, который по умолчанию заблокирован, в то время как CREATE USER создаёт идентификатор авторизации, который по умолчанию разблокирован. Следует помнить, что это различие не является неизменным; пользователь с соответствующими привилегиями может заблокировать или разблокировать роли или (других) пользователей после их создания.

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

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

Таким образом, привилегии CREATE ROLE и DROP ROLE не так мощны, как CREATE USER, и могут быть предоставлены пользователям, которым разрешено только создавать и удалять роли, а не выполнять более общие операции с учетными записями.

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

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

CREATE USER 'u1';
CREATE ROLE 'r1';
GRANT SELECT ON db1.* TO 'u1';
GRANT SELECT ON db2.* TO 'r1';
CREATE USER 'u2';
CREATE ROLE 'r2';
GRANT 'u1', 'r1' TO 'u2';
GRANT 'u1', 'r1' TO 'r2';

В каждом случае результатом является предоставление объекту-получателю привилегий, связанных с объектом, который предоставляется. После выполнения этих команд каждому из u2 и r2 были предоставлены привилегии от пользователя (u1) и роли (r1):

mysql> SHOW GRANTS FOR 'u2' USING 'u1', 'r1';
+-------------------------------------+
| Grants for u2@%                     |
+-------------------------------------+
| GRANT USAGE ON *.* TO `u2`@`%`      |
| GRANT SELECT ON `db1`.* TO `u2`@`%` |
| GRANT SELECT ON `db2`.* TO `u2`@`%` |
| GRANT `u1`@`%`,`r1`@`%` TO `u2`@`%` |
+-------------------------------------+
mysql> SHOW GRANTS FOR 'r2' USING 'u1', 'r1';
+-------------------------------------+
| Grants for r2@%                     |
+-------------------------------------+
| GRANT USAGE ON *.* TO `r2`@`%`      |
| GRANT SELECT ON `db1`.* TO `r2`@`%` |
| GRANT SELECT ON `db2`.* TO `r2`@`%` |
| GRANT `u1`@`%`,`r1`@`%` TO `r2`@`%` |
+-------------------------------------+

Предшествующий пример иллюстративный, но взаимозаменяемость учетных записей пользователей и ролей имеет практическое применение, например, в следующей ситуации: Предположим, что проект разработки приложений legacy начался до появления ролей в MySQL, поэтому всем учетным записям пользователей, связанным с проектом, предоставлялись привилегии непосредственно (а не через предоставление ролей). Одна из этих учетных записей — это учетная запись разработчика, которая изначально получала привилегии следующим образом:

CREATE USER 'old_app_dev'@'localhost' IDENTIFIED BY 'old_app_devpass';
GRANT ALL ON old_app.* TO 'old_app_dev'@'localhost';

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