Debian Codegraph @

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

raw
1 # Tests of grants and users
2
3 --source include/not_group_replication_plugin.inc
4 let $rpl_privilege_checks_user_grant_option = 1;
5 source include/set_privilege_checks_user_as_system_user.inc;
6 source include/master-slave.inc;
7
8 --echo **** On Master ****
9 connection master;
10
11 CREATE USER dummy@localhost;
12 CREATE USER dummy1@localhost, dummy2@localhost;
13
14 SELECT user, host FROM mysql.user WHERE user like 'dummy%';
15 SELECT COUNT(*) FROM mysql.user WHERE user like 'dummy%';
16 --source include/sync_slave_sql_with_master.inc
17 --echo **** On Slave ****
18 SELECT user,host FROM mysql.user WHERE user like 'dummy%';
19 SELECT COUNT(*) FROM mysql.user WHERE user like 'dummy%';
20
21 --echo **** On Master ****
22 connection master;
23
24 # No user exists
25 error ER_CANNOT_USER;
26 DROP USER nonexisting@localhost;
27
28 # At least one user exists, but not all
29 error ER_CANNOT_USER;
30 DROP USER nonexisting@localhost, dummy@localhost;
31
32 # All users exist
33 DROP USER dummy@localhost, dummy1@localhost, dummy2@localhost;
34
35 SELECT user, host FROM mysql.user WHERE user like 'dummy%';
36 SELECT COUNT(*) FROM mysql.user WHERE user like 'dummy%';
37 --source include/sync_slave_sql_with_master.inc
38 --echo **** On Slave ****
39 SELECT user,host FROM mysql.user WHERE user like 'dummy%';
40 SELECT COUNT(*) FROM mysql.user WHERE user like 'dummy%';
41
42
43 ######################################################
44
45 #
46 # BUG#54866: Partially failed REVOKE not binlogged, causes inconsistency or replication abort
47 #
48
49 --echo ##########
50 --echo ########## setup (PART I)
51 --echo ##########
52 -- source include/rpl_reset.inc
53 -- connection master
54
55 --let $dbname= b54866
56 --let $dbuser= b54866_user
57
58 --eval CREATE DATABASE $dbname
59 --eval use $dbname
60 CREATE TABLE t1 ( c1 INT, c2 INT, c3 INT );
61
62 --echo ##########
63 --echo ########## GRANT ALL
64 --echo ##########
65
66 ## create user, grant and revoke (the last command fails partially)
67 --eval CREATE USER '$dbuser'@'localhost'
68 --eval GRANT ALL ON $dbname.* TO '$dbuser'@'localhost'
69 --error ER_NONEXISTING_GRANT
70 --eval REVOKE ALL ON $dbname.* FROM '$dbuser'@'localhost', 'b54866_fake_user'
71
72 --echo ****** Checking grants on the master for user: $dbuser ******
73 --eval SHOW GRANTS FOR '$dbuser'@'localhost'
74 --echo *************************************************************
75
76 --connection master
77 --source include/sync_slave_sql_with_master.inc
78
79 ## assert that grants are the same on master and on slave for $dbuser
80 --connection master
81 --echo ****** Checking grants on the master for user: $dbuser ******
82 --eval SHOW GRANTS FOR '$dbuser'@'localhost'
83 --echo *************************************************************
84
85 --source include/sync_slave_sql_with_master.inc
86 --echo ****** Checking grants on the slave for user: $dbuser *******
87 --eval SHOW GRANTS FOR '$dbuser'@'localhost'
88 --echo *************************************************************
89
90 --echo ##########
91 --echo ########## TABLE GRANTS
92 --echo ##########
93
94 --connection master
95 --eval GRANT ALTER,CREATE,DROP ON TABLE $dbname.t1 TO '$dbuser'@'localhost'
96 --echo ****** Checking grants on the master for user: $dbuser ******
97 --eval SHOW GRANTS FOR '$dbuser'@'localhost'
98 --echo *************************************************************
99
100 --source include/sync_slave_sql_with_master.inc
101 --echo ****** Checking grants on the slave for user: $dbuser *******
102 --eval SHOW GRANTS FOR '$dbuser'@'localhost'
103 --echo *************************************************************
104
105 ## revoke should now fail and log a incident event
106 --connection master
107 --error ER_NONEXISTING_TABLE_GRANT
108 --eval REVOKE ALTER ON TABLE $dbname.t1 FROM '$dbuser'@'localhost', 'b54866_fake_user'
109
110 --connection master
111 --echo ****** Checking grants on the master for user: $dbuser ******
112 --eval SHOW GRANTS FOR '$dbuser'@'localhost'
113 --echo *************************************************************
114
115 --connection master
116 --source include/sync_slave_sql_with_master.inc
117
118 --echo ****** Checking grants on the slave for user: $dbuser *******
119 --eval SHOW GRANTS FOR '$dbuser'@'localhost'
120 --echo *************************************************************
121
122 --echo ##########
123 --echo ########## setup (PART II)
124 --echo ##########
125
126 -- connection master
127 DELIMITER |;
128 CREATE PROCEDURE b54866_p() BEGIN SELECT 1; END|
129 CREATE FUNCTION b54866_f() RETURNS INT BEGIN RETURN 1; END|
130 DELIMITER ;|
131
132 --eval GRANT EXECUTE ON PROCEDURE $dbname.b54866_p TO '$dbuser'@'localhost'
133 --eval GRANT EXECUTE ON FUNCTION $dbname.b54866_f TO '$dbuser'@'localhost'
134
135 --echo ****** Checking grants on the master for user: $dbuser ******
136 --eval SHOW GRANTS FOR '$dbuser'@'localhost'
137 --echo *************************************************************
138
139 --source include/sync_slave_sql_with_master.inc
140 --echo ****** Checking grants on the slave for user: $dbuser *******
141 --eval SHOW GRANTS FOR '$dbuser'@'localhost'
142 --echo *************************************************************
143
144 --echo ##########
145 --echo ########## PROCEDURE
146 --echo ##########
147
148 -- connection master
149
150 -- echo #### PROCEDURE ASSERTION
151 --error ER_NONEXISTING_PROC_GRANT
152 --eval REVOKE EXECUTE ON PROCEDURE $dbname.b54866_p FROM '$dbuser'@'localhost', 'b54866_fake_user'
153
154 --echo ****** Checking grants on the master for user: $dbuser ******
155 --eval SHOW GRANTS FOR '$dbuser'@'localhost'
156 --echo *************************************************************
157
158 --connection master
159 --source include/sync_slave_sql_with_master.inc
160
161 --echo ****** Checking grants on the slave for user: $dbuser *******
162 --eval SHOW GRANTS FOR '$dbuser'@'localhost'
163 --echo *************************************************************
164
165 --echo ##########
166 --echo ########## FUNCTION
167 --echo ##########
168 -- connection master
169
170 --error ER_NONEXISTING_PROC_GRANT
171 --eval REVOKE EXECUTE ON FUNCTION $dbname.b54866_f FROM '$dbuser'@'localhost', 'b54866_fake_user'
172
173 --connection master
174 --echo ****** Checking grants on the master for user: $dbuser ******
175 --eval SHOW GRANTS FOR '$dbuser'@'localhost'
176 --echo *************************************************************
177
178 --connection master
179 --source include/sync_slave_sql_with_master.inc
180
181 --echo ****** Checking grants on the slave for user: $dbuser *******
182 --eval SHOW GRANTS FOR '$dbuser'@'localhost'
183 --echo *************************************************************
184
185 --echo ##########
186 --echo ########## empty revokes should not be binlogged
187 --echo ##########
188
189 -- connection master
190
191 ## assert that failing revoke on non existing user will not
192 ## be binlogged
193 -- error ER_NONEXISTING_PROC_GRANT
194 REVOKE EXECUTE ON PROCEDURE b54866_p FROM 'fake_user'@'localhost';
195 -- error ER_NONEXISTING_PROC_GRANT
196 REVOKE EXECUTE ON FUNCTION b54866_f FROM 'fake_user'@'localhost';
197 -- error ER_REVOKE_GRANTS
198 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'fake_user'@'localhost';
199 --source include/sync_slave_sql_with_master.inc
200
201 --echo ##########
202 --echo ########## Cleanup
203 --echo ##########
204 --connection master
205 DROP PROCEDURE b54866_p;
206 DROP FUNCTION b54866_f;
207 --eval DROP USER '$dbuser'@'localhost'
208 --eval DROP DATABASE $dbname
209 --source include/sync_slave_sql_with_master.inc
210
211 #
212 # BUG#11827392: 60082: EVEN THOUGH IT FAILS, 'CREATE USER' STATEMENT SI STILL BINLOGGED.
213 #
214
215 #
216 # The test case is based on the one included in the
217 # original report. It works as follows:
218 #
219 # 1. We issue a failing statement on the master
220 # 2. Then we synchronize the slave
221 # - this asserts that there is no side-effect
222 # on the replication stream
223 # 3. We then dump the contents of the binlog and
224 # search for the CREATE USER entry. There should
225 # not be any, so we print the # of entries found.
226 #
227
228 --connection master
229 --source include/rpl_reset.inc
230 --connection master
231
232 --error ER_PLUGIN_IS_NOT_LOADED
233 CREATE USER foo IDENTIFIED WITH 'my_plugin';
234 --source include/sync_slave_sql_with_master.inc
235
236 --connection master
237
238 --disable_query_log
239
240 --let $MYSQLD_DATADIR= `select @@datadir`
241 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
242 --let $prefix=`SELECT UUID()`
243 --let $out_file=$MYSQLTEST_VARDIR/tmp/$prefix.out
244 --exec $MYSQL_BINLOG --verbose $MYSQLD_DATADIR/$binlog_file > $out_file
245
246 --let OUTF=$out_file
247
248 --enable_query_log
249
250 --echo # Search for occurrences of CREATE USER in the output from mysqlbinlog
251
252 perl;
253 use strict;
254 my $outf= $ENV{'OUTF'} or die "OUTF not set";
255 open(FILE, "$outf") or die("Unable to open $outf: $!\n");
256 my $count = () = grep(/create user 'foo'/gi,<FILE>);
257 print "- Occurrences: $count\n";
258 close(FILE);
259 EOF
260
261 --remove_file $out_file
262
263 --source include/rpl_end.inc