ALL PRIVILEGES except one table in MySQL

Question

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

you should revoke the `ALL` privilege and grand privileges for what you need. — bansi, Jun 13 '14 at 11:44

score 1 · Accepted Answer · edited May 23 '17 at 10:33

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

See this similar post MySQL grant all privileges to database except one table

score 0 · Answer 2 · answered Jun 13 '14 at 11:45

to revoke, use this :

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
FROM user [, user] ...

or

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

EDIT : Don't forgot to flush privileges :

FLUSH PRIVILEGES;

ALL PRIVILEGES except one table in MySQL

2 Answers2

Linked