Debian Codegraph @

mysql-test/suite/engines/funcs/r/rpl_do_grant.result

raw
1 include/master-slave.inc
2 [connection master]
3 connection master;
4 create user rpl_do_grant@localhost;
5 grant select on *.* to rpl_do_grant@localhost;
6 grant drop on test.* to rpl_do_grant@localhost;
7 connection slave;
8 show grants for rpl_do_grant@localhost;
9 Grants for rpl_do_grant@localhost
10 GRANT SELECT ON *.* TO `rpl_do_grant`@`localhost`
11 GRANT DROP ON `test`.* TO `rpl_do_grant`@`localhost`
12 connection master;
13 set password for rpl_do_grant@localhost=password("does it work?");
14 connection slave;
15 select authentication_string<>'' from mysql.user where user='rpl_do_grant';
16 authentication_string<>''
17 1
18 connection master;
19 update mysql.global_priv set priv=json_remove(priv, '$.authentication_string') where user='rpl_do_grant';
20 flush privileges;
21 select authentication_string<>'' from mysql.user where user='rpl_do_grant';
22 authentication_string<>''
23 0
24 set sql_mode='ANSI_QUOTES';
25 set password for rpl_do_grant@localhost=password('does it work?');
26 set sql_mode='';
27 connection slave;
28 select authentication_string<>'' from mysql.user where user='rpl_do_grant';
29 authentication_string<>''
30 1
31 connection master;
32 drop user rpl_do_grant@localhost;
33 connection slave;
34 connection master;
35 show grants for rpl_do_grant@localhost;
36 ERROR 42000: There is no such grant defined for user 'rpl_do_grant' on host 'localhost'
37 connection slave;
38 show grants for rpl_do_grant@localhost;
39 ERROR 42000: There is no such grant defined for user 'rpl_do_grant' on host 'localhost'
40 connection master;
41 create user rpl_do_grant@localhost;
42 show grants for rpl_do_grant@localhost;
43 Grants for rpl_do_grant@localhost
44 GRANT USAGE ON *.* TO `rpl_do_grant`@`localhost`
45 show grants for rpl_do_grant2@localhost;
46 ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host 'localhost'
47 connection slave;
48 show grants for rpl_do_grant@localhost;
49 Grants for rpl_do_grant@localhost
50 GRANT USAGE ON *.* TO `rpl_do_grant`@`localhost`
51 show grants for rpl_do_grant2@localhost;
52 ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host 'localhost'
53 connection master;
54 rename user rpl_do_grant@localhost to rpl_do_grant2@localhost;
55 show grants for rpl_do_grant2@localhost;
56 Grants for rpl_do_grant2@localhost
57 GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
58 connection slave;
59 show grants for rpl_do_grant2@localhost;
60 Grants for rpl_do_grant2@localhost
61 GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
62 connection master;
63 grant DELETE,INSERT on mysqltest1.* to rpl_do_grant2@localhost;
64 show grants for rpl_do_grant2@localhost;
65 Grants for rpl_do_grant2@localhost
66 GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
67 GRANT INSERT, DELETE ON `mysqltest1`.* TO `rpl_do_grant2`@`localhost`
68 connection slave;
69 show grants for rpl_do_grant2@localhost;
70 Grants for rpl_do_grant2@localhost
71 GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
72 GRANT INSERT, DELETE ON `mysqltest1`.* TO `rpl_do_grant2`@`localhost`
73 connection master;
74 revoke DELETE on mysqltest1.* from rpl_do_grant2@localhost;
75 show grants for rpl_do_grant2@localhost;
76 Grants for rpl_do_grant2@localhost
77 GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
78 GRANT INSERT ON `mysqltest1`.* TO `rpl_do_grant2`@`localhost`
79 connection slave;
80 show grants for rpl_do_grant2@localhost;
81 Grants for rpl_do_grant2@localhost
82 GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
83 GRANT INSERT ON `mysqltest1`.* TO `rpl_do_grant2`@`localhost`
84 connection master;
85 revoke all privileges, grant option from rpl_do_grant2@localhost;
86 show grants for rpl_do_grant2@localhost;
87 Grants for rpl_do_grant2@localhost
88 GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
89 connection slave;
90 show grants for rpl_do_grant2@localhost;
91 Grants for rpl_do_grant2@localhost
92 GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
93 connection master;
94 drop user rpl_do_grant2@localhost;
95 show grants for rpl_do_grant2@localhost;
96 ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host 'localhost'
97 connection slave;
98 show grants for rpl_do_grant2@localhost;
99 ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host 'localhost'
100 connection master;
101 call mtr.add_suppression("Slave: Operation DROP USER failed for 'create_rout_db'@'localhost' error.* 1396");
102 connection slave;
103 connection master;
104 DROP DATABASE IF EXISTS bug42217_db;
105 CREATE DATABASE bug42217_db;
106 GRANT CREATE ROUTINE ON bug42217_db.* TO 'create_rout_db'@'localhost'
107 IDENTIFIED BY 'create_rout_db' WITH GRANT OPTION;
108 connection slave;
109 connection master;
110 connect create_rout_db_master, localhost, create_rout_db, create_rout_db, bug42217_db,$MASTER_MYPORT,;
111 connect create_rout_db_slave, localhost, create_rout_db, create_rout_db, bug42217_db, $SLAVE_MYPORT,;
112 connection create_rout_db_master;
113 USE bug42217_db;
114 CREATE FUNCTION upgrade_del_func() RETURNS CHAR(30)
115 BEGIN
116 RETURN "INSIDE upgrade_del_func()";
117 END//
118 connection master;
119 USE bug42217_db;
120 SELECT * FROM mysql.procs_priv;
121 Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
122 localhost bug42217_db create_rout_db upgrade_del_func FUNCTION create_rout_db@localhost Execute,Alter Routine #
123 SELECT upgrade_del_func();
124 upgrade_del_func()
125 INSIDE upgrade_del_func()
126 connection slave;
127 SELECT * FROM mysql.procs_priv;
128 Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
129 localhost bug42217_db create_rout_db upgrade_del_func FUNCTION create_rout_db@localhost Execute,Alter Routine #
130 SHOW GRANTS FOR 'create_rout_db'@'localhost';
131 Grants for create_rout_db@localhost
132 GRANT USAGE ON *.* TO `create_rout_db`@`localhost` IDENTIFIED BY PASSWORD '*08792480350CBA057BDE781B9DF183B263934601'
133 GRANT CREATE ROUTINE ON `bug42217_db`.* TO `create_rout_db`@`localhost` WITH GRANT OPTION
134 GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `bug42217_db`.`upgrade_del_func` TO `create_rout_db`@`localhost`
135 USE bug42217_db;
136 SHOW CREATE FUNCTION upgrade_del_func;
137 Function sql_mode Create Function character_set_client collation_connection Database Collation
138 upgrade_del_func STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_del_func`() RETURNS char(30) CHARSET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci
139 BEGIN
140 RETURN "INSIDE upgrade_del_func()";
141 END latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
142 SELECT upgrade_del_func();
143 upgrade_del_func()
144 INSIDE upgrade_del_func()
145 "Check whether the definer user will be able to execute the replicated routine on slave"
146 connection create_rout_db_slave;
147 USE bug42217_db;
148 SHOW CREATE FUNCTION upgrade_del_func;
149 Function sql_mode Create Function character_set_client collation_connection Database Collation
150 upgrade_del_func STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_del_func`() RETURNS char(30) CHARSET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci
151 BEGIN
152 RETURN "INSIDE upgrade_del_func()";
153 END latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
154 SELECT upgrade_del_func();
155 upgrade_del_func()
156 INSIDE upgrade_del_func()
157 connection slave;
158 DELETE FROM mysql.procs_priv;
159 FLUSH PRIVILEGES;
160 USE bug42217_db;
161 "Can't execute the replicated routine on slave like before after procs privilege is deleted "
162 SELECT upgrade_del_func();
163 ERROR 42000: execute command denied to user 'create_rout_db'@'localhost' for routine 'bug42217_db.upgrade_del_func'
164 "Test the user who creates a function on master doesn't exist on slave."
165 "Hence SQL thread ACL_GLOBAL privilege jumps in and no mysql.procs_priv is inserted"
166 DROP USER 'create_rout_db'@'localhost';
167 connection create_rout_db_master;
168 CREATE FUNCTION upgrade_alter_func() RETURNS CHAR(30)
169 BEGIN
170 RETURN "INSIDE upgrade_alter_func()";
171 END//
172 connection master;
173 SELECT upgrade_alter_func();
174 upgrade_alter_func()
175 INSIDE upgrade_alter_func()
176 connection slave;
177 SHOW CREATE FUNCTION upgrade_alter_func;
178 Function sql_mode Create Function character_set_client collation_connection Database Collation
179 upgrade_alter_func STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_alter_func`() RETURNS char(30) CHARSET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci
180 BEGIN
181 RETURN "INSIDE upgrade_alter_func()";
182 END latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
183 "Should no privilege record for upgrade_alter_func in mysql.procs_priv"
184 SELECT * FROM mysql.procs_priv;
185 Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
186 SELECT upgrade_alter_func();
187 ERROR HY000: The user specified as a definer ('create_rout_db'@'localhost') does not exist
188 disconnect create_rout_db_master;
189 disconnect create_rout_db_slave;
190 connection master;
191 USE bug42217_db;
192 DROP FUNCTION upgrade_del_func;
193 DROP FUNCTION upgrade_alter_func;
194 DROP DATABASE bug42217_db;
195 connection slave;
196 connection master;
197 SET SQL_LOG_BIN= 0;
198 DROP USER 'create_rout_db'@'localhost';
199 SET SQL_LOG_BIN= 1;
200 include/rpl_reset.inc
201 USE test;
202 ######## BUG#49119 #######
203 ### i) test case from the 'how to repeat section'
204 connection master;
205 CREATE TABLE t1(c1 INT);
206 CREATE PROCEDURE p1() SELECT * FROM t1 |
207 REVOKE EXECUTE ON PROCEDURE p1 FROM 'root'@'localhost';
208 ERROR 42000: There is no such grant defined for user 'root' on host 'localhost' on routine 'p1'
209 connection slave;
210 connection master;
211 DROP TABLE t1;
212 DROP PROCEDURE p1;
213 connection slave;
214 #
215 # TODO: Re-enable test-case after fixing MDEV-29848
216 #
217 include/rpl_reset.inc
218 connection master;
219 grant all on *.* to foo@"1.2.3.4";
220 revoke all privileges, grant option from "foo";
221 ERROR HY000: Can't revoke all privileges for one or more of the requested users
222 include/show_binlog_events.inc
223 Log_name Pos Event_type Server_id End_log_pos Info
224 master-bin.000001 # Gtid # # GTID #-#-#
225 master-bin.000001 # Query # # use `test`; grant all on *.* to foo@"1.2.3.4"
226 master-bin.000001 # Gtid # # GTID #-#-#
227 master-bin.000001 # Query # # use `test`; revoke all privileges, grant option from "foo"
228 connection slave;
229 include/check_slave_no_error.inc
230 connection master;
231 DROP USER foo@"1.2.3.4";
232 connection slave;
233
234 # Bug#27606 GRANT statement should be replicated with DEFINER information
235 include/rpl_reset.inc
236 connection master;
237 GRANT SELECT, INSERT ON mysql.user TO user_bug27606@localhost;
238 SELECT Grantor FROM mysql.tables_priv WHERE User='user_bug27606';
239 Grantor
240 root@localhost
241 connection slave;
242 SELECT Grantor FROM mysql.tables_priv WHERE User='user_bug27606';
243 Grantor
244 root@localhost
245 connection master;
246 REVOKE SELECT ON mysql.user FROM user_bug27606@localhost;
247 SELECT Grantor FROM mysql.tables_priv WHERE User='user_bug27606';
248 Grantor
249 root@localhost
250 connection slave;
251 SELECT Grantor FROM mysql.tables_priv WHERE User='user_bug27606';
252 Grantor
253 root@localhost
254 connection master;
255 DROP USER user_bug27606@localhost;
256 select priv into @root_priv from mysql.global_priv where user='root' and host='127.0.0.1';
257 update mysql.global_priv set priv=@root_priv where user='root' and host='localhost';
258 include/rpl_end.inc