I want to deactivate truncate operation on a mysql table. So even if someone tries to execute a truncate operation that would fail. How to do that?
Depending on what version of MySQL you are running you either have to deny the DELETE privilege or the DROP privilege.
My MySQL version is less than 5.1.16. So ideally I need to deny the DELETE privilege. Doing so it won't be possible to delete records from that table anymore which is not possible for my application. I only need to stop the execution of truncate command. What is the alternate approach.
This could be done by creating a stored procedure which performs the delete operation on the table. Set the SQL SECURITY of the stored procedure to DEFINER. The definer of the stored procedure should have delete access on the table. Revoke delete privilege on the table and grant EXECUTE on the stored procedure to the user invoking the stored procedure.
This topic is now archived. It is frozen and cannot be changed in any way.