stop slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; delete from mysql.user where user=_binary'rpl_do_grant'; delete from mysql.db where user=_binary'rpl_do_grant'; flush privileges; delete from mysql.user where user=_binary'rpl_ignore_grant'; delete from mysql.db where user=_binary'rpl_ignore_grant'; flush privileges; grant select on *.* to rpl_do_grant@localhost; grant drop on test.* to rpl_do_grant@localhost; show grants for rpl_do_grant@localhost; Grants for rpl_do_grant@localhost GRANT SELECT ON *.* TO 'rpl_do_grant'@'localhost' GRANT DROP ON `test`.* TO 'rpl_do_grant'@'localhost' set password for rpl_do_grant@localhost=password("does it work?"); select password<>_binary'' from mysql.user where user=_binary'rpl_do_grant'; password<>_binary'' 1 update mysql.user set password='' where user='rpl_do_grant'; flush privileges; select password<>'' from mysql.user where user='rpl_do_grant'; password<>'' 0 set sql_mode='ANSI_QUOTES'; set password for rpl_do_grant@localhost=password('does it work?'); set sql_mode=''; select password<>'' from mysql.user where user='rpl_do_grant'; password<>'' 1 delete from mysql.user where user=_binary'rpl_do_grant'; delete from mysql.db where user=_binary'rpl_do_grant'; flush privileges; delete from mysql.user where user=_binary'rpl_do_grant'; delete from mysql.db where user=_binary'rpl_do_grant'; flush privileges; show grants for rpl_do_grant@localhost; ERROR 42000: There is no such grant defined for user 'rpl_do_grant' on host 'localhost' show grants for rpl_do_grant@localhost; ERROR 42000: There is no such grant defined for user 'rpl_do_grant' on host 'localhost' create user rpl_do_grant@localhost; show grants for rpl_do_grant@localhost; Grants for rpl_do_grant@localhost GRANT USAGE ON *.* TO 'rpl_do_grant'@'localhost' show grants for rpl_do_grant2@localhost; ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host 'localhost' show grants for rpl_do_grant@localhost; Grants for rpl_do_grant@localhost GRANT USAGE ON *.* TO 'rpl_do_grant'@'localhost' show grants for rpl_do_grant2@localhost; ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host 'localhost' rename user rpl_do_grant@localhost to rpl_do_grant2@localhost; show grants for rpl_do_grant2@localhost; Grants for rpl_do_grant2@localhost GRANT USAGE ON *.* TO 'rpl_do_grant2'@'localhost' show grants for rpl_do_grant2@localhost; Grants for rpl_do_grant2@localhost GRANT USAGE ON *.* TO 'rpl_do_grant2'@'localhost' grant DELETE,INSERT on mysqltest1.* to rpl_do_grant2@localhost; show grants for rpl_do_grant2@localhost; Grants for rpl_do_grant2@localhost GRANT USAGE ON *.* TO 'rpl_do_grant2'@'localhost' GRANT INSERT, DELETE ON `mysqltest1`.* TO 'rpl_do_grant2'@'localhost' show grants for rpl_do_grant2@localhost; Grants for rpl_do_grant2@localhost GRANT USAGE ON *.* TO 'rpl_do_grant2'@'localhost' GRANT INSERT, DELETE ON `mysqltest1`.* TO 'rpl_do_grant2'@'localhost' revoke DELETE on mysqltest1.* from rpl_do_grant2@localhost; show grants for rpl_do_grant2@localhost; Grants for rpl_do_grant2@localhost GRANT USAGE ON *.* TO 'rpl_do_grant2'@'localhost' GRANT INSERT ON `mysqltest1`.* TO 'rpl_do_grant2'@'localhost' show grants for rpl_do_grant2@localhost; Grants for rpl_do_grant2@localhost GRANT USAGE ON *.* TO 'rpl_do_grant2'@'localhost' GRANT INSERT ON `mysqltest1`.* TO 'rpl_do_grant2'@'localhost' revoke all privileges, grant option from rpl_do_grant2@localhost; show grants for rpl_do_grant2@localhost; Grants for rpl_do_grant2@localhost GRANT USAGE ON *.* TO 'rpl_do_grant2'@'localhost' show grants for rpl_do_grant2@localhost; Grants for rpl_do_grant2@localhost GRANT USAGE ON *.* TO 'rpl_do_grant2'@'localhost' drop user rpl_do_grant2@localhost; show grants for rpl_do_grant2@localhost; ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host 'localhost' show grants for rpl_do_grant2@localhost; ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host 'localhost' call mtr.add_suppression("Slave: Operation DROP USER failed for 'create_rout_db'@'localhost' Error_code: 1396"); DROP DATABASE IF EXISTS bug42217_db; CREATE DATABASE bug42217_db; GRANT CREATE ROUTINE ON bug42217_db.* TO 'create_rout_db'@'localhost' IDENTIFIED BY 'create_rout_db' WITH GRANT OPTION; USE bug42217_db; CREATE FUNCTION upgrade_del_func() RETURNS CHAR(30) BEGIN RETURN "INSIDE upgrade_del_func()"; END// USE bug42217_db; SELECT * FROM mysql.procs_priv; Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp localhost bug42217_db create_rout_db upgrade_del_func FUNCTION create_rout_db@localhost Execute,Alter Routine # SELECT upgrade_del_func(); upgrade_del_func() INSIDE upgrade_del_func() SELECT * FROM mysql.procs_priv; Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp localhost bug42217_db create_rout_db upgrade_del_func FUNCTION create_rout_db@localhost Execute,Alter Routine # SHOW GRANTS FOR 'create_rout_db'@'localhost'; Grants for create_rout_db@localhost GRANT USAGE ON *.* TO 'create_rout_db'@'localhost' IDENTIFIED BY PASSWORD '*08792480350CBA057BDE781B9DF183B263934601' GRANT CREATE ROUTINE ON `bug42217_db`.* TO 'create_rout_db'@'localhost' WITH GRANT OPTION GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `bug42217_db`.`upgrade_del_func` TO 'create_rout_db'@'localhost' USE bug42217_db; SHOW CREATE FUNCTION upgrade_del_func; Function sql_mode Create Function character_set_client collation_connection Database Collation upgrade_del_func CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_del_func`() RETURNS char(30) CHARSET latin1 BEGIN RETURN "INSIDE upgrade_del_func()"; END latin1 latin1_swedish_ci latin1_swedish_ci SELECT upgrade_del_func(); upgrade_del_func() INSIDE upgrade_del_func() "Check whether the definer user will be able to execute the replicated routine on slave" USE bug42217_db; SHOW CREATE FUNCTION upgrade_del_func; Function sql_mode Create Function character_set_client collation_connection Database Collation upgrade_del_func CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_del_func`() RETURNS char(30) CHARSET latin1 BEGIN RETURN "INSIDE upgrade_del_func()"; END latin1 latin1_swedish_ci latin1_swedish_ci SELECT upgrade_del_func(); upgrade_del_func() INSIDE upgrade_del_func() DELETE FROM mysql.procs_priv; FLUSH PRIVILEGES; USE bug42217_db; "Can't execute the replicated routine on slave like before after procs privilege is deleted " SELECT upgrade_del_func(); ERROR 42000: execute command denied to user 'create_rout_db'@'localhost' for routine 'bug42217_db.upgrade_del_func' "Test the user who creates a function on master doesn't exist on slave." "Hence SQL thread ACL_GLOBAL privilege jumps in and no mysql.procs_priv is inserted" DROP USER 'create_rout_db'@'localhost'; CREATE FUNCTION upgrade_alter_func() RETURNS CHAR(30) BEGIN RETURN "INSIDE upgrade_alter_func()"; END// SELECT upgrade_alter_func(); upgrade_alter_func() INSIDE upgrade_alter_func() SHOW CREATE FUNCTION upgrade_alter_func; Function sql_mode Create Function character_set_client collation_connection Database Collation upgrade_alter_func CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_alter_func`() RETURNS char(30) CHARSET latin1 BEGIN RETURN "INSIDE upgrade_alter_func()"; END latin1 latin1_swedish_ci latin1_swedish_ci "Should no privilege record for upgrade_alter_func in mysql.procs_priv" SELECT * FROM mysql.procs_priv; Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp SELECT upgrade_alter_func(); ERROR HY000: The user specified as a definer ('create_rout_db'@'localhost') does not exist USE bug42217_db; DROP FUNCTION upgrade_del_func; DROP FUNCTION upgrade_alter_func; DROP DATABASE bug42217_db; DROP USER 'create_rout_db'@'localhost'; stop slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; ######## BUG#49119 ####### ### i) test case from the 'how to repeat section' stop slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; CREATE TABLE t1(c1 INT); CREATE PROCEDURE p1() SELECT * FROM t1 | REVOKE EXECUTE ON PROCEDURE p1 FROM 'root'@'localhost'; ERROR 42000: There is no such grant defined for user 'root' on host 'localhost' on routine 'p1' DROP TABLE t1; DROP PROCEDURE p1; ### ii) Test case in which REVOKE partially succeeds stop slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; CREATE TABLE t1(c1 INT); CREATE PROCEDURE p1() SELECT * FROM t1 | CREATE USER 'user49119'@'localhost'; GRANT EXECUTE ON PROCEDURE p1 TO 'user49119'@'localhost'; ############################################################## ### Showing grants for both users: root and user49119 (master) SHOW GRANTS FOR 'user49119'@'localhost'; Grants for user49119@localhost GRANT USAGE ON *.* TO 'user49119'@'localhost' GRANT EXECUTE ON PROCEDURE `test`.`p1` TO 'user49119'@'localhost' SHOW GRANTS FOR CURRENT_USER; Grants for root@localhost GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION ############################################################## ############################################################## ### Showing grants for both users: root and user49119 (master) SHOW GRANTS FOR 'user49119'@'localhost'; Grants for user49119@localhost GRANT USAGE ON *.* TO 'user49119'@'localhost' GRANT EXECUTE ON PROCEDURE `test`.`p1` TO 'user49119'@'localhost' SHOW GRANTS FOR CURRENT_USER; Grants for root@localhost GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION ############################################################## ## This statement will make the revoke fail because root has no ## execute grant. However, it will still revoke the grant for ## user49119. REVOKE EXECUTE ON PROCEDURE p1 FROM 'user49119'@'localhost', 'root'@'localhost'; ERROR 42000: There is no such grant defined for user 'root' on host 'localhost' on routine 'p1' ############################################################## ### Showing grants for both users: root and user49119 (master) ### after revoke statement failure SHOW GRANTS FOR 'user49119'@'localhost'; Grants for user49119@localhost GRANT USAGE ON *.* TO 'user49119'@'localhost' SHOW GRANTS FOR CURRENT_USER; Grants for root@localhost GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION ############################################################## ############################################################# ### Showing grants for both users: root and user49119 (slave) ### after revoke statement failure (should match SHOW GRANTS FOR 'user49119'@'localhost'; Grants for user49119@localhost GRANT USAGE ON *.* TO 'user49119'@'localhost' SHOW GRANTS FOR CURRENT_USER; Grants for root@localhost GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION ############################################################## DROP TABLE t1; DROP PROCEDURE p1; DROP USER 'user49119'@'localhost'; stop slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; grant all on *.* to foo@"1.2.3.4"; revoke all privileges, grant option from "foo"; ERROR HY000: Can't revoke all privileges for one or more of the requested users show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; grant all on *.* to foo@"1.2.3.4" master-bin.000001 # Query # # use `test`; revoke all privileges, grant option from "foo" DROP USER foo@"1.2.3.4"; "End of test"