89
89
ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host 'localhost'
90
90
show grants for rpl_do_grant2@localhost;
91
91
ERROR 42000: There is no such grant defined for user 'rpl_do_grant2' on host 'localhost'
92
DROP DATABASE IF EXISTS bug42217_db;
93
CREATE DATABASE bug42217_db;
94
GRANT CREATE ROUTINE ON bug42217_db.* TO 'create_rout_db'@'localhost'
95
IDENTIFIED BY 'create_rout_db' WITH GRANT OPTION;
97
CREATE FUNCTION upgrade_del_func() RETURNS CHAR(30)
99
RETURN "INSIDE upgrade_del_func()";
102
SELECT * FROM mysql.procs_priv;
103
Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
104
localhost bug42217_db create_rout_db upgrade_del_func FUNCTION create_rout_db@localhost Execute,Alter Routine #
105
SELECT upgrade_del_func();
107
INSIDE upgrade_del_func()
108
SELECT * FROM mysql.procs_priv;
109
Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
110
localhost bug42217_db create_rout_db upgrade_del_func FUNCTION create_rout_db@localhost Execute,Alter Routine #
111
SHOW GRANTS FOR 'create_rout_db'@'localhost';
112
Grants for create_rout_db@localhost
113
GRANT USAGE ON *.* TO 'create_rout_db'@'localhost' IDENTIFIED BY PASSWORD '*08792480350CBA057BDE781B9DF183B263934601'
114
GRANT CREATE ROUTINE ON `bug42217_db`.* TO 'create_rout_db'@'localhost' WITH GRANT OPTION
115
GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `bug42217_db`.`upgrade_del_func` TO 'create_rout_db'@'localhost'
117
SHOW CREATE FUNCTION upgrade_del_func;
118
Function sql_mode Create Function character_set_client collation_connection Database Collation
119
upgrade_del_func CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_del_func`() RETURNS char(30) CHARSET latin1
121
RETURN "INSIDE upgrade_del_func()";
122
END latin1 latin1_swedish_ci latin1_swedish_ci
123
SELECT upgrade_del_func();
125
INSIDE upgrade_del_func()
126
"Check whether the definer user will be able to execute the replicated routine on slave"
128
SHOW CREATE FUNCTION upgrade_del_func;
129
Function sql_mode Create Function character_set_client collation_connection Database Collation
130
upgrade_del_func CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_del_func`() RETURNS char(30) CHARSET latin1
132
RETURN "INSIDE upgrade_del_func()";
133
END latin1 latin1_swedish_ci latin1_swedish_ci
134
SELECT upgrade_del_func();
136
INSIDE upgrade_del_func()
137
DELETE FROM mysql.procs_priv;
140
"Can't execute the replicated routine on slave like before after procs privilege is deleted "
141
SELECT upgrade_del_func();
142
ERROR 42000: execute command denied to user 'create_rout_db'@'localhost' for routine 'bug42217_db.upgrade_del_func'
143
"Test the user who creates a function on master doesn't exist on slave."
144
"Hence SQL thread ACL_GLOBAL privilege jumps in and no mysql.procs_priv is inserted"
145
DROP USER 'create_rout_db'@'localhost';
146
CREATE FUNCTION upgrade_alter_func() RETURNS CHAR(30)
148
RETURN "INSIDE upgrade_alter_func()";
150
SELECT upgrade_alter_func();
152
INSIDE upgrade_alter_func()
153
SHOW CREATE FUNCTION upgrade_alter_func;
154
Function sql_mode Create Function character_set_client collation_connection Database Collation
155
upgrade_alter_func CREATE DEFINER=`create_rout_db`@`localhost` FUNCTION `upgrade_alter_func`() RETURNS char(30) CHARSET latin1
157
RETURN "INSIDE upgrade_alter_func()";
158
END latin1 latin1_swedish_ci latin1_swedish_ci
159
"Should no privilege record for upgrade_alter_func in mysql.procs_priv"
160
SELECT * FROM mysql.procs_priv;
161
Host Db User Routine_name Routine_type Grantor Proc_priv Timestamp
162
SELECT upgrade_alter_func();
163
ERROR HY000: The user specified as a definer ('create_rout_db'@'localhost') does not exist
165
DROP FUNCTION upgrade_del_func;
166
DROP FUNCTION upgrade_alter_func;
167
DROP DATABASE bug42217_db;
168
DROP USER 'create_rout_db'@'localhost';