RE: Privileges needed for truncate

From: Bernard, Gilbert <Gilbert.Bernard_at_caissedesdepots.fr>
Date: Thu, 19 Sep 2002 08:14:02 -0800
Message-ID: <F001.004D3DE5.20020919081402@fatcity.com>

create or replace PROCEDURE
truncate_table(tbl_name in varchar2)
AUTHID DEFINER
is

total		INTEGER							;
trouve		EXCEPTION						;
cursor_name  	INTEGER 						;
ret          	INTEGER 						;
trun         	VARCHAR2(30) 	:= 'truncate table '	||
  					tbl_name			;

BEGIN
select 1
into total

    from 	  user_tables
    where     table_name 	= upper(tbl_name)

;

Raise TROUVE
;

EXCEPTION

	When TROUVE
	then
		cursor_name     := DBMS_SQL.OPEN_CURSOR


;

		DBMS_SQL.PARSE(cursor_name, trun, DBMS_SQL.native)


;

		ret             := DBMS_SQL.EXECUTE(cursor_name)


;

		DBMS_SQL.CLOSE_CURSOR(cursor_name)


;

	When others
	then
		raise_application_error(-20011,'*** Table '||tbl_name||' Not

authorized ! ***');
END
;

/

	-----Message d'origine-----
	De:	Khedr, Waleed [mailto:Waleed.Khedr_at_FMR.COM]
	Date:	jeudi 19 septembre 2002 17:24
	À:	Multiple recipients of list ORACLE-L
	Objet:	RE: Privileges needed for truncate

	Unfortunately drop any table.

	-----Original Message-----
	Sent: Thursday, September 19, 2002 10:24 AM
	To: Multiple recipients of list ORACLE-L


	Hi


	I need to create a user/role that among other stuff must be able to
	truncate a table. I can't figure out which privileges are needed
(DBA is a
	bit OTT :-))
	Try them one by one does not sound appealing at all

	TIA


	Jack

	===================================================================
	De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
	uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
	vermenigvuldiging, verspreiding en/of verstrekking van deze
informatie aan
	derden is, behoudens voorafgaande schriftelijke toestemming van
Ernst &
	Young, niet toegestaan. Ernst & Young staat niet in voor de juiste
en
	volledige overbrenging van de inhoud van een verzonden
e-mailbericht, noch
	voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen
dat een
	verzonden e-mailbericht vrij is van virussen, noch dat
e-mailberichten
	worden overgebracht zonder inbreuk of tussenkomst van onbevoegde

derden.

Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u

vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender

en het origineel en eventuele kopieën te verwijderen en te vernietigen.

Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene

voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De

algemene voorwaarden worden u op verzoek kosteloos toegezonden.

The information contained in this communication is confidential and is

intended solely for the use of the individual or entity to whom it is

addressed. You should not copy, disclose or distribute this communication

without the authority of Ernst & Young. Ernst & Young is neither liable for

the proper and complete transmission of the information contained in this

communication nor for any delay in its receipt. Ernst & Young does not

guarantee that the integrity of this communication has been maintained nor

that the communication is free of viruses, interceptions or interference.

If you are not the intended recipient of this communication please return

the communication to the sender and delete and destroy all copies.

In carrying out its engagements, Ernst & Young applies general terms and

conditions, which contain a clause that limits its liability. A copy of

	these terms and conditions is available on request free of charge.
	===================================================================





	-- 
	Please see the official ORACLE-L FAQ: http://www.orafaq.com
	-- 
	Author: Jack van Zanen
	  INET: nlzanen1_at_EY.NL

	Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
	San Diego, California        -- Mailing list and web hosting

services

	To REMOVE yourself from this mailing list, send an E-Mail message
	to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
	the message BODY, include a line containing: UNSUB ORACLE-L
	(or the name of mailing list you want to be removed from).  You may
	also send the HELP command for other information (like subscribing).
	-- 
	Please see the official ORACLE-L FAQ: http://www.orafaq.com
	-- 
	Author: Khedr, Waleed
	  INET: Waleed.Khedr_at_FMR.COM

	Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
	San Diego, California        -- Mailing list and web hosting

services

	To REMOVE yourself from this mailing list, send an E-Mail message
	to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
	the message BODY, include a line containing: UNSUB ORACLE-L
	(or the name of mailing list you want to be removed from).  You may
	also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bernard, Gilbert
  INET: Gilbert.Bernard_at_caissedesdepots.fr

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Sep 19 2002 - 11:14:02 CDT