Debian Codegraph @

mysql-test/suite/roles/set_role-database-simple.test

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