6 Replies Latest reply: Feb 1, 2013 10:13 AM by user522961 RSS

    grant truncate

    user522961
      Hi,
      on 11G R2,
      We want to grant truncate any table to a user.
      How ?
      We should create the following procedure ?
      1. Create the procedure to truncate the table.
      
      create or replace procedure truncate_table (
      table_name varchar2,
      storage_type varchar2)
      as
      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 on <procedure_name> to <user>
      Is it for only one table ? If yes how to do that for all tables ? Or the tables of a schema ?

      Thank you.