include/master-slave.inc
[connection master]
connection master;
create user rpl_do_grant@localhost;
grant select on *.* to rpl_do_grant@localhost;
grant drop on test.* to rpl_do_grant@localhost;
connection slave;
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`
connection master;
set password for rpl_do_grant@localhost=password("does it work?");
connection slave;
select authentication_string<>'' from mysql.user where user='rpl_do_grant';
authentication_string<>''
1
connection master;
update mysql.global_priv set priv=json_remove(priv, '$.authentication_string') where user='rpl_do_grant';
flush privileges;
select authentication_string<>'' from mysql.user where user='rpl_do_grant';
authentication_string<>''
0
set sql_mode='ANSI_QUOTES';
set password for rpl_do_grant@localhost=password('does it work?');
set sql_mode='';
connection slave;
select authentication_string<>'' from mysql.user where user='rpl_do_grant';
authentication_string<>''
1
connection master;
drop user rpl_do_grant@localhost;
connection slave;
connection master;
show grants for rpl_do_grant@localhost;
ERROR 42000: There is no such grant defined for user 'rpl_do_grant' on host 'localhost'
connection slave;
show grants for rpl_do_grant@localhost;
ERROR 42000: There is no such grant defined for user 'rpl_do_grant' on host 'localhost'
connection master;
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'
connection slave;
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'
connection master;
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`
connection slave;
show grants for rpl_do_grant2@localhost;
Grants for rpl_do_grant2@localhost
GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
connection master;
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`
connection slave;
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`
connection master;
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`
connection slave;
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`
connection master;
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`
connection slave;
show grants for rpl_do_grant2@localhost;
Grants for rpl_do_grant2@localhost
GRANT USAGE ON *.* TO `rpl_do_grant2`@`localhost`
connection master;
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'
connection slave;
show grants for rpl_do_grant2@localhost;
ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host 'localhost'
connection master;
call mtr.add_suppression("Slave: Operation DROP USER failed for 'create_rout_db'@'localhost' error.* 1396");
connection slave;
connection master;
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;
connection slave;
connection master;
connect  create_rout_db_master, localhost, create_rout_db, create_rout_db, bug42217_db,$MASTER_MYPORT,;
connect  create_rout_db_slave, localhost, create_rout_db, create_rout_db, bug42217_db, $SLAVE_MYPORT,;
connection create_rout_db_master;
USE bug42217_db;
CREATE FUNCTION upgrade_del_func() RETURNS CHAR(30)
BEGIN
RETURN "INSIDE upgrade_del_func()";
END//
connection master;
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()
connection slave;
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	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 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"
connection create_rout_db_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	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 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()
connection slave;
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';
connection create_rout_db_master;
CREATE FUNCTION upgrade_alter_func() RETURNS CHAR(30)
BEGIN
RETURN "INSIDE upgrade_alter_func()";
END//
connection master;
SELECT upgrade_alter_func();
upgrade_alter_func()
INSIDE upgrade_alter_func()
connection slave;
SHOW CREATE FUNCTION upgrade_alter_func;
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
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 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
disconnect create_rout_db_master;
disconnect create_rout_db_slave;
connection master;
USE bug42217_db;
DROP FUNCTION upgrade_del_func;
DROP FUNCTION upgrade_alter_func;
DROP DATABASE bug42217_db;
connection slave;
connection master;
SET SQL_LOG_BIN= 0;
DROP USER 'create_rout_db'@'localhost';
SET SQL_LOG_BIN= 1;
include/rpl_reset.inc
USE test;
######## BUG#49119 #######
### i) test case from the 'how to repeat section'
connection master;
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'
connection slave;
connection master;
DROP TABLE t1;
DROP PROCEDURE p1;
connection slave;
### ii) Test case in which REVOKE partially succeeds
connection master;
include/rpl_reset.inc
connection master;
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
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
##############################################################
connection slave;
##############################################################
### 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
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
##############################################################
connection master;
## 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
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
##############################################################
connection slave;
#############################################################
### 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
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
##############################################################
connection master;
DROP TABLE t1;
DROP PROCEDURE p1;
DROP USER 'user49119'@'localhost';
connection slave;
include/rpl_reset.inc
connection master;
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
include/show_binlog_events.inc
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Gtid	#	#	GTID #-#-#
master-bin.000001	#	Query	#	#	use `test`; grant all on *.* to foo@"1.2.3.4"
master-bin.000001	#	Gtid	#	#	GTID #-#-#
master-bin.000001	#	Query	#	#	use `test`; revoke all privileges, grant option from "foo"
connection slave;
include/check_slave_no_error.inc
connection master;
DROP USER foo@"1.2.3.4";
connection slave;

# Bug#27606 GRANT statement should be replicated with DEFINER information
include/rpl_reset.inc
connection master;
GRANT SELECT, INSERT ON mysql.user TO user_bug27606@localhost;
SELECT Grantor FROM mysql.tables_priv WHERE User='user_bug27606';
Grantor
root@localhost
connection slave;
SELECT Grantor FROM mysql.tables_priv WHERE User='user_bug27606';
Grantor
root@localhost
connection master;
REVOKE SELECT ON mysql.user FROM user_bug27606@localhost;
SELECT Grantor FROM mysql.tables_priv WHERE User='user_bug27606';
Grantor
root@localhost
connection slave;
SELECT Grantor FROM mysql.tables_priv WHERE User='user_bug27606';
Grantor
root@localhost
connection master;
DROP USER user_bug27606@localhost;
select priv into @root_priv from mysql.global_priv where user='root' and host='127.0.0.1';
update mysql.global_priv set priv=@root_priv where user='root' and host='localhost';
include/rpl_end.inc