PLEASE HELP on granting permissions

[sfullman]

Probably very simple.

I want to grant a user full permissions on every database in mysql. hOwever, I then want to restrict his access on one database only. The commands I've been using are:

1. grant all on . to joe identified by 'blow'
2. revoke all on administrative.* from joe
3. grant select, update on administrative.* to joe identified by 'blow'

However, I never get past the first statement, it says:

"There is no such grant defined for user 'joe' on host '%'"

Anyone have a better approach on this, or a way to do it at all?

[[deleted]]

Try adding the hostname to the grant statement, it is complaining that it can't revoke for host '%', which is probably because you didn't grant for host '%'.

Have a look in the mysql manual.

[sfullman]

I tried that and it doesn't seem to work.

Basically, I want the user to have access to every database, and every new database created. However, I don't want them to have access to the mysql database.

Any series of commands that works?
Sam

Originally posted by vincente
Try adding the hostname to the grant statement, it is complaining that it can't revoke for host '%', which is probably because you didn't grant for host '%'.

Have a look in the mysql manual.

[[deleted]]

That's not how it works.

The rights are entered as seperate records in a rights-table. Every row in the users table in the 'mysql' database acts as a single right.

These records can only give access, they cannot take it away. When a right is revoked, the record that granted that right is deleted. It is not possible to enter a record that denies access. Access is always denied unless it is specifically granted by a record in the rights table.

In your case you granted the right to ., but now you want to revoke the right to a single database. This cannot be done because there is no record that specifically gives rights to that database.

The user has rights to ., which includes the database you want to revoke the rights for, but there is no specific right to just that single database, so you cannot revoke it.

The only way around it is to grant permissions on just the databases you want to give access to. If you do not grant permission to the 'mysql' database, the user will not have access to it.

[sfullman]

Which is a fancy way of saying it can't be done :-). I did study the rights tables and it appears if you're correct. I can't believe the mysql people set up such a complicated schema as the rights tables in the mysql database without thinking something like this would come up.

So, basically, I just have to give a user ACCESS TO every new database that is created.

Thanks,
Sam

[[deleted]]

" I can't believe the mysql people set up such a complicated schema as the rights tables in the mysql database without thinking something like this would come up."

It could do with a few improvements, but then again, how often do you give a person all rights to all databases? In a 'normal' setup you'd always give a person very limited rights to just one or two databases, and any new databases are off-limits by default.

So it's not that bad...