OraFAQ Forum: SQL & PL/SQL » Truncate execution

Home » SQL & PL/SQL » SQL & PL/SQL » Truncate execution (oracle 8.i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Truncate execution [message #345698]

Thu, 04 September 2008 09:26

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.

Report message to a moderator

Re: Truncate execution [message #345701 is a reply to message #345698]

Thu, 04 September 2008 09:32

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.

Report message to a moderator

Re: Truncate execution [message #345741 is a reply to message #345698]

Thu, 04 September 2008 12:36

Bill B
Messages: 1971
Registered: December 2004

Senior Member

how about have the otheruser grant delete to him on the table.

Report message to a moderator

Re: Truncate execution [message #345751 is a reply to message #345741]

Thu, 04 September 2008 13:18

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

Report message to a moderator

Re: Truncate execution [message #345753 is a reply to message #345698]

Thu, 04 September 2008 13:27

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Truncate execution [message #345758 is a reply to message #345698]

Thu, 04 September 2008 13:37

Bill B
Messages: 1971
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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 10 01:40:46 CST 2024