MySQL grant all privileges to database except one table

Question

I've been unable to find a reasonable solution to achieve the following:

I wish to have a user that has ALL privileges on a database (or series of databases with the same schema), except for one table, to which they will only have SELECT privileges.

Essentially I want the user to have free reign over a database but not to be able to update a specific table.

So far I have tried, to no avail:

• Granting all privileges on that database (db_name.*) and then specifically granting only select privileges on that desired table (hoping it would overwrite the "all", stupid I know).

• Granting all privileges on that database (db_name.*) then revoking insert, update, and delete. But this produced an error saying there was no grant rule for db_name.table_name.

From what I've been able to gather I'll have to individually grant all privileges on each table of the database except the read only table.

Please someone tell me there is a easier way

Note: I'm running MySQL 5.1. The latest available on Ubuntu 10.04.

Answer 1

I know this is an old post, but I thought I'd add on to @tdammers question for others to see. You can also perform a SELECT CONCAT on information_schema.tables to create your grant commands, and not have to write a separate script.

First revoke all privileges from that db:

REVOKE ALL PRIVILEGES ON db.* FROM user@localhost;  

Then create your GRANT statements:

SELECT CONCAT("GRANT UPDATE ON db.", table_name, " TO user@localhost;")
FROM information_schema.TABLES
WHERE table_schema = "YourDB" AND table_name <> "table_to_skip";

Copy and paste the results into your MySQL client and run them all.