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).

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.