MySQL Bugs: #53613: mysql_upgrade incorrectly revokes TRIGGER privilege on given table

Bug #53613	mysql_upgrade incorrectly revokes TRIGGER privilege on given table
Submitted:	13 May 2010 1:43	Modified:	14 Oct 2010 15:20
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.1.34>5.1.38/.40 and 5.1.34>5.1.46	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	regression?

Description:
Create a grant like this in 5.1.34:

| Grants for user3@%
+--------------------
| GRANT USAGE ON *.* TO 'user3'@'%'
| GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%'

Upgrade to 5.1.38/.40, run mysql_upgrade, observer changed privilege:

| Grants for user3@%
+--------------------
| GRANT USAGE ON *.* TO 'user3'@'%'
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW ON `roelt`.`test2` TO 'user3'@'%'

How to repeat:
As per the above.

Suggested fix:
Do not "TRIGGER" privilege.

s/Do not "TRIGGER" privilege./Do not revoke/remove "TRIGGER" privilege./

Verified on 5.1.34 > 5.1.46 upgrade - simply copy /data/mysql/ over and run mysql_upgrade, then check grant: TRIGGER is revoked.

Easy workaround:

mysql> GRANT TRIGGER ON `roelt`.`test2` TO 'user3'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for user3;
+--------------------------------------------------------+
| Grants for user3@%                                     |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user3'@'%'                      |
| GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

But, this workaround does not work if the table does not exist (though the same statement works fine in older versions...):

mysql> GRANT TRIGGER ON `roelt`.`test2` TO 'user3'@'%';
ERROR 1146 (42S02): Table 'roelt.test2' doesn't exist

The problem is inside mysql_fix_privilege_tables.sql : 

if does the following with mysql.tables_priv:

1. CREATE TABLE IF NOT EXISTS tables_priv ( ... Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') COLLATE utf8_general_ci DEFAULT '' NOT NULL ...) ...;

2. ALTER TABLE tables_priv ... MODIFY Table_priv set('Select','Insert','Update','Delete','Create',                        'Drop','Grant','References','Index','Alter', 'Create View','Show view')

3. ALTER TABLE tables_priv MODIFY Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') COLLATE utf8_general_ci DEFAULT '' NOT NULL;

The first statement is OK : it doesn't do anything to already existing tables. However the second one is the culprit : it modifies the column definition to a set one too narrow. This trims off the Trigger bit. Now the third statement re-creates the correct definition, but since the data are already stripped off the Trigger part, the trigger privilege is lost.

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/112360

3448 Georgi Kodinov	2010-06-28
      Bug #53613: mysql_upgrade incorrectly revokes TRIGGER privilege on given table
      
      Fixed an incomplete historical ALTER TABLE MODIFY trimming the trigger 
      privilege bit from mysql.tables_priv.Table_priv column.
      Test suite added.

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/112653

3448 Georgi Kodinov	2010-07-01
      Bug #53613: mysql_upgrade incorrectly revokes TRIGGER privilege on given table
      
      Fixed an incomplete historical ALTER TABLE MODIFY trimming the trigger 
      privilege bit from mysql.tables_priv.Table_priv column.
      Removed the duplicate ALTER TABLE MODIFY.
      Test suite added.

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/112873

3464 Georgi Kodinov	2010-07-01
      Bug #53613: mysql_upgrade incorrectly revokes TRIGGER privilege on given table
      
      Fixed an incomplete historical ALTER TABLE MODIFY trimming the trigger 
      privilege bit from mysql.tables_priv.Table_priv column.
      Removed the duplicate ALTER TABLE MODIFY.
      Test suite added.

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/113711

3126 Davi Arnaut	2010-07-15
      Bug#53613: mysql_upgrade incorrectly revokes ...
      
      Post-merge fix: adjust line numbers in pfs_upgrade test case
      result given that mysql_system_tables_fix.sql was modified.

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)

Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)

Noted in 5.1.50, 5.5.6 changelogs.

mysql_upgrade could incorrectly remove TRIGGER privileges.

Pushed into mysql-5.1 5.1.51 (revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (version source revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (merge vers: 5.1.51) (pib:20)

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)

Already documented in the 5.1.50 changelog; no new changelog entries required. setting back to Closed state.