Re: How to grant truncate privilege

From: Eugenio <eugenio.spadafora_nospam_at_compaq.com>
Date: 2000/06/16
Message-ID: <8icrem$qn$1@mailint03.im.hou.compaq.com>#1/1

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.

Received on Fri Jun 16 2000 - 00:00:00 CDT