SET ROLE in stored procedure

[mitj...@gmail.com]

Hi,

I would like to set Role in stored procedures. Anybody has done this before?

DELIMITER //

CREATE or replace PROCEDURE test()

SQL SECURITY INVOKER

BEGIN

set @v_role = 'my_role2';

IF lower(USER()) NOT like 'root%' THEN 

??????????? SET session ROLE = @v_role; ???????????

END IF;

select current_user(), current_role();

END;

//

DELIMITER ;

SET ROLE my_role1;

call test();

select current_user(), current_role();

-- expect my_role2