Is there a way to effectively GRANT on either TRUNCATE or DROP TABLE in MySQL?

Question

I recently tried this in MySQL 5.5.x:

GRANT
    SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON crawler.*
    TO 'my_user'@'localhost' WITH GRANT OPTION;

This results in an error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRUNCATE ON crawler.* TO 'my_user'@'localhost' WITH GRANT OPTION' at line 2

This used to work before I added TRUNCATE, so after a bit of research I find that this is not supported in MySQL.

The reason for this is that TRUNCATE is classified as a DDL operation, and so it doesn't use DELETE internally, it uses DROP. Alright, so I'd like to restrict this user to dropping tables (in the event of a security breach, at least a malicious user would have to determine the names of tables and drop them individually).

However, it turns out I would need to grant this user the DROP privilege, which allows the user to drop whole databases too. Given that there is not a grant for individual tables, is there another way to do this? I suppose I could hand this off to another process with a different user, but it feels a bit cumbersome for such a small issue.

For the time being, I'll stick with DELETE, even though it is rather slow! (On my laptop it takes ~55 sec to delete 1.6M small rows, and a fraction of a second to truncate the same). However, I am all ears if there is a faster and secure alternative.

Answer 1

To grant DROP privilege on a specific table in a specific database to a specific user in MySQL, you can use a GRANT statement like this. (This assumes that table fi exists in database fee, and this is the table you want to allow the user 'fo'@'%' to be able to TRUNCATE):

  GRANT DROP ON TABLE fee.fi TO 'fo'@'%'

To see that the user has privilege to truncate that specific table:

  SHOW GRANTS FOR 'fo'@'%' ;

And connect as user 'fo'@'%' to test:

  TRUNCATE TABLE fee.fi ;

(Obviously, the user also has the privilege to DROP that same table. But that's just the way it is in MySQL.)

---

As an alternative, to allow the user to perform only the TRUNCATE operation on that specific table, without granting the user DROP privilege on the table...

create a stored procedure that performs a TRUNCATE fee.fi; (That will probably need to be executed dynamically since it's DDL.) The procedure will need to be created with DEFINER privileges, and created by a user that has the required privileges.

Then you can grant execute on the procedure to the user:

  GRANT EXECUTE ON fee.truncate_table_fee_fi TO 'fo'@'%';

Then user 'fo'@'%' can

  CALL fee.truncate_table_fee_fi