Debian Codegraph @

mysql-test/suite/rpl_nogtid/t/rpl_insert_priv_check.test

raw
1 # ==== Purpose ====
2 #
3 # To Verify that Write_rows_log_event, Update_rows_log_event,
4 # Partial_update_rows_log_event, and Delete_rows_log_event can fail
5 # due to insufficient privileges, and succeed with sufficient privileges.
6 #
7 # ==== Implementation ====
8 #
9 # TC1. Execute INSERT/UPDATE/DELETE with and without the necessary privileges
10 # ----------------------------------------------------------------------------
11 # 1) Create a table on master and insert a row
12 # 2) On slave create an user 'u1' which will be used as a
13 # PRIVILEGE_CHECKS_USER to apply events in the replication stream.
14 # 3) Start slave and expect an error as the user doesn't have INSERT privilege.
15 # 4) Stop slave and grant INSERT privilege.
16 # 5) Start slave again and this time there should not be any error.
17 # 6) Revoke the privilege granted in step 4)
18 # 7) Ensure the table is the same on master and slave.
19 # 9) Drop the table from master and slave.
20 #
21 # The above described test-case will be repeated for the combinations of the
22 # pair `privilege target` and `privilege`, where each has the following values:
23 # - `privilege target`: *.*, $RPL_PRIV_DB.*, $RPL_PRIV_DB.t
24 # - `privilege`: INSERT, UPDATE, UPDATE with partial updates, DELETE,
25 # INSERT(c,d), UPDATE(c)
26 # where `c` and `d` are columns in the create table.
27 #
28 # ==== References ====
29 #
30 # WL#12966: Replication with Restricted Privileges
31 #
32
33 --source include/not_group_replication_plugin.inc
34 --source include/have_binlog_format_row.inc
35 --source include/skip_config_privilege_checks_user.inc
36 --let $applier_user = 'a_bigger_username_just_for_test'
37 if ($grant_to == '')
38 {
39 --let $grant_to = $applier_user
40 }
41 --let $rpl_privilege_checks_user = 2:$applier_user
42 # Since $rpl_privilege_checks_user is instantiated the replication applier has
43 # no privileges on the test database
44 --let $RPL_PRIV_DB = test
45 --let $rpl_skip_start_slave=1
46 --source include/master-slave.inc
47
48 --write_file $MYSQLTEST_VARDIR/tmp/check_privilege.inc PROCEDURE
49
50 --source include/rpl_connection_master.inc
51 --eval $sql_statment
52 --source include/save_master_pos.inc
53
54 --source include/rpl_connection_slave.inc
55 # 3) Start slave and expect an error as the user doesn't have the privilege.
56 START SLAVE;
57 --let $slave_sql_errno= convert_error(ER_TABLEACCESS_DENIED_ERROR)
58 --source include/wait_for_slave_sql_error.inc
59
60 # 4) Stop slave and grant the privilege.
61 STOP SLAVE;
62 --eval GRANT $privilege ON $priv_context TO $grant_to
63
64 # 5) Start slave again and this time there should not be any error.
65 START SLAVE;
66 --source include/sync_slave_sql.inc
67
68 # 6) Revoke the privilege granted in step 4)
69 --eval REVOKE $privilege ON $priv_context FROM $grant_to
70
71 #END OF
72 PROCEDURE
73
74 # 1) Create a table on master and insert a row
75 SET @@session.sql_log_bin = OFF;
76 CREATE TABLE t(c INT, d INT);
77 SET @@session.sql_log_bin = ON;
78
79 # 2) On slave create an user 'u1' which will be used as a
80 # PRIVILEGE_CHECKS_USER to apply events in the replication stream.
81 --source include/rpl_connection_slave.inc
82 CALL mtr.add_suppression(".*command denied to user.");
83 CALL mtr.add_suppression(".*the option binlog_row_value_options.*");
84 CALL mtr.add_suppression(".*The replica coordinator and worker threads are stopped.*");
85 CALL mtr.add_suppression(".*Replica worker thread has failed to apply an event. As a consequence, the coordinator*");
86 CREATE TABLE t(c INT, d INT);
87
88 --let $context = 3
89
90 while ($context != 0)
91 {
92 --let $privileges = 6
93
94 if ($context == 3)
95 {
96 --let $priv_context = *.*
97 }
98 if ($context == 2)
99 {
100 --let $priv_context = $RPL_PRIV_DB.*
101 }
102 if ($context == 1)
103 {
104 --let $priv_context = $RPL_PRIV_DB.t
105 }
106
107 while ($privileges != 0)
108 {
109 if ($privileges == 6)
110 {
111 --let $privilege = INSERT
112 --let $sql_statment = INSERT INTO t VALUES(10, 10)
113 }
114 if ($privileges == 5)
115 {
116 --let $privilege = UPDATE
117 --let $sql_statment = UPDATE t SET c = 12 WHERE c = 10
118 }
119 if ($privileges == 4)
120 {
121 --let $privilege = UPDATE
122 --let $sql_statment = UPDATE t SET c = 8 WHERE c = 12
123
124 --source include/rpl_connection_slave.inc
125 # Set the options to generate partial_update_rows_event on slave
126 --disable_warnings
127 SET @binlog_row_value_options_save= @@GLOBAL.BINLOG_ROW_VALUE_OPTIONS;
128 SET @@GLOBAL.BINLOG_ROW_VALUE_OPTIONS= PARTIAL_JSON;
129 --enable_warnings
130 }
131 if ($privileges == 3)
132 {
133 --let $privilege = DELETE
134 --let $sql_statment = DELETE FROM t WHERE c = 8
135 }
136 if (`SELECT $privileges = 2 AND $context = 1`)
137 {
138 # Added a negative test for column level privilege check when row type is
139 # MINIMAL
140 if (`SELECT @@SESSION.binlog_row_image = "MINIMAL"`)
141 {
142 --eval GRANT INSERT(c) ON $priv_context TO $grant_to
143 --source include/rpl_connection_master.inc
144 INSERT INTO t VALUES(10, 10);
145 --source include/save_master_pos.inc
146 --source include/rpl_connection_slave.inc
147 START SLAVE;
148 --let $slave_sql_errno= convert_error(ER_TABLEACCESS_DENIED_ERROR)
149 --source include/wait_for_slave_sql_error.inc
150 STOP SLAVE;
151 }
152 --let $privilege = INSERT(c, d)
153 --let $sql_statment = INSERT INTO t VALUES(10, 10)
154 }
155 if (`SELECT $privileges = 1 AND $context = 1`)
156 {
157 --let $privilege = UPDATE(c)
158 --let $sql_statment = UPDATE t SET c = 12 WHERE c = 10
159 }
160
161 if (`SELECT $privileges > 2 OR ($privileges <= 2 AND $context = 1)`)
162 {
163 --echo #
164 --echo # Running test for
165 --echo # GRANT $privilege ON $priv_context TO ...
166 --echo #
167
168 --source $MYSQLTEST_VARDIR/tmp/check_privilege.inc
169 STOP SLAVE;
170
171 if ($privileges == 4)
172 {
173 --disable_warnings
174 SET @@GLOBAL.BINLOG_ROW_VALUE_OPTIONS= @binlog_row_value_options_save;
175 --enable_warnings
176 }
177
178 # 8) Ensure the table is the same on master and slave.
179 --let $diff_tables = master:t, slave:t
180 --source include/diff_tables.inc
181 }
182
183 --dec $privileges
184 }
185
186 --dec $context
187 }
188
189 # 9) Drop the table from master and slave.
190 DROP TABLE t;
191 --source include/rpl_connection_master.inc
192 DROP TABLE t;
193
194 # Clean up
195 --remove_file $MYSQLTEST_VARDIR/tmp/check_privilege.inc
196 --let $rpl_only_running_threads=1
197 --source include/rpl_end.inc