MySQL Bugs: #21725: Assigning privileges to columns does not work

Bug #21725	Assigning privileges to columns does not work
Submitted:	18 Aug 2006 17:10	Modified:	19 Oct 2006 14:14
Reporter:	Jasper Knockaert	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Administrator	Severity:	S3 (Non-critical)
Version:	1.1.11	OS:	MacOS (Mac OS X 10.4.7 (Intel))
Assigned to:	Vladimir Kolesnikov	CPU Architecture:	Any
Tags:	object privileges

Description:
Assigning privileges to a column of a table does result in an update of mysql.columns_priv setting the "Column_priv" field to the corresponding value. However, in order to work properly also the "Column_priv" field in mysql.tables_priv should be updated, which does not happen automatically.

How to repeat:
Go to the "Object privileges" tab of the "Accounts" section and assign a privilege (e.g. "Select") to column a of table b.
Next start mysql as root. Switch to the mysql scheme (use mysql) and browse the table columns_priv (select * from columns_priv;) where you will see that a row is added for column a of table b with the "Column_priv" field set to the privilege. Now browse table tables_priv (select * from tables_priv;) and you will notice that for table b the "Column_priv" field will not carry the corresponding privilege.

Hi,

The problem is that the privileges corresponding to values of table_privs.column_priv where simply missing in GUI. The privileges that you changed are actually those from column_privs table and they are changed correctly.  The missing privileges will be added to GUI.

The information contained in tables_priv.column_priv is somehow related to what is in columns_priv. It seems pointless to me not to update tables_priv.column_priv when changing columns_priv (unless the corresponding privilege was already assigned to another column of the same table), such an action would result in tables_priv.column_priv to be inconsistent with the information contained by columns_priv. Is there a reason not to update tables_priv.column_priv automatically to reflect changes to columns_priv?
Furthermore, I came accross an issue where the GUI didn't allow me to assign privileges to views, but maybe I should report this in a separate bug report.

Hi Jasper,

The reason not to update tables_priv when you update columns_priv is that tables_priv keep general lock for column-wise privs, while columns_priv control individual privileges. Implementing a kind of auto-action in the MySQL Administrator would introduce a limitation to the corresponding server functoinality.

About view privs - yes please file a separate report.

Thanks,
Vlad

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html