Truncating tables you don’t own

This topic came up again today – “I have delete privileges on the table, why can’t I truncate it?” I remembered getting surprised by this when Oracle changed it. In 7.0 anyone who had delete privileges could also truncate the table. When I upgraded to 7.1 my solution was to create a dynamic PL/SQL procedure owned by the table owner and grant execute to the other user. This is still the best workaround (today we would use execute immediate). I was surprised to see that an old post in comp.databases.oracle is still around – since 1994! I guess it’s true that nothing ever gets deleted on the internet 🙂

Discussion subject changed to “TRUNCATE & Version 7.1.3” by Steve Harville
Steve Harville
View profile 
 More options Dec 2 1994, 10:19 am
Oracle shipped a new plsql package with 7.1.3 that
provides a way to circumvent the new restrictions
on truncate. The dbms_sql package is installed
during a standard install. Set serveroutput on in
the glogin.sql script to see the output of this
procedure.
———————————————————–create or replace procedure truncate_table (
table_name   varchar2,
storage_type varchar2)
as
— This procedure accepts 2 arguments. table_name is
— the name of the table to truncate. It must be owned
— by the owner of this procedure. storage_type can be
— ‘drop storage’ or ‘reuse storage’. Grant execute on
— this procedure to whoever needs truncate privleges
— on this user’s tables.
— Steve Harville, Stemco Inc, 12/1/94

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;
/
— grant execute to any user or role that needs truncate
grant execute on truncate_table to username, role;

———————————————————–

— truncate_table.sql
— example : sqlplus / @truncate_table emp reuse storage
— Steve Harville, Stemco Inc, 12/1/94

execute <owner name goes here>.truncate_table(‘&1′,’&2 &3’)

———————————————————–

# truncate_table unix shell script
# example : truncate_table emp reuse storage
# Steve Harville, Stemco Inc, 12/1/94

sqlplus / @truncate_table $1 $2 $3

———————————————————–

Hope this helps.

Steve Harville

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s