Home » RDBMS Server » Security » Insufficient privilege while using truncate
Insufficient privilege while using truncate [message #47683] Wed, 08 September 2004 05:10 Go to next message
Rahul Desai
Messages: 26
Registered: March 2002
Junior Member
Hi all,

Im using execute immediate command in a procedure to truncate table which is present in another user of the same database. when i execute the procedure it is throwing our insufficient privilege, ORA-01031 error. When i give the same execute immediate in sequel anonymous block it is working fine.

 i tried by giving grant privileges to the table and also by setting role to none (even though this is not the correct method) but still im getting this error when i run the procedure.

  Since the table has 3 million records and delete commands takes a long time, im forced to use truncate command.

   How will get rid of this error.Kindly help in this

Thanks

Rahul Desai 

 
Re: Insufficient privilege while using truncate [message #47688 is a reply to message #47683] Wed, 08 September 2004 09:26 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You will need the very powerful and dangerous DROP ANY TABLE system privilege in order to truncate a table in another schema.
Re: Insufficient privilege while using truncate [message #47690 is a reply to message #47683] Wed, 08 September 2004 14:26 Go to previous messageGo to next message
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
Senior Member
> also by setting role to none

The idea of using SET ROLE NONE in SQL*Plus is to simulate the privilege set that will be available within PL/SQL. In a situation where you can do X in SQL*Plus but not from a procedure, try SET ROLE NONE and see if you can still do X in SQL*Plus. If not, then you were only able to do it before because of a role.
Re: Insufficient privilege while using truncate [message #47696 is a reply to message #47688] Wed, 08 September 2004 22:30 Go to previous message
Rahul Desai
Messages: 26
Registered: March 2002
Junior Member
Hi,

Thanks for the support. I gave set role none and ran the procedure it gave me the same error. But when i gave drop any table privilege to the user, it worked fine:). I was wondering is this the only method we have. This might be a problem at the client side. Granting and revoking of the privileges cannot be done evertime.

Thanks again
Rahul Desai
Previous Topic: Role related question.
Next Topic: Row Level Security in Oracle
Goto Forum:
  


Current Time: Mon Aug 15 07:37:16 CDT 2022