Re: Truncate problem

From: Steve Harville <jsharville_at_fleetguard.com>
Date: 1997/04/14
Message-ID: <01bc48f1$bf853e70$5625de8f@ckws57>#1/1

Oracle changed the way truncate works. Users could truncate a table if they had delete privleges, now you need to be the owner. I wrote a procedure as a work around. The procedure is owned by the table owner and is granted to the regular users. Oracle has this documented in a bulletin.

Here's the procedure:
create or replace procedure truncate_table (

                            table_name   varchar2,
                            storage_type varchar2)

as
/* This procedure accepts 2 arguments. table_name is

   the name of the table to truncate. It must be owned    by the owner of this procedure. storage_type can be    drop storage or reuse storage. Grant execute on this    procedure to whoever needs truncate privleges on this    user's tables.
   Steve Harville, Stemco Inc, 12/1/94
*/
crsor integer;
rval integer;
begin
 dbms_output.put_line('Truncating Table : '|| table_name ||

                     ' Storage : '|| storage_type);

 crsor := dbms_sql.open_cursor;
 dbms_sql.parse(crsor, 'truncate table '|| table_name ||

                ' '|| storage_type ,dbms_sql.v7);

 rval := dbms_sql.execute(crsor);
 dbms_sql.close_cursor(crsor);
end;
/
/* grant execute to any user or role that needs truncate */ grant execute on truncate_table to username;

Here's a sqlplus script to call it:
/* truncate_table.sql

   example : sqlplus / @truncate_table emp reuse storage    Steve Harville, Stemco Inc, 12/1/94
*/
execute ciim86.truncate_table('&1','&2 &3') exit

mlarson <mdlarson_at_brain.uccs.edu> wrote in article <3345718C.7B69_at_brain.uccs.edu>...
> Try giving the role "DROP ANY TABLE" privs. It seems like this worked
> for me awhile ago. Email me if it works.
>
> Matt
> mdlarson_at_mail.uccs.edu
>
> Steve Main wrote:
>
> Hey gang,
>
> I have run into a problem that is probably documented somewhere but
> I can't
> find it.
>
> I create a table "stores" as owner1
> I create a public synonym "synstores" for table stores.
>
> I then grant a role to owner2 that has, among others "delete any
> table" as
> one of it's permissions.
>
> the problem is owner2 can "see" the stores table but can't truncate
> the
> table without qualifying it.
>
> ex: as owner2
>
> desc synstores; OK
>
> truncate table synstores; Fails with table or view does not exist
>
> truncate table owner1.stores OK
>
> Does anyone know why this works this way ? Am I setting this up
> wrong ?
>
> Thanks
>
> Steve
>
>
>
>
Received on Mon Apr 14 1997 - 00:00:00 CDT