1create database mysqltest1; 2create table mysqltest1.t1 (a int, b int); 3create table mysqltest1.t2 (a int, b int); 4insert mysqltest1.t1 values (1,2),(3,4); 5insert mysqltest1.t2 values (5,6),(7,8); 6create procedure mysqltest1.pr1() select "pr1"; 7create user foo@localhost; 8create role role1; 9create role role2; 10grant role2 to role1; 11grant role1 to foo@localhost; 12grant reload on *.* to role2; 13grant select on mysql.* to role2; 14grant execute on procedure mysqltest1.pr1 to role2; 15grant select on mysqltest1.t1 to role2; 16grant select (a) on mysqltest1.t2 to role2; 17connect foo,localhost,foo; 18flush tables; 19ERROR 42000: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation 20select * from mysql.roles_mapping; 21ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'roles_mapping' 22show tables from mysqltest1; 23ERROR 42000: Access denied for user 'foo'@'localhost' to database 'mysqltest1' 24set role role1; 25flush tables; 26select * from mysql.roles_mapping; 27Host User Role Admin_option 28 role1 role2 N 29localhost foo role1 N 30localhost root role1 Y 31localhost root role2 Y 32show tables from mysqltest1; 33Tables_in_mysqltest1 34t1 35t2 36select * from mysqltest1.t1; 37a b 381 2 393 4 40select * from mysqltest1.t2; 41ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2' 42select a from mysqltest1.t2; 43a 445 457 46call mysqltest1.pr1(); 47pr1 48pr1 49connection default; 50revoke execute on procedure mysqltest1.pr1 from role2; 51connection foo; 52call mysqltest1.pr1(); 53ERROR 42000: execute command denied to user 'foo'@'localhost' for routine 'mysqltest1.pr1' 54connection default; 55drop role role2; 56connection foo; 57show grants; 58Grants for foo@localhost 59GRANT `role1` TO `foo`@`localhost` 60GRANT USAGE ON *.* TO `foo`@`localhost` 61GRANT USAGE ON *.* TO `role1` 62select * from information_schema.enabled_roles; 63ROLE_NAME 64role1 65flush tables; 66select * from mysql.roles_mapping; 67ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'roles_mapping' 68select * from mysqltest1.t1; 69ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1' 70select a from mysqltest1.t2; 71ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2' 72set role none; 73connection default; 74grant reload on *.* to role1; 75grant select on mysql.* to role1; 76grant execute on procedure mysqltest1.pr1 to role1; 77grant select on mysqltest1.t1 to role1; 78grant select (a) on mysqltest1.t2 to role1; 79connection foo; 80set role role1; 81flush tables; 82select * from mysql.roles_mapping; 83Host User Role Admin_option 84localhost foo role1 N 85localhost root role1 Y 86show tables from mysqltest1; 87Tables_in_mysqltest1 88t1 89t2 90select * from mysqltest1.t1; 91a b 921 2 933 4 94select * from mysqltest1.t2; 95ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2' 96select a from mysqltest1.t2; 97a 985 997 100call mysqltest1.pr1(); 101pr1 102pr1 103connection default; 104drop role role1; 105connection foo; 106flush tables; 107select * from mysql.roles_mapping; 108ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'roles_mapping' 109select * from mysqltest1.t1; 110ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1' 111select a from mysqltest1.t2; 112ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2' 113show grants; 114Grants for foo@localhost 115GRANT USAGE ON *.* TO `foo`@`localhost` 116select * from information_schema.enabled_roles; 117ROLE_NAME 118NULL 119select * from information_schema.enabled_roles; 120ROLE_NAME 121NULL 122select current_role(); 123current_role() 124role1 125disconnect foo; 126connection default; 127drop user foo@localhost; 128drop database mysqltest1; 129