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 (OCA) 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
Tags: regression?
Triage: Triaged: D2 (Serious)

[13 May 2010 1:43] Roel Van de Paar
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.
[13 May 2010 2:02] Roel Van de Paar
s/Do not "TRIGGER" privilege./Do not revoke/remove "TRIGGER" privilege./
[13 May 2010 3:26] Roel Van de Paar
Verified on 5.1.34 > 5.1.46 upgrade - simply copy /data/mysql/ over and run mysql_upgrade, then check grant: TRIGGER is revoked.
[13 May 2010 3:30] Roel Van de Paar
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
[28 Jun 2010 13:42] Georgi Kodinov
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.
[28 Jun 2010 15:04] Bugs System
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.
[1 Jul 2010 9:05] Bugs System
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.
[5 Jul 2010 10:08] Bugs System
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.
[15 Jul 2010 22:30] Bugs System
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.
[23 Jul 2010 12:26] Bugs System
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)
[23 Jul 2010 12:33] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[31 Jul 2010 16:44] Paul Dubois
Noted in 5.1.50, 5.5.6 changelogs.

mysql_upgrade could incorrectly remove TRIGGER privileges.
[19 Aug 2010 15:40] Bugs System
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)
[14 Oct 2010 8:37] Bugs System
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)
[14 Oct 2010 8:52] Bugs System
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)
[14 Oct 2010 9:08] Bugs System
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)
[14 Oct 2010 15:20] Jon Stephens
Already documented in the 5.1.50 changelog; no new changelog entries required. setting back to Closed state.