This discussion is archived
2 Replies Latest reply: Jul 29, 2008 5:55 AM by 641480 RSS

How to give a truncate grants to user?

639811 Newbie
Currently Being Moderated
Hi all,
how to give a truncate grants for a table to particular user?
while i was trying to find in net i got some procedures.
after creating that procedures we have to give execute procedure grants to that user?
but i came to know partially that apart from creating procedure and giving exec grants there is something which can de done thru dictionary_accesscibility_07 view or something.
i m not sure about that.
so if anybody know pls let me know.
thanks in advance..
  • 1. Re: How to give a truncate grants to user?
    Sabdar Syed Oracle ACE
    Currently Being Moderated
    >> how to give a truncate grants for a table to particular user?

    Normally a user can truncate his own schema table, but that user can't truncate another user's schema table, moreover, truncate is not a DML privilege to grant, rather it's DDL command, so, to truncate another user's schema table, that user has to have the DROP ANY TABLE (This is not at all suggestable).

    >> after creating that procedures we have to give execute procedure grants to that user?

    To grant the execute privilege on procedure,

    grant execute on <procedure_name> to <user>

    >> but i came to know partially that apart from creating procedure and giving exec grants there is something which can de done thru dictionary_accesscibility_07 view or something.

    This is wrong.

    Read the following link notes.

    http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/policies.htm#sthref667

    Regards,
    Sabdar Syed.
  • 2. Re: How to give a truncate grants to user?
    641480 Newbie
    Currently Being Moderated
    Murali,

    Drop any table privilege is used to truncate the table.( Delete any table privs is not used to truncate the table)
    Its not good way.so we go to create procedure script method.

    Eg:

    conn system/manager;
    Connected.

    grant drop any table to test_trunc;
    Grant succeeded.

    conn test_trunc/trunc
    Connected.

    truncate table test_table.test;
    Table truncated.


    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;
    /
    2. Grant execute on truncate_table to <user_name>;

    Regards
    RajaBaskar