Debian Codegraph @

mysql-test/suite/rpl_gtid/t/rpl_gtid_grant_as.test

raw
1 # ==== Requirements ====
2 #
3 # R1. All forms of GRANT should be replicated with the syntax
4 # GRANT ... AS user WITH roles when --partial_revokes is set to ON.
5 # R2. The effect of GRANT should be the same on slave as on master.
6 # R3. The effect of GRANT should be the same when replaying mysqlbinlog
7 # as on master.
8 #
9 # ==== Implementation ====
10 #
11 # 1. Run several forms of GRANT statements on master.
12 # 2. Replicate the changes to slave and check that the slave has the same
13 # grants as the master.
14 # 3. Delete the users and grants on the slave, run mysqlbinlog on master,
15 # apply the output on slave, and check that the slave has the same grants
16 # as the master.
17 #
18 # ==== References ====
19 #
20 # WL#12820: Extend GRANT/REVOKE syntax to cover partial revokes information
21
22 #
23 # Setup
24 #
25
26 --source include/set_privilege_checks_user_as_system_user.inc
27 --let $rpl_privilege_checks_user_grant_option = 1
28 --source include/master-slave.inc
29
30 #
31 # Tests
32 #
33
34 --let $server_uuid= `SELECT @@GLOBAL.SERVER_UUID`
35 --let $datadir= `SELECT @@datadir`
36 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
37
38 --echo # Case 1: With --partial_revokes
39
40 # 1.1 Make sure --partial_revokes is set to ON
41 --let $rpl_connection_name = master
42 --source include/rpl_connection.inc
43 SET @save_partial_revokes_master = @@global.partial_revokes;
44 SET GLOBAL partial_revokes=ON;
45 --let $rpl_connection_name = slave
46 --source include/rpl_connection.inc
47 SET @save_partial_revokes_slave = @@global.partial_revokes;
48 SET GLOBAL partial_revokes=ON;
49
50
51 # 1.2 Create accounts and perform grants/partial revokes
52 --let $rpl_connection_name = master
53 --source include/rpl_connection.inc
54
55 CREATE USER foo;
56 CREATE ROLE r1, r2;
57 GRANT INSERT ON *.* TO r1 WITH GRANT OPTION;
58 REVOKE INSERT ON mysql.* FROM r1;
59 GRANT SELECT ON *.* TO r2 WITH GRANT OPTION;
60 REVOKE SELECT ON mysql.* FROM r2;
61 GRANT r1, r2 TO foo;
62 ALTER USER foo DEFAULT ROLE ALL;
63 CREATE USER u1, u2, u3, u4, u5, u6, u7;
64
65
66 # 1.3 Various cases for GRANT ... AS ...
67 GRANT INSERT, SELECT ON *.* TO u1 AS foo WITH ROLE r1, r2;
68 GRANT INSERT, SELECT ON *.* TO u2 AS foo WITH ROLE ALL;
69 GRANT INSERT, SELECT ON *.* TO u3 AS foo WITH ROLE ALL EXCEPT r1;
70 GRANT INSERT, SELECT ON *.* TO u4 AS foo WITH ROLE DEFAULT;
71 GRANT INSERT, SELECT ON *.* TO u5 AS foo WITH ROLE r1;
72 GRANT INSERT, SELECT ON *.* TO u6 AS foo WITH ROLE NONE;
73
74 --connect(foo_conn, localhost, foo,,,$MASTER_MYPORT)
75
76 GRANT INSERT, SELECT ON *.* TO u7;
77
78 --let $rpl_connection_name = master
79 --source include/rpl_connection.inc
80 --disconnect foo_conn
81
82
83 # 1.4 Verify that both - master and slave has consistent grants
84 # Sync it with slave
85 --sync_slave_with_master
86
87 --let $user_number = 1
88 while ($user_number <= 7) {
89 --let $rpl_diff_statement = SHOW GRANTS FOR u$user_number
90 --source include/rpl_diff.inc
91 --inc $user_number
92 }
93
94 # 1.5 Stop slave -> drop users -> apply binary log -> start slave -> verify
95 --let $rpl_connection_name = slave
96 --source include/rpl_connection.inc
97 --source include/stop_slave.inc
98
99 DROP USER u1, u2, u3, u4, u5, u6, u7;
100 DROP ROLE r1, r2;
101 DROP USER foo;
102 RESET MASTER;
103 RESET SLAVE;
104
105 --exec $MYSQL_BINLOG $datadir/$binlog_file | $MYSQL --user=root --host=127.0.0.1 --port=$SLAVE_MYPORT
106
107 --let $rpl_connection_name = slave
108 --source include/rpl_connection.inc
109 --source include/start_slave.inc
110
111 # Verification
112 --let $user_number = 1
113 while ($user_number <= 7) {
114 --let $rpl_diff_statement = SHOW GRANTS FOR u$user_number
115 --source include/rpl_diff.inc
116 --inc $user_number
117 }
118
119
120 # 1.6 Case 1 Cleanup
121 --let $rpl_connection_name = master
122 --source include/rpl_connection.inc
123 DROP USER u1, u2, u3, u4, u5, u6, u7;
124 DROP ROLE r1, r2;
125 DROP USER foo;
126
127 # Sync it with slave
128 --sync_slave_with_master
129
130 # 1.6.1 Restore original value of partial_revokes
131 --let $rpl_connection_name = master
132 --source include/rpl_connection.inc
133 SET @@global.partial_revokes = @save_partial_revokes_master;
134 --let $rpl_connection_name = slave
135 --source include/rpl_connection.inc
136 SET @@global.partial_revokes = @save_partial_revokes_slave;
137
138 # 1.6.2 Reset master and slave - because we need a clean slate
139 --source include/rpl_reset.inc
140
141 --echo # Case 2: Without --partial_revokes
142
143 # 2.1 Make sure --partial_revokes is set to OFF
144 --let $rpl_connection_name = master
145 --source include/rpl_connection.inc
146 SET @save_partial_revokes_master = @@global.partial_revokes;
147 SET GLOBAL partial_revokes=OFF;
148 --let $rpl_connection_name = slave
149 --source include/rpl_connection.inc
150 SET @save_partial_revokes_slave = @@global.partial_revokes;
151 SET GLOBAL partial_revokes=OFF;
152
153
154 # 2.2 Create accounts and perform grants/partial revokes
155 --let $rpl_connection_name = master
156 --source include/rpl_connection.inc
157
158 CREATE USER foo;
159 CREATE ROLE r1, r2;
160 GRANT INSERT ON *.* TO r1 WITH GRANT OPTION;
161 GRANT SELECT ON *.* TO r2 WITH GRANT OPTION;
162 GRANT r1, r2 TO foo;
163 ALTER USER foo DEFAULT ROLE ALL;
164 CREATE USER u1, u2, u3, u4, u5, u6, u7;
165
166
167 # 2.3 Various cases for GRANT ... AS ...
168 GRANT INSERT, SELECT ON *.* TO u1 AS foo WITH ROLE r1, r2;
169 GRANT INSERT, SELECT ON *.* TO u2 AS foo WITH ROLE ALL;
170 GRANT INSERT, SELECT ON *.* TO u3 AS foo WITH ROLE ALL EXCEPT r1;
171 GRANT INSERT, SELECT ON *.* TO u4 AS foo WITH ROLE DEFAULT;
172 GRANT INSERT, SELECT ON *.* TO u5 AS foo WITH ROLE r1;
173 GRANT INSERT, SELECT ON *.* TO u6 AS foo WITH ROLE NONE;
174
175 --connect(foo_conn, localhost, foo,,,$MASTER_MYPORT)
176
177 GRANT INSERT, SELECT ON *.* TO u7;
178
179 --let $rpl_connection_name = master
180 --source include/rpl_connection.inc
181 --disconnect foo_conn
182
183 # 2.4 Verify that both - master and slave has consistent grants
184 # Sync it with slave
185 --sync_slave_with_master
186
187 --let $user_number = 1
188 while ($user_number <= 7) {
189 --let $rpl_diff_statement = SHOW GRANTS FOR u$user_number
190 --source include/rpl_diff.inc
191 --inc $user_number
192 }
193
194
195 # 2.5 Stop slave -> drop users -> apply binary log -> start slave -> verify
196 --let $rpl_connection_name = slave
197 --source include/rpl_connection.inc
198 --source include/stop_slave.inc
199
200 DROP USER u1, u2, u3, u4, u5, u6, u7;
201 DROP ROLE r1, r2;
202 DROP USER foo;
203 RESET MASTER;
204 RESET SLAVE;
205
206 --exec $MYSQL_BINLOG $datadir/$binlog_file | $MYSQL --user=root --host=127.0.0.1 --port=$SLAVE_MYPORT
207
208 --let $rpl_connection_name = slave
209 --source include/rpl_connection.inc
210 --source include/start_slave.inc
211
212 # Verification
213 --let $user_number = 1
214 while ($user_number <= 7) {
215 --let $rpl_diff_statement = SHOW GRANTS FOR u$user_number
216 --source include/rpl_diff.inc
217 --inc $user_number
218 }
219
220 # 2.6 Case 2 Cleanup
221 --let $rpl_connection_name = master
222 --source include/rpl_connection.inc
223 DROP USER u1, u2, u3, u4, u5, u6, u7;
224 DROP ROLE r1, r2;
225 DROP USER foo;
226
227 # 2.6.1 Restore original value of partial_revokes
228 --let $rpl_connection_name = master
229 --source include/rpl_connection.inc
230 SET @@global.partial_revokes = @save_partial_revokes_master;
231 --let $rpl_connection_name = slave
232 --source include/rpl_connection.inc
233 SET @@global.partial_revokes = @save_partial_revokes_slave;
234
235
236 #
237 # cleanup
238 #
239
240 --source include/rpl_end.inc