Protecting a Table from Truncated or Delete [message #65645] |
Sun, 21 November 2004 20:14 |
Milind Deshpande
Messages: 93 Registered: May 2004
|
Member |
|
|
Hi All,
I have seen this script provided by Mahesh in reply to the Question (Logging DDL statements, etc)(Page No.-9) on this forum.
mag@mutation_mutation > truncate table emp;
truncate table emp
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20111: truncate is not allowed .... table is protected by DBA
ORA-06512: at line 7
My question is how we can protect a table from Truncate or Delete. Are there any grants or privileges that are to be revoked from the user so that he will not be able to perform Delete or Truncate.
Pls. calrify for the same.
Milind.
|
|
|
Re: Protecting a Table from Truncated or Delete [message #65646 is a reply to message #65645] |
Sun, 21 November 2004 23:04 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Revoking drop any table would be a good start to prevent truncating. (assuming the user is not the owner of the table)
Not granting delete on the table will prevent deletion (d'oh), again assuming the user is not the owner.
To prevent the owner of the table from deleting rows or truncating (bad idea, better not use this user in your application!) you'd have to write ddl-triggers.
(See docs)
hth
|
|
|
|