Bug #87112 SET ROLE disappears when activate_all_roles_on_login is set

• Submitted: 19 Jul 2017 12:05
• Modified: 19 Jul 2017 12:36
• Reporter: Giuseppe Maxia (OCA)
• Status: Closed
• Category: MySQL Server: Security: Roles
• Severity: S1 (Critical)
• Version: 8.0.2
• OS: Any
• CPU Architecture: Any

[19 Jul 2017 12:05] Giuseppe Maxia

Description:
When activate_all_roles_on_login is set, the user can't run 'SET ROLE' anymore.

mysql [localhost] {dummy} ((none)) > select @@activate_all_roles_on_login;
+-------------------------------+
| @@activate_all_roles_on_login |
+-------------------------------+
|                             1 |
+-------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {dummy} ((none)) > show variables like "%mandatory%";
+-----------------+-----------------+
| Variable_name   | Value           |
+-----------------+-----------------+
| mandatory_roles | r_lotr_observer |
+-----------------+-----------------+
1 row in set (0.00 sec)

mysql [localhost] {dummy} ((none)) > select current_role();
+-----------------------+
| current_role()        |
+-----------------------+
| `r_lotr_observer`@`%` |
+-----------------------+
1 row in set (0.00 sec)

mysql [localhost] {dummy} ((none)) > set role=none;
ERROR 1193 (HY000): Unknown system variable 'role'

When activate_all_roles_on_login is not set, SET ROLE works as expected.

How to repeat:
restart the server with activate_all_roles_on_login and mandatory_roles=some_role
create a new user
connect to the database as the new user
run SET ROLE=NONE

[19 Jul 2017 12:21] MySQL Verification Team

Hello Giuseppe,

Thank you for the report and feedback.
Looking at the syntax there is no "=" while setting option - https://dev.mysql.com/doc/refman/8.0/en/set-role.html. Please could you try SET ROLE NONE; instead? 

root@localhost [(none)]> select @@activate_all_roles_on_login;
+-------------------------------+
| @@activate_all_roles_on_login |
+-------------------------------+
|                             1 |
+-------------------------------+
1 row in set (0.00 sec)

root@localhost [(none)]> show variables like "%mandatory%";
+-----------------+----------------+
| Variable_name   | Value          |
+-----------------+----------------+
| mandatory_roles | test@localhost |
+-----------------+----------------+
1 row in set (0.00 sec)

root@localhost [(none)]> set role=none;
ERROR 1193 (HY000): Unknown system variable 'role'
root@localhost [(none)]>
root@localhost [(none)]> set role none;
Query OK, 0 rows affected (0.00 sec)

Thanks,
Umesh

[19 Jul 2017 12:27] Giuseppe Maxia

Hi Umesh,
Thanks for the quick response.
Indeed, using SET ROLE without '=' works. You can close the issue.
I have to say that the syntax for role activation is most confusing.

[19 Jul 2017 12:36] MySQL Verification Team

Thank you Giuseppe, for confirming.
Closing the issue for now.

regards,
Umesh