Debian Codegraph @

mysql-test/suite/roles/set_and_drop.test

raw
1 --source include/not_embedded.inc
2 #
3 # test setting and dropping a role
4 #
5
6 create database mysqltest1;
7 create table mysqltest1.t1 (a int, b int);
8 create table mysqltest1.t2 (a int, b int);
9 insert mysqltest1.t1 values (1,2),(3,4);
10 insert mysqltest1.t2 values (5,6),(7,8);
11
12 create procedure mysqltest1.pr1() select "pr1";
13
14 create user foo@localhost;
15 create role role1;
16 create role role2;
17
18 grant role2 to role1;
19 grant role1 to foo@localhost;
20 grant reload on *.* to role2;
21 grant select on mysql.* to role2;
22 grant execute on procedure mysqltest1.pr1 to role2;
23 grant select on mysqltest1.t1 to role2;
24 grant select (a) on mysqltest1.t2 to role2;
25
26 connect (foo,localhost,foo);
27
28 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
29 flush tables;
30 --error ER_TABLEACCESS_DENIED_ERROR
31 select * from mysql.roles_mapping;
32 --error ER_DBACCESS_DENIED_ERROR
33 show tables from mysqltest1;
34
35 set role role1;
36
37 flush tables;
38 --sorted_result
39 select * from mysql.roles_mapping;
40 show tables from mysqltest1;
41 select * from mysqltest1.t1;
42 --error ER_TABLEACCESS_DENIED_ERROR
43 select * from mysqltest1.t2;
44 select a from mysqltest1.t2;
45 call mysqltest1.pr1();
46
47 connection default;
48 revoke execute on procedure mysqltest1.pr1 from role2;
49 connection foo;
50
51 --error ER_PROCACCESS_DENIED_ERROR
52 call mysqltest1.pr1();
53
54 connection default;
55 drop role role2;
56 connection foo;
57
58 show grants;
59 select * from information_schema.enabled_roles;
60
61 flush tables;
62 --error ER_TABLEACCESS_DENIED_ERROR
63 select * from mysql.roles_mapping;
64 --error ER_TABLEACCESS_DENIED_ERROR
65 select * from mysqltest1.t1;
66 --error ER_TABLEACCESS_DENIED_ERROR
67 select a from mysqltest1.t2;
68
69 set role none;
70
71 connection default;
72
73 grant reload on *.* to role1;
74 grant select on mysql.* to role1;
75 grant execute on procedure mysqltest1.pr1 to role1;
76 grant select on mysqltest1.t1 to role1;
77 grant select (a) on mysqltest1.t2 to role1;
78
79 connection foo;
80 set role role1;
81
82 flush tables;
83 --sorted_result
84 select * from mysql.roles_mapping;
85 show tables from mysqltest1;
86 select * from mysqltest1.t1;
87 --error ER_TABLEACCESS_DENIED_ERROR
88 select * from mysqltest1.t2;
89 select a from mysqltest1.t2;
90 call mysqltest1.pr1();
91
92 connection default;
93 drop role role1;
94 connection foo;
95
96 flush tables;
97 --error ER_TABLEACCESS_DENIED_ERROR
98 select * from mysql.roles_mapping;
99 --error ER_TABLEACCESS_DENIED_ERROR
100 select * from mysqltest1.t1;
101 --error ER_TABLEACCESS_DENIED_ERROR
102 select a from mysqltest1.t2;
103
104 show grants;
105 select * from information_schema.enabled_roles;
106 select * from information_schema.enabled_roles; # yes, repeat it twice
107 select current_role();
108
109 disconnect foo;
110 connection default;
111
112 drop user foo@localhost;
113 drop database mysqltest1;