I knew that this would be quite a complicated process inside the mysql privilege database. All the global privileges would have to be set to 'N' and MySQL would have to insert a new record for each existing database (except the one where the privileges are revoked) to the db table to grant the privileges at database level instead of globally. So I tried it out:
mysql> CREATE USER testuser@localhost
-> IDENTIFIED BY 'abc123';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL ON *.*
-> TO testuser@localhost
-> WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> REVOKE ALL ON test.*
-> FROM testuser@localhost;
ERROR 1141 (42000): There is no such grant defined for
user 'testuser' on host 'localhost'
It's not that big surprise that it doesn't work, but what I would be interested in: should it work (according to the SQL standards)?
4 comments:
This was something I looked into a while ago, but didn't get very far.
People generally didn't seem to grasp the idea of what I was trying to achieve or said I was being stupid and that you should assign it on a database-by-database basis instead of a global-with-exceptions basis.
I will be interested in the answer. Definitely.
To be honest - it was just a spontanous idea from me to look if that works. But indeed, it's an interesting question. I also don't know how other RDBMS handle this. So I thought, this could be a good question for my blog ;-).
This is not a bug! At least, not according to what I've read in MySQL Pro (Mike Kruckenberg & Jay Pipes).
The idea is that a wildcard grant is just that - a wildcard grant. You never explicitly granted it for the one database, so there is nothing to be revoked.
I don't think Oracle's got wildcard grants, so there you are always dealing with a grant on a specific object. So there, there's alwasy a 1 to 1 relationship between the REVOKE and a previously bestowed GRANT.
Fortunately, this is no current problem for me (just a spontanous idea that I wanted to try out) ;-). Generally, I would only grant all privileges to users who definetly are administrators and so should be permitted to access every database. For all kinds of other users, permissions should of course only be set at database level (or below).
Post a Comment