MySQL Bugs: #42217: mysql.procs_priv does not get replicated

Bug #42217	mysql.procs_priv does not get replicated
Submitted:	20 Jan 2009 13:41	Modified:	29 Apr 2009 10:41
Reporter:	Vemund Østgaard	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0, 5.1, 6.0 bzr	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any

Description:
It seems that whatever entries go into the mysql.procs_priv table does not get replicated to the slave. The consequence is that any privileges stored there are not valid on the slave, ie. a user can execute his procedure on the master but not on the slave.

The problem was seen both with statement based and row based replication.

How to repeat:
1. Create a test user 
2. Grant CREATE ROUTINE privilege to the test user
3. connect as the test user
4. Create a stored procedure

This should give an entry in the mysql.procs_priv table that is not replicated to the slave.

Thank you for the report.

Please provide your configuration files for both master and slave.

MTR is used to start the servers, with the following commandline: 

/usr/bin/perl ./mysql-test-run.pl --master_port=9306 --slave_port=9308 --vardir=/export/home/tmp/systest/var/master --mysqld=--innodb_data_file_path=ibdata1:50M:autoextend --mysqld=--max_allowed_packet=256M --user=root --skip-ndb --mysqld=--innodb --mysqld=--default-character-set=utf8 --mysqld=--concurrent_insert=0 --testcase-timeout=10 --suite-timeout=20 --start-and-exit --mysqld=--innodb rpl_alter

After that two tests are executed against the master: 
start-replication and initial00

I will attach these two tests to the report. Note however that it is not necessary to use these tests to reproduce the problem, the manual steps indicated in the "How to repeat" field of the original report should be sufficient.

If using the tests to reproduce the problem you should see that the procs_priv table on the master has 6 rows while the same table on the slave is empty. The expected result would be to see identical contents on both master and slave.

first test to run

Attachment: replication-start.test (application/octet-stream, text), 391 bytes.

Second test to run

Attachment: initial00.test (application/octet-stream, text), 90.09 KiB.

result file for first test

Attachment: replication-start.result (application/octet-stream, text), 52 bytes.

Result file for second test

Attachment: initial00.result (application/octet-stream, text), 86.89 KiB.

Thank you for the feedback.

Verified as described.

Easier test case:

--source include/master-slave.inc

DROP DATABASE IF EXISTS privdb;
CREATE DATABASE  privdb;

use privdb;

--disable_warnings
DROP TABLE IF EXISTS t1, t2, already_there, drop_table, delete_table;
DROP VIEW IF EXISTS v1, v2, v3, v4, v5, v6, v7, v8;
--enable_warnings

GRANT CREATE ROUTINE ON privdb.* TO 'create_rout_db'@'localhost'
        IDENTIFIED BY 'create_rout_db' WITH GRANT OPTION;
		
connect (create_rout_db, localhost, create_rout_db, create_rout_db, privdb);
connection create_rout_db;

use privdb;

DELIMITER //;
CREATE FUNCTION upgrade_del_func() RETURNS CHAR(20)
BEGIN
   RETURN "INSIDE upgrade_del_func()";
END//

CREATE FUNCTION upgrade_alter_func() RETURNS CHAR(20)
BEGIN
   RETURN "INSIDE upgrade_alter_func()";
END//

CREATE PROCEDURE downgrade_del_proc() SQL SECURITY INVOKER
BEGIN
   SELECT c1, English, French FROM
          t1 JOIN t2 ON t1.c3 = t2.col2;
END//

CREATE PROCEDURE downgrade_alter_proc() SQL SECURITY DEFINER
BEGIN
   SELECT c1, English, French FROM
          t1 JOIN t2 ON t1.c3 = t2.col2;
END//

CREATE PROCEDURE alter_proc() SQL SECURITY DEFINER 
BEGIN
   SELECT "Inside alter_proc()";
END//
DELIMITER ;//

connection master;

select * from mysql.procs_priv;

select upgrade_del_func();

sleep 1;

connection slave;

select * from mysql.procs_priv;
show grants for 'create_rout_db'@'localhost';

use privdb;

show create function upgrade_del_func;

select upgrade_del_func();

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67854

2827 Guangbao Ni	2009-02-27
      Bug #42217  	mysql.procs_priv does not get replicated
      
      
      mysql.procs_priv does not get replicated.Yet mysql.procs_priv table operations don't have correspoding 
      statement replicated to slave. the operations for mysql.procs_priv is triggered by creating 
      function/procedure statements according to current user's privileges.
      Because the current user of SQL thread has GLOBAL_ACL, so needn't check mysql.procs_priv privilege 
      when create routines. Corresponding GLOBAL_ACL privilege doesn't need to insert routine privilege 
      into mysql.procs_priv.
      
      Fxied by switch current user of SQL thread to definer user (if the definer user existes on slave,
       or current user of SQL thread is still used) when checking whether the routine privilege is needed
      to insert mysql.procs_priv table or not.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68172

2827 Guangbao Ni	2009-03-04
      Bug #42217      mysql.procs_priv does not get replicated
      
      mysql.procs_priv table itself does not get replicated.
      Inserting routine privilege record into mysql.procs_priv table
      is triggered by creating function/procedure statements
      according to current user's privileges.
      Because the current user of SQL thread has GLOBAL_ACL,
      which doesn't need any check mysql.procs_priv privilege
      when create/alter/execute routines.
      Corresponding GLOBAL_ACL privilege user
      doesn't insert routine privilege record into
      mysql.procs_priv when creating a routine.
      
      Fixed by switching the current user of SQL thread to definer user if
      the definer user exists on slave.
      That populates procs_priv, otherwise to keep the SQL thread
      user and procs_priv remains unchanged.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68298

2827 Guangbao Ni	2009-03-05
      Bug #42217      mysql.procs_priv does not get replicated
      
      mysql.procs_priv table itself does not get replicated.
      Inserting routine privilege record into mysql.procs_priv table
      is triggered by creating function/procedure statements
      according to current user's privileges.
      Because the current user of SQL thread has GLOBAL_ACL,
      which doesn't need any check mysql.procs_priv privilege
      when create/alter/execute routines.
      Corresponding GLOBAL_ACL privilege user
      doesn't insert routine privilege record into
      mysql.procs_priv when creating a routine.
      
      Fixed by switching the current user of SQL thread to definer user if
      the definer user exists on slave.
      That populates procs_priv, otherwise to keep the SQL thread
      user and procs_priv remains unchanged.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68442

2827 Guangbao Ni	2009-03-06
      Bug #42217      mysql.procs_priv does not get replicated
      
      mysql.procs_priv table itself does not get replicated.
      Inserting routine privilege record into mysql.procs_priv table
      is triggered by creating function/procedure statements
      according to current user's privileges.
      Because the current user of SQL thread has GLOBAL_ACL,
      which doesn't need any check mysql.procs_priv privilege
      when create/alter/execute routines.
      Corresponding GLOBAL_ACL privilege user
      doesn't insert routine privilege record into
      mysql.procs_priv when creating a routine.
      
      Fixed by switching the current user of SQL thread to definer user if
      the definer user exists on slave.
      That populates procs_priv, otherwise to keep the SQL thread
      user and procs_priv remains unchanged.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/69525

2839 Guangbao Ni	2009-03-18
      Bug #42217      mysql.procs_priv does not get replicated
      
      mysql.procs_priv table itself does not get replicated.
      Inserting routine privilege record into mysql.procs_priv table
      is triggered by creating function/procedure statements
      according to current user's privileges.
      Because the current user of SQL thread has GLOBAL_ACL,
      which doesn't need any check mysql.procs_priv privilege
      when create/alter/execute routines.
      Corresponding GLOBAL_ACL privilege user
      doesn't insert routine privilege record into
      mysql.procs_priv when creating a routine.
      
      Fixed by switching the current user of SQL thread to definer user if
      the definer user exists on slave.
      That populates procs_priv, otherwise to keep the SQL thread
      user and procs_priv remains unchanged.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/69527

3125 Guangbao Ni	2009-03-18 [merge]
      Auto-merge bug#42217 from 5.1-bugteam to 6.0-bugteam

merged into mysql-5.1-bugteam and mysql-6.0-bugteam

Pushed into 5.1.34 (revid:joro@sun.com-20090327143448-wuuuycetc562ty6o) (version source revid:leonard@mysql.com-20090316090622-sr8lylqvsl1jrcnv) (merge vers: 5.1.34) (pib:6)

Documented bugfix in the 5.1.34 changelog as follows:

        The mysql.procs_priv system table was not replicated.

Set to NDI pending merges to 5.0 and 6.0 trees.

Pushed into 6.0.11-alpha (revid:alik@sun.com-20090413084402-snnrocwzktcl88ny) (version source revid:gni@mysql.com-20090318141914-pi3cdhskptadkwem) (merge vers: 6.0.11-alpha) (pib:6)

Bugfix now documented in the 6.0.11 changelog.

Will this be fixed in 5.0 tree? If not, please indicate with a comment and then close this bug report.

Thanks!

We are not planning to push this to 5.0 at this time.

If there are customer support issues opened on this, 
we will consider backporting it.

Closed per comment from Lars

Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (merge vers: 5.1.34-ndb-6.2.18) (pib:6)

Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (merge vers: 5.1.34-ndb-6.3.25) (pib:6)

Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (merge vers: 5.1.34-ndb-7.0.6) (pib:6)