ALL PRIVILEGES except one table in MySQL

I want to grant all privileges to a user.

For this I do:

GRANT ALL PRIVILEGES TO 'carl'@'%';

Now, I want to revoke the privileges for a specified table from user carl. Does I have now to revoke the ALL PRIVILEGES and GRANT each table seperatly or is there another way to grant ALL PRIVILEGES and REVOKE the special one?

Thanks for your Help.

Thomas

Solution

or is there another way to grant ALL PRIVILEGES and REVOKE the special one?

NO, there is not any shortcut to do this. You will have to revoke ALL PRIVILEGE from user and then GRANT for each table separately.

REVOKE ALL PRIVILEGES ON db.* FROM 'carl'@'%';

Though there is option for automating this task of GRANTING all tables separately using scripting like:

Pseudo code

1. You can list out all tables using `SHOW TABLES;`

2. for each item is on table list

3. if(item != "specific table name") then
   GRANT table privilege to user