MySQL Bugs: #110997: Any "SET ROLE" operation results in loss of permission

Bug #110997	Any "SET ROLE" operation results in loss of permission
Submitted:	11 May 2023 14:51	Modified:	7 Aug 2023 23:04
Reporter:	Steven Walter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In a user with no granted roles, If I run any of "SET ROLE ALL", "SET ROLE NONE", or "SET ROLE DEFAULT", mysql forgets the permissions that are associated directly with the user account.  Even "SHOW TABLES" will fail after "SET ROLE ..."  There seems to be no way to undo this effect other than to terminate the session and start a new one.

How to repeat:
$ sudo mysql

mysql> create database testdb;
Query OK, 1 row affected (0.28 sec)

mysql> create user testuser@localhost identified by 'testpass';
Query OK, 0 rows affected (0.12 sec)

mysql> grant all privileges on testdb.* to testuser@localhost ;
Query OK, 0 rows affected (0.14 sec)

mysql> ^DBye

$ mysql -u testuser -p testdb
mysql> show tables;
Empty set (0.01 sec)

mysql> set role all;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
ERROR 1044 (42000): Access denied for user 'testuser'@'localhost' to database 'testdb'

$ mysql -u testuser -p testdb
mysql> show tables;
Empty set (0.00 sec)

mysql> set role none;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
ERROR 1044 (42000): Access denied for user 'testuser'@'localhost' to database 'testdb'

$ mysql -u testuser -p testdb
mysql> show tables;
Empty set (0.01 sec)

mysql> set role default;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
ERROR 1044 (42000): Access denied for user 'testuser'@'localhost' to database 'testdb'

Hi Mr. Walter,

Thank you for your bug report.

Our Manual has this sentence in the section on the roles:

"
Privileges that the user has been granted directly (rather than through roles) remain unaffected by changes to the active roles.
"

Are you reporting that this does not hold true in 8.0.33 ??

We are waiting on your feedback.

Yes, that is what I'm observing.  I believe you should be able to reproduce the same behavior with the steps I documented above.

Hi,

We reproduced the behaviour.

This is now a verified bug.

HI,

This was double checked and this report is now a fully verified bug.

Verified as reported.

Documented fix as follows in the MySQL 8.2.0 changelog:

    For a user with no roles granted to it, any SET ROLE statement
    caused MySQL to forget any permissions associated directly with
    that user account until the session ongoing was terminated and a
    new one started. For example, a SHOW TABLES that succeeded
    previously was rejected following SET ROLE ALL, SET ROLE NONE,
    or SET ROLE DEFAULT because the user's privileges were no longer
    recognized.

Closed.

Thank you, Jon.