CREATE ROLE R_ADMIN;
CREATE ROLE R_LIMIT_MOD;
CREATE ROLE R_LIMIT_VIEW;

GRANT R_LIMIT_MOD TO R_ADMIN WITH ADMIN OPTION;
GRANT R_LIMIT_VIEW TO R_ADMIN WITH ADMIN OPTION;
GRANT R_LIMIT_VIEW TO R_LIMIT_MOD;

DELIMITER //
CREATE or replace FUNCTION "g1"(p varchar2
) RETURN varchar(4000) CHARSET utf8mb3 COLLATE utf8mb3_bin
AS
BEGIN
RETURN p;
END//
DELIMITER ;

GRANT EXECUTE ON function g1 TO R_LIMIT_MOD;
GRANT R_ADMIN TO SUPERVISOR WITH ADMIN option;
SET DEFAULT ROLE R_ADMIN FOR SUPERVISOR;

-- test

-- connect AS SUPERVISOR

SELECT g1('param')

-- works OK

-- restart mysql service

-- connect AS SUPERVISOR

SELECT g1('param')

-- Error
-- /* SQL Error (1370): execute command denied to user 'SUPERVISOR'@'%' for routine 'g1' */



