MySQL grant function not working properly MySQL WorkBench 8.0

Question

I defined grants for the user John :

grant select on * to JOHN@localhost;

and upon running:

show grants for JOHN@localhost;

I get :

GRANT SELECT ON *.* TO `JOHN`@`localhost`

But when I try to remove the grant from a specific table, actor:

revoke select on sakila.actor from JOHN@localhost;

it says:

Error Code: 1147. There is no such grant defined for user 'JOHN' on host 'localhost' on table 'actor'

What could have gone wrong?

I tried to give select grants separately on sakila.* but still it won't identify that John has grants over actor table. Only when I explicitly grant select to JOHN for sakila.actor, it identifies it.

Answer 1

Because, there is no specific grant given to the user JOHN@localhost on the table sakila.actor. The permission given is on * - i.e. all DBs, all tables.

Think about what this REVOKE will have to do, if it is supported.

It will have to subtract sakila.actor from *.*. That means, MySQL will have to add ALL the different DB.* grants for that user, and then for all the tables of sakila, except actor.

That MySQL cannot do.

MySQL will only revoke the permissions that are explicitly given.

If the permissions are given on *.* only those permissions can be revoked.

If you give explicit permission on sakila.actor, then only that can be revoked. If you give explicit permission on sakila.*, then only that can be revoked, and so on...

If you give permission on sakila.* or *.* and then try to revoke permissions from only a sub-set of those, MySQL won't do that, as if it does, it would have to actually assume permissions on everything else.