True enough, assuming all the tables that the person will be truncating are in the same schema.
> -----Original Message-----
> Jared Still
>
> You don't need to give anyone the 'drop any table' priv.
>
> Just create the procedures in the same schema as the table,
> and be selective about who to grant execute to.
>
> Jared
>
> On Fri, 2004-02-13 at 18:16, Jacques Kilchoer wrote:
> > I hate to steal someone else's thunder, but I wrote
> something a while ago that does exactly the same thing, so I
> will take the liberty of posting it here.
> > Create two procedures owned by USERA. USERA has DROP ANY
> TABLE privilege.
> > grant execute on TRUNCATE_TABLE to USERB ;
> >
> > USERB has DELETE privilege on USERC.TABLENAME ;
> >
> > then USERB can say
> > execute usera.truncate_table ('USERC', 'TABLENAME')
> >
> > create or replace procedure do_truncate (table_owner_in
> varchar2, table_name_in varchar2)
> > is
> > begin
> > execute immediate 'truncate table "' || table_owner_in || '"."'
> > || table_name_in || '"' ;
> > end do_truncate ;
> > /
> > create or replace procedure truncate_table
> > (table_owner_in varchar2, table_name_in varchar2)
> > authid current_user
> > is
> > begin
> > execute immediate 'delete from "' || table_owner_in ||
> '"."' || table_name_in
> > || '" where rownum < 2' ;
> > rollback ;
> > do_truncate (table_owner_in, table_name_in) ;
> > end truncate_table ;
> > /
> >
> > > -----Original Message-----
> > > Bobak, Mark
> > >
> > > Sounds like a nice implementation. If you have the=20
> > > code handy, I'd be interested.
> > >
> > > -----Original Message-----
> > > From: John Flack [ mailto:JohnF_at_smdi.com]
> > >
> > >
> > > Yes, you could grant "drop any table" and it would work, but
> > > I hate to =
> > > =3D
> > > grant that much power to do this. So, I create a truncate
> > > procedure in =
> > > =3D
> > > a COMMON schema that has the drop any table privilege. It
> > > does the =3D
> > > truncate with an EXECUTE IMMEDIATE, only if the user has the
> > > DELETE =3D
> > > privilege on the table. If you're interested, I'll go find
> > > the code for =
> > > =3D
> > > the procedure.
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Wed Feb 18 2004 - 19:20:59 CST
