Why TRUNCATE statements requires ALTER privileges instead DELETE?

Question

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.

I don't think anyone will be able to answer *why* authoritatively. It's a decision that was probably made decades ago. — Damien_The_Unbeliever, Jul 27 '18 at 08:00
See https://stackoverflow.com/questions/139630/whats-the-difference-between-truncate-and-delete-in-sql — Steve Drake, Jul 27 '18 at 08:02
@DenisRubashkin mmmm..... well, what's next? So why TRUNCATE is a DDL operation? Obviously, it makes data manipulation actions? — Meow Meow, Jul 27 '18 at 08:13
How is knowing *why* going to help you solve any practical problem? It is what it is, and you cannot *change* it. — Damien_The_Unbeliever, Jul 27 '18 at 08:16
@meow meow, no, it doesn't make the actions, it deallocates table pages and doesn't worry about data on those pages. TRUNCATE is almost the same as DROP/CREATE the table. — Denis Rubashkin, Jul 27 '18 at 08:24

Steve Drake · Accepted Answer · 2018-07-27T10:35:50.320

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. (new versions allow)         |                                              |                                              |
| 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.

Remember, when the permissions were defined it would have been on very early SQL versions and changing what permissions is needed for an action might be seen as a breaking change.

It's really very very very useful information despite the fact that it doesn't answer my question enough coz internals still close, thx! — Meow Meow, Jul 27 '18 at 08:08
If someone wants I may add information about delete syntax into the table above. — Meow Meow, Jul 27 '18 at 08:11
What version of SQL Server are you using? AFAIK, `TRUNCATE` can be rolled back in all modern SQL Server versions. This may not apply to other DBMS productions. For example, one cannot perform DDL in a transaction with Oracle. — Dan Guzman, Jul 27 '18 at 09:51
The where clause is probably the most important part of this. DML is applied optionally using filters that specify the data (such as select FROM... WHERE, delete FROM... WHERE). DDL applies to data structures without the need to specify filtering of the data within the data structure itself. — Zorkolot, Jul 27 '18 at 16:23

Shakeer Mirza · Answer 2 · 2018-07-27T10:36:16.873

1

Truncate will delete the data,besides it is resetting identity property of the table. Which is dealing with structure of table. Nothing but altering table.

So we need ALTER permissions for the same.

Note: Not documented. Made this reason for keeping myself calm with the same question.

edited Jul 27 '18 at 10:36

answered Jul 27 '18 at 10:23

Shakeer Mirza

5,054
2
18
41

Be careful saying that "truncate will delete the data". Technically, truncate leaves the table structure but releases the memory/storage associated to it. This is why the TRUNCATE statement doesn't effect the transaction log. No data is physically moved during the operation. – Vinnie Jul 27 '18 at 10:42

score 0 · Answer 3 · answered Jul 27 '18 at 08:02

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.

Why TRUNCATE statements requires ALTER privileges instead DELETE?

3 Answers3