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

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

Для создания ролей используйте оператор 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.23, «Оператор 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.23, «Оператор 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.7, «Управление доступом к хранимым объектам»):

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

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`@`%` |
+-------------------------------------+

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

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

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