Bug #26421 RENAME TABLE doesn't update permissions for this table or view
Submitted: 15 Feb 2007 19:20 Modified: 21 Feb 2007 2:39
Reporter: Alex Rytov Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.1.15 OS:
Assigned to: Paul DuBois CPU Architecture:Any
Tags: permissions, permits, rename table, rights, VIEW

[15 Feb 2007 19:20] Alex Rytov

Renaming table or view doesn't change table name in mysql.tables_priv as it should and therefore, apparently, leaves behind all previously granted permissions for that table.  

This problem is similar (but not identical) to the one in Bug#17564 (RENAME DATABASE), but in that case the problem was documented in the mySQL manual as a 'temporary limitation'.  The article for RENAME TABLE doesn't mention that the command would leave behind any permissions.

How to repeat:
create view v(a,b) as select 1,2;

grant insert on v to `some_user`@`somewhere.com` identified by 'test';

rename table v to v_new;

select * from mysql.tables_priv;

(still shows 'v' in `TABLE_NAME`)
[16 Feb 2007 11:02] Sveta Smirnova
Thank you for the report.

But according to Paul DuBois comment to the Bug #17564 and "GRANT Syntax" manual page it is expected behaviour too.

Page http://dev.mysql.com/doc/refman/5.1/en/grant.html contains next text:

MySQL allows you to grant privileges even on database objects that do not exist. In such cases, the privileges to be granted must include the CREATE privilege. This behavior is by design, and is intended to enable the database administrator to prepare user accounts and privileges for database objects that are to be created at a later time.

If you think it is not enough, is possible to convert this report to documentation request.
[16 Feb 2007 14:15] Alex Rytov
> MySQL allows you to grant privileges even on database objects 
> that do not exist. In such cases, the privileges to be granted 
> must include the CREATE privilege.

In my opinion it is not the same situation. Intentionally granting access rights to objects not yet created is a feature that a DBA might or might not want to use. 

On the other hand, leaving behind permission records for dropped or renamed tables is something that just happens and I'm sure in most situations it would not be the desired or expected behaviour.

One exception may be when the table has to be dropped and then re-created (with the same name), then the current mechanism is perfect for preserving the permission during the process.  In all other situations it is either an inconvenience (lost permissions) or even a security threat (retaining permissions for dropped objects).

If it is considered a known limitation of the current version, it should at least be clearly documented in the "ALTER TABLE ... RENAME ..." and put into the todo list for some future version,  but that certainly doesn't look like a feature worth keeping.

What makes it worse is that there is no SQL syntax for revoking ALL permissions from ALL users for the specified object. One would actually have to scan MYSQL.TABLES_PRIV for the list of users having access to the dropped or renamed object and programmatically construct a REVOKE for each of them.

May be you should provide some way to control this behaviour (leaving behind permissions), either as a new SQL clause in DROP/RENAME  (e.g. DROP TABLE ... WITH REVOKE, RENAME TABLE ... to ... WITH GRANT) or as another bit in @@SQL_MODE.

I also wonder if it is possible/safe to manipulate MYSQL.TABLES_PRIV directly, e.g. like this:

  UPDATE mysql.tables_priv SET table_name = 'new_name' 
     WHERE table_name = 'old_name'
[19 Feb 2007 8:03] Sveta Smirnova
Thank you for the comment.

Verified as documentation bug.

And yes, you can update mysql.* tables directly.
[21 Feb 2007 2:39] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated the ALTER TABLE ... RENAME and RENAME TABLE sections
to note that privileges for the table are not migrated to the new