| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Truncate table
CANNOT TRUNCATE EVEN WITH DELETE ANY TABLE PRIVILEGES
PROBLEM DESCRIPTION:
SQL Language Reference Manual states that user with DELETE ANY TABLE system privilege can use the TRUNCATE command. Use Truncate to quickly remove all rows from a table or cluster. Removing rows with Truncate is faster than removing them with the DELETE command.
BUT you have DELETE privileges and are trying to Truncate and cannot. You can only use Truncate on tables you own.
SOLUTION DESCRIPTION:
You must now have DROP ANY TABLE privileges to use the Truncate command on any table that you don't own.
This is due to bug 207590. In which the following was decided:
"This is a major security violation for this user because the truncate bypasses the triggers. They have many triggers set up to avoid deletes that should not happen."
"Unqualified deletes are subject to enforcement of triggers and referential integrity constraints. In some ways, TRUNCATE is equivalent to DROP and RECREATE." "It might be best to say that ONLY the owner of a table can TRUNCATE it or only users with DROP ANY TABLE privilege, rather than DELETE ANY TABLE."
SOLUTION EXPLANATION:
The TRUNCATE operation is permitted by any user with DELETE privilege on a table. This may be undesirable for applications which depend on having delete triggers fire whenever rows are removed from a table by users. This functionality has been changed so that TRUNCATE now requires DROP access - by table owner or a user with DROP ANY TABLE privilege.
Bug corrected in release 7.1.3
"The Views expressed here are my own and not necessarily those of Oracle Corporation"
.
Liem T. Duong <af193_at_FreeNet.Carleton.CA> wrote in message news:81j6hr$13t_at_freenet-news.carleton.ca...
>
 |
 |