Debian Codegraph @

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

raw
1 # ==== Purpose ====
2 #
3 # To Verify that Load_query_events like Begin_load_query_event,
4 # Append_load_query_event, Execute_load_query_event and Delete_file_log_event
5 # can fail due to insufficient privileges, and succeed with sufficient
6 # privileges.
7 #
8 # ==== Implementation ====
9 #
10 # TC1. Execute Begin_load_query and Execute_load with and without the necessary
11 # privileges
12 # -----------------------------------------------------------------------------
13 # 1) Create a table on master and insert a row.
14 # 2) On slave create a user 'u1' which will be used as a PIVILEGE_CHECKS_USER to
15 # connect to master and create a table similar to the one on master.
16 # 3) Start slave and expect an error as the user doesn't have FILE and INSERT
17 # privileges.
18 # 4) Stop slave and grant FILE and INSERT privilege.
19 # 5) Start slave again and this time there should not be any error.
20 # 6) Revoke the privileges granted to user 'u1'.
21 # 7) Add a debug option to skip the privilege check for Begin_load_query and
22 # only perform the check for Execute_load_query event
23 # 8) Repeat 3) - 5)
24 # 9) Drop the table from master and slave, and also revoke the privilege from
25 # 'u1'
26 #
27 # TC2. Execute Begin_load_query and Delete_file with and without the necessary
28 # privileges
29 # ----------------------------------------------------------------------------
30 # 1) Create a table with a primary key on master and insert a row.
31 # 2) Create a data file with a record with the same primary key as inserted in
32 # the table.
33 # 3) Load this in the table at master, expecting a failure as the primary key is
34 # the same. This will create a Begin_load_query and a Delete_file event.
35 # 4) Start slave and expect an error as the user doesn't have FILE privilege.
36 # 5) Stop slave and grant FILE privilege.
37 # 6) Start slave again and this time there should not be any error.
38 # 7) Revoke the privileges granted to user 'u1'.
39 # 8) Add a debug option to skip the privilege check for Begin_load_query and
40 # only perform the check for Delete_file event
41 # 9) Repeat 4) - 6)
42 # 10) Drop the table from master and slave.
43
44 # ==== References ====
45 #
46 # WL#12966: Replication with Restricted Privileges
47 #
48
49 --source include/not_group_replication_plugin.inc
50 --source include/have_debug.inc
51 --source include/have_debug_sync.inc
52 --source include/have_binlog_format_statement.inc
53
54 --let $applier_user = 'u1'@'localhost'
55 if ($grant_to == '')
56 {
57 --let $grant_to = $applier_user
58 }
59 --let $rpl_privilege_checks_user = 2:$applier_user
60 --let $rpl_skip_start_slave=1
61 --source include/skip_config_privilege_checks_user.inc
62 --source include/master-slave.inc
63
64 --echo #
65 --echo # TC1. Execute Begin_load_query and Execute_load with and without the
66 --echo # necessary privileges
67 --echo # -------------------------------------------------------------------
68
69 SET @@SESSION.sql_log_bin = OFF;
70 CREATE TABLE t1 (word CHAR(20) NOT NULL);
71 SET @@SESSION.sql_log_bin = ON;
72
73 --let $master_log_pos_1= query_get_value(SHOW MASTER STATUS, Position, 1)
74
75 LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1;
76 --source include/save_master_pos.inc
77
78 --source include/rpl_connection_slave.inc
79 CALL mtr.add_suppression("The PRIVILEGE_CHECKS_USER for channel '' would need FILE");
80 CALL mtr.add_suppression(".*The replica coordinator and worker threads are stopped.*");
81 CREATE TABLE t1(word CHAR(20) NOT NULL);
82
83 START SLAVE;
84 --let $slave_sql_errno= convert_error(ER_CLIENT_FILE_PRIVILEGE_FOR_REPLICATION_CHECKS)
85 --source include/wait_for_slave_sql_error.inc
86
87 --eval GRANT FILE, INSERT ON *.* TO $grant_to
88 START SLAVE;
89 --source include/sync_slave_sql.inc
90
91 # Now check for the privilege required by Execute_load_query_log_event separately.
92
93 STOP SLAVE;
94 --eval REVOKE FILE, INSERT ON *.* FROM $grant_to
95
96 SET @@GLOBAL.DEBUG = "+d,skip_the_priv_check_in_begin_load";
97 # Start reading the LOAD DATA INFILE statement but skip the privilege check for
98 # Begin_load_query event this time, and just verify for Execute_load_query event
99
100 --replace_result $MASTER_MYPORT MASTER_PORT $master_log_pos_1 MASTER_LOG_POS
101 eval CHANGE REPLICATION SOURCE TO SOURCE_USER='root', SOURCE_PORT=$MASTER_MYPORT, SOURCE_HOST='127.0.0.1', PRIVILEGE_CHECKS_USER = $applier_user, SOURCE_LOG_POS= $master_log_pos_1;
102
103 START SLAVE;
104 SET DEBUG_SYNC= "now WAIT_FOR skipped_the_priv_check_in_begin_load";
105 --let $slave_sql_errno= convert_error(ER_CLIENT_FILE_PRIVILEGE_FOR_REPLICATION_CHECKS)
106 --source include/wait_for_slave_sql_error.inc
107
108 SET @@GLOBAL.DEBUG = "-d,skip_the_priv_check_in_begin_load";
109 STOP SLAVE;
110 --eval GRANT FILE, INSERT ON *.* TO $grant_to
111
112 --replace_result $MASTER_MYPORT MASTER_PORT $master_log_pos_1 MASTER_LOG_POS
113 eval CHANGE REPLICATION SOURCE TO SOURCE_USER='root', SOURCE_PORT=$MASTER_MYPORT, SOURCE_HOST='127.0.0.1', PRIVILEGE_CHECKS_USER = $applier_user, SOURCE_LOG_POS= $master_log_pos_1;
114 START SLAVE;
115 --source include/sync_slave_sql.inc
116 DROP TABLE t1;
117 STOP SLAVE;
118
119 --eval REVOKE FILE, INSERT ON *.* FROM $grant_to
120 --source include/rpl_connection_master.inc
121
122 --echo #
123 --echo # TC2. Execute Begin_load_query and Delete_file with and without the
124 --echo # necessary privileges
125 --echo # -------------------------------------------------------------------
126
127 --source include/have_myisam.inc
128 SET @@SESSION.sql_log_bin = OFF;
129 DROP TABLE t1;
130 CREATE TABLE t1 (c1 INT NOT NULL, PRIMARY KEY (c1)) ENGINE = MyISAM;
131
132 # Insert some data into the table to generate an error on LOAD DATA INSFILE
133 INSERT INTO t1 VALUES (1);
134
135 SET @@SESSION.sql_log_bin = ON;
136 --let $master_log_pos_1= query_get_value(SHOW MASTER STATUS, Position, 1)
137 # Generates the file to be loaded
138 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
139 --write_file $MYSQLTEST_VARDIR/tmp/data01
140 1
141 EOF
142 --error ER_DUP_ENTRY
143 --eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/data01' INTO TABLE t1
144
145 --source include/rpl_connection_slave.inc
146 CREATE TABLE t1 (c1 INT NOT NULL, PRIMARY KEY (c1)) ENGINE = MyISAM;
147
148 # We want to replicate only from the last LOAD DATA INFILE statement
149 # So doing a change master and start replicating from master_log_pos_1
150
151 START SLAVE;
152 --let $slave_sql_errno= convert_error(ER_CLIENT_FILE_PRIVILEGE_FOR_REPLICATION_CHECKS)
153 --source include/wait_for_slave_sql_error.inc
154
155 --eval GRANT FILE ON *.* TO $grant_to
156
157 START SLAVE;
158 --source include/sync_slave_sql.inc
159
160 # Now check for the privilege required by Delete_file event separately.
161
162 STOP SLAVE;
163 --eval REVOKE FILE ON *.* FROM $grant_to
164
165 SET @@GLOBAL.DEBUG = "+d,skip_the_priv_check_in_begin_load";
166 # Start reading the LOAD DATA INFILE statement but skip the privilege check for
167 # Begin_load_query event this time, and just verify for Execute_load_query event
168 --replace_result $MASTER_MYPORT MASTER_PORT $master_log_pos_1 MASTER_LOG_POS
169 eval CHANGE REPLICATION SOURCE TO SOURCE_USER='root', SOURCE_PORT=$MASTER_MYPORT, SOURCE_HOST='127.0.0.1', PRIVILEGE_CHECKS_USER = $applier_user, SOURCE_LOG_POS= $master_log_pos_1;
170
171 START SLAVE;
172 SET DEBUG_SYNC = "now WAIT_FOR skipped_the_priv_check_in_begin_load";
173 --let $slave_sql_errno= convert_error(ER_CLIENT_FILE_PRIVILEGE_FOR_REPLICATION_CHECKS)
174 --source include/wait_for_slave_sql_error.inc
175
176 SET @@GLOBAL.DEBUG = "-d,skip_the_priv_check_in_begin_load";
177 STOP SLAVE;
178 --eval GRANT FILE ON *.* TO $grant_to
179 --replace_result $MASTER_MYPORT MASTER_PORT $master_log_pos_1 MASTER_LOG_POS
180 eval CHANGE REPLICATION SOURCE TO SOURCE_USER='root', SOURCE_PORT=$MASTER_MYPORT, SOURCE_HOST='127.0.0.1', PRIVILEGE_CHECKS_USER = $applier_user, SOURCE_LOG_POS= $master_log_pos_1;
181
182 START SLAVE;
183 --source include/sync_slave_sql.inc
184 DROP TABLE t1;
185 STOP SLAVE;
186 connection master;
187 DROP TABLE t1;
188 --let $rpl_only_running_threads=1
189 --remove_file $MYSQLTEST_VARDIR/tmp/data01
190 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp SQL_LOAD*
191 --source include/rpl_connection_master.inc
192 --source include/rpl_reset.inc
193 --source include/rpl_end.inc