Debian Codegraph @

mysql-test/suite/roles/set_role-multiple-role.test

raw
1 source include/not_embedded.inc;
2
3 #create a user with no privileges
4 create user 'test_user'@'localhost';
5
6 create role r_sel;
7 create role r_ins;
8 create role r_upd;
9 create role r_del;
10 create role r_crt;
11 create role r_drp;
12 create role r_rld;
13
14 grant select on *.* to r_sel;
15 grant insert on *.* to r_ins;
16 grant update on *.* to r_upd;
17 grant delete on *.* to r_del;
18 grant create on *.* to r_crt;
19 grant drop on *.* to r_drp;
20 grant reload on *.* to r_rld;
21
22 #####################################
23 #set up roles mapping
24 #####################################
25 grant r_sel to test_user@localhost;
26 grant r_ins to test_user@localhost;
27 grant r_upd to test_user@localhost;
28 grant r_del to test_user@localhost;
29 grant r_crt to test_user@localhost;
30 grant r_drp to test_user@localhost;
31 grant r_rld to test_user@localhost;
32 flush privileges;
33
34 change_user 'test_user';
35
36 --error ER_TABLEACCESS_DENIED_ERROR
37 select * from mysql.roles_mapping;
38
39 --sorted_result
40 show grants;
41 select current_user(), current_role();
42 set role r_sel;
43 select current_user(), current_role();
44 --sorted_result
45 show grants;
46 --sorted_result
47 select * from mysql.roles_mapping;
48
49 set role r_ins;
50 select current_user(), current_role();
51 --sorted_result
52 show grants;
53 --error ER_TABLEACCESS_DENIED_ERROR
54 select * from mysql.roles_mapping;
55 insert into mysql.roles_mapping values ('', 'r_sel', 'r_rld', 'N');
56 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
57 flush privileges;
58 set role r_rld;
59 select current_user(), current_role();
60 flush privileges;
61 set role r_sel;
62 select current_user(), current_role();
63 flush privileges;
64 set role none;
65 select current_user(), current_role();
66 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
67 flush privileges;
68
69 set role r_ins;
70 select current_user(), current_role();
71 insert into mysql.roles_mapping values ('', 'r_sel', 'r_upd', 'N');
72 insert into mysql.roles_mapping values ('', 'r_sel', 'r_del', 'N');
73 insert into mysql.roles_mapping values ('', 'r_sel', 'r_crt', 'N');
74 insert into mysql.roles_mapping values ('', 'r_sel', 'r_drp', 'N');
75 insert into mysql.roles_mapping values ('', 'r_del', 'r_ins', 'N');
76 set role r_rld;
77 select current_user(), current_role();
78 flush privileges;
79 set role r_sel;
80 select current_user(), current_role();
81 update mysql.roles_mapping set Role='r_ins' where Role='r_ins_wrong';
82 flush privileges;
83 set role r_sel;
84 select current_user(), current_role();
85
86 create table mysql.random_test_table (id INT);
87 insert into mysql.random_test_table values (1);
88 --sorted_result
89 select * from mysql.random_test_table;
90 delete from mysql.roles_mapping where Role='r_ins';
91 flush privileges;
92 set role r_sel;
93 select current_user(), current_role();
94 --error ER_TABLEACCESS_DENIED_ERROR
95 insert into mysql.random_test_table values (1);
96 drop table mysql.random_test_table;
97
98 change_user 'root';
99 delete from mysql.user where user like 'r\_%';
100 delete from mysql.roles_mapping where Role like 'r\_%';
101 flush privileges;
102 drop user 'test_user'@'localhost';