Debian Codegraph @

mysql-test/suite/rpl/t/rpl_priv_checks_user_set_vars.test

raw
1 # ==== Purpose ====
2 #
3 # This script tests error handling regarding setting session and/or global
4 # variables while applying events in the replication stream.
5 #
6 # ==== Requirements ====
7 #
8 # R1. Variables `auto_increment_increment`, `auto_increment_offset`,
9 # `collation_database`, `explicit_defaults_for_timestamp`,
10 # `default_collation_for_utf8mb4` and `rbr_exec_mode` are allowed to be set
11 # without privileges.
12 # R2. Variables `gtid_next`, `original_commit_timestamp`,
13 # `original_server_version`, `immediate_server_version` and
14 # `pseudo_replica_mode` are allowed to be set when having `SUPER`,
15 # `SYSTEM_VARIABLES_ADMIN`, `SESSION_VARIABLES_ADMIN` or
16 # `REPLICATION_APPLIER` privileges.
17 # R3. Variables `pseudo_thread_id` and `sql_require_primary_key` are allowed to
18 # be set when having `SUPER`, `SYSTEM_VARIABLES_ADMIN`,
19 # `SESSION_VARIABLES_ADMIN` privileges.
20 # R4. Variable `default_table_encryption` is allowed to be set when having
21 # `SUPER` or both `SYSTEM_VARIABLES_ADMIN` and `TABLE_ENCRYPTION_ADMIN`
22 # privileges.
23 #
24 # ==== Implementation ====
25 #
26 # TC1. Verify that replication only succeeds with `SYSTEM_VARIABLES_ADMIN` due
27 # to the set of `pseudo_thread_id`
28 # -----------------------------------------------------------------------------
29 # 1) Create a table on the master and insert a record.
30 # 2) Grant `REPLICATION_APPLIER`, `CREATE`, `DROP` and `INSERT` to the
31 # `PRIVILEGE_CHECKS_USER` user.
32 # 3) Start slave and wait for applier to error out.
33 # 4) Stop the slave.
34 # 5) Grant `SYSTEM_VARIABLES_ADMIN` to the `PRIVILEGE_CHECKS_USER` user.
35 # 6) Start the slave and sync it with master.
36 # 7) Ensure that database contents are the same in master and slave.
37 #
38 # TC2. Verify that replication only succeeds with `SUPER` due
39 # to the set of `pseudo_thread_id`
40 # -----------------------------------------------------------------------------
41 # 1) Create a table on the master and insert a record.
42 # 2) Grant `REPLICATION_APPLIER`, `CREATE`, `DROP` and `INSERT` to the
43 # `PRIVILEGE_CHECKS_USER` user.
44 # 3) Start slave and wait for applier to error out.
45 # 4) Stop the slave.
46 # 5) Grant `SUPER` to the `PRIVILEGE_CHECKS_USER` user.
47 # 6) Start the slave and sync it with master.
48 # 7) Ensure that database contents are the same in master and slave.
49 #
50 # TC3. Verify that replication only succeeds with `SESSION_VARIABLES_ADMIN` due
51 # to the set of `pseudo_thread_id`
52 # -----------------------------------------------------------------------------
53 # 1) Create a table on the master and insert a record.
54 # 2) Grant `REPLICATION_APPLIER`, `CREATE`, `DROP` and `INSERT` to the
55 # `PRIVILEGE_CHECKS_USER` user.
56 # 3) Start slave and wait for applier to error out.
57 # 4) Stop the slave.
58 # 5) Grant `SESSION_VARIABLES_ADMIN` to the `PRIVILEGE_CHECKS_USER` user.
59 # 6) Start the slave and sync it with master.
60 # 7) Ensure that database contents are the same in master and slave.
61 #
62 # TC4. Verify that setting `default_table_encryption` only succeeds on the slave
63 # when having `SUPER` or `SYSTEM_VARIABLES_ADMIN` and
64 # `TABLE_ENCRYPTION_ADMIN`
65 # ------------------------------------------------------------------------------
66 # 1) Change value for `default_table_encryption` on master and create a new
67 # database.
68 # 2) Ensure that `PRIVILEGE_CHECKS_USER` doesn't have the necessary privileges
69 # to set `default_table_encryption`.
70 # 3) Start slave and wait for applier to error out.
71 # 4) Grant `SYSTEM_VARIABLES_ADMIN` and `TABLE_ENCRYPTION_ADMIN` to
72 # `PRIVILEGE_CHECKS_USER` user.
73 # 5) Start the slave and sync it with master.
74 # 6) Ensure that we can use the database on the slave.
75 #
76 # ==== References ====
77 #
78 # WL#12966 Replication with privilege checks
79 #
80
81 --let $rpl_privilege_checks_user= *:'u1'@'localhost'
82 --let $rpl_skip_grant_privilege_checks_user_roles = 1
83 --let $rpl_skip_start_slave = 1
84 --source include/master-slave.inc
85
86 --echo #
87 --echo # TC1. Verify that replication only succeeds with
88 --echo # `SYSTEM_VARIABLES_ADMIN` due to the set of `pseudo_thread_id`
89 --echo # ------------------------------------------------------------------
90 --echo #
91
92 # 1) Create a table on the master and insert a record.
93 CREATE TABLE t_system (c1 INT);
94 INSERT INTO t_system VALUES (1), (2);
95
96 --source include/rpl_connection_slave.inc
97 CALL mtr.add_suppression(".*you need .at least one of. the SUPER.* SYSTEM_VARIABLES_ADMIN.*");
98 CALL mtr.add_suppression(".*The replica coordinator and worker threads are stopped.*");
99
100 # 2) Grant `REPLICATION_APPLIER`, `CREATE`, `DROP` and `INSERT` to the
101 # `PRIVILEGE_CHECKS_USER` user.
102 GRANT REPLICATION_APPLIER, CREATE, INSERT, DROP ON *.* TO 'u1'@'localhost';
103 SHOW GRANTS FOR 'u1'@'localhost';
104
105 # 3) Start slave and wait for applier to error out.
106 START SLAVE;
107 --let $slave_sql_errno = convert_error(ER_SPECIFIC_ACCESS_DENIED_ERROR)
108 --source include/wait_for_slave_sql_error.inc
109
110 # 4) Stop the slave.
111 STOP SLAVE;
112
113 # 5) Grant `SYSTEM_VARIABLES_ADMIN` to the `PRIVILEGE_CHECKS_USER` user.
114 GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'u1'@'localhost';
115 SHOW GRANTS FOR 'u1'@'localhost';
116
117 # 6) Start the slave and sync it with master.
118 START SLAVE;
119 --source include/rpl_connection_master.inc
120 --source include/sync_slave_sql_with_master.inc
121
122 # 7) Ensure that database contents are the same in master and slave.
123 --let $diff_tables= master:t_system, slave:t_system
124 --source include/diff_tables.inc
125
126 STOP SLAVE;
127 REVOKE SYSTEM_VARIABLES_ADMIN ON *.* FROM 'u1'@'localhost';
128
129 --echo #
130 --echo # TC2. Verify that replication only succeeds with
131 --echo # `SUPER` due to the set of `pseudo_thread_id`
132 --echo # -------------------------------------------------
133 --echo #
134
135 # 1) Create a table on the master and insert a record.
136 --source include/rpl_connection_master.inc
137 CREATE TABLE t_super (c1 INT);
138 INSERT INTO t_super VALUES (1), (2);
139
140 --source include/rpl_connection_slave.inc
141
142 # 2) Grant `REPLICATION_APPLIER`, `CREATE`, `DROP` and `INSERT` to the
143 # `PRIVILEGE_CHECKS_USER` user.
144 # -> Already done in previous test-case
145 SHOW GRANTS FOR 'u1'@'localhost';
146
147 # 3) Start slave and wait for applier to error out.
148 START SLAVE;
149 --let $slave_sql_errno = convert_error(ER_SPECIFIC_ACCESS_DENIED_ERROR)
150 --source include/wait_for_slave_sql_error.inc
151
152 # 4) Stop the slave.
153 STOP SLAVE;
154
155 # 5) Grant `SUPER` to the `PRIVILEGE_CHECKS_USER` user.
156 GRANT SUPER ON *.* TO 'u1'@'localhost';
157 SHOW GRANTS FOR 'u1'@'localhost';
158
159 # 6) Start the slave and sync it with master.
160 START SLAVE;
161 --source include/rpl_connection_master.inc
162 --source include/sync_slave_sql_with_master.inc
163
164 # 7) Ensure that database contents are the same in master and slave.
165 --let $diff_tables= master:t_super, slave:t_super
166 --source include/diff_tables.inc
167
168 STOP SLAVE;
169 REVOKE SUPER ON *.* FROM 'u1'@'localhost';
170
171 --echo #
172 --echo # TC3. Verify that replication only succeeds with
173 --echo # `SESSION_VARIABLES_ADMIN` due to the set of `pseudo_thread_id`
174 --echo # -------------------------------------------------------------------
175 --echo #
176
177 # 1) Create a table on the master and insert a record.
178 --source include/rpl_connection_master.inc
179 CREATE TABLE t_session (c1 INT);
180 INSERT INTO t_session VALUES (1), (2);
181
182 --source include/rpl_connection_slave.inc
183 # 2) Grant `REPLICATION_APPLIER`, `CREATE`, `DROP` and `INSERT` to the
184 # `PRIVILEGE_CHECKS_USER` user.
185 # -> Already done in previous test-case
186 SHOW GRANTS FOR 'u1'@'localhost';
187
188 # 3) Start slave and wait for applier to error out.
189 START SLAVE;
190 --let $slave_sql_errno = convert_error(ER_SPECIFIC_ACCESS_DENIED_ERROR)
191 --source include/wait_for_slave_sql_error.inc
192
193 # 4) Stop the slave.
194 STOP SLAVE;
195
196 # 5) Grant `SESSION_VARIABLES_ADMIN` to the `PRIVILEGE_CHECKS_USER` user.
197 GRANT SESSION_VARIABLES_ADMIN ON *.* TO 'u1'@'localhost';
198 SHOW GRANTS FOR 'u1'@'localhost';
199
200 # 6) Start the slave and sync it with master.
201 START SLAVE;
202 --source include/rpl_connection_master.inc
203 --source include/sync_slave_sql_with_master.inc
204
205 # 7) Ensure that database contents are the same in master and slave.
206 --let $diff_tables= master:t_session, slave:t_session
207 --source include/diff_tables.inc
208
209 STOP SLAVE;
210
211 --echo #
212 --echo # TC4. Verify that setting `default_table_encryption` only succeeds on
213 --echo # the slave when having `SUPER` or `SYSTEM_VARIABLES_ADMIN` and
214 --echo # `TABLE_ENCRYPTION_ADMIN`
215 --echo # --------------------------------------------------------------------
216 --echo #
217 --source include/rpl_connection_master.inc
218
219 # 1) Change value for `default_table_encryption` on master and create a new
220 # database.
221 --let $opt_default_table_encryption = `SELECT @@default_table_encryption`
222 SET default_table_encryption = 1;
223 CREATE DATABASE new_db;
224
225 --source include/rpl_connection_slave.inc
226 # 2) Ensure that `PRIVILEGE_CHECKS_USER` doesn't have the necessary privileges
227 # to set `default_table_encryption`.
228 REVOKE SUPER, SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN ON *.* FROM 'u1'@'localhost';
229
230 # 3) Start slave and wait for applier to error out.
231 START SLAVE;
232 --let $slave_sql_errno = convert_error(ER_SPECIFIC_ACCESS_DENIED_ERROR)
233 --source include/wait_for_slave_sql_error.inc
234
235 # 4) Grant `SYSTEM_VARIABLES_ADMIN` and `TABLE_ENCRYPTION_ADMIN` to
236 # `PRIVILEGE_CHECKS_USER` user.
237 STOP SLAVE;
238 GRANT SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN ON *.* TO 'u1'@'localhost';
239 SHOW GRANTS FOR 'u1'@'localhost';
240
241 # 5) Start the slave and sync it with master.
242 START SLAVE;
243 --source include/rpl_connection_master.inc
244 --source include/sync_slave_sql_with_master.inc
245
246 # 6) Ensure that we can use the database on the slave.
247 --let $databases_list = `SHOW DATABASES LIKE 'new_db'`
248 --let $assert_text= Database new_db exists on slave
249 --let $assert_cond= "$databases_list" = "new_db"
250 --source include/assert.inc
251
252 # Clean up
253 --source include/rpl_connection_master.inc
254 DROP TABLE t_system;
255 DROP TABLE t_super;
256 DROP TABLE t_session;
257 DROP DATABASE new_db;
258 --replace_result $opt_default_table_encryption OPT_DEFAULT_TABLE_ENCRYPTION
259 --eval SET default_table_encryption = $opt_default_table_encryption
260 --source include/rpl_end.inc