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.
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'@'%';
EDIT
NOTE: syntax above may be faulty, and may need to include the keyword PROCEDURE. whatever statement it takes to allow fo to call the procedure
GRANT EXECUTE ON PROCEDURE fee.truncate_table_fee_fi TO 'fo'@'%';
Then user 'fo'@'%' can
CALL fee.truncate_table_fee_fi