| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Privilege to be abl to truncate another user's table
Thanks to all for your insights! Will definetly be using procedures to grant the privs.
Viktor
One other quick quwstion. Would a user identified externally with no DBA privs be able to do full export with emp_full_database priv? Or what privs, besides DBA, would allow user to export another use's schema?
Jared Still <jkstill_at_cybcon.com> wrote:
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_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
> -----------------------------------------------------------------
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- --------------------------------- Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online ---------------------------------------------------------------- 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 Sun Feb 15 2004 - 21:50:47 CST
 |
 |