Wednesday, January 25, 2006

Is this a bug?

I was wondering if it would work to create a user, grant him all privileges on all databases (so make him a superuser) and then revoke all permissions from one particular database.

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

5 comments:

Anonymous said...

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.

Markus Popp said...

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 ;-).

Roland Bouman said...

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.

Sheeri said...

Well, if you create a new database, then the user would automatically be given grants. This is a dangerous feature.

I do think that there might be an exception to this: something like "grant all on *.* to user@foo.com except system tables", where a system table is defined as the information schema and/or anything in the mysql database.

But if you have reason to exclude a person from one database on the system, you might want to exclude them from another. Let's say you have developers that should be locked out of, say, the billing database, but need to have access to the users database and all the others. Then you create some other database, maybe an archival reporting database that has old billing info. You still want to lock folks out of it. . .

So it's kind of a good idea NOT to have that. As another commenter said, either you want wildcard, which means everything, or you do not.

However, I bet you could write a UDF or trigger of some kind that, whenever a database is created, a few grant statements are called.

Markus Popp said...

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