MySQL Bugs: #82955: Difficult to check if a role is active

Bug #82955	Difficult to check if a role is active
Submitted:	12 Sep 2016 19:07	Modified:	14 Sep 2016 13:24
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Roles	Severity:	S4 (Feature request)
Version:	8.0.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	current_role, roles

Description:
CURRENT_ROLE() shows a comma separated list of roles if there are >1.

And because of the >1 this won't work:

select current_role() = '`role1`@`%`';

How to repeat:
-- as root
create role role1;
create role role2;
create user user1 identified by 'user1';
grant role1 to user1;
grant role2 to user1;

-- connect as user1
set roles all;
select current_role();
select current_role() = '`role1`@`%`';  -- doesn't match

Now try to find a simple way to check if role1 is in the list of active roles. (e.g. for a script)

Suggested fix:
Maybe make the output of CURRENT_ROLE() a table, JSON or an ARRAY. e.g.

SELECT COUNT(*) > 0 FROM CURRENT_ROLE() WHERE role = '`role1`@`%`'; -- table
SELECT JSON_SEARCH(CURRENT_ROLE(),'one','`role1`@`%`') IS NOT NULL; -- json

And/or: * create a I_S table with this info. * create a IS_ACTIVE_ROLE() fuction.

Hello Daniël, 

Thank you for the report. 

Thanks, 
Umesh

-- 

mysql> SET ROLE ALL;
Query OK, 0 rows affected (0.00 sec)

mysql> select current_role();
+-------------------------+
| current_role()          |
+-------------------------+
| `role1`@`%`,`role2`@`%` |
+-------------------------+
1 row in set (0.00 sec)

mysql> select current_role() = '`role1`@`%`';  -- doesn't match
+--------------------------------+
| current_role() = '`role1`@`%`' |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (0.00 sec)

Thank you for the good observation. This is very reasonable feature request. IMHO a P_S table would be the preferred way.

PostgreSQL uses an information schema table for this.
https://www.postgresql.org/docs/current/static/infoschema-enabled-roles.html

You probably know about FIND_IN_SET(). 
  SELECT FIND_IN_SET('`role`@`%`', current_role());

It would be nice to have a function which checked if a role was active or at least fixed quoting for the authorization identifier. Maybe something like this:
  SELECT FIND_IN_SET(quote_authorization_id('role'),current_role());