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.

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

Solution 2

AFAIK, yes, you need to grant individually per table. But hey, you have a computer there. Computers are great at automating repetitive tasks for you, so why don't you make a script that does the following:

1. Get a list of all tables in the database (SHOW TABLES;)
2. For each item on the list, grant all permissions
3. Revoke permissions on the special table

Or, alternatively: 2. For each item on the list, check if it is the special table; if it's not, grant all permissions

The reason I'm not giving code is that it can be done in any scripting language with MySQL facilities, even shell script; use what you're most comfortable using.

Comments

[Steven T. Snyder]

While this solution works right now, if you ever add tables to the database you will have to add permissions for each user for the new tables. There doesn't seem to be a way to just add permissions on * but have anti-permissions on a specific table.

[Para]

Is there really no way to do this except this way? It doesn't seem right. I'd except better from mysql. I need to do this as well.

[Stuart Cardall]

to use wildcard exceptions instead of using AND table_name <> use AND table_name NOT LIKE "%_something_%"

[Bowi]

This is really handy. :)

[Charlie Reitzel]

Using roles will avoid the "new user" problem. Assign detailed, table level permissions to appropriate roles. Grant and revoke roles to/from users. Now the table level grants need to be updated only in the event of schema changes (when you want to revisit permissions anyway).

[Mark E]

Almost 11 years after the question was asked and this still works like a charm.

[user193130]

Now it's exactly 11 years after the question was asked and this still works like a charm.