Re: How to grant truncate privilege

And the best way, in my opinion. Granting 'drop any table' privilege to a user is simply asking for trouble. The solution by Eugenio is elegant and the ability to truncate is resticted to the EMPLOYEE table which can save the DBA, and the user community, a great deal of grief.

David Fitzjarrell
Oracle Certified DBA

In article <8icrem$qn$1_at_mailint03.im.hou.compaq.com>,   "Eugenio" <eugenio.spadafora_nospam_at_compaq.com> wrote:
> Another way.
> Create a procedure TRUNC_EMPLOYEE owned by XYZ
> that truncates the table EMPLOYEE (using DBMS_SQL.....)
> and grant EXECUTE privilege on TRUNC_EMPLOYEE to SCOTT.
>
> As XYZ
>
> create or replace procedure TRUNC_EMPLOYEE as
> cid INTEGER;
> begin
> cid := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cid, 'TRUNCATE TABLE EMPLOYEE', dbms_sql.v7);
> DBMS_SQL.CLOSE_CURSOR(cid);
> EXCEPTION
> WHEN OTHERS THEN
> DBMS_SQL.CLOSE_CURSOR(cid);
> RAISE;
> end TRUNC_EMPLOYEE;
>
> grant EXECUTE on TRUNC_EMPLOYEE to SCOTT;
>
> --
> be happy
>
> Eugenio
> remove _nospam from reply address
>
> Opinions are mine and do not necessarily reflect those of my company
>
> =======================================================
> sigdock_at_my-deja.com wrote in message <8icpok$a6u$1_at_nnrp1.deja.com>...
> >A very tricky solution IMHO since the user will actually be able to
> >remove the table. I would prefer a 'delete' object-privilege on the
> >required table(s). Perhaps not as nice as a truncate but a lot safer.
> >
> >my2C's
> >
> >Bert Jan Meinders
> >Oracle DBA
> >ASR-ICT
> >
> >In article <20000615165347.28093.00000610_at_ng-bk1.aol.com>,
> > l120bj_at_aol.com (L120bj) wrote:
> >> >Hi Guys,
> >> >I have the following question. Could someone please help me.
> >> >I would like to grant user: SCOTT, truncate a table 'EMPLOYEE'
> >> >which is owned by another user 'XYZ'. How can grant truncate to
 SCOTT
> >> >on XYZ.EMPLOYEE table.
> >> >
> >> >Thanks in advance.
> >> >
> >> >Kiran
> >> >
> >> >
> >> Kiran,
> >> The permission required for a user to be able to truncate another
 users table
> >> is the drop any table system privilege.
> >> HTH,
> >> Rob
> >>
> >>
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jun 16 2000 - 00:00:00 CDT