RE: Privilege to be abl to truncate another user's table
- From: Jared Still <jkstill@xxxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 15 Feb 2004 14:18:22 -0800
Jacques,
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@xxxxxxxx]
> >
> >
> > 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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
