Revoke only delete from user with all privileges on MySQL

Question

I have a user with all privileges for a specific DB in MySQL 8:

GRANT ALL PRIVILEGES ON `mydatabase`.* TO `foo`@`localhost`

I can check the grants with SHOW GRANTS FOR 'foo'@'localhost'; and I get:

+-------------------------------------------------------------+
| Grants for foo@localhost                                    |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `foo`@`localhost`                     |
| GRANT ALL PRIVILEGES ON `mydatabase`.* TO `foo`@`localhost` |
+-------------------------------------------------------------+

Now I need to remove the DELETE grant on a specific table, so I've tried with:

REVOKE DELETE ON `mydatabase`.`mytable` FROM 'foo'@'localhost';

but I get the following error:

ERROR 1147 (42000): There is no such grant defined for user 'foo' on host 'localhost' on table 'mytable'

How can I remove the delete grant? I have to add all grants one by one (which ones are they?) and then remove the delete grant?

Answer 1

GRANT adds according row into privileges table.

REVOKE deletes the row with specified privilege from this table, not add another row with removing the privilege. So you can revoke only those privilege which is present in a table. Precisely.

You may:

1. Add separate privileges list with all privileges included into ALL PRIVILEGES except DELETE privilege on the database level
2. Add DELETE privilege on all tables except mytable
3. Remove ALL PRIVILEGES privilege

This is too complex. But correct.

---

Alternatively you may simplify the solution, do not use privileges system (of course this is not good practice), and forbid the deletion on the programming level using according trigger:

CREATE TRIGGER forbid_delete_for_user
BEFORE DELETE 
ON mytable
FOR EACH ROW
BEGIN
    IF LOCATE(USER(), 'foo@localhost,bar@localhost') THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Deletion from 'mytable' not allowed for current user.';
    END IF;
END

But you must remember that cascaded foreign key actions do not activate triggers. So the user can find the way for to delete the rows from this table nevertheless.