RE: Truncate another users table

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Fri, 3 Dec 2004 14:57:43 -0500
Message-ID: <AA29A27627F842409E1D18FB19CDCF275A9C28@AABO-EXCHANGE02.bos.il.pqe>

Larry,

Synonyms don't work on DDL statements, as you discovered.

However, rather than granting a user DROP ANY TABLE, which it seems to me could be easily misused or abused, why not do:

As user1:
create function truncate_the_table as
begin
  execute immediate 'truncate table this_table'; end;
/
grant execute on truncate_the_table to user2;

As user2:

exec truncate_the_table;

That may be a better solution for you....

-Mark

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Larry Hahn
> Sent: Friday, December 03, 2004 2:43 PM
> To: Oracle-L_at_freelists.org
> Subject: Truncate another users table

>=20
>=20

> List,

>=20

> I am running version 8.1.7 on Sun.

>=20

> I am trying to allow a user (A) to truncate another users
> (B) table. I have already given the DROP ANY TABLE right to
> A, and made a public synonym for the table owned by B.=20

>=20

> I can't do the truncate if I just give the table name, but
> if I put the owner first (truncate table A.<tablename>) it
> works. What have I missed?

>=20

> Because the truncate command that I ultimately need to run
> is buried in the vendors code, I am not able to create and
> execute the stored procedure that I have seen suggested
> here on the list in the past.=20

>=20

> Thanks in advance for any help.

>=20

> Larry Hahn

>=20
>=20


> 	=09

> __________________________________=20

> Do you Yahoo!?
> Send holiday email and support a worthy cause. Do good.
> http://celebrity.mail.yahoo.com
> --
> http://www.freelists.org/webpage/oracle-l
>=20

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 03 2004 - 13:54:59 CST