Re: Truncate table

From: <markp7832_at_my-deja.com>
Date: Thu, 25 Nov 1999 16:44:23 GMT
Message-ID: <81jp17$rbj$1@nnrp1.deja.com>

In article <81j6o3$151_at_freenet-news.carleton.ca>, af193_at_FreeNet.Carleton.CA (Liem T. Duong) wrote:
>
> Hi everybody !
> I want to grant user the truncate table privilege, according to ORACLE
> book, to execute this command user only needs delete privilege, I
still
> have the error message insufficient privileges, any help is
appreciated.
> Please email me at
> duongli_at_csc-scc.gc.ca
> Thanks in advance
> Liem Duong
> PeopleSoft Project
> Department Correctional Service
> Canada
>

To truncate your own table no special privilege is needed. To truncate a table belonging to another user you must have the delete any privilege which is a very powerful privilege that I do not believe a user should have (nor should developers have this).

You can write a stored procedure that uses the dbms_sql package to perform this task for version before 8.1 and you can use the execute immediate command in ver 8.1 up.

Here is sample code that can perform the task. You should add a check against an authorized list of tables that this procedure could be used on.

create
procedure
TRUNCATE_TABLE (

table_name
varchar2,

storage_type
varchar2

)

--
-- procedure to allow truncation of tables owned by this procedure's
-- owner by authorized users of this procedure, ie, everyone granted
-- execute priviledge on this procedure has truncate authority on all
-- of the procedure owner's
tables.
--
-- This procedure accepts two parameters: the name of the table to be
-- truncated and instructions on how to handle storage allowcated to
-- the
table.
--

Example: execute jit.truncate_table('table_name','storage_option') ;
where storage_option = 'drop storage' or 'reuse storage' --

--
mdp
--
-- DO NOT CREATE A PUBLIC SYNONYM FOR THIS PROCEDURE!
--

crsor
integer;
rval
integer;
begin

/* dbms_output.put_line('Truncating table : '|| table_name ||

                     ' Storage : '|| storage_type ) ;

*/
crsor := dbms_sql.open_cursor
;
dbms_sql.parse(crsor,'truncate table '|| table_name ||

' '|| storage_type , dbms_sql.v7) ;
rval := dbms_sql.execute(crsor)
;
dbms_sql.close_cursor(crsor)
;
end ;
--
Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 25 1999 - 10:44:23 CST