content-class: urn:content-classes:message
MIME-Version: 1.0
Content-type: text/plain
Subject: RE: Privilege to be abl to truncate another user's table
Date: Wed, 18 Feb 2004 17:20:59 -0800
Message-ID: <B5C5F99D765BB744B54FFDF35F602621033DAEE9@irvmbxw02>
Thread-Topic: Privilege to be abl to truncate another user's table
Thread-Index: AcP0EUozbQSuSVBmQUasv6v8klryegCdTtnw
From: "Jacques Kilchoer" <Jacques.Kilchoer@quest.com>
To: <oracle-l@freelists.org>
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-To: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: oracle-l@freelists.org

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@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@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
-----------------------------------------------------------------