MySQL Bugs: #98767: user roles

Bug #98767	user roles
Submitted:	27 Feb 2020 19:20	Modified:	28 Feb 2020 5:32
Reporter:	Chris Gillepie	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	role function store procedure

Description:
Unsure if this is a bug or just more complicated to setup then is described.

i created a function and assigned it to a role. then assigned the role to a user. The user cannot execute the function. Receive back an error stating

Error Code: 1370. execute command denied to user 'Test'@'%' for routine 'vagt_db.system_message'

I have set DEFINER=`r_test`@`localhost` and DEFINER=`r_test`@`%` as part of the create statement. Also tried using SQL SECURITY INVOKER.

the only was it seems to work is if I GRANT EXECUTE, SELECT ON vagt_db.* TO 'r_test' role. But the user now has access to all functions and stored procedures. Would like to limit users by group roles to specific functions and procedures. My understanding according to the link below i should be able to do that. Am i missing a setting in the my.cnf file or something?

https://dev.mysql.com/doc/refman/8.0/en/roles.html

How to repeat:

CREATE DATABASE IF NOT EXISTS vagt_db;

CREATE TABLE `vagt_db`.`system_message` (
  `message_id` SMALLINT NOT NULL,
  `log_level` TINYINT DEFAULT NULL,
	`display_message` varchar(200) DEFAULT NULL,
	PRIMARY KEY (message_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

INSERT INTO `vagt_db`.`system_message`
(`message_id`,
`log_level`,
`display_message`)
VALUES
(1,
2,
"Test message");

DELIMITER $$
CREATE DEFINER=`r_test`@`localhost` FUNCTION `system_message`(i_message_id SMALLINT) RETURNS varchar(200) CHARSET utf8mb4
BEGIN
	/* Return a system message */
	SET @o_display_message = (SELECT display_message
		FROM vagt_db.system_message
		WHERE message_id = i_message_id);
        
	IF (@o_display_message <> "") THEN RETURN (@o_display_message);
	ELSE RETURN ("");
	END IF;
END$$

DELIMITER ;

Use vagt_db

CREATE ROLE 'r_test'@'localhost' ;

GRANT EXECUTE ON FUNCTION vagt_db.system_message TO 'r_test'@'localhost';

GRANT 'r_test'@'localhost' TO 'Test';

FLUSH PRIVILEGES;

/* As user Test */
SET ROLE all ;
SELECT CURRENT_ROLE();

set @message = vagt_db.system_message(1) ;
select @message;

Found if i grant execute to both the user and role with definer = to the role, it seems to work. But i feel that takes away the use of creating a role.

GRANT EXECUTE ON FUNCTION vagt_db.system_message TO 'Test';

Noticed when I run 

SHOW GRANTS FOR 'Test' USING 'r_test'@'localhost' ;

It shows the function as a procedure from the role not as a function, possible bug?

# Grants for Test@%
'GRANT USAGE ON *.* TO `Test`@`%`'
'GRANT SELECT ON `vagt_db`.`system_message` TO `Test`@`%`'
'GRANT EXECUTE ON PROCEDURE `vagt_db`.`system_message` TO `Test`@`%`'
'GRANT `r_admin`@`%`,`r_test`@`localhost` TO `Test`@`%`'

When I also GRANT the function to the user it shows as a function.

# Grants for Test@%
'GRANT USAGE ON *.* TO `Test`@`%`'
'GRANT SELECT ON `vagt_db`.`system_message` TO `Test`@`%`'
'GRANT EXECUTE ON PROCEDURE `vagt_db`.`system_message` TO `Test`@`%`'
'GRANT EXECUTE ON FUNCTION `vagt_db`.`system_message` TO `Test`@`%`'
'GRANT `r_admin`@`%`,`r_test`@`localhost` TO `Test`@`%`'

Hell Chris Gillepie,

Thank you for the report and feedback.
Imho this is duplicate of Bug #98570, please see Bug #98570

regards,
Umesh