Description:
Consider the following test case:
# Includes
-- source include/have_binlog_format_row.inc
-- source include/not_embedded.inc
-- source include/master-slave.inc
# Begin clean up test section
connection master;
--disable_warnings
DROP PROCEDURE IF EXISTS test.p1;
DROP PROCEDURE IF EXISTS test.p2;
DROP PROCEDURE IF EXISTS test.p3;
# Begin test section 1
# Create user user1 with no particular access rights
grant usage on *.* to user1@localhost;
flush privileges;
SELECT CURRENT_USER();
SELECT USER();
CREATE PROCEDURE test.p1 () SQL SECURITY INVOKER SELECT CURRENT_USER(), USER();
CREATE PROCEDURE test.p2 () SQL SECURITY DEFINER CALL test.p1();
CREATE PROCEDURE test.p3 () SQL SECURITY INVOKER CALL test.p1();
GRANT EXECUTE ON PROCEDURE p1 TO user1@localhost;
GRANT EXECUTE ON PROCEDURE p2 TO user1@localhost;
GRANT EXECUTE ON PROCEDURE p3 TO user1@localhost;
let $message=<******** Master user1 p3 & p2 calls *******>;
--source include/show_msg.inc
connect (muser1,localhost,user1,,);
connection muser1;
SELECT CURRENT_USER();
SELECT USER();
CALL test.p3();
CALL test.p2();
let $message=<******** Slave user1 p3 & p2 calls *******>;
--source include/show_msg.inc
connect (suser1,127.0.0.1,user1,,test,$SLAVE_MYPORT,);
connection suser1;
SELECT CURRENT_USER();
SELECT USER();
sleep 3;
#CALL test.p3(); <--- Commented out due to failing in RBR, pass in SBR
#CALL test.p2(); <--- Commented out due to failing in RBR, pass in SBR
connection master;
#show binlog events;
# lets cleanup
DROP PROCEDURE IF EXISTS test.p1;
DROP PROCEDURE IF EXISTS test.p3;
DROP PROCEDURE IF EXISTS test.p2;
********** RESULTS FROM STATEMENT BASED **********************************
<******** Master user1 p3 & p2 calls *******>
+ ----------------------------------------------
+ SELECT CURRENT_USER();
+ CURRENT_USER()
+ user1@localhost
+ SELECT USER();
+ USER()
+ user1@localhost
+ CALL test.p3();
+ CURRENT_USER() USER()
+ user1@localhost user1@localhost
+ CALL test.p2();
+ CURRENT_USER() USER()
+ root@localhost user1@localhost
+
+ <******** Slave user1 p3 & p2 calls *******>
+ ---------------------------------------------
+ SELECT CURRENT_USER();
+ CURRENT_USER()
+ user1@localhost
+ SELECT USER();
+ USER()
+ user1@localhost
+ CALL test.p3();
+ CURRENT_USER() USER()
+ user1@localhost user1@localhost
+ CALL test.p2();
+ CURRENT_USER() USER()
+ user1@localhost user1@localhost
--------------- BIN LOG -------------------------
+ Log_name Pos Event_type Server_id End_log_pos Info
+ master-bin.000001 4 Format_desc 1 102 Server ver: 5.0.12-beta-log, Binlog ver: 4
+ master-bin.000001 102 Query 1 202 use `test`; grant usage on *.* to user1@localhost
+ master-bin.000001 202 Query 1 281 use `test`; flush privileges
+ master-bin.000001 281 Query 1 422 use `test`; CREATE PROCEDURE test.p1 () SQL SECURITY INVOKER SELECT CURRENT_USER(), USER()
+ master-bin.000001 422 Query 1 548 use `test`; CREATE PROCEDURE test.p2 () SQL SECURITY DEFINER CALL test.p1()
+ master-bin.000001 548 Query 1 674 use `test`; CREATE PROCEDURE test.p3 () SQL SECURITY INVOKER CALL test.p1()
+ master-bin.000001 674 Query 1 793 use `test`; GRANT EXECUTE ON PROCEDURE p1 TO user1@localhost
+ master-bin.000001 793 Query 1 912 use `test`; GRANT EXECUTE ON PROCEDURE p2 TO user1@localhost
+ master-bin.000001 912 Query 1 1031 use `test`; GRANT EXECUTE ON PROCEDURE p3 TO user1@localhost
+ master-bin.000001 1031 Query 1 1116 use `test`; CALL test.p3()
+ master-bin.000001 1116 Query 1 1201 use `test`; CALL test.p2()
********** RESULTS FROM ROW BASED **********************************
Errors are (from /home/ndbdev/jmiller/mysql5.0/mysql-test/var/log/mysqltest-time) :
/home/ndbdev/jmiller/mysql5.0/client/.libs/lt-mysqltest: At line 53: query 'CALL test.p3()' failed: 1370: execute command denied to user ''@'localhost' for routine 'test.p3'
+ <******** Master user1 p3 & p2 calls *******>
+ ----------------------------------------------
+ SELECT CURRENT_USER();
+ CURRENT_USER()
+ user1@localhost
+ SELECT USER();
+ USER()
+ user1@localhost
+ CALL test.p3();
+ CURRENT_USER() USER()
+ user1@localhost user1@localhost
+ CALL test.p2();
+ CURRENT_USER() USER()
+ root@localhost user1@localhost
+
+ <******** Slave user1 p3 & p2 calls *******>
+ ---------------------------------------------
+ SELECT CURRENT_USER();
+ CURRENT_USER()
+ @localhost
+ SELECT USER();
+ USER()
--------------- BIN LOG -------------------------
+ Log_name Pos Event_type Server_id End_log_pos Info
+ master-bin.000001 4 Format_desc 1 102 Server ver: 5.0.12-beta-log, Binlog ver: 4
+ master-bin.000001 102 Query 1 181 use `test`; flush privileges
+ master-bin.000001 181 Query 1 322 use `test`; CREATE PROCEDURE test.p1 () SQL SECURITY INVOKER SELECT CURRENT_USER(), USER()
+ master-bin.000001 322 Query 1 448 use `test`; CREATE PROCEDURE test.p2 () SQL SECURITY DEFINER CALL test.p1()
+ master-bin.000001 448 Query 1 574 use `test`; CREATE PROCEDURE test.p3 () SQL SECURITY INVOKER CALL test.p1()
+ master-bin.000001 574 Query 1 659 use `test`; CALL test.p3()
+ master-bin.000001 659 Query 1 744 use `test`; CALL test.p2()
############### NOTE ###############
I assume that this would have issues with all security, not just stored procedures. The grant calls should be passed to slave replicating that database from the master.
How to repeat:
see above
