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.