Bug #64532 columns_priv record not clear or modify when table defination changed
Submitted: 3 Mar 2012 9:42 Modified: 3 Mar 2012 10:44
Reporter: lou shuai (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S2 (Serious)
Version:5.5.20 OS:Any
Assigned to: CPU Architecture:Any
Tags: columns_priv, table defination

[3 Mar 2012 9:42] lou shuai
Description:
when alter a table ,change a column ,the privilege in columns_priv not change or clear

How to repeat:
create database test;
use test;

mysql> create table t1(c1 int, c2 int, c3 int);
Query OK, 0 rows affected (0.23 sec)

create user u1 identified by 'aaaaaa';

grant select(c2) on t1 to u1;

mysql> SELECT * FROM MYSQL.TABLES_PRIV;
+------+------+------+------------+----------------+---------------------+------------+-------------+
| Host | Db   | User | Table_name | Grantor        | Timestamp           | Table_priv | Column_priv |
+------+------+------+------------+----------------+---------------------+------------+-------------+
| %    | test | U1   | t1         | root@localhost | 2012-03-03 17:31:55 | Select     |             |
+------+------+------+------------+----------------+---------------------+------------+-------------+
1 row in set (0.03 sec)

alter table t1 change c2 cc2 int;

mysql> SELECT * FROM MYSQL.COLUMNS_PRIV;
+------+------+------+------------+-------------+---------------------+-------------+
| Host | Db   | User | Table_name | Column_name | Timestamp           | Column_priv |
+------+------+------+------------+-------------+---------------------+-------------+
| %    | test | U1   | t1         | C2          | 2012-03-03 17:33:05 | Select      |
+------+------+------+------------+-------------+---------------------+-------------+
1 row in set (0.00 sec)

Suggested fix:
clear the record which grant c2 on t1 to u1
OR
update the record
[3 Mar 2012 10:44] Valeriy Kravchuk
This is easy to verify and even somewhat documented (for table rename case). See http://dev.mysql.com/doc/refman/5.5/en/alter-table.html:

"Any privileges granted specifically for the renamed table are not migrated to the new name. They must be changed manually."

But if the same is true (and by design) for renaming columns, then we have a valid documentation request here (in addition to valid FR to modify privileges properly).