I would like to check if a user has the permission to truncate tables in MySQL DB programmatically. This check needs to be done without any side effects.
Any suggestions on how I can do this?
TRUNCATE TABLE needs to two table privileges:
DELETE which stores in
mysql.tables_priv table, so you need to check if user has the both privileges. i.e.: this query shows which user has truncate permission on which table:
select user,table from mysql.tables_priv where Table_priv like '%Delete%' and Table_priv like '%Drop%'