Clash Royale CLAN TAG

Why TRUNCATE statements requires ALTER privileges instead DELETE?

May someone explains why TRUNCATE instructions requires ALTER privileges?

At the same time DELETE instructions requires DELETE privileges.

I know that TRUNCATE marks pages as deleted and DELETE marks string as deleted.

So it has different transaction log usage and speed of working but this knowledge doesn't allow understand a difference in necessary privileges.

2 Answers
2

It's hard to know why, but if you look at the differences from :

What's the difference between TRUNCATE and DELETE in SQL

+----------------------------------------+----------------------------------------------+
| Truncate | Delete |
+----------------------------------------+----------------------------------------------+
| We can't Rollback after performing | We can Rollback after delete. |
| Truncate. | |
| | |
| Example: | Example: |
| BEGIN TRAN | BEGIN TRAN |
| TRUNCATE TABLE tranTest | DELETE FROM tranTest |
| SELECT * FROM tranTest | SELECT * FROM tranTest |
| ROLLBACK | ROLLBACK |
| SELECT * FROM tranTest | SELECT * FROM tranTest |
+----------------------------------------+----------------------------------------------+
| Truncate reset identity of table. | Delete does not reset identity of table. |
+----------------------------------------+----------------------------------------------+
| It locks the entire table. | It locks the table row. |
+----------------------------------------+----------------------------------------------+
| Its DDL(Data Definition Language) | Its DML(Data Manipulation Language) |
| command. | command. |
+----------------------------------------+----------------------------------------------+
| We can't use WHERE clause with it. | We can use WHERE to filter data to delete. |
+----------------------------------------+----------------------------------------------+
| Trigger is not fired while truncate. | Trigger is fired. |
+----------------------------------------+----------------------------------------------+
| Syntax : | Syntax : |
| 1) TRUNCATE TABLE table_name | 1) DELETE FROM table_name |
| | 2) DELETE FROM table_name WHERE |
| | example_column_id IN (1,2,3) |
+----------------------------------------+----------------------------------------------+


As Truncate operates differently, it needs to have a different requirements for its permission. The fact the triggers are not fired would be a big one for me if I was on the 'why' committee and the fact that it cannot be roll backed would also be a big one.

One reason is tha t thy are not delete. Delete triggers triggers, for example. Alter does not. Alter is ametadata operation that bypasses a lot of possible security measures in the system. Paticularly triggers that may do logging or forbid or modify a delete operation.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.