1source include/not_embedded.inc; 2 3#create a user with no privileges 4create user 'test_user'@'localhost'; 5create role test_role1; 6 7grant test_role1 to test_user@localhost; 8--sorted_result 9select user, host from mysql.user where user not like 'root'; 10--sorted_result 11select * from mysql.roles_mapping; 12 13grant select on mysql.* to test_role1; 14grant insert, delete on mysql.roles_mapping to test_role1; 15 16grant reload on *.* to test_role1; 17 18change_user 'test_user'; 19 20--error ER_TABLEACCESS_DENIED_ERROR 21select * from mysql.roles_mapping; 22 23select current_user(), current_role(); 24set role test_role1; 25select current_user(), current_role(); 26--sorted_result 27select * from mysql.roles_mapping; 28--error ER_TABLEACCESS_DENIED_ERROR 29insert into mysql.user (user, host) values ('Dummy', 'Dummy'); 30insert into mysql.roles_mapping values ('localhost', 'test_user', 'test_role2', 'N'); 31delete from mysql.roles_mapping where Role='test_role2'; 32 33use mysql; 34 35set role none; 36select current_user(), current_role(); 37 38--error ER_DBACCESS_DENIED_ERROR 39use mysql; 40--error ER_TABLEACCESS_DENIED_ERROR 41select * from mysql.roles_mapping; 42--error ER_TABLEACCESS_DENIED_ERROR 43insert into mysql.user (user, host) values ('Dummy', 'Dummy'); 44--error ER_TABLEACCESS_DENIED_ERROR 45insert into mysql.roles_mapping values ('localhost', 'test_user', 'test_role2', 'N'); 46--error ER_TABLEACCESS_DENIED_ERROR 47delete from mysql.roles_mapping where Role='test_role2'; 48 49change_user 'root'; 50drop user 'test_user'@'localhost'; 51revoke select on mysql.* from test_role1; 52revoke insert, delete on mysql.roles_mapping from test_role1; 53drop role test_role1; 54delete from mysql.roles_mapping where Role='test_role1'; 55flush privileges; 56 57