Debian Codegraph @

mysql-test/suite/galera/t/galera_roles.test

raw
1 #
2 # Test for CREATE/DROP/GRANT/REVOKE role.
3 #
4
5 --source include/galera_cluster.inc
6 --source include/have_innodb.inc
7
8 --echo #
9 --echo # Testing CREATE/GRANT role
10 --echo #
11
12 --echo
13 --echo # On node_1
14 --connection node_1
15
16 CREATE DATABASE test1;
17 CREATE TABLE test1.t1 (a int, b int);
18 CREATE TABLE test1.t2 (a int, b int);
19 INSERT INTO test1.t1 values (1,2),(3,4);
20 INSERT INTO test1.t2 values (5,6),(7,8);
21
22 CREATE PROCEDURE test1.pr1() SELECT "pr1";
23
24 CREATE USER foo@localhost;
25 CREATE ROLE role1;
26
27 GRANT role1 TO foo@localhost;
28 GRANT RELOAD ON *.* TO role1;
29 GRANT SELECT ON mysql.* TO role1;
30 GRANT EXECUTE ON PROCEDURE test1.pr1 TO role1;
31 GRANT SELECT ON test1.t1 TO role1;
32 GRANT SELECT (a) ON test1.t2 TO role1;
33
34 --echo # Open connections to the 2 nodes using 'foo' user.
35 --let $port_1= \$NODE_MYPORT_1
36 --connect(foo_node_1,127.0.0.1,foo,,"*NO-ONE*",$port_1,)
37
38 --let $port_2= \$NODE_MYPORT_2
39 --sleep 1
40 --connect(foo_node_2,127.0.0.1,foo,,"*NO-ONE*",$port_2,)
41
42 --echo
43 --echo # Connect with foo_node_1
44 --connection foo_node_1
45
46 SHOW GRANTS;
47
48 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
49 FLUSH TABLES;
50 --error ER_TABLEACCESS_DENIED_ERROR
51 SELECT * FROM mysql.roles_mapping;
52 --error ER_DBACCESS_DENIED_ERROR
53 SHOW TABLES FROM test1;
54
55 SET ROLE role1;
56
57 FLUSH TABLES;
58 --sorted_result
59 SELECT * FROM mysql.roles_mapping;
60 SHOW TABLES FROM test1;
61 SELECT * FROM test1.t1;
62 --error ER_TABLEACCESS_DENIED_ERROR
63 SELECT * FROM test1.t2;
64 SELECT a FROM test1.t2;
65 CALL test1.pr1();
66
67 --echo
68 --echo # Connect with foo_node_2
69 --connection foo_node_2
70
71 SHOW GRANTS;
72
73 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
74 FLUSH TABLES;
75 --error ER_TABLEACCESS_DENIED_ERROR
76 SELECT * FROM mysql.roles_mapping;
77 --error ER_DBACCESS_DENIED_ERROR
78 SHOW TABLES FROM test1;
79
80 SET ROLE role1;
81
82 FLUSH TABLES;
83 --sorted_result
84 SELECT * FROM mysql.roles_mapping;
85 SHOW TABLES FROM test1;
86 SELECT * FROM test1.t1;
87 --error ER_TABLEACCESS_DENIED_ERROR
88 SELECT * FROM test1.t2;
89 SELECT a FROM test1.t2;
90 CALL test1.pr1();
91
92 --echo #
93 --echo # Testing REVOKE role
94 --echo #
95
96 --echo #
97 --echo # Connect with node_1
98 --connection node_1
99 REVOKE EXECUTE ON PROCEDURE test1.pr1 FROM role1;
100
101 --echo
102 --echo # Connect with foo_node_1
103 --connection foo_node_1
104
105 --sleep 1
106 --error ER_PROCACCESS_DENIED_ERROR
107 CALL test1.pr1();
108
109 --echo
110 --echo # Connect with foo_node_2
111 --connection foo_node_2
112 --sleep 1
113 --error ER_PROCACCESS_DENIED_ERROR
114 CALL test1.pr1();
115
116 --echo #
117 --echo # Testing DROP role
118 --echo #
119
120 --echo
121 --echo # Connect with node_1
122 --connection node_1
123
124 DROP ROLE role1;
125
126 --echo
127 --echo # Connect with foo_node_1
128 --connection foo_node_1
129
130 FLUSH TABLES;
131 --error ER_TABLEACCESS_DENIED_ERROR
132 SELECT * FROM mysql.roles_mapping;
133 --error ER_TABLEACCESS_DENIED_ERROR
134 SELECT * FROM test1.t1;
135 --error ER_TABLEACCESS_DENIED_ERROR
136 SELECT a FROM test1.t2;
137
138 SHOW GRANTS;
139 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES;
140 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES; # yes, repeat it twice
141 SELECT CURRENT_ROLE();
142
143 --echo
144 --echo # Connect with foo_node_2
145 --connection foo_node_2
146
147 FLUSH TABLES;
148 --error ER_TABLEACCESS_DENIED_ERROR
149 SELECT * FROM mysql.roles_mapping;
150 --error ER_TABLEACCESS_DENIED_ERROR
151 SELECT * FROM test1.t1;
152 --error ER_TABLEACCESS_DENIED_ERROR
153 SELECT a FROM test1.t2;
154
155 SHOW GRANTS;
156 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES;
157 SELECT * FROM INFORMATION_SCHEMA.ENABLED_ROLES; # yes, repeat it twice
158 SELECT CURRENT_ROLE();
159
160 # Cleanup
161 disconnect foo_node_2;
162 --echo # Connect with node_1
163 --connection node_1
164
165 DROP USER foo@localhost;
166 DROP DATABASE test1;
167
168 --echo #
169 --echo # MDEV-10566: Create role statement replicated inconsistently in Galera Cluster
170 --echo #
171 --echo
172 --echo # On node_1
173 --connection node_1
174 CREATE USER foo@localhost;
175 CREATE ROLE role1;
176 CREATE ROLE role2 WITH ADMIN CURRENT_USER;
177 CREATE ROLE role3 WITH ADMIN foo@localhost;
178 CREATE ROLE role4 WITH ADMIN role1;
179
180 --sorted_result
181 SELECT * FROM mysql.roles_mapping;
182 --sorted_result
183 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES;
184
185 --echo
186 --echo # On node_2
187 --connection node_2
188 --sorted_result
189 SELECT * FROM mysql.roles_mapping;
190 --sorted_result
191 SELECT * FROM INFORMATION_SCHEMA.APPLICABLE_ROLES;
192
193 # Cleanup
194 DROP ROLE role1;
195 DROP ROLE role2;
196 DROP ROLE role3;
197 DROP ROLE role4;
198 DROP USER foo@localhost;
199
200 --source include/galera_end.inc
201 --echo # End of test