If:
1. A table t1 is created in database test;
2. A login is granted select permission on t1;
3. t1 is dropped and then recreated.
Then that login would still be able to read the newly recreated table t1, even if t1 has totally different columns. The reason is that the table select privilege is stored in tables_priv in mysql database, and when t1 is dropped, that privilege will not be cleared in tables_priv.
Personally, I think this needs to be changed. In other words, when a drop table command is issued, not only the table needs to be dropped, this command should also go to tables_priv in mysql to remove that select permission. Not too sure how big a task that is, perhaps I should try to see if I can implement it myself, after I get comfortable with the source code and development process of MySQL.
5 responses to “Dropping a table does not remove permissions granted to it in MySQL”
The issue fades to insignificance when compared to this:
“When a client attempts to connect, the server looks through the sorted rows and uses the first match found. For a connection from localhost by jeffrey, two of the rows from the table match: the one with Host and User values of ‘localhost’ and ”, and the one with values of ‘%’ and ‘jeffrey’. The ‘localhost’ row appears first in sorted order, so that is the one the server uses.”
http://dev.mysql.com/doc/refman/5.1/en/connection-access.html
There are quite a few edge cases I don’t think you’ve realized as well. What if I dropped the table (on accident or purpose) and needed to re-create it? Or what if I am importing data from a dump-file (which usually includes DROP TABLE statements, and for good reason)?
So I was thinking about this a bit and the thought occurred to me – you might be able to get this to work by simply modifying the DROP DATABASE syntax. So, say, DROP DATABASE by itself would not remove the users associated with that database. But, perhaps something like “DROP DATABASE PURGE USERS” or something might work.
That would actually be pretty nice since it doesn’t mess with existing behavior but still allows some nice functionality.
Just some thoughts!
Tim S.
Tim S.,
I like that, it’s clever. Though I’m not much of a fan of deviating from the SQL standard (more than all RDBMSs do already).
How about an extension to the user account management syntax? PURGE ACCOUNTS WITHOUT VALID GRANTS or some such?
Hi Don and Tim,
Thanks for the great comments.
@Don, thanks for the link. Interestingly, I think I encountered a bug of MySQL 5.1 Community on Windows, where if you do:
create user ‘haidong’@’%’ identified by ‘StrongPassword’;
grant select on test.t1 to ‘haidong’@’%’;
flush privileges
and haidong could not connect! I think I will need to dig further.
@Tim S.
The DROP DATABASE PURGE USERS is an intriguing idea. I still think that when something is dropped, everything that associated with that object should go, or dereferenced, for lack of better word. I know that it is a common practice to for pre-built script to drop and recreate tables, but I will submit part of that script should take care of permissions.
Been busy lately. I may come back to this when I get a moment to spare. Thanks again for the comments.