Anush Anush - 6 months ago 3x
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?


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%'