Anush Anush - 7 months ago 6
SQL Question

Check if user has permision to truncate tables programmatically

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?

Answer

The TRUNCATE TABLE needs to two table privileges: Drop and 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%'