Debian Codegraph @

mysql-test/suite/galera/r/galera_roles.result

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