| 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 |