Home » SQL & PL/SQL » SQL & PL/SQL » Truncate execution (oracle 8.i)
Truncate execution [message #345698] Thu, 04 September 2008 09:26 Go to next message
callimaco0082
Messages: 18
Registered: March 2008
Junior Member
Hi all,

my problem is the following:

I have a PL/SQL procedure in a schema which does some operations and one of them fails; this one is:

execute immediate 'truncate table OTHERSCHEMA.MYTABLE'

the table I want to truncate is in another schema and i can't move the procedure to that one. How to execute the command from my actual schema?

Thanks to all will help.
Re: Truncate execution [message #345701 is a reply to message #345698] Thu, 04 September 2008 09:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need the DROP ANY TABLE privilege, and that's on you don't hand out without thinking about it quite carefully.
Re: Truncate execution [message #345741 is a reply to message #345698] Thu, 04 September 2008 12:36 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
how about have the otheruser grant delete to him on the table.
Re: Truncate execution [message #345751 is a reply to message #345741] Thu, 04 September 2008 13:18 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Hi,

As per my knowledge, a user won't be able to TRUNCATE the table in a different schema even if he has DELETE privileges assigned to him.

As JRowbottom suggested, DROP ANY TABLE privilege should be assigned to achieve this goal.

I have found the following link that addresses a security risks of DROP ANY TABLE privilege:

http://www.petefinnigan.com/weblog/archives/00000003.htm

Regards,
Jo
Re: Truncate execution [message #345753 is a reply to message #345698] Thu, 04 September 2008 13:27 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
One possible solution is a package owned by the table owner which does

EXECUTE IMMEDIATE TRUNCATE TABLE FOO

& then the other schema is granted EXECUTE for this new package.
Re: Truncate execution [message #345758 is a reply to message #345698] Thu, 04 September 2008 13:37 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
A nice package for the truncate is available at the bottom of the following link.

http://www.psoug.org/reference/truncate.html
Previous Topic: outer join (merged, was Please help with a (+) question (newbe) and how do you constrain a result se
Next Topic: Paritioning tables- Indexes
Goto Forum:
  


Current Time: Mon Dec 05 02:38:40 CST 2016

Total time taken to generate the page: 0.05890 seconds