MySQL Bugs: #20589: Missing some table level privileges after upgrade

Bug #20589	Missing some table level privileges after upgrade
Submitted:	20 Jun 2006 23:51	Modified:	13 Dec 2006 19:23
Reporter:	Erica Moss	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S2 (Serious)
Version:	5.0.23	OS:	Linux (Fedora core 5)
Assigned to:	Magnus Blåudd	CPU Architecture:	Any

Description:
While working on a suite of upgrade/downgrade tests this problem was discovered:

The below SQL was run,
The client was stopped, and then started again to source the upgrade script mysql_fix_privilege_tables.sql
At this point both of the earlier granted table level privs are gone, and the user is only left with GRANT USAGE.

It appears from looking at the mysql_fix_privilege_tables.sql script that in several places, ALTER TABLE is run against the tables_priv table and the SET() clause contains an incomplete list of possible table level privs.  The two GRANTS in the below code are missing from that list.

Given that this is not the method of table upgrade that is recommended for unix users, for completeness I verified that the same thing happens if you run the shell script instead of directly sourcing the SQL file.

Unless the session is in 'traditional' mode, then any records which contain the missing values are set to NULL.  If the session is set to a strict mode then the statement will throw an error and this damage is done.

However we do state in a number of places that it is safe to execute this script at any time even if you've already upgrade so this should be resolved.

Additionally, in a true upgrade scenario it the script would fail to add these two legal table level GRANTS to the table definition.

Also the comments at the top of the script should be updated as they currently state:
-- This script converts any old privilege tables to privilege tables suitable
-- for MySQL 4.0.

How to repeat:
connect (root, localhost, root,,);
DROP DATABASE IF EXISTS privDB;
CREATE DATABASE privDB;
CREATE TABLE privDB.t1 (c1 INT,
                 English CHAR(10),
                 c3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE VIEW privDB.v1 AS
        SELECT * FROM privDB.t1;

GRANT CREATE VIEW, SHOW VIEW ON privDB.v1 TO 'show_view_tbl'@'localhost'
           IDENTIFIED BY 'show_view_tbl';
SHOW GRANTS FOR 'show_view_tbl'@'localhost';
SELECT user,db,Table_name, Table_priv, Column_priv FROM mysql.tables_priv;
SELECT * FROM information_schema.table_privileges;
SELECT user FROM mysql.user;
disconnect root;

# stop server
# run the table upgrade script
$ mysql --socket="$VAR_DIR"/tmp/master.sock --port=$PORT \
           --force --verbose --user=root mysql \
           < "$SCRIPTS_DIR_NEW"/mysql_fix_privilege_tables.sql \
           > "$UPDOWN_DIR"/r/up_fixpriv_run.out 2>&1

Suggested fix:
Please resolve the SET () list wherever necessary,
Please change the comments

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

ChangeSet@1.2557, 2006-11-22 09:52:31+01:00, msvensson@neptunus.(none) +1 -0
  Bug #20589 Missing some table level privileges after upgrade
  - Update comments to reflect correct version

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

ChangeSet@1.2330, 2006-11-23 18:43:12+01:00, msvensson@neptunus.(none) +3 -0
  Bug#20589 Missing some table level privileges after upgrade
   - The table_priv column of table_privs table was altered to a enum type
     with fewer enums causing the SHOW/CREATE VIEW grants to be truncated.
   - Improved comments and moved all declarations for table_privs, column_privs
     and proc_privs to one section for each table making it easy to see hat alterations
     are performed on each table

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

ChangeSet@1.2330, 2006-11-29 12:04:29+01:00, msvensson@neptunus.(none) +4 -0
  Bug#20589 Missing some table level privileges after upgrade
   - The table_priv column of table_privs table was altered to a enum type
     with fewer enums causing the SHOW/CREATE VIEW grants to be truncated.
   - Improved comments and moved all declarations for table_privs, column_privs
     and proc_privs to one section for each table making it easy to see hat alterations
     are performed on each table
   - Reduced the number of ALTER's slightly, but as this is an upgrade script we need
     to take all possibilites into account.

Noted in 5.0.32, 5.1.15 changelogs.

mysql_fix_privilege_tables.sql altered the table_privs.table_priv
column to contain too few privileges, causing loss of the CREATE VIEW
and SHOW VIEW privileges.