MySQL Bugs: #26421: RENAME TABLE doesn't update permissions for this table or view

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:	None
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

Description:

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

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.

> 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'

Thank you for the comment.

Verified as documentation bug.

And yes, you can update mysql.* tables directly.

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